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

class Reports_model extends CI_Model
{


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

	public function getAllProducts()
	{
		$q = $this->db->get('products');
		if ($q->num_rows() > 0) {
			foreach (($q->result()) as $row) {
				$data[] = $row;
			}

			return $data;
		}
	}

	public function getAllCustomers()
	{
		$this->db->where('status',1);
		$q = $this->db->get('customers');
		if ($q->num_rows() > 0) {
			foreach (($q->result()) as $row) {
				$data[] = $row;
			}
			return $data;
		}
	}

	public function topProducts()
	{
		$m = date('Y-m');
		$this->db->select($this->db->dbprefix('products') . ".code as product_code, " . $this->db->dbprefix('products') . ".name as product_name, sum(" . $this->db->dbprefix('sale_items') . ".quantity) as quantity")
			->join('products', 'products.id=sale_items.product_id', 'left')
			->join('sales', 'sales.id=sale_items.sale_id', 'left')
			// ->where('sales.status !=', "DEBE")
			->where('sales.invoice_id !=', NULL)
			->where('sales.estado =', 1) //**********TRJ074 - ALEXANDER ROCA - 23/08/2019**************
			->order_by("sum(" . $this->db->dbprefix('sale_items') . ".quantity)", 'desc')
			->group_by('sale_items.product_id')
			->limit(10)
			->like('sales.date', $m, 'both');
		$q = $this->db->get('sale_items');
		if ($q->num_rows() > 0) {
			foreach (($q->result()) as $row) {
				$data[] = $row;
			}
			return $data;
		}
	}

	public function topProducts1()
	{
		$m = date('Y-m', strtotime('first day of last month'));
		$this->db->select($this->db->dbprefix('products') . ".code as product_code, " . $this->db->dbprefix('products') . ".name as product_name, sum(" . $this->db->dbprefix('sale_items') . ".quantity) as quantity")
			->join('products', 'products.id=sale_items.product_id', 'left')
			->join('sales', 'sales.id=sale_items.sale_id', 'left')
			// ->where('sales.status !=', "DEBE")
			->where('sales.invoice_id !=', NULL)
			->where('sales.estado =', 1) //**********TRJ074 - ALEXANDER ROCA - 23/08/2019**************
			->order_by("sum(" . $this->db->dbprefix('sale_items') . ".quantity)", 'desc')
			->group_by('sale_items.product_id')
			->limit(10)
			->like('sales.date', $m, 'both');
		$q = $this->db->get('sale_items');
		if ($q->num_rows() > 0) {
			foreach (($q->result()) as $row) {
				$data[] = $row;
			}
			return $data;
		}
	}

	public function topProducts3()
	{
		$this->db->select($this->db->dbprefix('products') . ".code as product_code, " . $this->db->dbprefix('products') . ".name as product_name, sum(" . $this->db->dbprefix('sale_items') . ".quantity) as quantity")
			->join('products', 'products.id=sale_items.product_id', 'left')
			->join('sales', 'sales.id=sale_items.sale_id', 'left')
			// ->where('sales.status !=', "DEBE")
			->where('sales.invoice_id !=', NULL)
			->where('sales.estado =', 1) //**********TRJ074 - ALEXANDER ROCA - 23/08/2019**************
			->order_by("sum(" . $this->db->dbprefix('sale_items') . ".quantity)", 'desc')
			->group_by('sale_items.product_id')
			->limit(10)
			->where($this->db->dbprefix('sales') . '.date >= last_day(now()) + interval 1 day - interval 3 month', NULL, FALSE);
		$q = $this->db->get('sale_items');
		if ($q->num_rows() > 0) {
			foreach (($q->result()) as $row) {
				$data[] = $row;
			}
			return $data;
		}
	}

