<?php  if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Order_m extends MY_Model {

	protected $_table_name = 'orders';
	protected $_primary_key = 'id_orders';
	protected $_order_by = 'id_orders';

	public $add_order_rules = array(
		array(
			'field'   => 'customer_id',
			'label'   => 'Customer',
			'rules'   => 'trim|required'
		),  
		  array(
             'field'   => 'marketplace_id',
             'label'   => 'Marketplace',
             'rules'   => 'trim|required'
          ),
    );

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

	public function add_order($data){
		$this->db->insert('orders', $data);	
		return $this->db->insert_id();	
	}

	//function count all record for orders
	public function record_count() {
        return $this->db->get('orders')->num_rows();
    }

    //pagination included
	function get_all_orders($limit, $start) {
		$this->db->select('*');
		$this->db->from('orders');
		// $this->db->join('customers', 'customers.id_customers = orders.customer_id');
		$this->db->order_by('id_orders', 'desc');
		$this->db->limit($limit, $start);
		$query = $this->db->get();		
		return $query->result();
	}

	function warestats($wareid){
		$this->db->select('warehouse_id')->from('orders_detail')->where('orders_id', $wareid);
        $warehouse_ids = $this->db->get()->result();

        $warehouse_id_array = array();

       foreach ($warehouse_ids as $warehouse_id) {
            if(!in_array($warehouse_id->warehouse_id, $warehouse_id_array)) {
                 $warehouse_id_array[] = $warehouse_id->warehouse_id;
            } 
        }

        if(count($warehouse_id_array) > 1){
        	return 'Iya';
        } else {
        	return 'Tidak';
        }
	}

	function filterdata($search, $filterkey, $dataenter, $iduser, $length, $start){
		$this->db->select('warehouse_id,role')->from('users')->where('id', $iduser);
        $user = $this->db->get()->row();

		$this->db->select('*');
        $this->db->from('orders');
        if(!empty($search)){ $this->db->like("orders.id_orders",$search); }

        if($user->role == 'apoteker') {
        	$this->db->join('orders_detail', 'orders_detail.orders_id = orders.id_orders');
        	$this->db->where('orders_detail.warehouse_id', $user->warehouse_id);
        	$this->db->group_by('orders.id_orders');
        }

        if($filterkey == 'voucher'){
        	if($dataenter == '1'){
        		$this->db->where('redeemed_voucher_code !=',null);
        	} else {
        		$this->db->where('redeemed_voucher_code',null);
        	}
        } else if($filterkey == 'flashsale'){
        	if($user->role != 'apoteker') { $this->db->join('orders_detail', 'orders_detail.orders_id = orders.id_orders'); }
        	$this->db->where('orders_detail.is_flashsale',$dataenter);
        } else if($filterkey == 'sale'){
        	if($user->role != 'apoteker') { $this->db->join('orders_detail', 'orders_detail.orders_id = orders.id_orders'); }
        	$this->db->where('orders_detail.is_sale',$dataenter);
        } else if($filterkey == 'date'){
        	$ex = explode('_',$dataenter);
        	$this->db->where('orders.order_date >=', $ex[0]);
        	$this->db->where('orders.order_date <=', $ex[1]);
        } else if($filterkey == 'totalorder'){
        	$ex = explode('_',$dataenter);
        	$this->db->where('((orders.total_amount+IFNULL(ABS(orders.sisa_kembali),0))-IFNULL(orders.redeemed_voucher_value,0))+(IFNULL(orders.shipping_fee,0)-IFNULL(orders.free_shipping_fee,0)) >=', $ex[0]);
        	$this->db->where('((orders.total_amount+IFNULL(ABS(orders.sisa_kembali),0))-IFNULL(orders.redeemed_voucher_value,0))+(IFNULL(orders.shipping_fee,0)-IFNULL(orders.free_shipping_fee,0)) <=', $ex[1]);
        } else if($filterkey == 'product'){
        	if($user->role != 'apoteker') { $this->db->join('orders_detail', 'orders_detail.orders_id = orders.id_orders'); }
        	$this->db->where('orders_detail.item_id',$dataenter);
                	$this->db->where('orders_detail.item_id',$dataenter);
         } else if($filterkey == 'sync'){
        	$this->db->where('orders.status_jurnalid','sync');	
         } else if($filterkey == 'open'){
        	$this->db->where('orders.status_jurnalid','open');	
        	$this->db->where('orders.payment_status','5');		
        } else {
        	if(!empty($filterkey)){ $this->db->like($filterkey, $dataenter); }
        }

        if($length != '' AND $start != ''){ $this->db->limit($length,$start); }
        $this->db->order_by('orders.id_orders', 'desc');
        return $this->db->get();
	}

	function filterexcel($filterkey, $dataenter, $iduser){
		$this->db->select('warehouse_id,role')->from('users')->where('id', $iduser);
        $user = $this->db->get()->row();

		$this->db->select('*');
        $this->db->from('orders_detail');

        if($user->role == 'apoteker') {
        	$this->db->join('orders', 'orders_detail.orders_id = orders.id_orders');
        	$this->db->where('orders_detail.warehouse_id', $user->warehouse_id);
        	$this->db->group_by('orders.id_orders');
        } else {
        	$this->db->join('orders', 'orders_detail.orders_id = orders.id_orders');
        }

        if($filterkey != ''){
	        if($filterkey == 'voucher'){
	        	if($dataenter == '1'){
	        		$this->db->where('orders.redeemed_voucher_code !=',null);
	        	} else {
	        		$this->db->where('orders.redeemed_voucher_code',null);
	        	}
	        } else if($filterkey == 'flashsale'){
	        	$this->db->where('orders_detail.is_flashsale',$dataenter);
	        } else if($filterkey == 'sale'){
	        	$this->db->where('orders_detail.is_sale',$dataenter);
	        } else if($filterkey == 'date'){
	        	$ex = explode('_',$dataenter);
	        	$this->db->where('orders.order_date >=', $ex[0]);
	        	$this->db->where('orders.order_date <=', $ex[1]);
	        } else if($filterkey == 'totalorder'){
	        	$ex = explode('_',$dataenter);
	        	$this->db->where('((orders.total_amount+IFNULL(ABS(orders.sisa_kembali),0))-IFNULL(orders.redeemed_voucher_value,0))+(IFNULL(orders.shipping_fee,0)-IFNULL(orders.free_shipping_fee,0)) >=', $ex[0]);
	        	$this->db->where('((orders.total_amount+IFNULL(ABS(orders.sisa_kembali),0))-IFNULL(orders.redeemed_voucher_value,0))+(IFNULL(orders.shipping_fee,0)-IFNULL(orders.free_shipping_fee,0)) <=', $ex[1]);
	        } else if($filterkey == 'product'){
	        	$this->db->where('orders_detail.item_id',$dataenter);
	        } else {
	        	if(!empty($filterkey)){ $this->db->like($filterkey, $dataenter); }
	        }
	    }

        $this->db->order_by('orders_detail.orders_id', 'desc');
        return $this->db->get()->result();
	}

	function paystats($statspay){
		switch ($statspay) {
	        case 0:
	            return '<span style="color:black; font-weight:bold;">Pending</span>';
	            break;
	        case 1:
	            return '<span style="color:brown; font-weight:bold;">Belum bayar</span>';
	            break;
	        case 2:
	            return '<span style="color:red; font-weight:bold;">Batal</span>';
	            break;
	        case 3:
	            return '<span style="color:green; font-weight:bold;">Sudah bayar</span>';
	            break;
	        case 4:
	            return '<span style="color:blue; font-weight:bold;">Proses</span>';
	            break;
	        case 5:
	            return '<span style="color:blue; font-weight:bold;">Terkirim</span>';
	        case 6:
	            return '<span style="color:brown; font-weight:bold;">Dibayar sebagian (indent)</span>';    
	            break;
	    }
	}

	function paymettype($paytype, $payconf, $paystatsmsg){
		if($paytype == 'bank transfer BCA'){
            if($payconf == 1 ) {
                return 'Konfirmasi bayar: Sudah';
            } else {
                return 'Konfirmasi bayar: Belum';
            }
        } elseif($paytype == 'bank transfer MANDIRI'){
            if($payconf == 1 ) {
                return 'Konfirmasi bayar: Sudah';
            } else {
                return 'Konfirmasi bayar: Belum';
            }
        } elseif($paytype == 'midtrans'){
            return $paystatsmsg;
        }   
	}
	
	//pagination included
	function report_get_all_orders($limit, $start) {
		$this->db->select('*');
		$this->db->from('orders');
		$this->db->join('customers', 'customers.id_customers = orders.customer_id');
		$this->db->group_start();
		$this->db->where('payment_status', 3);
		$this->db->or_where('payment_status', 4);
		$this->db->or_where('payment_status', 5);
		$this->db->group_end();
		$this->db->order_by('id_orders', 'desc');
		$this->db->limit($limit, $start);
		$query = $this->db->get();		
		return $query->result();
	}

	function sales_report($mulai, $getData, $title, $startDate, $endDate) {
		$page = isset($getData)? (int)$getData:1;
		$halaman = ($page>1) ? ($page * $mulai) - $mulai : 0;

		$this->db->select('*');
		$this->db->from('orders');
		$this->db->join('orders_detail', 'orders.id_orders = orders_detail.orders_id AND orders_detail.status = 2');
		if(!empty($title)){
			$this->db->like('item_name', $title);
		}
		
		if(!empty($startDate) && !empty($endDate)){
            $this->db->where("orders.order_date >=",$startDate);
            $this->db->where('orders.order_date <=', $endDate);
		} else {
			if(!empty($startDate)){
	            $this->db->where("orders.order_date >=",$startDate);
			} else if(!empty($endDate)){
	            $this->db->where('orders.order_date <=', $endDate);
			}
		}
		$this->db->where('orders.customer_id <>', '6292');
		$this->db->group_by('orders_detail.item_id,orders_detail.item_price');
		$this->db->order_by('orders_detail.item_name', 'desc');
		$this->db->limit($mulai, $halaman);
		$query = $this->db->get();
		return $query->result();
	}

	function getallqty($prodid, $title, $startDate, $endDate, $price){
		$this->db->select('sum(orders_detail.quantity) as qty');
		//$this->db->select('*');
		$this->db->from('orders');
		$this->db->join('orders_detail', 'orders.id_orders = orders_detail.orders_id AND orders_detail.status = 2');
		if(!empty($title)){
			$this->db->like('item_name', $title);
		}
		
		if(!empty($startDate) && !empty($endDate)){
            $this->db->where("orders.order_date >=",$startDate);
            $this->db->where('orders.order_date <=', $endDate);
		} else {
			if(!empty($startDate)){
	            $this->db->where("orders.order_date >=",$startDate);
			} else if(!empty($endDate)){
	            $this->db->where('orders.order_date <=', $endDate);
			}
		}
		if(!empty($price)){
			$this->db->where("orders_detail.item_price",$price);
		}
		$this->db->where('orders.customer_id <>', '6292');
		$this->db->where("orders_detail.item_id",$prodid);
		$query = $this->db->get();
		return $query->row();
	}

	function sales_report_all($title, $startDate, $endDate){
		$this->db->select('*');
		$this->db->from('orders_detail');
		$this->db->where('orders_detail.status', 2);
		if(!empty($title)){
			$this->db->like('item_name', $title);
		}
		
		if(!empty($startDate) && !empty($endDate)){
			$this->db->join('orders', 'orders.id_orders = orders_detail.orders_id');
            $this->db->where("orders.order_date >=",$startDate);
            $this->db->where('orders.order_date <=', $endDate);
		} else {
			$this->db->join('orders', 'orders.id_orders = orders_detail.orders_id');
			if(!empty($startDate)){
	            $this->db->where("orders.order_date >=",$startDate);
			} else if(!empty($endDate)){
	            $this->db->where('orders.order_date <=', $endDate);
			}
		}
		$query = $this->db->get();
		return $query->result();
	}

	function tpage_salesreport($title, $startDate, $endDate){
		$this->db->select('*');
		$this->db->from('orders');
		$this->db->join('orders_detail', 'orders.id_orders = orders_detail.orders_id AND orders_detail.status = 2');
		if(!empty($title)){
			$this->db->like('item_name', $title);
		}
		
		if(!empty($startDate) && !empty($endDate)){
            $this->db->where("orders.order_date >=",$startDate);
            $this->db->where('orders.order_date <=', $endDate);
		} else {
			if(!empty($startDate)){
	            $this->db->where("orders.order_date >=",$startDate);
			} else if(!empty($endDate)){
	            $this->db->where('orders.order_date <=', $endDate);
			}
		}
		$this->db->where('orders.customer_id <>', '6292');
		$this->db->group_by('orders_detail.item_id');
		//$this->db->group_by('orders_detail.item_id,orders_detail.item_price');
		$this->db->order_by('orders_detail.item_name', 'desc');
		$query = $this->db->get();
		return $query->result();
	}

	function excel_export() {
		$this->db->select('
			orders_id,
			item_name,
			item_price,
			quantity,
			subtotal,
			warehouse_id,
			chosen_shipping_id,
			shipping_fee,
			is_backorder,
			status,
			no_resi
		');
		$this->db->from('orders_detail');
		$this->db->order_by('orders_id', 'desc');
		$query = $this->db->get();		
		return $query->result();
	}

	//get specific order with its customer details 
	function get_order($id) {
		$this->db->select('*');
		$this->db->from('orders'); 
		$this->db->where('id_orders', $id);
		$query = $this->db->get();		
		return $query->row();
	}
	
	//update credit card payment status
	function update_payment_status($id, $data) {
		$this->db->where('id_orders', $id);
		$this->db->update('orders', $data);	
	}

	function get_order_history($id_customer) {
		$this->db->select('*');
		$this->db->from('orders'); 
		$this->db->where('customer_id', $id_customer);
		$this->db->order_by('id_orders', 'DESC');
		$query = $this->db->get();		
		return $query->result();
	}

	//function count record based on chosen date range
	function record_count_search_date($date_start, $date_end) {

		$this->db->select('*');
		$this->db->from('orders');
		$this->db->group_start();
		$this->db->where('payment_status', 3);
		$this->db->or_where('payment_status', 4);
		$this->db->or_where('payment_status', 5);
		$this->db->group_end();

		$this->db->where('order_date >=', $date_start);
		$this->db->where('order_date <=', $date_end);
		$query = $this->db->get();		
		return $query->num_rows();
	}

	//function find store by filtering between 2 dates
	function find_order_by_date($limit, $start, $date_start, $date_end) {
		$this->db->select('*');
		$this->db->from('orders');
		$this->db->group_start();
		$this->db->where('payment_status', 3);
		$this->db->or_where('payment_status', 4);
		$this->db->or_where('payment_status', 5);
		$this->db->group_end();
		
		$this->db->where('order_date >=', $date_start);
		$this->db->where('order_date <=', $date_end);
		$this->db->order_by('order_date', 'DESC');
		$this->db->limit($limit, $start);
		$query = $this->db->get();		
		return $query->result();
	}

	function get_notification_order($warehouse_id) {
		$ordernotif=0;
		$data = array(3,1,4); //Paid, Unpaid, Process

		if($warehouse_id == 0){
			$this->db->select('*');
			$this->db->from('orders');
			$this->db->where_in('payment_status',$data);
			$ordernotif = $this->db->get()->num_rows();

		}else{
			$this->db->select('distinct id_orders',false);
			$this->db->from('orders');
			$this->db->join('orders_detail', 'orders.id_orders = orders_detail.orders_id');
			$this->db->where_in('payment_status',$data);
			$this->db->where('warehouse_id',$warehouse_id);
			$ordernotif = $this->db->get()->num_rows();
		}

		return $ordernotif;

	}

	function get_customer_by_order($order_id){
		$this->db->select('*');
		$this->db->from('orders');
		$this->db->where('id_orders',$order_id);

		$id_customer = $this->db->get()->row()->customer_id;
		return $id_customer;			
		
	}

	//pagination included
	function report_get_all_orders_count() {
		$this->db->select('*');
		$this->db->from('orders');
		$this->db->join('customers', 'customers.id_customers = orders.customer_id');
		$this->db->group_start();
		$this->db->where('payment_status', 3);
		$this->db->or_where('payment_status', 4);
		$this->db->or_where('payment_status', 5);
		$this->db->group_end();
		$this->db->order_by('id_orders', 'desc');
		$query = $this->db->get();		
		return $query->num_rows();
	}
	
	function cek_order($id,$paymentstatus){
		$this->db->select('*');
		$this->db->from('orders');
		$this->db->where('id_orders',$id);

		$currpayment = $this->db->get()->row()->payment_status;
		//return $currpayment." - ".$paymentstatus;
		
		if($paymentstatus == 2){
			return true;
		}else if($paymentstatus > $currpayment){
			return true;
		}else{
			return false;
		}
	}	

	function cek_orderdetail($id,$status){
		$this->db->select('*');
		$this->db->from('orders_detail');
		$this->db->where('id_orders_detail',$id);

		$currpayment = $this->db->get()->row()->status;
		//return $currpayment." - ".$paymentstatus;

		if($status == $currpayment){
			return true;
		}else{
			return false;
		}
	}
	
	function cek_stok($idproduct, $qty, $warehouse){
		$wh_type = $this->db->select("*")->from("warehouse")->where('id',$warehouse)->get()->row()->warehouse_type;
		$count = $this->db->select("*")->from("stock")->where("warehouse_id",$warehouse)->where("id_product",$idproduct)->get()->num_rows();

		if($count > 0){
			if($wh_type == "virtual"){
				$current_stock = (int) $this->db->select("IFNULL(stock_virtual,0) stock_virtual")->from("stock")->where("warehouse_id",$warehouse)->where("id_product",$idproduct)->get()->row()->stock_virtual;
			}else{
				$current_stock = (int) $this->db->select("IFNULL(stock,0) stock")->from("stock")->where("warehouse_id",$warehouse)->where("id_product",$idproduct)->get()->row()->stock;
			}

			if($qty > $current_stock){
				return false;
			}else{
				return true;
			}
		}else{
			return false;
		}

	}

}