private function appendQueryFilters($query, $loggedUserId, $filter, $ignoreRegionalFilter = false)
{
if (in_array('annual_revenue', array_keys($filter)) && $filter['annual_revenue'] != '') {
$query->where('E.AnnualRevenue = ?', $filter['annual_revenue']);
}
if (in_array('category_sector_id', array_keys($filter)) && $filter['category_sector_id'] != '') {
$query->where('E.CategorySectorId = ?', $filter['category_sector_id']);
}
if (in_array('education_id', array_keys($filter)) && $filter['education_id'] != '') {
$query->where('P.EducationId = ?', $filter['education_id']);
}
if (in_array('category_award_id', array_keys($filter)) && $filter['category_award_id'] != '') {
$query->where('E.CategoryAwardId = ?', $filter['category_award_id']);
}
if (!$ignoreRegionalFilter) {
$regionalId = null;
if (in_array('regional_id', array_keys($filter)) && $filter['regional_id'] != '') {
$regionalId = $filter['regional_id'];
}
$this->appendUserRegionalJoins($query, $loggedUserId, $regionalId);
}
if (in_array('metier_id', array_keys($filter)) && $filter['metier_id'] != '') {
$query->where('E.MetierId = ?', $filter['metier_id']);
}
if (in_array('state_id', array_keys($filter)) && $filter['state_id'] != '') {
$query->where("AE.StateId = ?", $filter['state_id']);
}
if (in_array('city_id', array_keys($filter)) && $filter['city_id'] != '') {
$query->where("AE.CityId = ?", $filter['city_id']);
}
if (in_array('neighborhood_id', array_keys($filter)) && $filter['neighborhood_id'] != '') {
$query->where("AE.NeighborhoodId = ?", $filter['neighborhood_id']);
}
if (in_array('status', array_keys($filter)) && $filter['status'] != '') {
$query->where('E.Status = ?', $filter['status']);
}
if (in_array('verified_subscription', array_keys($filter)) && $filter['verified_subscription'] != '') {
$query->where('ECAC.Verified = ?', $filter['verified_subscription']);
}
if (in_array('coop_name', array_keys($filter)) && $filter['coop_name'] != '') {
$query->where("E.SocialName LIKE ? OR E.FantasyName LIKE ?", '%' . $filter['coop_name'] . '%');
}
if (in_array('candidatura', array_keys($filter)) && $filter['candidatura'] != '') {
if ($filter['candidatura'] == 'C') {
$query->where('EXE.DevolutivePath is not null');
} else {
$query->where('EXE.DevolutivePath is null');
}
}
if (in_array('cnpj', array_keys($filter)) && $filter['cnpj'] != '') {
$clean_cnpj = preg_replace('/[^0-9]/', '', $filter['cnpj']);
$query->where('E.Cnpj LIKE "%' . $clean_cnpj . '%"');
}
if (in_array('president_name', array_keys($filter)) && $filter['president_name'] != '') {
$query->where("P.Name LIKE ? OR P.NickName LIKE ?", '%' . $filter['president_name'] . '%');
}
if (in_array('cpf', array_keys($filter)) && $filter['cpf'] != '') {
$clean_cpf = preg_replace('/[^0-9]/', '', $filter['cpf']);
$query->where('P.Cpf LIKE "%' . $clean_cpf . '%"');
}
if (in_array('employees_quantity', array_keys($filter)) && $filter['employees_quantity'] != '') {
$clean_employees_quantity = trim($filter['employees_quantity']);
$query->where('E.EmployeesQuantity = ?', $clean_employees_quantity);
}
if (in_array('appraiser_id', array_keys($filter)) && $filter['appraiser_id'] != '') {
$appraiser_id = $filter['appraiser_id'];
$query->where('APE1.UserId = ?', $appraiser_id);
$query->ORwhere('APE2.UserId = ?', $appraiser_id);
$query->ORwhere('APE3.UserId = ?', $appraiser_id);
}
if (in_array('faixa', array_keys($filter)) && $filter['faixa'] != '') {
$range = Vtx_Util_Array::faixaIdadePSMN($filter['faixa']);
$start = preg_replace('/[^0-9]/', '', $range[1]);
$end = preg_replace('/[^0-9]/', '', $range[2]);
$query->where(new Zend_Db_Expr("FLOOR(DATE_FORMAT(NOW(),'%Y')-DATE_FORMAT(P.Borndate,'%Y')) BETWEEN {$start} AND {$end}"));
}
}