	public function topProducts12()
	{
		$this->db->select($this->db->dbprefix('products') . ".code as product_code, " . $this->db->dbprefix('products') . ".name as product_name, sum(" . $this->db->dbprefix('sale_items') . ".quantity) as quantity")
			->join('products', 'products.id=sale_items.product_id', 'left')
			->join('sales', 'sales.id=sale_items.sale_id', 'left')
			// ->where('sales.status !=', "DEBE")
			->where('sales.invoice_id !=', NULL)
			->where('sales.estado =', 1) //**********TRJ074 - ALEXANDER ROCA - 23/08/2019**************
			->order_by("sum(" . $this->db->dbprefix('sale_items') . ".quantity)", 'desc')
			->group_by('sale_items.product_id')
			->limit(10)
			->where($this->db->dbprefix('sales') . '.date >= last_day(now()) + interval 1 day - interval 12 month', NULL, FALSE);
		$q = $this->db->get('sale_items');
		if ($q->num_rows() > 0) {
			foreach (($q->result()) as $row) {
				$data[] = $row;
			}

			return $data;
		}
	}

	public function getDailySales($year, $month)
	{

		// $myQuery = "SELECT DATE_FORMAT( date,  '%e' ) AS date, COALESCE(sum(total), 0) as total, COALESCE(sum(grand_total), 0) as grand_total,
		// COALESCE(sum(total_tax), 0) as tax, COALESCE(sum(total_discount), 0) as discount FROM (".$this->db->dbprefix('sales').")
		// WHERE DATE_FORMAT( date,  '%Y-%m' ) =  '{$year}-{$month}' AND estado = 1 AND status != 'DEBE'
		// GROUP BY DATE_FORMAT( date,  '%e' )";

		$myQuery = " SELECT DATE_FORMAT( date,  '%e' ) AS date,
		COALESCE(sum(IF(currency='USD', ROUND(total * exchange, 2), total)), 0) as total,
		COALESCE(sum(IF(currency='USD', ROUND(grand_total * exchange, 2), grand_total)), 0) as grand_total,
		COALESCE(sum(IF(currency='USD', ROUND(total_tax * exchange, 2), total_tax)), 0) as tax,
		COALESCE(sum(IF(currency='USD', ROUND(total_discount * exchange, 2), total_discount)), 0) as discount
		FROM (" . $this->db->dbprefix('sales') . ")
		WHERE DATE_FORMAT( date,  '%Y-%m' ) =  '{$year}-{$month}' AND estado = 1 AND invoice_id IS NOT NULL
		GROUP BY DATE_FORMAT( date,  '%e' )";

		$q = $this->db->query($myQuery, false);
		if ($q->num_rows() > 0) {
			foreach (($q->result()) as $row) {
				$data[] = $row;
			}
			return $data;
		}
		return FALSE;
	}


	public function getMonthlySales($year)
	{

		$myQuery = "SELECT DATE_FORMAT( date,  '%c' ) AS date,
		COALESCE(sum(IF(currency='USD', ROUND(total * exchange, 2), total)), 0) as total,
		COALESCE(sum(IF(currency='USD', ROUND(grand_total * exchange, 2), grand_total)), 0) as grand_total,
		COALESCE(sum(IF(currency='USD', ROUND(total_tax * exchange, 2), total_tax)), 0) as tax,
		COALESCE(sum(IF(currency='USD', ROUND(total_discount * exchange, 2), total_discount)), 0) as discount
		FROM (" . $this->db->dbprefix('sales') . ")
		WHERE DATE_FORMAT( date,  '%Y' ) =  '{$year}' AND estado = 1 AND invoice_id IS NOT NULL
		GROUP BY date_format( date, '%c' ) ORDER BY date_format( date, '%c' ) ASC";

		$q = $this->db->query($myQuery, false);
		if ($q->num_rows() > 0) {
			foreach (($q->result()) as $row) {
				$data[] = $row;
			}
			return $data;
		}
		return FALSE;
	}

	public function getTotalSalesforCustomer($customer_id, $user = NULL, $start_date = NULL, $end_date = NULL)
	{
		if ($start_date && $end_date) {
			$this->db->where('date >=', $start_date);
			$this->db->where('date <=', $end_date);
		}
		if ($user) {
			$this->db->where('created_by', $user);
		}

		// $this->db->where('status !=', "DEBE")
		$this->db->where('invoice_id !=', NULL)
			->where('estado =', 1); //**********TRJ074 - ALEXANDER ROCA - 23/08/2019**************
		$q = $this->db->get_where('sales', array('customer_id' => $customer_id));
		return $q->num_rows();
	}

