<?php namespace App\Models;

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

class gudangarea extends Sximo  {
	
	protected $table = 'm_area_kebun';
	protected $primaryKey = 'id';

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

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

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

	public function cekKode()
    {
        return \DB::table('m_area_kebun')
            ->select(\DB::raw('count(*)+1 as hit'))
            ->where('unit', substr(\Session::get('fid'), 0, 6))
            ->first();
    }

	public function saldo_pupuk($plant, $tgl=null)
    {
        $unit = $plant;
		$filter_tgl = "";
		if($tgl != null){
			$filter_tgl = " AND DATE(c.tgl_permintaan) <= DATE('".$tgl."') ";
		}

        return \DB::table('m_pupuk_herbisida as a')
            ->select('a.nama', 'a.kode_material', 'a.id', 'a.jenis', 'a.satuan', 
                     \DB::raw('IFNULL(SUM(b.jumlah), 0) AS saldo'),
                     \DB::raw('(SELECT IFNULL(SUM(d.jumlah), 0) FROM t_permintaan_pupuk_herbisida d
                               WHERE a.id = d.barang
                               AND d.status = 3 AND d.unit like "'.$unit.'%" AND DATE(d.tgl_permintaan) = DATE(NOW())) AS keluar_hi'),
                     \DB::raw('(SELECT IFNULL(SUM(c.jumlah), 0) FROM t_permintaan_pupuk_herbisida c
                               WHERE a.id = c.barang
                               AND c.status = 3 AND c.unit like "'.$unit.'%" '.$filter_tgl.') AS keluar_sd'))
            ->leftJoin('t_penerimaan_pupuk_herbisida as b', function($join) use ($unit, $tgl) {
				$join->on('a.id', '=', 'b.nama_barang')
					 ->where('b.unit', 'like', $unit . '%')
					 ->where('b.status', '=', 2);
					 if($tgl != null){
						$join->whereRaw('DATE(b.tgl_pengiriman) <= DATE("'.$tgl.'")');
					 }	
            })
            ->groupBy('a.id')
			
			->orderBy('a.jenis', 'desc')
			->orderBy('a.id', 'asc')
			

            ->get();
    }

    public function ppk($plant, $tgl)
    {
        $unit = $plant;
		$tgl = $tgl;
        return \DB::select('SELECT
				`a`.`id` AS `barang_id`,
				`d`.`kode_area`,
				`a`.`nama`,
				IFNULL( SUM( f.jumlah ), 0 ) + IFNULL((
					SELECT
						SUM( u.jumlah ) 
					FROM
						t_gudang_pupuk_transfer u 
					WHERE
						u.barang = a.id 
						AND u.dari_gudang = d.kode_area 
						AND u.STATUS = 2 
						AND u.unit like "'.$unit.'%" 
						),
					0 
					) - IFNULL((
					SELECT
						SUM( t.jumlah ) 
					FROM
						t_distribusi_pupuk_herbisida t 
					WHERE
						t.barang = a.id 
						AND t.area = d.kode_area 
						AND t.STATUS = 2 
						AND t.unit like "'.$unit.'%" 
						AND t.tanggal <= "'.$tgl.'"
					),
					0 
				) AS jml 
			FROM
				`vw_kebun_area` AS `d`
				INNER JOIN `m_pupuk_herbisida` AS `a` ON 0 = 0
				LEFT JOIN `t_gudang_pupuk_area` AS `f` ON `d`.`kode_area` = `f`.`area` 
				AND `a`.`id` = `f`.`barang` 
			WHERE
				`d`.`unit` like "'.$unit.'%" 
			GROUP BY
				a.id, d.kode_area
			ORDER BY
				`d`.`kode_area`,a.jenis desc, a.id ASC');
    }

	public function ppkmap($plant)
    {
		$unit = substr($plant, 0, 6);
		$area = $plant;

        return \DB::select('SELECT
				`a`.`id` AS `barang_id`,
				`d`.`kode_area`,
				`a`.`nama`,
				IFNULL( SUM( f.jumlah ), 0 ) + IFNULL((
					SELECT
						SUM( u.jumlah ) 
					FROM
						t_gudang_pupuk_transfer u 
					WHERE
						u.barang = a.id 
						AND u.dari_gudang = d.kode_area 
						AND u.STATUS = 2 
						AND u.unit like "'.$unit.'%" 
						),
					0 
					) - IFNULL((
					SELECT
						SUM( t.jumlah ) 
					FROM
						t_distribusi_pupuk_herbisida t 
					WHERE
						t.barang = a.id 
						AND t.area = d.kode_area 
						AND t.STATUS = 2 
						AND t.unit like "'.$unit.'%" 
					),
					0 
				) AS stok 
			FROM
				`vw_kebun_area` AS `d`
				INNER JOIN `m_pupuk_herbisida` AS `a` ON 0 = 0
				LEFT JOIN `t_gudang_pupuk_area` AS `f` ON `d`.`kode_area` = `f`.`area` 
				AND `a`.`id` = `f`.`barang` 
			WHERE
				`d`.`unit` like "'.$unit.'%" 
				and d.kode_area = "'.$area.'"
			GROUP BY
				a.id, d.kode_area
			ORDER BY
				`d`.`kode_area`,a.jenis desc, a.id ASC');
    }

    public function total_ppk($plant, $tgl=null)
    {
		$filter_tgl = "";
		$filter_tgl2 = "";
		if($tgl != null){
			$filter_tgl = " AND DATE(u.tgl_transfer) <= DATE('".$tgl."') ";
			$filter_tgl2 = " AND DATE(t.tanggal) <= DATE('".$tgl."') ";
		}	
			
        return \DB::table('m_pupuk_herbisida as a')
            ->select('a.nama','a.id',
                     \DB::raw('IFNULL(SUM(f.jumlah), 0) +
                              IFNULL((SELECT SUM(u.jumlah) FROM t_gudang_pupuk_transfer u WHERE u.barang = a.id AND u.status = 2 AND u.unit like "'.$plant.'%" '.$filter_tgl.'), 0) -
                              IFNULL((SELECT SUM(t.jumlah) FROM t_distribusi_pupuk_herbisida t WHERE t.barang = a.id AND t.status = 2 AND t.unit like "'.$plant.'%" '.$filter_tgl2.'), 0) AS jml,
							  IFNULL((SELECT SUM(t.jumlah) FROM t_distribusi_pupuk_herbisida t WHERE t.barang = a.id AND t.status = 2 AND t.unit like "'.$plant.'%" '.$filter_tgl2.'), 0) AS keluar'))
			->leftJoin('t_gudang_pupuk_area as f', function($join) use ($plant, $tgl) {
				$join->on('a.id', '=', 'f.barang')
						->where('f.unit', 'like', $plant . '%'); 
						if($tgl != null){
							$join->whereRaw('DATE(f.tgl_terima) <= DATE("'.$tgl.'")');
						}
			})
            ->groupBy('a.id')
			->orderBy('a.jenis', 'desc')
			->orderBy('a.id', 'asc')
            ->get();
    }

    public function area($plant)
    {
        $unit = $plant;
        return \DB::table('vw_kebun_area as a')
            ->select('a.kode_area', 'a.nama_area')
            ->where('a.unit', 'like', $unit . '%')
            ->orderBy('a.kode_area')
            ->get();
    }

	public function saldo_pupuk_daerah($tgl1, $tgl2, $plant)
    {
		$unit = $plant;
		return \DB::select('SELECT
			`a`.`id` AS `barang_id`,
			`d`.`kode_area`,
			`a`.`nama`,
			IFNULL((
				SELECT
					SUM( t.jumlah ) 
				FROM
					t_distribusi_pupuk_herbisida t 
				WHERE
					t.barang = a.id 
					AND t.area = d.kode_area 
					AND t.STATUS = 2 
					AND t.unit like "'.$unit.'%" 
					AND t.tanggal BETWEEN "'.$tgl1.'" 
					AND "'.$tgl2.'" 
					),
				0 
			) AS jml 
		FROM
			`vw_kebun_area` AS `d`
			INNER JOIN `m_pupuk_herbisida` AS `a` ON 0 = 0
			LEFT JOIN `t_gudang_pupuk_area` AS `f` ON `d`.`kode_area` = `f`.`area` 
			AND `a`.`id` = `f`.`barang` 
		WHERE
			`d`.`unit` like "'.$unit.'%"
		GROUP BY
			a.id, d.kode_area
		ORDER BY
			`d`.`kode_area`,a.jenis desc, a.id ASC');
        // return \DB::select('SELECT
		// 					a.id AS barang_id,
		// 					d.kode_area,
		// 					a.nama,
		// 					COALESCE(f.jumlah,0)
		// 					+ COALESCE(trf.jumlah,0)
		// 					- COALESCE(dist.jumlah,0) AS jml
		// 				FROM vw_kebun_area d
		// 				CROSS JOIN m_pupuk_herbisida a
		// 				LEFT JOIN
		// 				(
		// 					SELECT
		// 						area,
		// 						barang,
		// 						SUM(jumlah) jumlah
		// 					FROM t_gudang_pupuk_area
		// 					GROUP BY area,barang
		// 				) f
		// 				ON f.area=d.kode_area
		// 				AND f.barang=a.id
		// 				LEFT JOIN
		// 				(
		// 					SELECT
		// 						dari_gudang,
		// 						barang,
		// 						SUM(jumlah) jumlah
		// 					FROM t_gudang_pupuk_transfer
		// 					WHERE status=2
		// 					AND unit LIKE "'.$unit.'%"
		// 					GROUP BY dari_gudang,barang
		// 				) trf
		// 				ON trf.dari_gudang=d.kode_area
		// 				AND trf.barang=a.id
		// 				LEFT JOIN
		// 				(
		// 					SELECT
		// 						area,
		// 						barang,
		// 						SUM(jumlah) jumlah
		// 					FROM t_distribusi_pupuk_herbisida
		// 					WHERE status=2
		// 					AND unit LIKE "'.$unit.'%" AND tanggal BETWEEN "'.$tgl1.'" 
		//  			AND "'.$tgl2.'" 
		// 					GROUP BY area,barang
		// 				) dist
		// 				ON dist.area=d.kode_area
		// 				AND dist.barang=a.id
		// 				WHERE d.unit LIKE "'.$unit.'%"
		// 				ORDER BY
		// 					d.kode_area,
		// 					a.jenis DESC,
		// 					a.id');
    }

	public function saldo_pupuk_daerah_total($tgl1, $tgl2, $plant)
    {
		$unit = $plant;
        return \DB::select('
				SELECT
					a.id,
					a.nama,
					SUM( t.jumlah ) as total
				FROM
					t_distribusi_pupuk_herbisida t 
					inner join m_pupuk_herbisida a on t.barang = a.id 
				WHERE
					t.STATUS = 2 
					AND t.unit like "'.$unit.'%" 
					AND t.tanggal BETWEEN "'.$tgl1.'" 
					AND "'.$tgl2.'" 
					group by t.barang
					order by a.jenis desc, a.id ASC
					');
    }
}