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

class Affiliator_m extends MY_Model
{

	protected $_table_name = 'affiliators';
	protected $_primary_key = 'id';
	protected $_order_by = 'id';

	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('affiliators')
			->count_all_results();
	}

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

	// Count rejected affiliates
	public function count_rejected_affiliate()
	{
		return $this->db->where('status', 'rejected')
			->from('affiliators')
			->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('affiliators a', 'o.redeemed_voucher_code = a.referral_code OR o.referral = a.referral_code OR o.source = a.referral_code', 'inner')
			->where('a.referral_code IS NOT NULL');

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

	public function get_paid_commission($affiliator_id = null)
	{
		$this->db->select('IFNULL(SUM(amount), 0) AS total_paid_commission');
		$this->db->from('affiliator_withdrawals');
		$this->db->where('status', 'completed');

		if (!empty($affiliator_id)) {
			$this->db->where('affiliator_id', $affiliator_id);
		}

		$query = $this->db->get();
		$result = $query->row();

		return (float) ($result->total_paid_commission ?? 0);
	}

	public function fetch_all_affiliates()
	{
		$this->db->select('
        a.*, 
        c.name AS customer_name,
        c.phone AS customer_phone,
        c.email AS customer_email
    ');
		$this->db->from('affiliators a');
		$this->db->join('customers c', 'a.customer_id = c.id_customers', 'left');
		$query = $this->db->get();
		return $query->result();
	}

	public function get_best_affiliate()
	{
		$this->db->select('c.name AS customer_name, a.referral_code')
			->from('orders o')
			->join('affiliators a', 'o.redeemed_voucher_code = a.referral_code OR o.referral = a.referral_code OR o.source = a.referral_code', 'inner')
			->join('customers c', 'a.customer_id = c.id_customers', 'left')
			->where('a.referral_code IS NOT NULL')
			->group_by('a.referral_code')
			->order_by('COUNT(o.id_orders)', 'DESC')
			->limit(1);

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



	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 information and address
		$this->db->select('
			a.*,
			c.id_customers,
			c.name as customer_name,
			c.email as customer_email,
			c.phone as customer_phone,
			ca.address as shipping_address,
			ca.province as shipping_province,
			ca.district as shipping_district,
			ca.subdistrict as shipping_subdistrict,
			ca.postal_code as shipping_postcode
		');
		$this->db->from('affiliators a');
		$this->db->join('customers c', 'a.customer_id = c.id_customers', 'left');
		$this->db->join('customer_addresses ca', 'c.id_customers = ca.customer_id AND ca.is_default = 1', 'left');
		$this->db->where('a.id', $affiliate_id);
		$query = $this->db->get();

		if ($query->num_rows() == 0) {
			return null;
		}

		$affiliate_data = $query->row();

		// Get product links with commission data
		$this->db->select('
			al.*,
			al.unique_clicks as unique_clicks,
			al.total_conversions as total_conversions,
			(al.unique_clicks * 50) as click_commissions,
			COALESCE(SUM(ac.commission_amount), 0) as order_commissions
		');
		$this->db->from('affiliator_links al');
		$this->db->join('affiliator_commissions ac', 'al.id = ac.link_id AND ac.status != "cancelled"', 'left');
		$this->db->where('al.affiliator_id', $affiliate_id);
		$this->db->where('al.status', 'active');
		$this->db->group_by('al.id');
		$this->db->order_by('al.created_at', 'DESC');
		$links_query = $this->db->get();

		$affiliate_data->links = $links_query->result();

		// Format affiliate data for display
		$affiliate_data->formatted_created = date('d M Y H:i', strtotime($affiliate_data->created_at));

		// Format status
		$status_config = [
			'active' => [
				'text' => 'Active',
				'class' => 'bg-green-100 text-green-700 px-2 py-1 rounded'
			],
			'pending' => [
				'text' => 'Pending',
				'class' => 'bg-yellow-100 text-yellow-700 px-2 py-1 rounded'
			],
			'suspended' => [
				'text' => 'Suspended',
				'class' => 'bg-red-100 text-red-700 px-2 py-1 rounded'
			],
			'rejected' => [
				'text' => 'Rejected',
				'class' => 'bg-red-100 text-red-700 px-2 py-1 rounded'
			]
		];

		$status = $status_config[$affiliate_data->status] ?? [
			'text' => ucfirst($affiliate_data->status),
			'class' => 'bg-gray-100 text-gray-700 px-2 py-1 rounded'
		];
		$affiliate_data->status_text = $status['text'];
		$affiliate_data->status_class = $status['class'];

		// Format account info
		$affiliate_data->account_info = $affiliate_data->bank_account_name . ' - ' .
			$affiliate_data->bank_account_number . ' (' .
			$affiliate_data->bank_name . ')';

		// Format shipping address
		$address_parts = array_filter([
			$affiliate_data->shipping_address,
			$affiliate_data->shipping_subdistrict,
			$affiliate_data->shipping_district,
			$affiliate_data->shipping_province,
			$affiliate_data->shipping_postcode
		]);
		$affiliate_data->full_address = implode(', ', $address_parts);

		// Initialize totals
		$total_unique_clicks = 0;
		$total_orders = 0;
		$total_order_comissions = 0;
		$total_click_commissions = 0;

		// Format each product link
		foreach ($affiliate_data->links as $key => $link) {
			// Format currency
			$affiliate_data->links[$key]->formatted_click_commissions = 'Rp ' . number_format($link->click_commissions, 0, ',', '.');
			$affiliate_data->links[$key]->formatted_order_commissions = 'Rp ' . number_format($link->order_commissions, 0, ',', '.');
			$affiliate_data->links[$key]->created_formatted = date('d M Y H:i', strtotime($link->created_at));

			// Calculate conversion rate
			$affiliate_data->links[$key]->conversion_rate = ($link->total_conversions > 0)
				? round(($link->total_conversions / $link->unique_clicks) * 100, 2)
				: 0;

			// Get shipping status from product assignment
			if ($link->link_type == 'product_specific') {
				$this->db->select('status');
				$this->db->from('affiliator_product_assignments');
				$this->db->where('affiliator_id', $affiliate_id);
				$this->db->where('product_id', $link->product_id);
				$this->db->order_by('created_at', 'DESC');
				$this->db->limit(1);
				$assignment = $this->db->get();

				if ($assignment->num_rows() > 0) {
					$assignment_data = $assignment->row();

					$shipping_status_config = [
						'delivered' => [
							'text' => 'Sudah Dikirim',
							'class' => 'bg-green-100 text-green-700 px-2 py-1 rounded'
						],
						'shipped' => [
							'text' => 'Sedang Dikirim',
							'class' => 'bg-blue-100 text-blue-700 px-2 py-1 rounded'
						],
						'approved' => [
							'text' => 'Disetujui',
							'class' => 'bg-teal-100 text-teal-700 px-2 py-1 rounded'
						],
						'proposed' => [
							'text' => 'Diajukan',
							'class' => 'bg-yellow-100 text-yellow-700 px-2 py-1 rounded'
						],
						'returned' => [
							'text' => 'Dikembalikan',
							'class' => 'bg-red-100 text-red-700 px-2 py-1 rounded'
						]
					];

					$shipping_status = $shipping_status_config[$assignment_data->status] ?? [
						'text' => ucfirst($assignment_data->status),
						'class' => 'bg-gray-100 text-gray-700 px-2 py-1 rounded'
					];

					$affiliate_data->links[$key]->shipping_status = $shipping_status['text'];
					$affiliate_data->links[$key]->shipping_status_class = $shipping_status['class'];
				} else {
					$affiliate_data->links[$key]->shipping_status = 'Belum Diajukan';
					$affiliate_data->links[$key]->shipping_status_class = 'bg-gray-100 text-gray-700 px-2 py-1 rounded';
				}
			} else {
				$affiliate_data->links[$key]->shipping_status = 'N/A';
				$affiliate_data->links[$key]->shipping_status_class = 'bg-gray-100 text-gray-700 px-2 py-1 rounded';
			}

			// Accumulate totals
			$total_unique_clicks += $link->unique_clicks;
			$total_orders += $link->total_conversions;
			$total_order_comissions += $link->order_commissions;
			$total_click_commissions += $link->click_commissions;
		}

		// Calculate total commissions (order commission + open commission)
		$total_commissions = $total_order_comissions + $total_click_commissions;

		// Get total withdrawn amount
		$this->db->select('COALESCE(SUM(amount), 0) as total_withdrawn');
		$this->db->from('affiliator_withdrawals');
		$this->db->where('affiliator_id', $affiliate_id);
		$this->db->where_in('status', ['completed', 'processing']);
		$withdrawal_query = $this->db->get();
		$total_withdrawn = $withdrawal_query->row()->total_withdrawn;

		// Calculate available balance
		$available_balance = $total_commissions - $total_withdrawn;

		// Set summary statistics
		$affiliate_data->stats = (object) [
			'total_links' => count($affiliate_data->links),
			'total_unique_clicks' => $total_unique_clicks,
			'total_orders' => $total_orders,
			'total_click_commissions' => $total_click_commissions,
			'total_order_comissions' => $total_order_comissions,
			'total_commissions' => $total_commissions,
			'total_withdrawn' => $total_withdrawn,
			'available_balance' => $available_balance,
			'formatted_click_commissions' => 'Rp ' . number_format($total_click_commissions, 0, ',', '.'),
			'formatted_order_commissions' => 'Rp ' . number_format($total_order_comissions, 0, ',', '.'),
			'formatted_total_commissions' => 'Rp ' . number_format($total_commissions, 0, ',', '.'),
			'formatted_withdrawn_commissions' => 'Rp ' . number_format($total_withdrawn, 0, ',', '.'),
			'formatted_current_balance' => 'Rp ' . number_format($available_balance, 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_by_id($id)
	{
		$this->db->select('*');
		$this->db->from('affiliators');
		$this->db->where('id', $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('affiliators');
		$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;
		}
	}
}