	public function getTotalSalesValueforCustomer($customer_id, $user = NULL, $start_date = NULL, $end_date = NULL)
	{
		$this->db->select('sum(total) as total');
		if ($start_date && $end_date) {
			$this->db->where('date >=', $start_date);
			$this->db->where('date <=', $end_date);
		}
		if ($user) {
			$this->db->where('created_by', $user);
		}

		// $this->db->where('status !=', "DEBE")
		$this->db->where('invoice_id !=', NULL)
			->where('estado =', 1); //**********TRJ074 - ALEXANDER ROCA - 23/08/2019**************
		$q = $this->db->get_where('sales', array('customer_id' => $customer_id));
		if ($q->num_rows() > 0) {
			$s = $q->row();
			return $s->total;
		}
		return FALSE;
	}

	public function getAllStaff()
	{

		$q = $this->db->get('users');
		if ($q->num_rows() > 0) {
			foreach (($q->result()) as $row) {
				$data[] = $row;
			}
			return $data;
		}
		return FALSE;
	}

	public function getTotalSales($start, $end)
	{
		// $this->db->select('count(id) as total, sum(COALESCE(grand_total, 0)) as total_amount, SUM(COALESCE(paid, 0)) as paid, SUM(COALESCE(total_tax, 0)) as tax', FALSE)
		$this->db->select('count(id) as total, sum(COALESCE(IF(currency="USD", ROUND(grand_total * exchange, 2), grand_total),0)) as total_amount, SUM(COALESCE(IF(currency="USD", ROUND(paid * exchange, 2), paid), 0)) as paid, SUM(COALESCE(IF(currency="USD", ROUND(total_tax * exchange, 2), total_tax), 0)) as tax', FALSE)
			->where("date >= '{$start}' and date <= '{$end}'", NULL, FALSE);

		// IF(currency="USD", ROUND(total_tax * exchange, 2), total_tax)

		$this->db->where('estado =', 1) //**********TRJ074 - ALEXANDER ROCA - 23/08/2019**************
			// ->where('sales.status !=', "DEBE");
			->where('sales.invoice_id !=', NULL);

		$q = $this->db->get('sales');
		if ($q->num_rows() > 0) {
			return $q->row();
		}
		return FALSE;
	}

	public function getTotalPurchases($start, $end)
	{
		// $this->db->select('count(id) as total, sum(COALESCE(grand_total, 0)) as total_amount', FALSE)
		$this->db->select('count(id) as total, sum(COALESCE(IF(currency="USD", ROUND(grand_total * exchange, 2), grand_total),0)) as total_amount', FALSE)
			->where("date >= '{$start}' and date <= '{$end}'", NULL, FALSE);
		$this->db->where('estado =', 1); //**********TRJ074 - ALEXANDER ROCA - 23/08/2019**************
		$q = $this->db->get('purchases');
		if ($q->num_rows() > 0) {
			return $q->row();
		}
		return FALSE;
	}

