<?php
 
 class Sistema_Model_AssessoriasPrepostos extends Agro_Db_Table_Abstract {
 	protected $_schema = 'sistema';
 	protected $_name = 'assessorias_prepostos';
 	protected $_primary = 'id_preposto';

 	public function save(array $dados) {
		$Row = $this->getRow($dados['id_preposto']);
		$Row->id_preposto   = $dados['id_preposto'];
		$Row->id_assessoria = $dados['id_assessoria'];
		$Row->vl_percentual = number_format(str_replace(',', '.', $dados['vl_percentual']), 2, '.', '');
		return $Row->save();
 	}

	public function getComboAssessoria() {
		$select = $this->select()->setIntegrityCheck(false);
		$select->from(array('p' => 'sistema.prepostos'), array('id as id_preposto','ds_nome_preposto', 'id_usuario'));
		$select->joinLeft(array('ap' => 'sistema.assessorias_prepostos'), 'p.id = ap.id_preposto', 'vl_percentual');
		$select->where('p.fl_ativo = true');
		$select->order('p.ds_nome_preposto');
		$Rs = $this->fetchAll($select);

		return $Rs->toArray();
	}

	public function getData($where) {
		$selectCorretor = $this->select()->setIntegrityCheck(false);
		$selectCorretor->from(array('ae' => 'sistema.assessorias_corretores'), "id_assessoria, vl_percentual, 'C' as tipo");
		$selectCorretor->join(array('c' => 'sistema.corretores'), 'ae.id_corretor = c.id', 'id_usuario, c.id as identificador, ds_nome_fantasia as nome');

		$selectPreposto = $this->select()->setIntegrityCheck(false);
		$selectPreposto->from(array('ap' => 'sistema.assessorias_prepostos'), "id_assessoria, vl_percentual, 'P' as tipo");
		$selectPreposto->join(array('p' => 'sistema.prepostos'), 'ap.id_preposto = p.id', 'id_usuario, p.id as identificador, ds_nome_preposto as nome');

		if(is_array($where)) {
			foreach($where as $clause) {
				$field = $clause['field'];
				$operator = $clause['operator'];
				$value = $clause['value'];
				$selectCorretor->where("$field $operator ?", $value);
				$selectPreposto->where("$field $operator ?", $value);
			}
		} elseif ($where != '') {
			$selectCorretor->where($where);
			$selectPreposto->where($where);
		}

	    $select = $this->select()->setIntegrityCheck(false);
        $select->union(array($selectCorretor, $selectPreposto));
        $select->order("nome");
		return $select;
	}

	public function getPercentualByPreposto($idPreposto) {
		$select = $this->select()->setIntegrityCheck(false);
		$select->from(array('ae' => 'sistema.assessorias_prepostos'), 'vl_percentual');
		$select->where('id_preposto = ?', $idPreposto);

		return $this->getAdapter()->fetchOne($select);
	}

	public function getAssessoriaByPreposto($idPreposto, $dtCriacao = '') {
		$select = $this->select()->setIntegrityCheck(false);
		$select->from(array('ae' => 'sistema.assessorias_prepostos'), 'vl_percentual');
		$select->join(array('a' => 'sistema.assessorias'), 'ae.id_assessoria = a.id', 
			array('id', 'cpf_cnpj', 'ds_nome_assessoria', 'ds_email', 'ds_telefone', 'tp_tipo', 'tp_comissao'));
		$select->where('ae.id_preposto = ?', $idPreposto);

		if ($dtCriacao) {
			$select->where("ae.dt_criacao <='".$dtCriacao."'");
		}

		return $this->fetchRow($select);
	}
}