<?php
class Fgout_mdl extends Trxout_mdl {

    function __construct() {

        parent::__construct();
		$this->pk = 'matout_id';
    }

    function getHdr($page,$rows,$key='',$KdJnsDok=''){
      $offset = ($page-1)*$rows;
      $result = array();
      $cond_KdJnsDok = "";
      if ($KdJnsDok != ""){
        $cond_KdJnsDok = " AND a.KdJnsDok = '$KdJnsDok' ";
      }
      
      $q = "SELECT distinct a.*,a.".$this->pk." AS id,
          DATE_FORMAT(matout_date,'%d/%m/%Y') AS Fmatout_date,
          DATE_FORMAT(TgDaf,'%d/%m/%Y') AS FTgDaf,
          DATE_FORMAT(TgAju,'%d/%m/%Y') AS FTgAju,
          DATE_FORMAT(TgTTD,'%d/%m/%Y') AS FTgTTD,
          DATE_FORMAT(etd,'%d/%m/%Y') AS Fetd,
          DATE_FORMAT(eta,'%d/%m/%Y') AS Feta,
          c.matout_name,d.NmPrshn,f.UrJnsDok,
          g.NmPrshn AS company, a.port_odispacth,a.port_odischarge,
          a.final_odestination,a.feder_name,a.insured,a.shipping,a.vpd,
          FORMAT(cif,2) AS cif,
          FORMAT(tot_amount,2) AS tot_amount
          FROM ".$this->tbl." a
          LEFT JOIN ".$this->tbl3." c ON c.".$this->pk3."=a.".$this->pk3."
          LEFT JOIN ".$this->tbl4." d ON d.".$this->pk4."=a.cust
          LEFT JOIN ".$this->tbl6." f ON f.".$this->pk6."=a.".$this->pk6."
          LEFT JOIN ".$this->tbl4." g ON g.".$this->pk4."=a.kd_cmpy
          WHERE mat_type = '".$this->mat_type."' and a.matout_type != '9'
          $cond_KdJnsDok";
          
      $q .= " AND (a.matout_no LIKE '%$key%' OR d.NmPrshn LIKE '%$key%' 
          OR a.NoDaf LIKE '%$key%' OR a.ref_no LIKE '%$key%')
          ORDER BY matout_id desc";
  
        //   var_dump($q);
        //   die();
      $rstot=$this->db->query($q)->result();
      //echo $q;
      $q .= " LIMIT $offset,$rows";
      $rs=$this->db->query($q)->result();
      
      $result["total"] = count($rstot);
      $result["rows"] = $rs;
      
      return $result;
    }

    function getHdrDet($page,$rows,$key='',$dtdari='',$dtsampai=''){
      $offset = ($page-1)*$rows;
      $result = array();
      
      $q="SELECT a.*,a.".$this->pk." AS id,
        DATE_FORMAT(matout_date,'%d/%m/%Y') AS Fmatout_date,
        DATE_FORMAT(TgDaf,'%d/%m/%Y') AS FTgDaf,
        b.*,
        FORMAT(qty, 2) AS Fqty,
        FORMAT(price, 4) AS Fprice,
        FORMAT(ng_qty,2) AS Ngqty,
        FORMAT(qty*price, 2) AS amount,
        c.matout_name,d.NmPrshn,f.UrJnsDok,
        e.NmBarang,e.Sat,c.matout_name,
        g.NmPrshn AS company
        FROM ".$this->tbl." a
        LEFT JOIN ".$this->tbl2." b ON b.".$this->pk."=a.".$this->pk."
        LEFT JOIN ".$this->tbl3." c ON c.".$this->pk3."=a.matout_type
        LEFT JOIN ".$this->tbl4." d ON d.".$this->pk4."=a.cust
        LEFT JOIN ".$this->tbl5." e ON e.".$this->pk5."= b.mat_id 
        LEFT JOIN ".$this->tbl6." f ON f.".$this->pk6."= a.KdJnsDok
          LEFT JOIN ".$this->tbl4." g ON g.".$this->pk4."=a.kd_cmpy
        WHERE mat_type = '".$this->mat_type."' and a.matout_type != '9' ";
        
      // if ($this->session->userdata('kd_cmpy') != "0001"){
      // 	$q .= " AND kd_cmpy = '".$this->session->userdata('kd_cmpy')."'";	
      // }
          
      if ($key != ""){  
        $q .= " AND (a.matout_no LIKE '%$key%' OR d.NmPrshn LIKE '%$key%' 
        OR KdBarang LIKE '%$key%' OR c.matout_name LIKE '%$key%' )";
      }	
          
      if($dtdari != '' && $dtsampai != ''):
        $q .= " AND matout_date BETWEEN '".$this->dmys2ymd($dtdari)."' AND '".$this->dmys2ymd($dtsampai)."'";
      endif;
      
      $q .= " ORDER BY a.matout_date ASC";
      $rstot=$this->db->query($q)->result();
      //echo $q;
      $q .= " LIMIT $offset,$rows";
      $rs=$this->db->query($q)->result();
      
      $result["total"] = count($rstot);
      $result["rows"] = $rs;
      
      return $result;
    }

    function getDetAll($key=''){
		
		$q="SELECT a.*,matout_id AS id,
			FORMAT(qty, 2) AS Fqty,
			FORMAT(price, 4) AS Fprice,
			FORMAT(qty*price, 2) AS amount,
			b.NmBarang,b.Sat,
            bom.JmlMat
		  	FROM ".$this->tbl2." a 
            LEFT JOIN ".$this->tbl5." b ON KdBarang = mat_id 
            LEFT JOIN (
                SELECT fg_id, COUNT(bh.bom_id) AS JmlMat 
                FROM mat_bomhdr_sub bh
                LEFT JOIN mat_bomdet_sub bd ON bd.bom_id = bh.bom_id
                GROUP BY bh.bom_id
            ) bom ON bom.fg_id = a.mat_id
		  	WHERE ".$this->pk."='$key' 
		  	ORDER BY child_no ASC";
		$rs=$this->db->query($q)->result();
		
		return $rs;
    }
    
  function getBomAll($key = '', $so_id = ''){
		
		$q="SELECT 
                bd.*,
                b.NmBarang,b.Sat,
                vai.NoAju, vai.NoDaf,
                DATE_FORMAT(vai.TgAju,'%Y%m%d') AS FTgAju,
                DATE_FORMAT(vai.TgDaf,'%d/%m/%Y') AS FTgDaf
            FROM mat_bomhdr_sub bh
            LEFT JOIN mat_bomdet_sub bd ON bd.bom_id = bh.bom_id
            LEFT JOIN ".$this->tbl5." b ON b.KdBarang = bd.mat_id 
            LEFT JOIN v_aju_incoming vai ON bd.mat_id = vai.mat_id AND vai.so_id = '$so_id'
            WHERE bh.fg_id = '$key' 
        ORDER BY child_no ASC";
    // die($q);
		$rs=$this->db->query($q)->result();
		
		return $rs;
	}

}