	public function getTotalExpenses($start, $end)
	{
		// $this->db->select('count(id) as total, sum(COALESCE(amount, 0)) as total_amount', FALSE)
		$this->db->select('count(id) as total, sum(COALESCE(IF(currency="USD", ROUND(amount * exchange, 2), amount),0)) as total_amount', FALSE)
			->where("date >= '{$start}' and date <= '{$end}'", NULL, FALSE);
		// $this->db->where('estado !=', 0);
		$q = $this->db->get('expenses');
		if ($q->num_rows() > 0) {
			return $q->row();
		}
		return FALSE;
	}
	public function consultarVentas($data)
	{
		$this->db->select(
			$this->db->dbprefix('sales') . ".date as 'fecha_venta',
			if(" . $this->db->dbprefix('sales') . ".expiration_date is null,''," . $this->db->dbprefix('sales') . ".expiration_date) AS 'fecha_vencimiento',
			IF(document_type = 1,'Boleta',IF(document_type = 2,'Factura','Nota de Venta')) AS 'tipo_comprobante',
			" . $this->db->dbprefix('sales') . ".invoice_id AS 'nro_comprobante',
			" . $this->db->dbprefix('sales') . ".customer_name AS 'nombre_cliente',
			IF(" . $this->db->dbprefix('customers') . ".cf2 = '', " . $this->db->dbprefix('customers') . ".cf1, " . $this->db->dbprefix('customers') . ".cf2) AS 'nro_documento',
			" . $this->db->dbprefix('sales') . ".currency AS moneda,
			IF(" . $this->db->dbprefix('sales') . ".estado = 1, " . $this->db->dbprefix('sales') . ".affected,'0.00') AS 'afecto',
            IF(" . $this->db->dbprefix('sales') . ".estado = 1, " . $this->db->dbprefix('sales') . ".total_tax,'0.00') AS 'IGV',
            IF(" . $this->db->dbprefix('sales') . ".estado = 1, " . $this->db->dbprefix('sales') . ".non_affected,'0.00') AS 'no_afecto',
            IF(" . $this->db->dbprefix('sales') . ".estado = 1, " . $this->db->dbprefix('sales') . ".exonerated,'0.00') AS 'exonerado',
            IF(" . $this->db->dbprefix('sales') . ".estado = 1, " . $this->db->dbprefix('sales') . ".order_discount,'0.00') AS 'descuento',
			IF(" . $this->db->dbprefix('sales') . ".estado = 1, " . $this->db->dbprefix('sales') . ".grand_total,'0.00') AS 'total',
			IF(" . $this->db->dbprefix('sales') . ".estado = 1, " . $this->db->dbprefix('sales') . ".paid,'0.00') AS 'pagado',
			" . $this->db->dbprefix('sales') . ".payment_method_code_sunat AS 'forma_pago',
			IF(" . $this->db->dbprefix('sales') . ".estado = 1, 'Activo', 'Anulado') AS 'estado_reg',
			IF(".$this->db->dbprefix('sales').".document_type = 2,IF(".$this->db->dbprefix('sales').".flg_response = 1 and ".$this->db->dbprefix('sales').".error_code=0 and ".$this->db->dbprefix('sales').".estado = 0 and ".$this->db->dbprefix('sales').".response_descrip like '%anulad%','Anulado',
			IF(".$this->db->dbprefix('sales').".flg_response = 1 AND ".$this->db->dbprefix('sales').".error_code = 0 AND ".$this->db->dbprefix('sales').".estado = 0 AND ".$this->db->dbprefix('sales').".response_descrip LIKE '%pendiente%', 'Anulacion pendiente', 
            IF(".$this->db->dbprefix('sales').".flg_response = 1 AND ".$this->db->dbprefix('sales').".error_code = 0 AND ".$this->db->dbprefix('sales').".estado = 1, 'Procesado', 'Rechazado'))),
			IF(".$this->db->dbprefix('sales').".document_type = 1,IF(".$this->db->dbprefix('sales').".flg_response = 1 and ".$this->db->dbprefix('sales').".error_code=0 and ".$this->db->dbprefix('sales').".response_descrip like '%genero%','Sin envio',
			if(".$this->db->dbprefix('sales').".flg_response = 1 and ".$this->db->dbprefix('sales').".error_code=0 and ".$this->db->dbprefix('sales').".response_descrip like '%ACEPTAD%','Procesado',
			if(".$this->db->dbprefix('sales').".flg_response = 1 or ".$this->db->dbprefix('sales').".error_code=0 and ".$this->db->dbprefix('sales').".response_descrip like '%anulad%','Anulado',                
			if(".$this->db->dbprefix('sales').".flg_response = 1 or ".$this->db->dbprefix('sales').".error_code=0 and ".$this->db->dbprefix('sales').".response_descrip like '%pendiente%', 'Anulacion pendiente','Error de envio')))),'')) AS 'estado_sunat',

			IF(".$this->db->dbprefix('sales').".response_descrip IS NOT NULL,IF(".$this->db->dbprefix('sales').".response_descrip like '%genero%','SYS: Pendiente de envio a SUNAT.',".$this->db->dbprefix('sales').".response_descrip),'') AS 'descripcion',
			IF(document_type <> 3,IF(" . $this->db->dbprefix('sales') . ".flg_response IS NULL AND " . $this->db->dbprefix('sales') . ".response_descrip IS NULL, 'SYS: consultar con el administrador del sistema', ''),'') AS 'observaciones',
			" . $this->db->dbprefix('users') . ".username AS 'creado_por',"
			. $this->db->dbprefix('locals') . ".name AS 'local'"
		)
			->join('users', 'sales.created_by=users.id', 'left')
			->join('customers', 'customers.id=sales.customer_id', 'left')
			->join('locals','sales.local_id=locals.id', 'left');
			
			if ($data['estado']!= null) {
				if ($data['estado'] == 2) {
					$status = 0;
				} else{
					$status = $data['estado'];
				}
				if ($status != 3) {
					$this->db->where('sales.estado =', $status);
				}
			}
		if ($data['fecha_inicio'] != "") {
			$this->db->where('sales.date >=', $data['fecha_inicio'] . " 00:00:00");
		}
		if ($data['fecha_fin'] != "") {
			$fecha_fin = strtotime('+1 day', strtotime($data['fecha_fin']));
			$this->db->where('sales.date <', date('Y-m-d 00:00:00', $fecha_fin));
		}
		if ($data['cliente'] != "0") {
			$this->db->where('customers.id =', $data['cliente']);
		}
		if ($data['usuario'] != "") {
			$this->db->where('users.id =', $data['usuario']);
		}
		if ($data['local'] != "") {
			$this->db->where('sales.local_id =', $data['local']);
		}
		if ($data['traders_id'] != "") {
			$this->db->where('sales.traders_id =', $data['traders_id']);
		}
		$this->db->order_by($this->db->dbprefix('sales') . ".date", 'asc');
		$q = $this->db->get('sales');
		if ($q->num_rows() > 0) {
			foreach (($q->result()) as $row) {
				$getdata[] = $row;
			}
			return $getdata;
		}
		return false;
	}

