<?php
namespace src\models;
use \core\Model;

class Deliverie extends Model {

	public static function saveDeliverie($delivery) {
		$ref = Deliverie::insert($delivery)
		->execute();

		return $ref;
	}

	public static function updateDeliverie($data, $delivery) {
		Deliverie::update($data)
			->where('id', $delivery)
		->execute();
	}

	public static function saveItemsDelivery($deliveryItem) {
		$ref = Deliverie::insert($deliveryItem)
			->table('deliveries_items')
		->execute();

		return $ref;
	}

	public static function updateItemDelivery($data, $ref) {
		Deliverie::update($data)
			->table('deliveries_items')
			->where('id', $ref)
		->execute();
	}

	public static function getAmountBalance($request) {
		$data = Deliverie::select()
			->where('id_request', $request)
		->sum('balance');

		return $data;
	}

	public static function getDeliveries($requestId) {
		$data = Deliverie::select()
			->where('id_request', $requestId)
		->get();

		return $data;
	}

	public static function getUniqueDelivery($idDelivery) {
		$id = Deliverie::select('id_deliverie')
			->table('deliveries_items')
			->where('id_deliverie', $idDelivery)
		->one();

		return $id['id_deliverie'];
	}

	public static function getDelivery($idDelivery, $product) {
		$id = Deliverie::select('id_deliverie')
			->table('deliveries_items')
			->where('id_deliverie', $idDelivery)
			->where('id_product', $product)
		->one();

		return $id['id_deliverie'];
	}

	public static function getDeliveryInfo($delivery) {
		$data = Deliverie::select()
			->where('id', $delivery)
		->one();

		return $data;
	}

	public static function getDeliveryItems($delivery) {
		$data = Deliverie::select()
			->table('deliveries_items')
			->where('id_deliverie', $delivery)
		->get();

		return $data;
	}

	public static function getDeliveryItem($delivery) {
		$data = Deliverie::select()
			->table('deliveries_items')
			->where('id', $delivery)
		->one();

		return $data;
	}

	public static function getAllSituation() {
		$data = Deliverie::select()
			->table('delivery_situation')
		->get();

		return $data;
	}

	public static function getSituationName($ref) {
		$data = Deliverie::select('name')
			->table('delivery_situation')
			->where('id', $ref)
		->one();

		return $data['name'];
	}

	public static function getDeliveriesCanceled($deliverie) {
		$c = Deliverie::select()
			->table('deliveries_items')
			->where('status', '<', 6)
			->andWhere('id_deliverie', $deliverie)
		->count();

		return $c;
	}

	public static function getLastDeliverieItemActive($idDeliverie) {
		$data = Deliverie::select()
			->table('deliveries_items')
			->where('status', '!=', 6)
			->andWhere('status', '!=', 7)
			->andWhere('id_deliverie', $idDeliverie)
		->last('id');

		return $data;
	}

	public static function existsShippingInvoice($number, $provider) {
		$data = Deliverie::select()
			->table('deliveries_items')
			->where('shipping_invoice', $number)
		->one();

		return $data;
	}

	// Ainda não usado
	public static function countItemsDelivery($deliverie) {
		$c = Deliverie::select()
			->table('deliveries_items')
			->where('id_deliverie', $deliverie)
		->count();

		return $c;
	}

	public static function getSalesThisYear($date_init, $months) {
        $this_year = array();

        for($i=0;$i<$months;$i++) {
			$first = date('Y-m-d', strtotime('+ '.$i.' Month', strtotime($date_init)));
			$last = date('Y-m-t', strtotime($first));

			$this_year[] = Deliverie::select()
				->table('deliveries_items')
				->where('status', 6)
				->where('date_deliverie', '>=', $first)
				->where('date_deliverie', '<=', $last)
			->sum('value_delivery_balance');
        }

        return $this_year;
    }


    // Relatórios
    public static function getResultReports($offset, $limit, $filter) {
    	$data = Deliverie::select()
    		->table('deliveries_items')
    		->where($filter['d_init'], $filter['d_init_simbol'], $filter['d_init_value'])
    		->where($filter['d_end'], $filter['d_end_simbol'], $filter['d_end_value'])
    		->where($filter['d_situation'], $filter['d_situation_simbol'], $filter['d_situation_value'])
    		->where($filter['d_request'], $filter['d_request_simbol'], $filter['d_request_value'])
    		->where($filter['d_shipping'], $filter['d_shipping_simbol'], $filter['d_shipping_value'])
    		->where($filter['d_provider'], $filter['d_provider_simbol'], $filter['d_provider_value'])
    		->where($filter['d_pgroup'], $filter['d_pgroup_simbol'], $filter['d_pgroup_value'])
    		->where($filter['d_product'], $filter['d_product_simbol'], $filter['d_product_value'])
    		->where($filter['d_client'], $filter['d_client_simbol'], $filter['d_client_value'])
    		->where($filter['d_shipp_company'], $filter['d_shipp_company_simbol'], $filter['d_shipp_company_value'])
    		->offset($offset)
    		->limit($limit)
    		->orderBy(['date_deliverie' => 'asc'])
    	->get();

    	return $data;
    }

}