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

class Gala_read_model extends CI_Model
{
	protected $gala_db;
	protected $allow_zero_location_override;
	protected $hide_store_account_payments_in_reports;
	protected $hide_layaways_sales_in_reports;

	public function __construct()
	{
		parent::__construct();
		$this->gala_db = $this->load->database('gala_read', TRUE);
		$this->allow_zero_location_override = $this->read_boolean_app_config('allow_zero_price_location_price_overrides');
		$this->hide_store_account_payments_in_reports = $this->read_boolean_app_config('hide_store_account_payments_in_reports');
		$this->hide_layaways_sales_in_reports = $this->read_boolean_app_config('hide_layaways_sales_in_reports');
	}

	public function authenticate_employee($username, $password)
	{
		$hashed_password = md5((string)$password);

		$this->gala_db->select('employees.person_id, employees.username, employees.employee_number, people.first_name, people.last_name, people.email');
		$this->gala_db->from('employees');
		$this->gala_db->join('people', 'people.person_id = employees.person_id');
		$this->gala_db->where('employees.deleted', 0);
		$this->gala_db->where('employees.inactive', 0);
		$this->gala_db->where('employees.password', $hashed_password);
		$this->gala_db->group_start();
		$this->gala_db->where('employees.username', (string)$username);
		$this->gala_db->or_where('employees.employee_number', (string)$username);
		$this->gala_db->group_end();

		$row = $this->gala_db->get()->row_array();
		if (!$row)
		{
			return null;
		}

		$row['full_name'] = trim((isset($row['first_name']) ? $row['first_name'] : '').' '.(isset($row['last_name']) ? $row['last_name'] : ''));
		return $row;
	}

	public function get_current_exchange_rate()
	{
		if (!$this->gala_db->table_exists('currency_exchange_rates'))
		{
			return null;
		}

		$this->gala_db->from('currency_exchange_rates');
		$this->gala_db->order_by('id', 'desc');
		return $this->gala_db->get()->row_array();
	}

	public function get_company_name_for_visor($location_id = 1)
	{
		if (!$this->gala_db->table_exists('locations'))
		{
			return 'Nombre Compania';
		}

		$location_id = (int)$location_id;
		$this->gala_db->select('name, company');
		$this->gala_db->from('locations');
		$this->gala_db->where('location_id', $location_id);
		$row = $this->gala_db->get()->row_array();

		if (!$row)
		{
			return 'Nombre Compania';
		}

		$company = isset($row['company']) ? trim((string)$row['company']) : '';
		if ($company !== '')
		{
			return $company;
		}

		$name = isset($row['name']) ? trim((string)$row['name']) : '';
		return $name !== '' ? $name : 'Nombre Compania';
	}

	public function get_employees_for_visor()
	{
		if (!$this->gala_db->table_exists('employees') || !$this->gala_db->table_exists('people'))
		{
			return array();
		}

		$this->gala_db->select('employees.person_id, employees.username, employees.inactive, employees.deleted, people.first_name, people.last_name', FALSE);
		$this->gala_db->from('employees');
		$this->gala_db->join('people', 'people.person_id = employees.person_id');
		$this->gala_db->where('employees.deleted', 0);
		$this->gala_db->order_by('people.first_name', 'ASC');
		$this->gala_db->order_by('people.last_name', 'ASC');
		$rows = $this->gala_db->get()->result_array();

		foreach ($rows as &$row)
		{
			$row['full_name'] = trim((isset($row['first_name']) ? $row['first_name'] : '').' '.(isset($row['last_name']) ? $row['last_name'] : ''));
			$row['is_active_in_gala'] = isset($row['inactive']) ? ((int)$row['inactive'] === 0) : TRUE;
		}
		unset($row);

		return $rows;
	}

	public function get_goal_categories()
	{
		if (!$this->gala_db->table_exists('categories'))
		{
			return array();
		}

		$this->gala_db->select('id, name, parent_id');
		$this->gala_db->from('categories');
		$this->gala_db->where('deleted', 0);
		$this->gala_db->order_by('name', 'ASC');
		return $this->gala_db->get()->result_array();
	}

	public function get_goal_items()
	{
		if (!$this->gala_db->table_exists('items'))
		{
			return array();
		}

		$this->gala_db->select('item_id, name, item_number');
		$this->gala_db->from('items');
		$this->gala_db->where('deleted', 0);
		$this->gala_db->where('system_item', 0);
		$this->gala_db->order_by('name', 'ASC');
		return $this->gala_db->get()->result_array();
	}

	public function get_goal_manufacturers()
	{
		if (!$this->gala_db->table_exists('manufacturers'))
		{
			return array();
		}

		$this->gala_db->select('id, name');
		$this->gala_db->from('manufacturers');
		$this->gala_db->where('deleted', 0);
		$this->gala_db->order_by('name', 'ASC');
		return $this->gala_db->get()->result_array();
	}

	public function get_goal_suppliers()
	{
		if (!$this->gala_db->table_exists('suppliers'))
		{
			return array();
		}

		// TS/PHP POS relates items.supplier_id against suppliers.person_id.
		// Expose that identifier here so saved metas keep the same key used by reports.
		$this->gala_db->select('person_id AS id, company_name AS name');
		$this->gala_db->from('suppliers');
		$this->gala_db->where('deleted', 0);
		$this->gala_db->order_by('company_name', 'ASC');
		return $this->gala_db->get()->result_array();
	}

	public function get_goal_metric_for_employee($employee_id, $scope_type, $scope_id, $metric_type, $date_start, $date_end)
	{
		$employee_id = (int)$employee_id;
		if ($employee_id <= 0)
		{
			return 0.0;
		}

		if ($this->should_use_report_style_sales_metric($scope_type, $metric_type))
		{
			$totals = $this->get_report_style_sales_totals_by_employee($date_start, $date_end);
			return isset($totals[$employee_id]) ? (float)$totals[$employee_id] : 0.0;
		}

		$value = $this->get_goal_metric_from_items($employee_id, $scope_type, $scope_id, $metric_type, $date_start, $date_end);
		$value += $this->get_goal_metric_from_item_kits($employee_id, $scope_type, $scope_id, $metric_type, $date_start, $date_end);
		return (float)$value;
	}

