<?php
    class sync_model extends CI_Model
    {
    	function __construct()
        {
            parent::__construct();
		}
		
		function get_sync_group_by_day($start, $end) {
			$query = $this->db->query(
				"SELECT \"Day\", SUM(\"Total\") AS \"Total\", SUM(\"EasyDoc\") AS \"EasyDoc\", SUM(\"Sap\") AS \"Sap\" FROM (

					SELECT to_char(create_time, 'DD/MM/YYYY') AS \"Day\", COUNT(*) AS \"Total\", 0 AS \"EasyDoc\", 0 AS \"Sap\"
					FROM sales_transactions 
					WHERE is_done = true AND is_canceled = false
					AND create_time BETWEEN '".$start."' AND '".$end."'
					AND folio_doc is not null and document_types_id = 39
					GROUP BY to_char(create_time, 'DD/MM/YYYY')
					
					UNION ALL
					
					SELECT to_char(create_time, 'DD/MM/YYYY') AS \"Day\", 0 AS \"Total\", COUNT(*) AS \"EasyDoc\", 0 AS \"Sap\"
					FROM sales_transactions 
					WHERE is_done = true AND is_canceled = false
					AND create_time BETWEEN '".$start."' AND '".$end."'
					AND folio_doc is not null and document_types_id = 39
					and in_portal = 1
					GROUP BY to_char(create_time, 'DD/MM/YYYY')
					
					UNION ALL
					
					SELECT to_char(create_time, 'DD/MM/YYYY') AS \"Day\", 0 AS \"Total\", 0 AS \"EasyDoc\", COUNT(*) AS \"Sap\"
					FROM sales_transactions 
					WHERE is_done = true AND is_canceled = false
					AND create_time BETWEEN '".$start."' AND '".$end."'
					AND folio_doc is not null and document_types_id = 39
					and in_sap = 1
					GROUP BY to_char(create_time, 'DD/MM/YYYY')
					
					) AS \"Data\"
					GROUP BY \"Day\"
					ORDER BY \"Day\" DESC"
			);

			return $query->result();
		}

		function get_sync_by_day($day) {
			$query = $this->db->query(
				"select
				to_char(create_time, 'HH24:MI:SS') AS \"Hour\", net_total as \"Total\", folio_doc as \"Folio\",
				in_portal AS \"EasyDoc\", in_sap AS \"Sap\", sap_status AS \"Error\"
				from sales_transactions
				where is_done = true and folio_doc is not null and document_types_id = 39
				and create_time between '".$day."T00:00:00' and '".$day."T23:59:59'
				order by cast(folio_doc as integer) asc"
			);

			return $query->result();
		}
		
		function get_periods_available () {
			$sql = "SELECT DISTINCT date_part('year', create_time) AS \"year\", date_part('month', create_time) AS \"month\" FROM sales_transactions 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;
		}
    }