BE_IBL/application/controllers/mockup/billpaymenttitip/Bill.php

<?php
class Bill extends MY_Controller
{
   var $db_onedev;
   public function index()
   {
      echo "Bill API";
   }
   public function __construct()
   {
      parent::__construct();
      $this->db_onedev = $this->load->database("onedev", true);
   }
   
   public function add_notes($orderid){
	   $sql = "	SELECT F_BillF_BillTitipLunasID as note_order_id,
				F_BillID as note_id, 
				F_BillTitipLunasDate as note_date,
				F_BillNumber as note_number,
				GROUP_CONCAT(DISTINCT M_PaymentTypeName separator ' , ') as paymenttypes_name,
				SUM(F_BillDetailAmount) as note_amount,
				M_UserUsername as note_user,
				F_BillDetailIsActive as note_active,
				'xxx' as tests,
				'N' as show_detail
				FROM f_bill
				JOIN f_bill_detail ON F_BillDetailF_BillID = F_BillID 
				JOIN m_paymenttype ON F_BillM_PaymentTypeID = M_PaymentTypeID
				LEFT JOIN m_user ON F_BillUserID = M_UserID
				WHERE
				F_BillF_BillTitipLunasID = {$orderid} 
				GROUP BY F_BillID";
		$query = $this->db_onedev->query($sql);
		if ($query) {
			$rows = $query->result_array();
			if($rows){
				foreach($rows as $k => $v){
					$rows[$k]['tests'] = $this->add_tests($v['note_id']);
				}
			}
			return $rows;
			
		} else {
			$this->sys_error_db("get notes", $this->db_onedev);
			exit;
		} 
   }
   public function add_tagihans($orderid){
	$sql = "	SELECT 		 F_BillTitipLunasID as tagihan_id, 
			 F_BillTitipLunasDetailT_OrderHeaderLabNumber as tagihan_number,
			 F_BillTitipLunasDetailFullPatientName as pasien,
			 F_BillDetailTotal as tagihan_total,
			 F_BillDetailUnpaid as tagihan_tagihan,
			 F_BillTitipLunasDetailAmount as tagihan_bayar,
			 F_BillTitipLunasDetailIsActive as tagihan_active,
			 'Y' as show_detail,
			 'true' as chex,
			 F_BillTitipLunasDetailID,
			 F_BillTitipLunasDetailT_OrderHeaderID
			 FROM f_bill_titip_lunas
			 JOIN f_bill_titip_lunas_detail ON F_BillTitipLunasDetailF_BillTitipLunasID = F_BillTitipLunasID AND F_BillTitipLunasDetailIsActive = 'Y'
			 JOIN f_bill_detail ON F_BillDetailID = F_BillTitipLunasDetailF_BillDetailID
			 WHERE
			 F_BillTitipLunasID = {$orderid} 
			 GROUP BY F_BillTitipLunasDetailID";
	 $query = $this->db_onedev->query($sql);
	 if ($query) {
		 $rows = $query->result_array();
		 return $rows;
		 
	 } else {
		 $this->sys_error_db("get notes", $this->db_onedev);
		 exit;
	 } 
}
   public function add_tests($orderid){
	$sql = "	SELECT F_BillF_BillTitipLunasID as note_order_id,
			 F_BillID as note_id, 
			 F_BillTitipLunasDate as note_date,
			 F_BillNumber as note_number,
			 GROUP_CONCAT(M_PaymentTypeName separator ' , ') as paymenttypes_name,
			 SUM(F_BillDetailAmount) as note_amount,
			 M_UserUsername as note_user,
			 F_BillDetailIsActive as note_active,
			 T_OrderHeaderLabNumber,
			 F_BillTitipLunasDetailTotal,
			 F_BillDetailAmount
			 FROM f_bill
			 JOIN f_bill_detail ON F_BillDetailF_BillID = F_BillID 
			 LEFT JOIN f_bill_titip_lunas ON F_BillDetailF_BillTitipLunasID = F_BillTitipLunasID
			 LEFT JOIN f_bill_titip_lunas_detail ON F_BillDetailF_BillTitipLunasDetailID = F_BillTitipLunasDetailID
			 LEFT JOIN t_orderheader ON F_BillTitipLunasDetailT_OrderHeaderID = T_OrderHeaderID
			 JOIN m_paymenttype ON F_BillM_PaymentTypeID = M_PaymentTypeID
			 LEFT JOIN m_user ON F_BillDetailUserID = M_UserID
			 WHERE
			 F_BillID = {$orderid} 
			 GROUP BY F_BillDetailID";
	 $query = $this->db_onedev->query($sql);
	 if ($query) {
		 $rows = $query->result_array();
		 if($rows){
		 }
		 return $rows;
		 
	 } else {
		 $this->sys_error_db("get notes", $this->db_onedev);
		 exit;
	 } 
}
   public function search()
   {
	    //# cek token valid
        if (! $this->isLogin) {
        $this->sys_error("Invalid Token");
        exit;
        }
      $prm = $this->sys_input;
	  $company = $prm["company"];
      $search = $prm["search"];
      $status = $prm["status"];
	  $number_limit = 10;
	  $number_offset = ($prm['current_page'] - 1) * $number_limit ;

	  $where = "(F_BillTitipLunasIsActive = 'Y' AND F_BillTitipLunasIsConfirm = 'N' AND (F_BillNo LIKE '%{$search}%') AND M_CompanyName LIKE '%{$company}%')";
		
	  
	    

      $sql = "	SELECT count(*) as total
				FROM f_bill_titip_lunas
				LEFT JOIN f_bill ON F_BillID = F_BillTitipLunasF_BillID AND F_BillIsActive = 'Y'
				LEFT JOIN m_company ON F_BillM_CompanyID = M_CompanyID
				LEFT JOIN m_mou ON F_BillM_MouID = M_MouID
				WHERE
				 $where";
//echo $sql;
		$query = $this->db_onedev->query($sql, $sql_param);

		
		$tot_count = 0;
		$tot_page = 0;
		if ($query) {
			$tot_count = $query->result_array()[0]["total"];
			$tot_page = ceil($tot_count/$number_limit);
		} else {
			$this->sys_error_db("f_bill_titip_lunas count", $this->db_onedev);
			exit;
		}    
	  
	  
		$sql = 	"SELECT f_bill_titip_lunas.*, 
				M_CompanyName,
				M_MouName,
				IFNULL(F_BillTotal,0) as totalbill,
				IFNULL(F_BillTotal - F_BillUnpaid,0) as paid,
				IFNULL(F_BillUnpaid,0) as unpaid,
				'N' as flaglunas,
				SUM(F_BillTitipLunasAmount) as F_BillTitipLunasAmount,			
				DATE_FORMAT(F_BillTitipLunasDate,'%d-%m-%Y') as F_BillTitipLunasDate,
				'' as notes,
				'' as tagihans,
				F_BillNo,
				F_BillTotal
				FROM f_bill_titip_lunas
				LEFT JOIN f_bill ON F_BillID = F_BillTitipLunasF_BillID AND F_BillIsActive = 'Y'
				LEFT JOIN m_company ON F_BillM_CompanyID = M_CompanyID
				LEFT JOIN m_mou ON F_BillM_MouID = M_MouID
				WHERE
				$where
				GROUP BY F_BillTitipLunasID
				ORDER BY F_BillTitipLunasID ASC
				limit $number_limit offset $number_offset";
		//echo $sql;
		$query = $this->db_onedev->query($sql, $sql_param);
		$rows = $query->result_array();
		if($rows){
			foreach($rows as $k => $v){
				$rows[$k]['tagihans'] = $this->add_tagihans($v['F_BillTitipLunasID']);
			}
		}
	  

		$result = array("total" => $tot_page, "records" => $rows, "sql"=> $this->db_onedev->last_query());
		$this->sys_ok($result);
		exit;
   }


}