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

class Affiliator_m extends MY_Model
{

	protected $_table_name = 'affiliator_register';
	protected $_primary_key = 'id_daftar';
	protected $_order_by = 'id_daftar';

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

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

	// Count active (approved) affiliates
	public function count_approved_affiliate()
	{
		return $this->db->where('status', 'approve')
			->from('affiliator_register')
			->count_all_results();
	}

	// Count pending affiliates
	public function count_pending_affiliate()
	{
		return $this->db->where('status', 'waiting')
			->from('affiliator_register')
			->count_all_results();
	}

	// Count rejected affiliates
	public function count_rejected_affiliate()
	{
		return $this->db->where('status', 'rejected')
			->from('affiliator_register')
			->count_all_results();
	}

	// Count transactions using affiliate referral codes
	public function count_affiliate_transactions()
	{
		$this->db->select('COUNT(o.id_orders) AS total_transactions')
			->from('orders o')
			->join('affiliator_register a', 'o.redeemed_voucher_code = a.referral OR o.referral = a.referral OR o.source = a.referral', 'inner')
			->where('a.referral IS NOT NULL');

		$query = $this->db->get();
		return $query->row()->total_transactions ?? 0;
	}

	public function get_paid_commission()
	{
		$this->db->select('SUM(commission) as total_paid_commission');
		$this->db->from('affiliator_commision');
		$query = $this->db->get();

		$result = $query->row();
		return $result->total_paid_commission ?? 0; // Jika NULL, kembalikan 0
	}
	public function fetch_all_affiliates()
	{
		$this->db->select('*');
		$this->db->from('affiliator_register');
		$query = $this->db->get();
		return $query->result();
	}
	public function get_best_affiliate()
	{
		$this->db->select('a.referral, a.nama')
			->from('orders o')
			->join('affiliator_register a', 'o.redeemed_voucher_code = a.referral OR o.referral = a.referral OR o.source = a.referral', 'inner')
			->where('a.referral IS NOT NULL')
			->group_by('a.referral')
			->order_by('COUNT(o.id_orders)', 'DESC')
			->limit(1);

		$query = $this->db->get();
		return $query->row()->nama ?? null;
	}


