public function getWhere($params = array())
{
if (count($this->_columns) == 0) {
throw new ZendT_Exception_Error('Favor adicionar as colunas!');
}
if ($params['filter_json']) {
if (!is_array($this->_profileDefault['value'])) {
$this->_profileDefault['value'] = array();
}
$whereJson = ZendT_Db_Where::fromJson(stripslashes($params['filter_json']));
foreach ($whereJson->getFilters() as $key => $val) {
$valueFilter = $val['value'];
if (is_array($valueFilter)) {
$valueFilter = implode(";", $valueFilter);
}
$this->_profileDefault['value'][$val['field']] = $val['operation'] . $valueFilter;
}
}
if (is_array($this->_profileDefault['value'])) {
foreach ($this->_profileDefault['value'] as $aliasColumn => $value) {
if (isset($params[$aliasColumn]) && $params[$aliasColumn]) {
$value = $params[$aliasColumn];
}
if ($value) {
$arrayNull = array('NULO', 'NULL', 'VAZIO');
/* Alterar também em: library\ZendT\Db\Where.php */
if (!in_array(strtoupper($value), $arrayNull) && !in_array(strtoupper(substr($value, 1)), $arrayNull) && !in_array(strtoupper(substr($value, 2)), $arrayNull)) {
$value = $this->_parseValue($value, $this->_columns[$aliasColumn]['type']);
}
if (isset($this->_columns[$aliasColumn]['expression'])) {
$params['expression-' . $aliasColumn] = $value;
} else {
$params[$this->_columns[$aliasColumn]['aliasTable'] . '-' . $this->_columns[$aliasColumn]['columnName']] = $value;
}
}
}
}
if (count($params) > 0) {
$columns = $this->getColumnsMapper();
$where = ZendT_Db_Where::fromAutoFilter($params, $columns);
} else {
$where = false;
}
return $where;
}
/**
*
* @param string $value
* @param string $column
* @return \ZendT_Grid_Data
*/
public function suggest($value, $columnAlias, $profileId = '')
{
if ($profileId) {
$_profile = new Profile_Model_ObjectView_Mapper();
$_profile->setId($profileId)->retrieve();
$config = unserialize($_profile->getConfig());
$this->parseExprProfile($config);
}
$configColumns = $this->getColumns()->toArray();
$mappers = $this->getColumns()->getColumnsMapper()->getColumnsMapper();
$listOptions = $configColumns[$columnAlias]['listOptions'];
if (!$listOptions && $mappers[$columnAlias]['mapper'] instanceof ZendT_Type_String && $mappers[$columnAlias]['mapper']->getListOptions() !== false) {
$listOptions = $mappers[$columnAlias]['mapper']->getListOptions();
}
/**
* @todo melhorar essa implementação
*/
if ($listOptions) {
foreach ($listOptions as $key => $value) {
$rows[] = array($key => $value);
}
return $rows;
}
$filters = Zend_Controller_Front::getInstance()->getRequest()->getParam('filters');
$paramValid = array();
$paramsAutoFilter = array();
$notIn = array();
$binds = array();
if ($filters) {
$filters = explode('&', $filters);
foreach ($filters as $data) {
$data = str_replace('!=', '<>', $data);
list($columnName, $valueFilter) = explode('=', $data);
$valueFilter = str_replace('<>', '!=', $valueFilter);
$columnName = str_replace('-multiple', '', $columnName);
if (!$valueFilter && $columnName != $columnAlias) {
continue;
}
if ($mappers[$columnName]) {
if ($mappers[$columnName]['expression']) {
$fieldSearch = 'expression-' . $columnName;
} else {
$fieldSearch = str_replace('.', '-', $configColumns[$columnName]['column']);
}
if ($columnName == $columnAlias) {
if ($valueFilter) {
$notIn[$fieldSearch] = true;
} else {
continue;
}
}
$paramsAutoFilter[$fieldSearch] = $valueFilter;
$paramValid[$columnName] = true;
if (is_array($configColumns[$columnName]['bind'])) {
$binds[$columnName] = $configColumns[$columnName]['bind'];
}
}
}
}
foreach ($configColumns as $columnName => $column) {
if ($column['required'] && !$paramValid[$columnName]) {
throw new ZendT_Exception_Alert($column['required']);
}
}
$_whereAutoFilter = ZendT_Db_Where::fromAutoFilter($paramsAutoFilter, $mappers, $notIn, $binds);
if (method_exists($this, 'setWhere')) {
$this->setWhere($_whereAutoFilter);
}
$rows = array();
if ($listOptions) {
foreach ($listOptions as $key => $value) {
$rows[] = array($key => $value);
}
} else {
if ($configColumns[$columnAlias]['expression']) {
$column = "(" . $configColumns[$columnAlias]['expression'] . ")";
} else {
$column = $configColumns[$columnAlias]['aliasTable'] . '.' . $configColumns[$columnAlias]['columnName'];
}
$autoCompleteFilter = $configColumns[$columnAlias]['autocomplete-filter'];
if ($autoCompleteFilter) {
$field = explode(".", $autoCompleteFilter);
$cmdSelect = "SELECT " . $field[1] . " as " . $columnAlias . " FROM " . $field[0] . " filtro WHERE EXISTS ( SELECT 1 ";
$cmdFrom = " FROM " . $this->_getSqlBase();
$cmdWhereFilter = " AND " . $column . " = filtro." . $field[1] . ")";
$cmdRownum = " AND ROWNUM = 1 ";
$cmdLimit = " LIMIT 1 ";
} else {
$cmdSelect = "SELECT DISTINCT " . $column . " as " . $columnAlias;
$cmdFrom = " FROM " . $this->_getSqlBase();
$cmdRownum = " AND ROWNUM <= 30000 ";
$cmdLimit = " LIMIT 30000 ";
}
$whereGroup = new ZendT_Db_Where_Group();
if ($_whereAutoFilter) {
$whereGroup->addWhere($_whereAutoFilter);
}
/**
* Avalia se existe algum Where específico do MapperView
* colocando o mesmo dentro do objeto que agrupa os wheres
*/
$_whereMapperView = $this->_getWhere($postData, $_whereAutoFilter);
if ($_whereMapperView) {
$whereGroup->addWhere($_whereMapperView);
}
$where = new ZendT_Db_Where();
if ($configColumns[$columnAlias]['expression']) {
$columnFilter = new Zend_Db_Expr($column);
} else {
$columnFilter = $column;
}
if ($value) {
$where->addFilter($columnFilter, $value, '?%', $mappers[$columnAlias]['mapper']);
$whereGroup->addWhere($where);
}
/**
* Monta o comando Where
*/
$binds = $whereGroup->getBinds();
$cmdWhere = " WHERE " . $whereGroup->getSqlWhere();
$cmdOrderBy = " ORDER BY 1 ";
$oracle = false;
if ($oracle) {
$sql = $cmdSelect . $cmdFrom . $cmdWhere . $cmdRownum . $cmdWhereFilter . $cmdGroupBy . $cmdOrderBy;
} else {
$sql = $cmdSelect . $cmdFrom . $cmdWhere . $cmdWhereFilter . $cmdGroupBy . $cmdOrderBy . $cmdLimit;
}
$this->_prepareSql($sql, $binds, 'full');
/**
* Pega os dados
*/
$stmt = $this->getModel()->getAdapter()->query($sql, $binds);
$data = new ZendT_Grid_Data($stmt, $mappers, true);
$rows = $data->getRows();
}
return $rows;
}
/**
*
* @return \ZendT_Db_Where
*/
public function getWhere($onlyValidParam = false)
{
$params = Zend_Controller_Front::getInstance()->getRequest()->getParams();
$columnsMapper = $this->_mapper->getColumns()->toArray();
$columns = $this->_mapper->getColumns()->getColumnsMapper();
$columns->add('*', get_class($this->_mapper));
$paramValid = array();
$binds = array();
/**
* Verifica se no profile existe filtro padrão configurado
*/
$columnsFilters = $this->_options['cols-filter']['fields'];
#var_dump($columnsMapper);die;
foreach ($params as $key => $val) {
if (!array_key_exists($key, $columnsFilters)) {
if ($columnsMapper[$key]) {
$value = $params[$key];
if (isset($columnsMapper[$key]['listOptions'][$value])) {
$value = $columnsMapper[$key]['listOptions'][$value];
}
$columnsFilters[$key] = array('label' => $columnsMapper[$key]['label'], 'value' => $value);
}
}
}
#var_dump($columnsFilters);die;
if (isset($columnsFilters)) {
foreach ($columnsFilters as $columnName => $column) {
/* echo $columnName;
print_r($this->_options);
exit; */
if ($columnsMapper[$columnName]['expression']) {
$field = 'expression-' . $columnName;
} else {
$field = str_replace('.', '-', $columnsMapper[$columnName]['column']);
}
$label = $columnsFilters[$columnName]['label'];
if (!$label) {
$label = $columnsMapper[$columnName]['label'];
}
if ($params[$columnName . '-multiple']) {
$this->_params[$columnName] = $params[$columnName . '-multiple'];
$labelWhere[str_replace('-', '.', $field)] = $label;
$labelWhere[$columnName] = $label;
$params[$field] = $params[$columnName . '-multiple'];
$paramValid[$columnName] = true;
unset($params[$columnName . '-multiple']);
if (is_array($columnsMapper[$columnName]['bind'])) {
$binds[$columnName] = $columnsMapper[$columnName]['bind'];
}
continue;
}
if ($params[$columnName]) {
$this->_params[$columnName] = $params[$columnName];
$labelWhere[str_replace('-', '.', $field)] = $label;
$labelWhere[$columnName] = $label;
$params[$field] = $params[$columnName];
$paramValid[$columnName] = true;
if (is_array($columnsMapper[$columnName]['bind'])) {
$binds[$columnName] = $columnsMapper[$columnName]['bind'];
}
unset($params[$columnName]);
continue;
}
if (isset($params[$columnName . '-multiple'])) {
unset($params[$columnName . '-multiple']);
}
#if ($params[str_replace('.','-',$columnsMapper[$columnName]['column'])]){
# continue;
#}
$valueParse = $columnsFilters[$columnName]['value'];
if (in_array($columnsMapper[$columnName]['type'], array('Date', 'DateTime'))) {
$values = array();
if (strpos($valueParse, ';')) {
$sep = ';';
$values = explode(';', $valueParse);
} else {
if (strpos($valueParse, ' ')) {
$sep = ' ';
$values = explode(' ', $valueParse);
} else {
if ($config['value'] != '') {
$sep = '';
$values = array($valueParse);
} else {
if ($valueParse) {
$sep = '';
$values = array($valueParse);
}
}
}
}
$valueParse = '';
foreach ($values as $value) {
$date = ZendT_Type_Date::parse($value, $columnsMapper[$columnName]['type']);
if ($valueParse) {
$valueParse .= $sep . str_replace(" ", "-", $date->get());
} else {
$valueParse = str_replace(" ", "-", $date->get());
}
}
} else {
if (strtolower(substr($valueParse, 0, 5)) == 'logon') {
$levels = explode('.', $valueParse);
$_sessionValue = $_SESSION;
foreach ($levels as $level) {
$_sessionValue = $_sessionValue[$level];
}
$valueParse = $_sessionValue;
}
}
if ($valueParse) {
$labelWhere[str_replace('-', '.', $field)] = $label;
$labelWhere[$columnName] = $label;
$params[$field] = $valueParse;
$paramValid[$columnName] = true;
$this->_params[$columnName] = $valueParse;
if (is_array($columnsMapper[$columnName]['bind'])) {
$binds[$columnName] = $columnsMapper[$columnName]['bind'];
}
}
}
}
#
foreach ($columnsMapper as $columnName => $column) {
if ($column['required'] && !$paramValid[$columnName]) {
throw new ZendT_Exception_Alert($column['required']);
}
}
#var_dump($params);die;
$where = ZendT_Db_Where::fromAutoFilter($params, $columns, null, $binds);
if (method_exists($this->_mapper, 'setWhere')) {
$this->_mapper->setWhere($where);
}
if (method_exists($this->_mapper, 'paramIsValid')) {
$this->_mapper->paramIsValid($where);
}
if ($onlyValidParam) {
return $where;
}
/**
* Trata parâmetros a serem impressos no PDF e XLS
*/
#print_r($labelWhere);
if ($this->_options['printLabelFilters']) {
$this->_labelFilters = $where->getFriendlyFilter($labelWhere);
/**
* Realiza a substituição dos ids pela descrição dos campos configurados na seeker
*/
foreach ($this->_configColumns as $array => $field) {
if (is_array($field['seeker'])) {
$newfield = $field['aliasTable'] . "." . $field['columnName'];
/*
* Monta uma lista dos ids a partir da label atual para aplicar filtro no banco
*/
$value = $this->_labelFilters[$newfield]['value'];
$union = '';
if (strpos($value, ',') !== false) {
$values = explode(',', $value);
$union = ',';
} else {
$values = explode(' e ', $value);
$union = ' e ';
}
$value = implode(';', $values);
$_mapperView = new $field['seeker']['mapperView']();
$_whereLabel = $_mapperView->getColumns()->mountWhere('id', $value);
$data = $_mapperView->recordset($_whereLabel);
/**
* Define os campos que serão exibidos, conforme a quantidade de labels informada
*/
$countLabels = 0;
$maxLabels = !$field['seeker']['maxLabels'] ? 1 : $field['seeker']['maxLabels'];
$fields = array();
foreach ($field['seeker']['fields'] as $_field => $_value) {
array_push($fields, $_field);
if (++$countLabels >= $maxLabels) {
break;
}
}
/**
* Monta uma nova label para exibir os campos definidos acima
*/
$newLabel = '';
while ($row = $data->getRow()) {
if ($newLabel) {
$newLabel .= $union;
}
$labels = '';
for ($i = 0; $i < count($fields); $i++) {
if ($labels) {
$labels .= ' - ';
}
$labels .= $row[$fields[$i]]->get();
}
$newLabel .= $labels;
}
if ($newLabel) {
$this->_labelFilters[$newfield]['value'] = $newLabel;
}
#echo $newLabel;die;
}
}
}
return $where;
}
/**
* Retona a condição de filtro que será usado
* para listar os dados de uma tabela e assim
* poderá ser usado nas ações de grid, retrieve e etc...
*
* @return ZendT_Db_Where_Group
*/
public function getWherePostData()
{
$param = $this->getRequest()->getParams();
$whereGroup = new ZendT_Db_Where_Group('AND');
if ($this->_mapper instanceof ZendT_Db_View) {
$columns = $this->getMapper()->getColumns()->getColumnsMapper();
$columns->add('*', $this->getModel()->getMapperName());
} else {
$columns = $this->getModel()->getColumnsMapper();
}
/**
* Pega do filtro que é usado em tela.
*/
if (isset($param['isSearch'])) {
$where1 = ZendT_Db_Where::fromPostDataSearch($param, $columns);
$whereGroup->addWhere($where1);
}
/**
*
*/
if (isset($param['_search'])) {
$where2 = ZendT_Db_Where::fromAutoFilter($param, $columns);
$whereGroup->addWhere($where2);
}
/**
* Pega o filtro usado usado em json
*/
if ($param['filter_json']) {
$where3 = ZendT_Db_Where::fromJson(stripslashes($param['filter_json']));
$whereGroup->addWhere($where3);
}
return $whereGroup;
}
