<?php
    class session_model extends CI_Model
    {
    	function __construct()
        {
            parent::__construct();
		}
		
		function get_sales_details_by_session($session_id='', $staff_id = '')
		{
			$this->db->select('sales_transactions_details.item_price, products.name product, sales_transactions_details.total_price');
            $this->db->from('sales_transactions');
			$this->db->join('sales_transactions_details', 'sales_transactions.id = sales_transactions_details.sales_transactions_id');
			$this->db->join('products', 'sales_transactions_details.products_id = products.id');
            $this->db->where('sales_transactions.sessions_id', $session_id);
			if(!empty($staff_id)){
				$this->db->where('sales_transactions.staff_id', $staff_id);
			}
            $query = $this->db->get();
			return $query->result();
		}
		
		function resume_total_session ($session_id) {
			$query = $this->db->query(
				"SELECT sessions.id, concat(staff.first_name, ' ', staff.last_name) as seller,
				sum(payments.amount) as total,
				(case sessions.is_open WHEN true THEN 'Abierta' ELSE 'Cerrada' END) as status
				FROM sessions
				INNER JOIN staff ON sessions.staff_id = staff.id
				INNER JOIN sales_transactions ON sessions.id = sales_transactions.sessions_id
				INNER JOIN payments ON sales_transactions.id = payments.sales_transactions_id
				WHERE sales_transactions.is_done = true
				AND sales_transactions.is_canceled = false
				AND sales_transactions.folio_doc IS NOT NULL
				AND sessions.business_units_id = ".BUSINESS_UNIT_ID."
				AND sessions.id = ".$session_id."
				GROUP BY sessions.id, concat(staff.first_name, ' ', staff.last_name)"
			);

			if ($query->num_rows() === 0) {
				$query = $this->db->query(
					"SELECT sessions.id, concat(staff.first_name, ' ', staff.last_name) as seller,
					0 as total,
					(case sessions.is_open WHEN true THEN 'Abierta' ELSE 'Cerrada' END) as status
					FROM sessions
					INNER JOIN staff ON sessions.staff_id = staff.id
					WHERE sessions.business_units_id = ".BUSINESS_UNIT_ID."
					AND sessions.id = ".$session_id
				);

				$resume = $query->row();
			}
			else {
				$resume = $query->row();
			}


			return $resume;
		}

		function canceled_sales_by_day ($day) {
			$in_value = $this->get_all_session_id_from_day($day);

			$query_total = $this->db->query(
				"SELECT COALESCE(SUM(payments.amount), 0) as total
				FROM sales_transactions
				INNER JOIN payments ON sales_transactions.id = payments.sales_transactions_id
				WHERE sales_transactions.is_canceled = true
				AND sales_transactions.sessions_id IN (".$in_value.")"
			);

			$total = $query_total->row()->total;

			$query_items = $this->db->query(
				"SELECT sales_transactions.folio_doc AS folio,
				payments.amount as total,
				to_char(sales_transactions.create_time, 'DD/MM/YYYY HH24:MI:SS') as date_at
				FROM sales_transactions
				INNER JOIN payments ON sales_transactions.id = payments.sales_transactions_id
				WHERE sales_transactions.is_canceled = true
				AND sales_transactions.sessions_id IN (".$in_value.")"
			);

			$items = $query_items->num_rows() === 0 ? array() : $query_items->result();

			return array(
				'total' => $total,
				'items' => $items
			);
		}

		function get_tickets_info_by_day ($day) {
			$in_value = $this->get_all_session_id_from_day($day);

			$query = $this->db->query(
				"SELECT
				(select folio_doc
				from sales_transactions
				WHERE is_done = true
				AND is_canceled = false
				and folio_doc is not null
				and sessions_id in (".$in_value.")
				order by id ASC limit 1) as folio_start,
				
				(select folio_doc
				from sales_transactions
				WHERE is_done = true
				AND is_canceled = false
				and folio_doc is not null
				and sessions_id in (".$in_value.")
				order by id DESC limit 1) as folio_end,
				
				(select COUNT(*)
				from sales_transactions
				WHERE is_done = true
				AND is_canceled = false
				and folio_doc is not null
				and sessions_id in (".$in_value.")) as quantity,
				
				(select COUNT(*)
				from sales_transactions
				INNER JOIN payments ON sales_transactions.id = payments.sales_transactions_id
				WHERE is_canceled = true
				AND net_total > 0
				and sales_transactions.sessions_id IN (".$in_value.")) as canceled_quantity"
			);

			$info = $query->row();

			return $info;
		}
		
		function get_all_pos()
        {
        	$encryption_key = "POL9XSpsRZwkXcZoDJecD2cIjNyD3Uj9";
			$licence_pos = $this->encrypt->decode(LICENCE_POS, $encryption_key);
			
            $this->db->select('id');
			$this->db->where('business_units_id', BUSINESS_UNIT_ID);
			$this->db->where('is_active', TRUE);
			//$this->db->where('in_use', FALSE);
			$this->db->limit($licence_pos);
            $query = $this->db->get('pos');
            return $query->result();
        }
		
		function get_available_pos($pos_in_use)
        {
            $this->db->select('id');
            $this->db->where_not_in('id', $pos_in_use);
			$this->db->where('business_units_id', BUSINESS_UNIT_ID);
			$this->db->where('in_use', FALSE);
			$this->db->where('is_active', TRUE);
            $query = $this->db->get('pos');
            return $query->result();
        }
		   
		function get_payments($session_id)
        {
            $this->db->select('SUM(amount) total, pm.name, p.payment_methods_id');
            $this->db->from('payments p');
			$this->db->join('sales_transactions st', 'st.id = p.sales_transactions_id');
			$this->db->join('payment_methods pm', 'pm.id = p.payment_methods_id');
            $this->db->where('st.sessions_id', $session_id);
			$this->db->where('st.is_done', TRUE);
			$this->db->group_by('p.payment_methods_id, pm.name');
            $query = $this->db->get();
			 
            return $query->result();
		}
		
		function get_payments_of_day($day)
        {
			$in_value = $this->get_all_session_id_from_day($day);

            $this->db->select('SUM(amount) total, pm.name, p.payment_methods_id');
            $this->db->from('payments p');
			$this->db->join('sales_transactions st', 'st.id = p.sales_transactions_id');
			$this->db->join('payment_methods pm', 'pm.id = p.payment_methods_id');
            $this->db->where("st.sessions_id IN (".$in_value.")");
			$this->db->where('st.is_done', TRUE);
			$this->db->group_by('p.payment_methods_id, pm.name');
            $query = $this->db->get();
			 
            return $query->result();
        }
		
		function get_cash_details($session_id)
        {
            $this->db->select('SUM(quantity) quantity, ct.name, (SUM(quantity) * ct.name) total, ct.id cash_type_id');
            $this->db->from('cash_details cd');
			$this->db->join('cash_types ct', 'ct.id = cd.cash_types_id');
            $this->db->where('cd.sessions_id', $session_id);
			$this->db->where('(sales_session is true or open_session is true)');
			$this->db->group_by('cd.cash_types_id, ct.name, ct.id');
			$this->db->order_by('ct.id');
            $query = $this->db->get();
			 
            return $query->result();
        }

		function get_close_cash_details($session_id)
        {
            $this->db->select('SUM(quantity) quantity, ct.name, (SUM(quantity) * ct.name) total, ct.id cash_type_id');
            $this->db->from('cash_details cd');
			$this->db->join('cash_types ct', 'ct.id = cd.cash_types_id');
            $this->db->where('cd.sessions_id', $session_id);
			$this->db->where('cd.close_session', TRUE);
			$this->db->group_by('cd.cash_types_id, ct.name, ct.id');
			$this->db->order_by('ct.id');
            $query = $this->db->get();
			 
            return $query->result();
        }
		
		//get_result_by_join('cash_details', 'cash_type', array('cash_types_id'=>'id'),array('sessions_id'=> SESSION_ID), array('cash_types_id'=>'DESC'));
		
		protected function _get_table_field($table, $alias)
		{
			$fields='';
			if($table!='')
			{
				$field_data = $this->db->field_data($table);
				foreach ($field_data as $key => $value) 
				{
					if (empty($alias)) {
						$fields .= $value->name.', ';
					} else {
						$fields .= $alias.'.'.$value->name.', ';
					}
				}
			}
			$fields = trim($fields, ',');
			return $fields;
		}
		function get_sessions($where){
			$query = $this->db->query("
			SELECT s.id,b.name as negocio,to_char(s.create_time, 'DD') as day,  to_char(s.create_time, 'DD/MM/YYYY HH24:MI') apertura, 
			to_char(s.logout_time, 'DD/MM/YYYY HH24:MI') cierre, (SUM(s.close_take_out_cash)/count(s.id)) total_ingreso ,
			((SUM(s.close_cash_sales)+SUM(s.close_card_sales))/count(s.id)) ven_cash_card ,
			SUM(st.gross_total) venta_total , p.name as pos
			FROM sessions s
			 JOIN sales_transactions st on s.id = st.sessions_id
			 JOIN business_units b on st.business_units_id = b.id
			 JOIN pos p on s.pos_id = p.id
			WHERE st.is_done = true AND st.is_canceled = false $where
			GROUP BY to_char(s.create_time, 'DD/MM/YYYY HH24:MI'),to_char(s.logout_time, 'DD/MM/YYYY HH24:MI'), s.id,b.name, p.name, to_char(s.create_time, 'DD')
			");
			return $query->result();
		}
		function get_sessions_group_by_day($start, $end, $business) {
			$in_value = $this->get_all_session_id_from_range($start, $end);

			if (empty($in_value)) {
				return NULL;
			}

			$query = $this->db->query(
				"SELECT \"Day\", SUM(\"Efectivo\") AS \"Efectivo\", SUM(\"Debito\") AS \"Debito\", SUM(\"Credito\") AS \"Credito\", SUM(\"Total\") AS \"Total\" FROM (

					SELECT to_char(sessions.create_time, 'DD/MM/YYYY') AS \"Day\", 0 AS \"Efectivo\", 0 AS \"Debito\", 0 AS \"Credito\", SUM(payments.amount) AS \"Total\" 
					FROM sessions INNER JOIN sales_transactions ON sales_transactions.sessions_id = sessions.id 
					INNER JOIN payments ON payments.sales_transactions_id = sales_transactions.id
					WHERE sales_transactions.is_done = true AND sales_transactions.is_canceled = false
					AND sessions.create_time BETWEEN '".$start."' AND '".$end."'
					AND sessions.business_units_id = ".$business."
					GROUP BY to_char(sessions.create_time, 'DD/MM/YYYY')
					
					UNION ALL
					
					SELECT TO_CHAR(sessions.create_time, 'DD/MM/YYYY') AS \"Day\", SUM(payments.amount) AS \"Efectivo\", 0 AS \"Debito\", 0 AS \"Credito\", 0 AS \"Total\"
					FROM sessions
					INNER JOIN sales_transactions ON sales_transactions.sessions_id = sessions.id
					INNER JOIN payments ON payments.sales_transactions_id = sales_transactions.id
					WHERE sales_transactions.is_done = true AND sales_transactions.is_canceled = false
					AND payments.payment_methods_id = 1
					AND sales_transactions.sessions_id IN (".$in_value.")
					AND sales_transactions.business_units_id = ".$business."
					GROUP BY to_char(sessions.create_time, 'DD/MM/YYYY')
					
					UNION ALL
					
					SELECT to_char(sessions.create_time, 'DD/MM/YYYY') AS \"Day\", 0 AS \"Efectivo\", SUM(payments.amount) AS \"Debito\", 0 AS \"Credito\", 0 AS \"Total\"
					FROM sessions
					INNER JOIN sales_transactions ON sales_transactions.sessions_id = sessions.id
					INNER JOIN payments ON payments.sales_transactions_id = sales_transactions.id
					WHERE sales_transactions.is_done = true AND sales_transactions.is_canceled = false
					AND payments.payment_methods_id = 3
					AND sales_transactions.sessions_id IN (".$in_value.")
					AND sales_transactions.business_units_id = ".$business."
					GROUP BY to_char(sessions.create_time, 'DD/MM/YYYY')
					
					UNION ALL
					
					SELECT to_char(sessions.create_time, 'DD/MM/YYYY') AS \"Day\", 0 AS \"Efectivo\", 0 AS \"Debito\", SUM(payments.amount) AS \"Credito\", 0 AS \"Total\"
					FROM sessions
					INNER JOIN sales_transactions ON sales_transactions.sessions_id = sessions.id
					INNER JOIN payments ON payments.sales_transactions_id = sales_transactions.id
					WHERE sales_transactions.is_done = true AND sales_transactions.is_canceled = false
					AND payments.payment_methods_id = 4
					AND sales_transactions.sessions_id IN (".$in_value.")
					AND sales_transactions.business_units_id = ".$business."
					GROUP BY to_char(sessions.create_time, 'DD/MM/YYYY')
					
					) AS \"Data\"
					GROUP BY \"Day\"
					ORDER BY \"Day\" DESC"
			);

			return $query->result();
		}

		function get_sessions_id_by_day($day) {
			$query = $this->db->query(
				"SELECT id AS \"Id\"
				FROM sessions WHERE create_time BETWEEN
				'".$day."T00:00:00' AND '".$day."T23:59:59'
				ORDER BY id ASC"
			);

			return $query->result();
		}
		function get_sessions_by_id($id) {
				$query = $this->db->query("
				SELECT s.id, to_char(s.create_time, 'DD/MM/YYYY HH24:MI') apertura, 
				to_char(s.logout_time, 'DD/MM/YYYY HH24:MI') cierre, p.name as pos, s.image_sales as imagen,
				(SUM(s.close_cash_sales)/count(s.id)) as cash, 
				(SUM(s.close_card_sales)/count(s.id)) as card,
				(SUM(s.close_take_out_cash)/count(s.id)) total_ingreso ,
				((SUM(s.close_cash_sales)+SUM(s.close_card_sales))/count(s.id)) ven_cash_card ,
				SUM(st.gross_total) venta_total, u.first_name as responsable,
				CASE s.is_open WHEN true THEN 'Abierta' ELSE 'Cerrada' END AS estado, st.in_day, st.in_month, st.in_year,
				(sum(s.staff_payment)/count(s.id)) as sueldos, (sum(s.sales_commission)/count(s.id)) as comisiones, s.description
				FROM sessions s
				 JOIN sales_transactions st on s.id = st.sessions_id
				 JOIN business_units b on st.business_units_id = b.id
				 JOIN pos p on s.pos_id = p.id
				 JOIN staff u on s.staff_id = u.id
				WHERE s.id = $id
				GROUP BY to_char(s.create_time, 'DD/MM/YYYY HH24:MI'),to_char(s.logout_time, 'DD/MM/YYYY HH24:MI'), s.id,b.name, p.name, u.first_name, st.in_day, st.in_month, st.in_year, s.image_sales, s.description
				
				");
				return $query->row();
		}

		function get_sessions_by_day($day) {
			$query = $this->db->query(
				"SELECT
				sessions.id AS \"Id\",
				to_char(sessions.create_time, 'HH24:MI:SS') as \"Apertura\",
				to_char(sessions.logout_time, 'HH24:MI:SS') as \"Cierre\",
				(CASE sessions.is_open WHEN true THEN 'Abierta' ELSE 'Cerrada' END) AS \"Estado\",
				CONCAT(staff.first_name, ' ', staff.last_name) AS \"Responsable\",
				pos.name AS \"Pos\",
				COALESCE((SELECT sum(payments.amount)
				 FROM sales_transactions
				 INNER JOIN payments
				 ON payments.sales_transactions_id = sales_transactions.id
				 WHERE payments.payment_methods_id = 1
				 
				 AND sales_transactions.is_done = true AND sales_transactions.is_canceled = false
				 AND sales_transactions.sessions_id = sessions.id), 0) AS \"Efectivo\",
				COALESCE((SELECT sum(payments.amount)
				 FROM sales_transactions
				 INNER JOIN payments
				 ON payments.sales_transactions_id = sales_transactions.id
				 WHERE payments.payment_methods_id = 3
				 
				 AND sales_transactions.is_done = true AND sales_transactions.is_canceled = false
				 AND sales_transactions.sessions_id = sessions.id), 0) AS \"Debito\",
				COALESCE((SELECT sum(payments.amount)
				 FROM sales_transactions
				 INNER JOIN payments
				 ON payments.sales_transactions_id = sales_transactions.id
				 WHERE payments.payment_methods_id = 4
				 
				 AND sales_transactions.is_done = true AND sales_transactions.is_canceled = false
				 AND sales_transactions.sessions_id = sessions.id), 0) AS \"Credito\",
				COALESCE((SELECT sum(payments.amount)
				 FROM sales_transactions
				 INNER JOIN payments ON payments.sales_transactions_id = sales_transactions.id
				 WHERE sales_transactions.is_done = true AND sales_transactions.is_canceled = false
				 AND sales_transactions.sessions_id = sessions.id), 0) AS \"Total\"
				FROM sessions
				INNER JOIN staff ON staff.id = sessions.staff_id
				INNER JOIN pos ON pos.id = sessions.pos_id
				WHERE sessions.create_time BETWEEN '".$day."T00:00:00' AND '".$day."T23:59:59'
				GROUP BY sessions.id, to_char(sessions.create_time, 'HH24:MI:SS'), to_char(sessions.logout_time, 'HH24:MI:SS'),
				(CASE sessions.is_open WHEN true THEN 'Abierta' ELSE 'Cerrada' END),
				CONCAT(staff.first_name, ' ', staff.last_name), pos.name
				ORDER BY sessions.create_time"
			);

			return $query->result();
		}

		function get_last_sessions_by_staff($staff_id) {
			$query = $this->db->query(
				"SELECT
				sessions.id AS \"Id\",
				to_char(sessions.create_time, 'DD/MM/YYYY') as \"Fecha\",
				to_char(sessions.create_time, 'HH24:MI:SS') as \"Apertura\",
				to_char(sessions.logout_time, 'HH24:MI:SS') as \"Cierre\",
				(CASE sessions.is_open WHEN true THEN 'Abierta' ELSE 'Cerrada' END) AS \"Estado\",
				pos.name AS \"Pos\",
				COALESCE((SELECT sum(payments.amount)
				 FROM sales_transactions
				 INNER JOIN payments
				 ON payments.sales_transactions_id = sales_transactions.id
				 WHERE payments.payment_methods_id = 1
				 
				 AND sales_transactions.is_done = true AND sales_transactions.is_canceled = false
				 AND sales_transactions.sessions_id = sessions.id), 0) AS \"Efectivo\",
				COALESCE((SELECT sum(payments.amount)
				 FROM sales_transactions
				 INNER JOIN payments
				 ON payments.sales_transactions_id = sales_transactions.id
				 WHERE payments.payment_methods_id = 3
				 
				 AND sales_transactions.is_done = true AND sales_transactions.is_canceled = false
				 AND sales_transactions.sessions_id = sessions.id), 0) AS \"Debito\",
				COALESCE((SELECT sum(payments.amount)
				 FROM sales_transactions
				 INNER JOIN payments
				 ON payments.sales_transactions_id = sales_transactions.id
				 WHERE payments.payment_methods_id = 4
				 
				 AND sales_transactions.is_done = true AND sales_transactions.is_canceled = false
				 AND sales_transactions.sessions_id = sessions.id), 0) AS \"Credito\",
				COALESCE((SELECT sum(payments.amount)
				 FROM sales_transactions
				 INNER JOIN payments ON payments.sales_transactions_id = sales_transactions.id
				 WHERE sales_transactions.is_done = true AND sales_transactions.is_canceled = false
				 AND sales_transactions.sessions_id = sessions.id), 0) AS \"Total\"
				FROM sessions
				INNER JOIN staff ON staff.id = sessions.staff_id
				INNER JOIN pos ON pos.id = sessions.pos_id
				WHERE sessions.staff_id = ".$staff_id."
				GROUP BY sessions.id, to_char(sessions.create_time, 'DD/MM/YYYY'), to_char(sessions.create_time, 'HH24:MI:SS'), to_char(sessions.logout_time, 'HH24:MI:SS'),
				(CASE sessions.is_open WHEN true THEN 'Abierta' ELSE 'Cerrada' END),
				CONCAT(staff.first_name, ' ', staff.last_name), pos.name
				ORDER BY sessions.create_time DESC
				LIMIT 5"
			);

			return $query->result();
		}
		
		protected function _get_tables_field_alias($table)
	    {
	        $fields='';
	        if(strlen($table) > 0)
	        {
	            $CI =& get_instance();
	           
	            $field_data = $CI->db->field_data($table);
	            foreach ($field_data as $key => $value) 
	            {
	            	$fields .= $table.'.'.$value->name.' AS '.$table.'_'.$value->name.' , ';
	            }
	        }
	        $fields = trim($fields, ',');
	        return $fields;
		}
		
		function get_periods_available () {
			$sql = "SELECT DISTINCT date_part('year', create_time) AS \"year\", date_part('month', create_time) AS \"month\" FROM sessions WHERE date_part('year', create_time) > 2015 ORDER BY \"year\", \"month\" ASC";
			
			$query = $this->db->query($sql);
			$data = $query->result();
			
			$monthNames = array(
				'1' => 'Enero', '2' => 'Febrero', '3' => 'Marzo', '4' => 'Abril',
				'5' => 'Mayo', '6' => 'Junio', '7' => 'Julio', '8' => 'Agosto',
				'9' => 'Septiembre', '10' => 'Octubre', '11' => 'Noviembre',
				'12' => 'Diciembre'
			);
			
			$periodos = array();
			
			foreach ($data as $key => $periodo) {
				$monthLocale = $monthNames[$periodo->month];
			
				array_push($periodos, array(
					'periodo' => $periodo->year.'/'.(intval($periodo->month) < 10 ? '0'.$periodo->month : $periodo->month),
					'name' => $monthLocale.' '.$periodo->year
				));
			}
			
			return $periodos;
		}

		function get_all_session_id_from_day($day) {
			$query = $this->db->query(
				"SELECT id FROM sessions WHERE to_char(create_time, 'YYYY-MM-DD') = '".$day."'"
			);
			$sessions_id = $query->result();
			$in_value = "";

			foreach ($sessions_id as $key => $session) {
				$in_value .= $session->id.($key + 1 !== count($sessions_id) ? ',' : '');
			}

			return $in_value;
		}

		function get_all_session_id_from_range($start, $end) {
			$query = $this->db->query(
				"SELECT id FROM sessions WHERE create_time BETWEEN '".$start."' AND '".$end."'"
			);
			$sessions_id = $query->result();
			$in_value = "";

			foreach ($sessions_id as $key => $session) {
				$in_value .= $session->id.($key + 1 !== count($sessions_id) ? ',' : '');
			}

			return $in_value;
		}

		function total_vta_personal_by_sessions($ids) {
			$in = array();

			foreach ($ids as $key => $value) {
				array_push($in, $value->Id);
			}

			$query = $this->db->query(
				"select sum(payments.amount) as total from sales_transactions
				inner join payments on payments.sales_transactions_id = sales_transactions.id
				where sales_transactions.vta_trabajador = 1 and sales_transactions.sessions_id in (
					".implode(',', $in)."
				)"
			);

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

			return $query->row()->total;
		}

		function total_devolution_by_sessions($ids) {
			$in = array();

			foreach ($ids as $key => $value) {
				array_push($in, $value->Id);
			}

			$query = $this->db->query(
				"SELECT SUM(sales_transactions_details.item_price * sales_transactions_details.refund_quantity) AS total
				FROM sales_transactions
				INNER JOIN sales_transactions_details ON sales_transactions.id = sales_transactions_details.sales_transactions_id
				INNER JOIN credit_notes ON sales_transactions.id = credit_notes.sales_transactions_id
				WHERE sales_transactions_details.refund = 1
				AND credit_notes.create_time
				BETWEEN (select MIN(sessions.create_time) FROM sessions where sessions.id IN (".implode(',',$in)."))
				AND (select MAX(sessions.logout_time) FROM sessions where sessions.id IN (".implode(',',$in)."))"
			);

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

			return $query->row()->total;
		}
    }