	/**
	 * Fetch detailed affiliate data by ID including shipping address, product links, and product shipping status
	 * 
	 * @param int $affiliate_id The ID of the affiliate (id_daftar from affiliator_register)
	 * @return object|null Affiliate data with shipping information and product links as object
	 */
	public function fetch_affiliate_data($affiliate_id)
	{
		// Validate input
		$affiliate_id = (int) $affiliate_id;
		if ($affiliate_id <= 0) {
			return null;
		}
		// Get affiliate data with customer shipping information
		$this->db->select('
			ar.*,
			c.shipping_address,
			c.shipping_province,
			c.shipping_district,
			c.shipping_subdistrict,
			c.shipping_postcode
		');
		$this->db->from('affiliator_register ar');
		$this->db->join('customers c', 'ar.id_customer = c.id_customers', 'left');
		$this->db->where('ar.id_daftar', $affiliate_id);
		$query = $this->db->get();
		if ($query->num_rows() == 0) {
			return null;
		}
		$affiliate_data = $query->row(); // Changed from row_array() to row()
		// Get product links for this affiliate
		$this->db->select('
			affiliator_link.*, 
			(SELECT COUNT(DISTINCT ip_address) 
			FROM link_tracks 
			WHERE link_tracks.link_url = affiliator_link.link) as open
		');
		$this->db->from('affiliator_link');
		$this->db->where('customer_id', $affiliate_data->id_customer);
		$this->db->order_by('created_date', 'DESC');
		$links_query = $this->db->get();
		$affiliate_data->product_links = $links_query->result(); // Changed from result_array() to result()

		// Format data for display
		$affiliate_data->formatted_komisi = 'Rp ' . number_format($affiliate_data->komisi, 0, ',', '.');
		$affiliate_data->formatted_komisi_order = 'Rp ' . number_format($affiliate_data->komisi, 0, ',', '.');
		$affiliate_data->formatted_created = date('d M Y H:i', strtotime($affiliate_data->created));

		// Format status with text and class for display
		switch ($affiliate_data->status) {
			case 'approve':
				$affiliate_data->status_text = 'Approved';
				$affiliate_data->status_class = 'bg-green-100 text-green-700 px-2 py-1 rounded';
				break;
			case 'waiting':
				$affiliate_data->status_text = 'Waiting';
				$affiliate_data->status_class = 'bg-yellow-100 text-yellow-700 px-2 py-1 rounded';
				break;
			case 'rejected':
				$affiliate_data->status_text = 'Rejected';
				$affiliate_data->status_class = 'bg-red-100 text-red-700 px-2 py-1 rounded';
				break;
			default:
				$affiliate_data->status_text = ucfirst($affiliate_data->status);
				$affiliate_data->status_class = 'bg-gray-100 text-gray-700 px-2 py-1 rounded';
		}

		// Format account info
		$affiliate_data->account_info = $affiliate_data->account_name . ' - ' .
			$affiliate_data->account_number . ' (' .
			$affiliate_data->account_type . ')';

		// Format shipping address
		$affiliate_data->full_address = $affiliate_data->shipping_address . ', ' .
			$affiliate_data->shipping_subdistrict . ', ' .
			$affiliate_data->shipping_district . ', ' .
			$affiliate_data->shipping_province .
			($affiliate_data->shipping_postcode ? ' ' . $affiliate_data->shipping_postcode : '');

		// Add statistics for each product link
		foreach ($affiliate_data->product_links as $key => $link) {
			// Hitung komisi dari open link (open * 50)
			$komisi_open = $link->open * 50;
			// Tambahkan komisi open ke tiap link
			$affiliate_data->product_links[$key]->komisi_open = $komisi_open;
			// Format nilai untuk ditampilkan
			$affiliate_data->product_links[$key]->formatted_komisi = 'Rp ' . number_format($komisi_open, 0, ',', '.');
			$affiliate_data->product_links[$key]->formatted_komisi_order = 'Rp ' . number_format($link->komisi_order, 0, ',', '.');
			$affiliate_data->product_links[$key]->created_formatted = date('d M Y H:i', strtotime($link->created_date));
			// Calculate conversion rate
			$affiliate_data->product_links[$key]->conversion_rate = ($link->open > 0) ?
				round(($link->order / $link->open) * 100, 2) : 0;

			// Cek status pengiriman produk dari tabel orders
			$this->db->select('o.payment_status');
			$this->db->from('orders_detail od');
			$this->db->join('orders o', 'od.orders_id = o.id_orders', 'inner');
			$this->db->where('o.customer_id', $affiliate_data->id_customer);
			$this->db->where('od.product_id', $link->product_id);
			$order_query = $this->db->get();

			if ($order_query->num_rows() > 0) {
				$order_data = $order_query->row();
				switch ($order_data->payment_status) {
					case 5:
						$affiliate_data->product_links[$key]->shipping_status = 'Sudah Dikirim';
						$affiliate_data->product_links[$key]->shipping_status_class = 'bg-green-100 text-green-700 px-2 py-1 rounded';
						break;
					case 4:
						$affiliate_data->product_links[$key]->shipping_status = 'Sedang Diproses';
						$affiliate_data->product_links[$key]->shipping_status_class = 'bg-blue-100 text-blue-700 px-2 py-1 rounded';
						break;
					default:
						$affiliate_data->product_links[$key]->shipping_status = 'Belum Dikirim';
						$affiliate_data->product_links[$key]->shipping_status_class = 'bg-gray-100 text-gray-700 px-2 py-1 rounded';
				}
			} else {
				$affiliate_data->product_links[$key]->shipping_status = 'Belum Order';
				$affiliate_data->product_links[$key]->shipping_status_class = 'bg-red-100 text-red-700 px-2 py-1 rounded';
			}
		}

		// Get summary statistics
		$total_opens = 0;
		$total_orders = 0;
		$total_komisi_order = 0;
		$total_komisi_open = 0;
		foreach ($affiliate_data->product_links as $link) {
			$total_opens += $link->open;
			$total_orders += $link->order;
			$total_komisi_order += $link->komisi_order;
			$total_komisi_open += $link->komisi_open; // Jumlahkan semua komisi open
		}

		// Menghitung total commissions yang benar
		// Total commissions = komisi dari affiliator_register + total komisi open link
		$total_commissions = $affiliate_data->komisi + $total_komisi_open;
		$affiliate_data->stats = (object) [
			'total_links' => count($affiliate_data->product_links),
			'total_opens' => $total_opens,
			'total_orders' => $total_orders,
			'total_commissions' => $total_commissions,
			'formatted_commissions' => 'Rp ' . number_format($total_commissions, 0, ',', '.')
		];

		return $affiliate_data;
	}

	// Old
	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 get_all_orders_retailer($limit, $start)
	{
		$this->db->select('*');
		$this->db->from('orders');
		$this->db->join('customers', 'customers.id_customers = orders.customer_id');
		$this->db->where('customers.reseller_id IS NOT NULL');
		$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');
		$this->db->join('customers', 'customers.id_customers = orders.customer_id');

		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');
		}
		$this->db->where('customers.reseller_id IS NULL');

		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.product_id', $dataenter);
			$this->db->where('orders_detail.product_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 filterdataretailer($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');
		$this->db->join('customers', 'customers.id_customers = orders.customer_id');
		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');
		}
		$this->db->where('customers.reseller_id IS NOT NULL');
		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>';
				break;
			case 8:
				return '<span style="color:blue; font-weight:bold;">Selesai</span>';
				break;
			case 9:
				return '<span style="color:red; font-weight:bold;">Komplain</span>';
				break;
			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;
		}
	}

	public function updateongkir($id, $data)
	{
		$this->db->where('id_orders', $id);
		$this->db->update('orders', $data);
	}


	//pagination included lol
	function report_get_all_orders()
	{
		$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 affiliator register with its customer details 
	function get_affiliator_register($id)
	{
		$this->db->select('*');
		$this->db->from('affiliator_register');
		$this->db->where('id_daftar', $id);
		$query = $this->db->get();
		return $query->row();
	}

	function get_sent_product()
	{
		$this->db->select('*');
		$this->db->from('affiliator_product');
		$this->db->order_by('id', 'desc');
		$query = $this->db->get();
		return $query->result();
	}

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

	function get_affiliator_customer($id)
	{
		$this->db->select('*');
		$this->db->from('affiliator_register');
		$this->db->where('id_customer', $id);
		$query = $this->db->get();
		return $query->row();
	}

	function get_unique_link($id)
	{
		$this->db->select('*');
		$this->db->from('affiliator_link');
		$this->db->where('customer_id', $id);
		$query = $this->db->get();
		return $query->result();
	}

	public function get_active_product()
	{
		$this->db->select('*');
		$this->db->from('products');
		$this->db->where('product_status', 1);
		$this->db->order_by('id_products', 'asc');
		$query = $this->db->get();
		return $query->result();
	}

	function get_affiliator_mission($id)
	{
		$this->db->select('*');
		$this->db->from('mission');
		$this->db->where('id_mission', $id);
		$query = $this->db->get();
		return $query->row();
	}

	function get_affiliator_mission_submit($id)
	{
		$this->db->select('*');
		$this->db->from('mission_progress_submit');
		$this->db->where('id_submit', $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 lol
	function find_order_by_date($date_start, $date_end, $payment)
	{
		$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);
		if ($payment != 'all') $this->db->where('payment_type', $payment);
		$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;
		}
	}
}