	public function get_goal_metric_ranking($scope_type, $scope_id, $metric_type, $date_start, $date_end)
	{
		if ($this->should_use_report_style_sales_metric($scope_type, $metric_type))
		{
			$totals = $this->get_report_style_sales_totals_by_employee($date_start, $date_end);
			arsort($totals, SORT_NUMERIC);
			$rows = array();
			foreach ($totals as $seller_id => $metric_value)
			{
				$rows[] = array(
					'seller_id' => (int)$seller_id,
					'metric_value' => (float)$metric_value,
				);
			}

			return $rows;
		}

		$ranking = array();
		$item_rows = $this->get_goal_metric_rows_from_items($scope_type, $scope_id, $metric_type, $date_start, $date_end);
		foreach ($item_rows as $row)
		{
			$seller_id = isset($row['seller_id']) ? (int)$row['seller_id'] : 0;
			if ($seller_id <= 0)
			{
				continue;
			}
			$ranking[$seller_id] = isset($ranking[$seller_id]) ? $ranking[$seller_id] : 0.0;
			$ranking[$seller_id] += isset($row['metric_value']) ? (float)$row['metric_value'] : 0.0;
		}

		$kit_rows = $this->get_goal_metric_rows_from_item_kits($scope_type, $scope_id, $metric_type, $date_start, $date_end);
		foreach ($kit_rows as $row)
		{
			$seller_id = isset($row['seller_id']) ? (int)$row['seller_id'] : 0;
			if ($seller_id <= 0)
			{
				continue;
			}
			$ranking[$seller_id] = isset($ranking[$seller_id]) ? $ranking[$seller_id] : 0.0;
			$ranking[$seller_id] += isset($row['metric_value']) ? (float)$row['metric_value'] : 0.0;
		}

		arsort($ranking, SORT_NUMERIC);
		$rows = array();
		foreach ($ranking as $seller_id => $metric_value)
		{
			$rows[] = array(
				'seller_id' => (int)$seller_id,
				'metric_value' => (float)$metric_value,
			);
		}

		return $rows;
	}

	public function get_goal_metric_totals_by_employee($scope_type, $scope_id, $metric_type, $date_start, $date_end)
	{
		if ($this->should_use_report_style_sales_metric($scope_type, $metric_type))
		{
			return $this->get_report_style_sales_totals_by_employee($date_start, $date_end);
		}

		$totals = array();
		$item_rows = $this->get_goal_metric_rows_from_items($scope_type, $scope_id, $metric_type, $date_start, $date_end);
		foreach ($item_rows as $row)
		{
			$seller_id = isset($row['seller_id']) ? (int)$row['seller_id'] : 0;
			if ($seller_id <= 0)
			{
				continue;
			}
			$totals[$seller_id] = isset($totals[$seller_id]) ? $totals[$seller_id] : 0.0;
			$totals[$seller_id] += isset($row['metric_value']) ? (float)$row['metric_value'] : 0.0;
		}

		$kit_rows = $this->get_goal_metric_rows_from_item_kits($scope_type, $scope_id, $metric_type, $date_start, $date_end);
		foreach ($kit_rows as $row)
		{
			$seller_id = isset($row['seller_id']) ? (int)$row['seller_id'] : 0;
			if ($seller_id <= 0)
			{
				continue;
			}
			$totals[$seller_id] = isset($totals[$seller_id]) ? $totals[$seller_id] : 0.0;
			$totals[$seller_id] += isset($row['metric_value']) ? (float)$row['metric_value'] : 0.0;
		}

		return $totals;
	}

	public function get_goal_metric_store_total($scope_type, $scope_id, $metric_type, $date_start, $date_end)
	{
		if ($this->should_use_report_style_sales_metric($scope_type, $metric_type))
		{
			return $this->get_report_style_sales_store_total($date_start, $date_end);
		}

		$total = 0.0;
		foreach ($this->get_goal_metric_rows_from_items($scope_type, $scope_id, $metric_type, $date_start, $date_end) as $row)
		{
			$total += isset($row['metric_value']) ? (float)$row['metric_value'] : 0.0;
		}
		foreach ($this->get_goal_metric_rows_from_item_kits($scope_type, $scope_id, $metric_type, $date_start, $date_end) as $row)
		{
			$total += isset($row['metric_value']) ? (float)$row['metric_value'] : 0.0;
		}

		return (float) $total;
	}

	public function get_goal_metric_daily_totals_for_employee($employee_id, $scope_type, $scope_id, $metric_type, $date_start, $date_end)
	{
		$employee_id = (int)$employee_id;
		if ($employee_id <= 0)
		{
			return array();
		}

		$totals = array();

		if ($this->gala_db->table_exists('sales') && $this->gala_db->table_exists('sales_items') && $this->gala_db->table_exists('items'))
		{
			$seller_expr = $this->get_sales_seller_expression();
			$metric_expr = $metric_type === 'quantity' ? 'SUM(sales_items.quantity_purchased)' : 'SUM(sales_items.total)';

			$this->gala_db->select('DATE(sales.sale_time) AS sale_date, '.$metric_expr.' AS metric_value', FALSE);
			$this->gala_db->from('sales AS sales');
			$this->gala_db->join('sales_items AS sales_items', 'sales_items.sale_id = sales.sale_id');
			$this->gala_db->join('items AS items', 'items.item_id = sales_items.item_id');
			$this->gala_db->where('sales.deleted', 0);
			$this->gala_db->where('sales.suspended', 0);
			$this->gala_db->where('sales.sale_time >=', $date_start);
			$this->gala_db->where('sales.sale_time <=', $date_end);
			$this->gala_db->where('items.deleted', 0);
			$this->gala_db->where('items.system_item', 0);
			$this->gala_db->where($seller_expr.' = '.$this->gala_db->escape($employee_id), null, FALSE);
			$this->apply_goal_scope_filters_to_items($scope_type, $scope_id);
			$this->gala_db->group_by('sale_date');

			$item_rows = $this->gala_db->get()->result_array();
			foreach ($item_rows as $row)
			{
				if (empty($row['sale_date']))
				{
					continue;
				}
				$key = (string)$row['sale_date'];
				$totals[$key] = isset($totals[$key]) ? $totals[$key] : 0.0;
				$totals[$key] += isset($row['metric_value']) ? (float)$row['metric_value'] : 0.0;
			}
		}

		if ($this->gala_db->table_exists('sales') && $this->gala_db->table_exists('sales_item_kits') && $this->gala_db->table_exists('item_kits'))
		{
			if ($scope_type !== 'item' && $scope_type !== 'supplier')
			{
				$seller_expr = $this->get_sales_seller_expression();
				$metric_expr = $metric_type === 'quantity' ? 'SUM(sales_item_kits.quantity_purchased)' : 'SUM(sales_item_kits.total)';

				$this->gala_db->select('DATE(sales.sale_time) AS sale_date, '.$metric_expr.' AS metric_value', FALSE);
				$this->gala_db->from('sales AS sales');
				$this->gala_db->join('sales_item_kits AS sales_item_kits', 'sales_item_kits.sale_id = sales.sale_id');
				$this->gala_db->join('item_kits AS item_kits', 'item_kits.item_kit_id = sales_item_kits.item_kit_id');
				$this->gala_db->where('sales.deleted', 0);
				$this->gala_db->where('sales.suspended', 0);
				$this->gala_db->where('sales.sale_time >=', $date_start);
				$this->gala_db->where('sales.sale_time <=', $date_end);
				$this->gala_db->where('item_kits.deleted', 0);
				$this->gala_db->where($seller_expr.' = '.$this->gala_db->escape($employee_id), null, FALSE);
				$this->apply_goal_scope_filters_to_kits($scope_type, $scope_id);
				$this->gala_db->group_by('sale_date');

				$kit_rows = $this->gala_db->get()->result_array();
				foreach ($kit_rows as $row)
				{
					if (empty($row['sale_date']))
					{
						continue;
					}
					$key = (string)$row['sale_date'];
					$totals[$key] = isset($totals[$key]) ? $totals[$key] : 0.0;
					$totals[$key] += isset($row['metric_value']) ? (float)$row['metric_value'] : 0.0;
				}
			}
		}

		return $totals;
	}