	public function getTotalComprobantesPorMes($year, $month){
		$myQuery = "SELECT date as fecha,
						CASE
							WHEN document_type = 1 THEN 'BOLETAS ELECTRÓNICAS'
							WHEN document_type = 2 THEN 'FACTURAS ELECTRÓNICAS'
							WHEN document_type = 3 THEN 'NOTAS DE VENTA'
						END as tipo_comprobante,
						COUNT(document_type) as cantidad_comprobantes, 
						COALESCE(sum(IF(currency='USD', ROUND(grand_total * exchange, 2), grand_total)), 0) as grand_total
					from (" . $this->db->dbprefix('sales') . ")
					WHERE DATE_FORMAT( date,  '%Y-%m' ) =  '{$year}-{$month}' AND estado = 1 AND invoice_id IS NOT NULL
					GROUP by DATE_FORMAT( date,  '%Y-%m' ),document_type";
		$q = $this->db->query($myQuery, false);
		if ($q->num_rows() > 0) {
			foreach (($q->result()) as $row) {
				$data[] = $row;
			}
			return $data;
		}
		return FALSE;
	}

	public function getTotalComprobantesPorAnio($year){
		$myQuery = "SELECT date as fecha,
			CASE
				WHEN document_type = 1 THEN 'BOLETAS ELECTRÓNICAS'
				WHEN document_type = 2 THEN 'FACTURAS ELECTRÓNICAS'
				WHEN document_type = 3 THEN 'NOTAS DE VENTA'
			END as tipo_comprobante,
			COUNT(document_type) as cantidad_comprobantes, 
			COALESCE(sum(IF(currency='USD', ROUND(grand_total * exchange, 2), grand_total)), 0) as grand_total
		FROM (" . $this->db->dbprefix('sales') . ")
		WHERE DATE_FORMAT( date,  '%Y' ) =  '{$year}' AND estado = 1 AND invoice_id IS NOT NULL
		GROUP by DATE_FORMAT( date,  '%Y' ),document_type";
		$q = $this->db->query($myQuery, false);
		if ($q->num_rows() > 0) {
		foreach (($q->result()) as $row) {
		$data[] = $row;
		}
		return $data;
		}
		return FALSE;
	}
	public function getAllLocals(){
		$q = $this->db->get('locals');
		if ($q->num_rows() > 0) {
			foreach (($q->result()) as $row) {
				$data[] = $row;
			}
			return $data;
		}
		return FALSE;
	}
}