	public function lookup_item_for_visor($reference, $location_id = 1)
	{
		$reference = trim((string)$reference);
		$location_id = (int)$location_id;
		if ($reference === '')
		{
			return null;
		}

		$result = $this->find_item_by_item_number($reference, $location_id);
		if ($result)
		{
			return $result;
		}

		$result = $this->find_item_by_variation_number($reference, $location_id);
		if ($result)
		{
			return $result;
		}

		$result = $this->find_item_by_additional_number($reference, $location_id);
		if ($result)
		{
			return $result;
		}

		return $this->find_item_by_product_id($reference, $location_id);
	}

	public function get_latest_vendor_sales_for_today($employee_id, $limit = 3)
	{
		if (!$this->gala_db->table_exists('sales'))
		{
			return array();
		}

		$employee_id = (int)$employee_id;
		$limit = max(1, (int)$limit);
		list($date_start, $date_end) = $this->get_today_range();
		$sold_by_field = $this->get_sales_seller_expression();
		$item_totals_sql = $this->build_sale_totals_subquery('sales_items', 'items_total');
		$kit_totals_sql = $this->build_sale_totals_subquery('sales_item_kits', 'kits_total');

		$this->gala_db->select("
			sales.sale_id,
			sales.sale_time,
			(COALESCE(sales_item_totals.items_total, 0) + COALESCE(sales_item_kit_totals.kits_total, 0)) AS total,
			sales.employee_id,
			cashier_people.first_name AS cashier_first_name,
			cashier_people.last_name AS cashier_last_name
		", FALSE);
		$this->gala_db->from('sales AS sales');
		$this->gala_db->join($item_totals_sql, 'sales_item_totals.sale_id = sales.sale_id', 'left', FALSE);
		$this->gala_db->join($kit_totals_sql, 'sales_item_kit_totals.sale_id = sales.sale_id', 'left', FALSE);
		$this->gala_db->join('employees AS cashier_employees', 'cashier_employees.person_id = sales.employee_id', 'left');
		$this->gala_db->join('people AS cashier_people', 'cashier_people.person_id = cashier_employees.person_id', 'left');
		$this->gala_db->where('sales.deleted', 0);
		$this->gala_db->where('sales.suspended', 0);
		$this->gala_db->where($sold_by_field.' = '.$this->gala_db->escape($employee_id), null, FALSE);
		$this->gala_db->where('sales.sale_time >=', $date_start);
		$this->gala_db->where('sales.sale_time <=', $date_end);
		$this->gala_db->order_by('sales.sale_time', 'DESC');
		$this->gala_db->limit($limit);

		$rows = $this->gala_db->get()->result_array();
		foreach ($rows as &$row)
		{
			$row['cashier_name'] = trim((isset($row['cashier_first_name']) ? $row['cashier_first_name'] : '').' '.(isset($row['cashier_last_name']) ? $row['cashier_last_name'] : ''));
			if ($row['cashier_name'] === '')
			{
				$row['cashier_name'] = 'No definido';
			}
			$row['items'] = $this->get_sale_detail_lines(isset($row['sale_id']) ? (int)$row['sale_id'] : 0);
		}
		unset($row);

		return $rows;
	}

	public function count_vendor_sales_for_today($employee_id)
	{
		if (!$this->gala_db->table_exists('sales'))
		{
			return 0;
		}

		$employee_id = (int)$employee_id;
		list($date_start, $date_end) = $this->get_today_range();
		$sold_by_field = $this->get_sales_seller_expression();

		$this->gala_db->from('sales AS sales');
		$this->gala_db->where('sales.deleted', 0);
		$this->gala_db->where('sales.suspended', 0);
		$this->gala_db->where($sold_by_field.' = '.$this->gala_db->escape($employee_id), null, FALSE);
		$this->gala_db->where('sales.sale_time >=', $date_start);
		$this->gala_db->where('sales.sale_time <=', $date_end);

		return (int)$this->gala_db->count_all_results();
	}

	public function count_sales_for_range($date_start, $date_end, $employee_id = null)
	{
		if (!$this->gala_db->table_exists('sales'))
		{
			return 0;
		}

		$this->gala_db->from('sales AS sales');
		$this->gala_db->where('sales.deleted', 0);
		$this->gala_db->where('sales.suspended', 0);
		$this->gala_db->where('sales.sale_time >=', $date_start);
		$this->gala_db->where('sales.sale_time <=', $date_end);

		if ($employee_id !== null)
		{
			$sold_by_field = $this->get_sales_seller_expression();
			$this->gala_db->where($sold_by_field.' = '.$this->gala_db->escape((int)$employee_id), null, FALSE);
		}

		return (int)$this->gala_db->count_all_results();
	}

	public function get_sales_counts_by_day($date_start, $date_end, $employee_id = null)
	{
		if (!$this->gala_db->table_exists('sales'))
		{
			return array();
		}

		$sold_by_field = $this->get_sales_seller_expression();
		$this->gala_db->select('DATE(sales.sale_time) AS sale_date, COUNT(*) AS sale_count', FALSE);
		$this->gala_db->from('sales AS sales');
		$this->gala_db->where('sales.deleted', 0);
		$this->gala_db->where('sales.suspended', 0);
		$this->gala_db->where('sales.sale_time >=', $date_start);
		$this->gala_db->where('sales.sale_time <=', $date_end);
		if ($employee_id !== null)
		{
			$this->gala_db->where($sold_by_field.' = '.$this->gala_db->escape((int)$employee_id), null, FALSE);
		}
		$this->gala_db->group_by('sale_date');

		$rows = $this->gala_db->get()->result_array();
		$index = array();
		foreach ($rows as $row)
		{
			if (empty($row['sale_date']))
			{
				continue;
			}
			$index[(string)$row['sale_date']] = (int)$row['sale_count'];
		}

		return $index;
	}

	public function get_vendor_sales_rank_for_today($employee_id)
	{
		if (!$this->gala_db->table_exists('sales'))
		{
			return array('position' => 1, 'total_amount' => 0.0, 'seller_count' => 0);
		}

		$employee_id = (int)$employee_id;
		list($date_start, $date_end) = $this->get_today_range();
		$sold_by_field = $this->get_sales_seller_expression();
		$item_totals_sql = $this->build_sale_totals_subquery('sales_items', 'items_total');
		$kit_totals_sql = $this->build_sale_totals_subquery('sales_item_kits', 'kits_total');

		$this->gala_db->select($sold_by_field.' AS seller_id, SUM(COALESCE(sales_item_totals.items_total, 0) + COALESCE(sales_item_kit_totals.kits_total, 0)) AS total_amount', FALSE);
		$this->gala_db->from('sales AS sales');
		$this->gala_db->join($item_totals_sql, 'sales_item_totals.sale_id = sales.sale_id', 'left', FALSE);
		$this->gala_db->join($kit_totals_sql, 'sales_item_kit_totals.sale_id = sales.sale_id', 'left', FALSE);
		$this->gala_db->where('sales.deleted', 0);
		$this->gala_db->where('sales.suspended', 0);
		$this->gala_db->where('sales.sale_time >=', $date_start);
		$this->gala_db->where('sales.sale_time <=', $date_end);
		$this->gala_db->group_by($sold_by_field);
		$this->gala_db->order_by('SUM(COALESCE(sales_item_totals.items_total, 0) + COALESCE(sales_item_kit_totals.kits_total, 0))', 'DESC', FALSE);
		$rows = $this->gala_db->get()->result_array();

		if (empty($rows))
		{
			return array('position' => 1, 'total_amount' => 0.0, 'seller_count' => 0);
		}

		$position = count($rows) + 1;
		$total_amount = 0.0;
		$display_position = 0;
		$previous_total = null;

		foreach ($rows as $index => $row)
		{
			$row_total = isset($row['total_amount']) ? (float)$row['total_amount'] : 0.0;
			if ($previous_total === null || abs($row_total - $previous_total) > 0.0001)
			{
				$display_position = $index + 1;
				$previous_total = $row_total;
			}

			if ((int)$row['seller_id'] === $employee_id)
			{
				$position = $display_position;
				$total_amount = $row_total;
				break;
			}
		}

		return array(
			'position' => $position,
			'total_amount' => $total_amount,
			'seller_count' => count($rows),
		);
	}

	public function get_vendor_sales_total_for_today($employee_id)
	{
		$rank = $this->get_vendor_sales_rank_for_today($employee_id);
		return isset($rank['total_amount']) ? (float)$rank['total_amount'] : 0.0;
	}

	public function get_vendor_sales_total_for_current_month($employee_id)
	{
		if (!$this->gala_db->table_exists('sales'))
		{
			return 0.0;
		}

		$employee_id = (int)$employee_id;
		list($date_start, $date_end) = $this->get_current_month_range();
		$sold_by_field = $this->get_sales_seller_expression();
		$item_totals_sql = $this->build_sale_totals_subquery('sales_items', 'items_total');
		$kit_totals_sql = $this->build_sale_totals_subquery('sales_item_kits', 'kits_total');

		$this->gala_db->select('SUM(COALESCE(sales_item_totals.items_total, 0) + COALESCE(sales_item_kit_totals.kits_total, 0)) AS total_amount', FALSE);
		$this->gala_db->from('sales AS sales');
		$this->gala_db->join($item_totals_sql, 'sales_item_totals.sale_id = sales.sale_id', 'left', FALSE);
		$this->gala_db->join($kit_totals_sql, 'sales_item_kit_totals.sale_id = sales.sale_id', 'left', FALSE);
		$this->gala_db->where('sales.deleted', 0);
		$this->gala_db->where('sales.suspended', 0);
		$this->gala_db->where($sold_by_field.' = '.$this->gala_db->escape($employee_id), null, FALSE);
		$this->gala_db->where('sales.sale_time >=', $date_start);
		$this->gala_db->where('sales.sale_time <=', $date_end);
		$row = $this->gala_db->get()->row_array();

		return $row && isset($row['total_amount']) ? (float)$row['total_amount'] : 0.0;
	}

	private function build_sale_totals_subquery($table, $alias)
	{
		$table_alias = $table === 'sales_items' ? 'sales_item_totals' : 'sales_item_kit_totals';

		if (!$this->gala_db->table_exists($table))
		{
			return '(SELECT 0 AS sale_id, 0 AS '.$alias.') '.$table_alias;
		}

		return '(SELECT sale_id, SUM(total) AS '.$alias.' FROM '.$this->gala_db->dbprefix($table).' GROUP BY sale_id) '.$table_alias;
	}

	private function get_goal_metric_from_items($employee_id, $scope_type, $scope_id, $metric_type, $date_start, $date_end)
	{
		$rows = $this->get_goal_metric_rows_from_items($scope_type, $scope_id, $metric_type, $date_start, $date_end, $employee_id);
		if (empty($rows))
		{
			return 0.0;
		}

		return isset($rows[0]['metric_value']) ? (float)$rows[0]['metric_value'] : 0.0;
	}

	private function get_goal_metric_rows_from_items($scope_type, $scope_id, $metric_type, $date_start, $date_end, $employee_id = null)
	{
		if (!$this->gala_db->table_exists('sales') || !$this->gala_db->table_exists('sales_items') || !$this->gala_db->table_exists('items'))
		{
			return array();
		}

		$seller_expr = $this->get_sales_seller_expression();
		$metric_expr = $metric_type === 'quantity' ? 'SUM(sales_items.quantity_purchased)' : 'SUM(sales_items.total)';

		$this->gala_db->select($seller_expr.' AS seller_id, '.$metric_expr.' AS metric_value', FALSE);
		$this->gala_db->from('sales AS sales');
		$this->gala_db->join('sales_items AS sales_items', 'sales_items.sale_id = sales.sale_id');
		$this->gala_db->join('items AS items', 'items.item_id = sales_items.item_id');
		$this->gala_db->where('sales.deleted', 0);
		$this->gala_db->where('sales.suspended', 0);
		$this->gala_db->where('sales.sale_time >=', $date_start);
		$this->gala_db->where('sales.sale_time <=', $date_end);
		$this->gala_db->where('items.deleted', 0);
		$this->gala_db->where('items.system_item', 0);

		if ($employee_id !== null)
		{
			$this->gala_db->where($seller_expr.' = '.$this->gala_db->escape((int)$employee_id), null, FALSE);
		}

		$this->apply_goal_scope_filters_to_items($scope_type, $scope_id);
		$this->gala_db->group_by($seller_expr);
		return $this->gala_db->get()->result_array();
	}

	private function get_goal_metric_from_item_kits($employee_id, $scope_type, $scope_id, $metric_type, $date_start, $date_end)
	{
		$rows = $this->get_goal_metric_rows_from_item_kits($scope_type, $scope_id, $metric_type, $date_start, $date_end, $employee_id);
		if (empty($rows))
		{
			return 0.0;
		}

		return isset($rows[0]['metric_value']) ? (float)$rows[0]['metric_value'] : 0.0;
	}

	private function get_goal_metric_rows_from_item_kits($scope_type, $scope_id, $metric_type, $date_start, $date_end, $employee_id = null)
	{
		if (!$this->gala_db->table_exists('sales') || !$this->gala_db->table_exists('sales_item_kits') || !$this->gala_db->table_exists('item_kits'))
		{
			return array();
		}

		if ($scope_type === 'item' || $scope_type === 'supplier')
		{
			return array();
		}

		$seller_expr = $this->get_sales_seller_expression();
		$metric_expr = $metric_type === 'quantity' ? 'SUM(sales_item_kits.quantity_purchased)' : 'SUM(sales_item_kits.total)';

		$this->gala_db->select($seller_expr.' AS seller_id, '.$metric_expr.' AS metric_value', FALSE);
		$this->gala_db->from('sales AS sales');
		$this->gala_db->join('sales_item_kits AS sales_item_kits', 'sales_item_kits.sale_id = sales.sale_id');
		$this->gala_db->join('item_kits AS item_kits', 'item_kits.item_kit_id = sales_item_kits.item_kit_id');
		$this->gala_db->where('sales.deleted', 0);
		$this->gala_db->where('sales.suspended', 0);
		$this->gala_db->where('sales.sale_time >=', $date_start);
		$this->gala_db->where('sales.sale_time <=', $date_end);
		$this->gala_db->where('item_kits.deleted', 0);

		if ($employee_id !== null)
		{
			$this->gala_db->where($seller_expr.' = '.$this->gala_db->escape((int)$employee_id), null, FALSE);
		}

		$this->apply_goal_scope_filters_to_kits($scope_type, $scope_id);
		$this->gala_db->group_by($seller_expr);
		return $this->gala_db->get()->result_array();
	}

	private function apply_goal_scope_filters_to_items($scope_type, $scope_id)
	{
		$scope_id = (int)$scope_id;
		switch ((string)$scope_type)
		{
			case 'category':
				$category_ids = $this->get_category_descendant_ids($scope_id);
				if (empty($category_ids))
				{
					$category_ids = array($scope_id);
				}
				$this->gala_db->where_in('items.category_id', $category_ids);
				break;
			case 'item':
				$this->gala_db->where('items.item_id', $scope_id);
				break;
			case 'manufacturer':
				$this->gala_db->where('items.manufacturer_id', $scope_id);
				break;
			case 'supplier':
				$this->gala_db->where('items.supplier_id', $this->resolve_goal_supplier_person_id($scope_id));
				break;
			case 'general':
			default:
				break;
		}
	}

	private function apply_goal_scope_filters_to_kits($scope_type, $scope_id)
	{
		$scope_id = (int)$scope_id;
		switch ((string)$scope_type)
		{
			case 'category':
				$category_ids = $this->get_category_descendant_ids($scope_id);
				if (empty($category_ids))
				{
					$category_ids = array($scope_id);
				}
				$this->gala_db->where_in('item_kits.category_id', $category_ids);
				break;
			case 'manufacturer':
				$this->gala_db->where('item_kits.manufacturer_id', $scope_id);
				break;
			case 'general':
			default:
				break;
		}
	}

	private function resolve_goal_supplier_person_id($scope_id)
	{
		$scope_id = (int)$scope_id;
		if ($scope_id <= 0 || !$this->gala_db->table_exists('suppliers'))
		{
			return $scope_id;
		}

		$this->gala_db->select('person_id');
		$this->gala_db->from('suppliers');
		$this->gala_db->group_start();
		$this->gala_db->where('person_id', $scope_id);
		$this->gala_db->or_where('id', $scope_id);
		$this->gala_db->group_end();
		$row = $this->gala_db->get()->row_array();
		if (!$row || !isset($row['person_id']))
		{
			return $scope_id;
		}

		return (int)$row['person_id'];
	}

	private function get_category_descendant_ids($category_id)
	{
		$category_id = (int)$category_id;
		if ($category_id <= 0 || !$this->gala_db->table_exists('categories'))
		{
			return array();
		}

		$db = $this->load->database('gala_read', TRUE);
		$db->select('id, parent_id');
		$db->from('categories');
		$db->where('deleted', 0);
		$categories = $db->get()->result_array();
		$children = array();
		foreach ($categories as $category)
		{
			$parent_id = isset($category['parent_id']) ? (int)$category['parent_id'] : 0;
			$children[$parent_id] = isset($children[$parent_id]) ? $children[$parent_id] : array();
			$children[$parent_id][] = (int)$category['id'];
		}

		$ids = array($category_id);
		$queue = array($category_id);
		while (!empty($queue))
		{
			$current_id = array_shift($queue);
			if (empty($children[$current_id]))
			{
				continue;
			}

			foreach ($children[$current_id] as $child_id)
			{
				if (!in_array($child_id, $ids, TRUE))
				{
					$ids[] = $child_id;
					$queue[] = $child_id;
				}
			}
		}

		return $ids;
	}

	private function get_today_range()
	{
		$timezone = new DateTimeZone('America/Caracas');
		$today = new DateTime('now', $timezone);
		$date = $today->format('Y-m-d');
		return array($date.' 00:00:00', $date.' 23:59:59');
	}

	private function get_current_month_range()
	{
		$timezone = new DateTimeZone('America/Caracas');
		$today = new DateTime('now', $timezone);
		$start = new DateTime($today->format('Y-m-01 00:00:00'), $timezone);
		$end = clone $start;
		$end->modify('last day of this month')->setTime(23, 59, 59);
		return array($start->format('Y-m-d H:i:s'), $end->format('Y-m-d H:i:s'));
	}

	private function get_sales_seller_expression()
	{
		if ($this->gala_db->field_exists('sold_by_employee_id', 'sales'))
		{
			return 'COALESCE(NULLIF(sales.sold_by_employee_id, 0), sales.employee_id)';
		}

		return 'sales.employee_id';
	}

	private function should_use_report_style_sales_metric($scope_type, $metric_type)
	{
		return (string)$scope_type === 'general' && (string)$metric_type === 'amount';
	}

	private function get_report_style_sales_store_total($date_start, $date_end, $sale_type = 'all', $location_ids = null)
	{
		if (!$this->gala_db->table_exists('sales'))
		{
			return 0.0;
		}

		$location_ids = $this->normalize_report_location_ids($location_ids);
		$location_csv = implode(',', array_map('intval', $location_ids));

		$this->gala_db->reset_query();
		$this->gala_db->select('SUM(sales.total) AS metric_value', FALSE);
		$this->gala_db->from('sales AS sales');
		$this->gala_db->where('sales.deleted', 0);
		$this->gala_db->where('sales.sale_time >=', $date_start);
		$this->gala_db->where('sales.sale_time <=', $date_end);
		$this->gala_db->where('sales.location_id IN ('.$location_csv.')', null, FALSE);

		if ($this->hide_store_account_payments_in_reports && $this->gala_db->field_exists('store_account_payment', 'sales'))
		{
			$this->gala_db->where('sales.store_account_payment', 0);
		}

		if ($this->gala_db->field_exists('suspended', 'sales'))
		{
			if ($this->hide_layaways_sales_in_reports)
			{
				$this->gala_db->where('sales.suspended', 0);
			}
			else
			{
				$this->gala_db->where('sales.suspended <', 2);
			}
		}

		if ((string)$sale_type === 'sales' && $this->gala_db->field_exists('total_quantity_purchased', 'sales'))
		{
			$this->gala_db->where('sales.total_quantity_purchased >', 0);
		}
		elseif ((string)$sale_type === 'returns' && $this->gala_db->field_exists('total_quantity_purchased', 'sales'))
		{
			$this->gala_db->where('sales.total_quantity_purchased <', 0);
		}

		$row = $this->gala_db->get()->row_array();
		return !empty($row['metric_value']) ? (float) $row['metric_value'] : 0.0;
	}
	private function get_report_style_sales_totals_by_employee($date_start, $date_end, $employee_type = 'sale_person', $sale_type = 'all', $location_ids = null)
	{
		if (!$this->gala_db->table_exists('sales'))
		{
			return array();
		}

		$employee_column = ((string)$employee_type === 'logged_in_employee' || (string)$employee_type === 'employee')
			? 'employee_id'
			: 'sold_by_employee_id';

		if ($employee_column === 'sold_by_employee_id' && !$this->gala_db->field_exists('sold_by_employee_id', 'sales'))
		{
			$employee_column = 'employee_id';
		}

		$location_ids = $this->normalize_report_location_ids($location_ids);
		$item_query = $this->build_report_style_sales_metric_query('sales_items', $employee_column, $date_start, $date_end, $sale_type, $location_ids);
		$kit_query = $this->build_report_style_sales_metric_query('sales_item_kits', $employee_column, $date_start, $date_end, $sale_type, $location_ids);
		$queries = array();
		if ($item_query !== '')
		{
			$queries[] = $item_query;
		}
		if ($kit_query !== '')
		{
			$queries[] = $kit_query;
		}

		if (empty($queries))
		{
			return array();
		}

		$sql = 'SELECT seller_id, SUM(metric_value) AS metric_value
			FROM ('.implode(' UNION ALL ', $queries).') AS metric_rows
			WHERE seller_id IS NOT NULL AND seller_id > 0
			GROUP BY seller_id';

		$rows = $this->gala_db->query($sql)->result_array();
		$totals = array();
		foreach ($rows as $row)
		{
			$seller_id = isset($row['seller_id']) ? (int)$row['seller_id'] : 0;
			if ($seller_id <= 0)
			{
				continue;
			}
			$totals[$seller_id] = isset($row['metric_value']) ? (float)$row['metric_value'] : 0.0;
		}

		return $totals;
	}

	private function build_report_style_sales_metric_query($detail_table, $employee_column, $date_start, $date_end, $sale_type, $location_ids)
	{
		if (!$this->gala_db->table_exists($detail_table))
		{
			return '';
		}

		$detail_alias = $detail_table === 'sales_items' ? 'sales_items' : 'sales_item_kits';
		$location_csv = implode(',', array_map('intval', $location_ids));

		$this->gala_db->reset_query();
		$this->gala_db->select('sales.'.$employee_column.' AS seller_id, SUM('.$detail_alias.'.total) AS metric_value', FALSE);
		$this->gala_db->from('sales AS sales');
		$this->gala_db->join($detail_table.' AS '.$detail_alias, $detail_alias.'.sale_id = sales.sale_id');
		$this->gala_db->where('sales.deleted', 0);
		$this->gala_db->where('sales.sale_time >=', $date_start);
		$this->gala_db->where('sales.sale_time <=', $date_end);
		$this->gala_db->where('sales.location_id IN ('.$location_csv.')', null, FALSE);

		if ($this->hide_store_account_payments_in_reports && $this->gala_db->field_exists('store_account_payment', 'sales'))
		{
			$this->gala_db->where('sales.store_account_payment', 0);
		}

		if ($this->gala_db->field_exists('suspended', 'sales'))
		{
			if ($this->hide_layaways_sales_in_reports)
			{
				$this->gala_db->where('sales.suspended', 0);
			}
			else
			{
				$this->gala_db->where('sales.suspended <', 2);
			}
		}

		if ((string)$sale_type === 'sales' && $this->gala_db->field_exists('total_quantity_purchased', 'sales'))
		{
			$this->gala_db->where('sales.total_quantity_purchased >', 0);
		}
		elseif ((string)$sale_type === 'returns' && $this->gala_db->field_exists('total_quantity_purchased', 'sales'))
		{
			$this->gala_db->where('sales.total_quantity_purchased <', 0);
		}

		$this->gala_db->where('sales.'.$employee_column.' IS NOT NULL', null, FALSE);
		$this->gala_db->where('sales.'.$employee_column.' >', 0);
		$this->gala_db->group_by('sales.'.$employee_column);

		return $this->gala_db->get_compiled_select();
	}

	private function normalize_report_location_ids($location_ids = null)
	{
		$ids = array_values(array_unique(array_filter(array_map('intval', (array)$location_ids))));
		if (!empty($ids))
		{
			return $ids;
		}

		$ci =& get_instance();
		if (isset($ci->input))
		{
			$request_location_ids = array_values(array_unique(array_filter(array_map('intval', (array)$ci->input->get('location_ids')))));
			if (!empty($request_location_ids))
			{
				return $request_location_ids;
			}
		}

		if (isset($ci->session))
		{
			$session_location_ids = array(
				(int)$ci->session->userdata('employee_current_location_id'),
				(int)$ci->session->userdata('current_location_id'),
			);
			if ($ci->session->userdata('visor_auth'))
			{
				$visor_auth = (array)$ci->session->userdata('visor_auth');
				$session_location_ids[] = isset($visor_auth['location_id']) ? (int)$visor_auth['location_id'] : 0;
			}

			$session_location_ids = array_values(array_unique(array_filter($session_location_ids)));
			if (!empty($session_location_ids))
			{
				return $session_location_ids;
			}
		}

		if ($this->gala_db->table_exists('locations'))
		{
			$this->gala_db->select('location_id');
			$this->gala_db->from('locations');
			$rows = $this->gala_db->get()->result_array();
			foreach ($rows as $row)
			{
				$location_id = isset($row['location_id']) ? (int)$row['location_id'] : 0;
				if ($location_id > 0)
				{
					$ids[] = $location_id;
				}
			}
		}

		return !empty($ids) ? $ids : array(1);
	}

	private function get_sale_detail_lines($sale_id)
	{
		$sale_id = (int)$sale_id;
		if ($sale_id <= 0)
		{
			return array();
		}

		$lines = array();

		if ($this->gala_db->table_exists('sales_items'))
		{
			$this->gala_db->select("
				sales_items.line,
				sales_items.quantity_purchased,
				items.name,
				item_variations.name AS variation_name
			", FALSE);
			$this->gala_db->from('sales_items');
			$this->gala_db->join('items', 'items.item_id = sales_items.item_id', 'left');
			if ($this->gala_db->table_exists('item_variations') && $this->gala_db->field_exists('item_variation_id', 'sales_items'))
			{
				$this->gala_db->join('item_variations', 'item_variations.id = sales_items.item_variation_id', 'left');
			}
			$this->gala_db->where('sales_items.sale_id', $sale_id);
			$this->gala_db->order_by('sales_items.line', 'ASC');
			$item_rows = $this->gala_db->get()->result_array();

			foreach ($item_rows as $row)
			{
				$name = isset($row['name']) ? trim((string)$row['name']) : 'Articulo';
				$variation = isset($row['variation_name']) ? trim((string)$row['variation_name']) : '';
				$lines[] = array(
					'name' => $variation !== '' ? $name.' - '.$variation : $name,
					'quantity' => isset($row['quantity_purchased']) ? (float)$row['quantity_purchased'] : 0,
					'type' => 'item',
				);
			}
		}

		if ($this->gala_db->table_exists('sales_item_kits'))
		{
			$this->gala_db->select("
				sales_item_kits.line,
				sales_item_kits.quantity_purchased,
				item_kits.name
			", FALSE);
			$this->gala_db->from('sales_item_kits');
			$this->gala_db->join('item_kits', 'item_kits.item_kit_id = sales_item_kits.item_kit_id', 'left');
			$this->gala_db->where('sales_item_kits.sale_id', $sale_id);
			$this->gala_db->order_by('sales_item_kits.line', 'ASC');
			$kit_rows = $this->gala_db->get()->result_array();

			foreach ($kit_rows as $row)
			{
				$lines[] = array(
					'name' => isset($row['name']) && trim((string)$row['name']) !== '' ? trim((string)$row['name']) : 'Kit',
					'quantity' => isset($row['quantity_purchased']) ? (float)$row['quantity_purchased'] : 0,
					'type' => 'kit',
				);
			}
		}

		return $lines;
	}

	private function find_item_by_item_number($reference, $location_id)
	{
		$this->gala_db->select('items.item_id, items.name, items.size, items.item_number, items.product_id, items.unit_price, items.promo_price, items.start_date, items.end_date, location_items.unit_price AS location_unit_price, location_items.promo_price AS location_promo_price, location_items.start_date AS location_start_date, location_items.end_date AS location_end_date', FALSE);
		$this->gala_db->from('items');
		$this->gala_db->join('location_items', 'location_items.item_id = items.item_id AND location_items.location_id = '.$location_id, 'left');
		$this->gala_db->where('items.deleted', 0);
		$this->gala_db->where('items.system_item', 0);
		$this->gala_db->where('items.item_number', $reference);
		$this->gala_db->limit(1);
		$row = $this->gala_db->get()->row_array();
		return $row ? $this->decorate_item_result($row, array(), 'item_number', $reference) : null;
	}

	private function find_item_by_product_id($reference, $location_id)
	{
		$this->gala_db->select('items.item_id, items.name, items.size, items.item_number, items.product_id, items.unit_price, items.promo_price, items.start_date, items.end_date, location_items.unit_price AS location_unit_price, location_items.promo_price AS location_promo_price, location_items.start_date AS location_start_date, location_items.end_date AS location_end_date', FALSE);
		$this->gala_db->from('items');
		$this->gala_db->join('location_items', 'location_items.item_id = items.item_id AND location_items.location_id = '.$location_id, 'left');
		$this->gala_db->where('items.deleted', 0);
		$this->gala_db->where('items.system_item', 0);
		$this->gala_db->where('items.product_id', $reference);
		$this->gala_db->limit(1);
		$row = $this->gala_db->get()->row_array();
		return $row ? $this->decorate_item_result($row, array(), 'product_id', $reference) : null;
	}

	private function find_item_by_variation_number($reference, $location_id)
	{
		if (!$this->gala_db->table_exists('item_variations'))
		{
			return null;
		}

		$this->gala_db->select('items.item_id, items.name, items.size, items.item_number, items.product_id, items.unit_price, items.promo_price, items.start_date, items.end_date, item_variations.id AS item_variation_id, item_variations.name AS variation_name, item_variations.item_number AS variation_item_number, item_variations.unit_price AS variation_unit_price, item_variations.promo_price AS variation_promo_price, item_variations.start_date AS variation_start_date, item_variations.end_date AS variation_end_date, location_items.unit_price AS location_unit_price, location_items.promo_price AS location_promo_price, location_items.start_date AS location_start_date, location_items.end_date AS location_end_date, location_item_variations.unit_price AS variation_location_unit_price', FALSE);
		$this->gala_db->from('item_variations');
		$this->gala_db->join('items', 'items.item_id = item_variations.item_id');
		$this->gala_db->join('location_items', 'location_items.item_id = items.item_id AND location_items.location_id = '.$location_id, 'left');
		if ($this->gala_db->table_exists('location_item_variations'))
		{
			$this->gala_db->join('location_item_variations', 'location_item_variations.item_variation_id = item_variations.id AND location_item_variations.location_id = '.$location_id, 'left');
		}
		$this->gala_db->where('items.deleted', 0);
		$this->gala_db->where('items.system_item', 0);
		if ($this->gala_db->field_exists('deleted', 'item_variations'))
		{
			$this->gala_db->where('item_variations.deleted', 0);
		}
		$this->gala_db->where('item_variations.item_number', $reference);
		$this->gala_db->limit(1);
		$row = $this->gala_db->get()->row_array();
		return $row ? $this->decorate_item_result($row, $row, 'variation_item_number', $reference) : null;
	}

	private function find_item_by_additional_number($reference, $location_id)
	{
		if (!$this->gala_db->table_exists('additional_item_numbers'))
		{
			return null;
		}

		$this->gala_db->select('additional_item_numbers.item_id, additional_item_numbers.item_variation_id');
		$this->gala_db->from('additional_item_numbers');
		$this->gala_db->where('additional_item_numbers.item_number', $reference);
		$this->gala_db->limit(1);
		$extra = $this->gala_db->get()->row_array();
		if (!$extra || empty($extra['item_id']))
		{
			return null;
		}

		if (!empty($extra['item_variation_id']) && $this->gala_db->table_exists('item_variations'))
		{
			$this->gala_db->select('items.item_id, items.name, items.size, items.item_number, items.product_id, items.unit_price, items.promo_price, items.start_date, items.end_date, item_variations.id AS item_variation_id, item_variations.name AS variation_name, item_variations.item_number AS variation_item_number, item_variations.unit_price AS variation_unit_price, item_variations.promo_price AS variation_promo_price, item_variations.start_date AS variation_start_date, item_variations.end_date AS variation_end_date, location_items.unit_price AS location_unit_price, location_items.promo_price AS location_promo_price, location_items.start_date AS location_start_date, location_items.end_date AS location_end_date, location_item_variations.unit_price AS variation_location_unit_price', FALSE);
			$this->gala_db->from('item_variations');
			$this->gala_db->join('items', 'items.item_id = item_variations.item_id');
			$this->gala_db->join('location_items', 'location_items.item_id = items.item_id AND location_items.location_id = '.$location_id, 'left');
			if ($this->gala_db->table_exists('location_item_variations'))
			{
				$this->gala_db->join('location_item_variations', 'location_item_variations.item_variation_id = item_variations.id AND location_item_variations.location_id = '.$location_id, 'left');
			}
			$this->gala_db->where('items.deleted', 0);
			$this->gala_db->where('items.system_item', 0);
			$this->gala_db->where('item_variations.id', (int)$extra['item_variation_id']);
			$this->gala_db->limit(1);
			$row = $this->gala_db->get()->row_array();
			return $row ? $this->decorate_item_result($row, $row, 'additional_item_number', $reference) : null;
		}

		$this->gala_db->select('items.item_id, items.name, items.size, items.item_number, items.product_id, items.unit_price, items.promo_price, items.start_date, items.end_date, location_items.unit_price AS location_unit_price, location_items.promo_price AS location_promo_price, location_items.start_date AS location_start_date, location_items.end_date AS location_end_date', FALSE);
		$this->gala_db->from('items');
		$this->gala_db->join('location_items', 'location_items.item_id = items.item_id AND location_items.location_id = '.$location_id, 'left');
		$this->gala_db->where('items.deleted', 0);
		$this->gala_db->where('items.system_item', 0);
		$this->gala_db->where('items.item_id', (int)$extra['item_id']);
		$this->gala_db->limit(1);
		$row = $this->gala_db->get()->row_array();
		return $row ? $this->decorate_item_result($row, array(), 'additional_item_number', $reference) : null;
	}

	private function decorate_item_result($item_row, $variation_row, $matched_by, $reference)
	{
		$price_divisa = $this->resolve_sale_price($item_row, $variation_row);
		$display_name = isset($item_row['name']) ? trim((string)$item_row['name']) : '';
		$subtitle = '';

		if (!empty($variation_row['variation_name']))
		{
			$subtitle = trim((string)$variation_row['variation_name']);
		}
		elseif (!empty($item_row['size']))
		{
			$subtitle = 'Talla: '.trim((string)$item_row['size']);
		}

		return array(
			'item_id' => isset($item_row['item_id']) ? (int)$item_row['item_id'] : 0,
			'item_variation_id' => !empty($variation_row['item_variation_id']) ? (int)$variation_row['item_variation_id'] : null,
			'name' => $display_name,
			'subtitle' => $subtitle,
			'reference' => $reference,
			'matched_by' => $matched_by,
			'item_number' => isset($item_row['item_number']) ? (string)$item_row['item_number'] : '',
			'product_id' => isset($item_row['product_id']) ? (string)$item_row['product_id'] : '',
			'variation_item_number' => !empty($variation_row['variation_item_number']) ? (string)$variation_row['variation_item_number'] : '',
			'price_divisa' => (float)$price_divisa,
		);
	}

	private function resolve_sale_price($item_row, $variation_row = array())
	{
		$today = strtotime(date('Y-m-d'));

		if (!empty($variation_row['item_variation_id']))
		{
			$variation_promo = isset($variation_row['variation_promo_price']) ? (float)$variation_row['variation_promo_price'] : 0.0;
			if ($variation_promo > 0 && $this->is_promo_active(isset($variation_row['variation_start_date']) ? $variation_row['variation_start_date'] : null, isset($variation_row['variation_end_date']) ? $variation_row['variation_end_date'] : null, $today))
			{
				return $variation_promo;
			}

			$variation_location_price = isset($variation_row['variation_location_unit_price']) ? $variation_row['variation_location_unit_price'] : null;
			$variation_unit_price = isset($variation_row['variation_unit_price']) ? $variation_row['variation_unit_price'] : null;
			$variation_price = null;
			if ($this->allow_zero_location_override)
			{
				if ($variation_location_price !== null && $variation_location_price !== '')
				{
					$variation_price = (float)$variation_location_price;
				}
				elseif ($variation_unit_price !== null && $variation_unit_price !== '')
				{
					$variation_price = (float)$variation_unit_price;
				}
			}
			else
			{
				if ($variation_location_price)
				{
					$variation_price = (float)$variation_location_price;
				}
				elseif ($variation_unit_price)
				{
					$variation_price = (float)$variation_unit_price;
				}
			}

			if ($variation_price !== null && $variation_price > 0)
			{
				return $variation_price;
			}

			return $this->resolve_item_sale_price($item_row, $today);
		}

		return $this->resolve_item_sale_price($item_row, $today);
	}

	private function resolve_item_sale_price($item_row, $today_timestamp)
	{
		$location_promo = isset($item_row['location_promo_price']) ? (float)$item_row['location_promo_price'] : 0.0;
		if ($location_promo > 0 && $this->is_promo_active(isset($item_row['location_start_date']) ? $item_row['location_start_date'] : null, isset($item_row['location_end_date']) ? $item_row['location_end_date'] : null, $today_timestamp))
		{
			return $location_promo;
		}

		$item_promo = isset($item_row['promo_price']) ? (float)$item_row['promo_price'] : 0.0;
		if ($item_promo > 0 && $this->is_promo_active(isset($item_row['start_date']) ? $item_row['start_date'] : null, isset($item_row['end_date']) ? $item_row['end_date'] : null, $today_timestamp))
		{
			return $item_promo;
		}

		$location_unit_price = isset($item_row['location_unit_price']) ? $item_row['location_unit_price'] : null;
		$item_unit_price = isset($item_row['unit_price']) ? $item_row['unit_price'] : null;
		if ($this->allow_zero_location_override)
		{
			if ($location_unit_price !== null && $location_unit_price !== '')
			{
				return (float)$location_unit_price;
			}
			return (float)$item_unit_price;
		}

		return (float)($location_unit_price ? $location_unit_price : $item_unit_price);
	}

	private function is_promo_active($start_date, $end_date, $today_timestamp)
	{
		$start_date = $start_date !== null ? trim((string)$start_date) : '';
		$end_date = $end_date !== null ? trim((string)$end_date) : '';
		if ($start_date === '' && $end_date === '')
		{
			return TRUE;
		}

		if ($start_date !== '' && $end_date !== '')
		{
			$start = strtotime($start_date);
			$end = strtotime($end_date);
			return $start && $end && $start <= $today_timestamp && $end >= $today_timestamp;
		}

		return FALSE;
	}

	private function read_boolean_app_config($key)
	{
		if (!$this->gala_db->table_exists('app_config'))
		{
			return FALSE;
		}

		$row = $this->gala_db->get_where('app_config', array('key' => (string)$key))->row_array();
		if (!$row || !isset($row['value']))
		{
			return FALSE;
		}

		$value = strtolower(trim((string)$row['value']));
		return in_array($value, array('1', 'true', 'yes', 'on'), TRUE);
	}
}


