public function GetCobranzasCheques() { /* SELECT cheques.id, clients.first_name, clients.last_name, operations.amount, cheques.date, cheques.check_n, cheques.amount, cobranzas.current_balance, cheques_status.status_list, cheques.liquidacion_id, credit_providers.name, addresses.zip_code, addresses.city FROM cheques left join operations on cheques.operation_id = operations.id left join clients on operations.client_id = clients.id left join cheques_status on cheques.status = cheques_status.id left join credit_providers on cheques.credit_provider_id = credit_providers.id left join addresses on clients.id = addresses.client_id left join cobranzas on cheques.id = cobranzas.cheque_id WHERE cheques.status = 3 AND (cobranzas.previous_balance <> cobranzas.paid_amount OR cobranzas.current_balance is NULL) GROUP BY cheques.id ORDER BY cheques.id * * NEW QUERY * SELECT C.id, clients.first_name, clients.last_name, operations.amount, C.date, C.check_n, C.amount, cobranzas.current_balance, cheques_status.status_list, C.liquidacion_id, credit_providers.name, addresses.zip_code, addresses.city FROM cheques as C left join operations on C.operation_id = operations.id left join clients on operations.client_id = clients.id left join cheques_status on C.status = cheques_status.id left join credit_providers on C.credit_provider_id = credit_providers.id left join addresses on clients.id = addresses.client_id left join cobranzas on cobranzas.cheque_id = C.id and cobranzas.current_balance = (select min(current_balance) from cobranzas where cheque_id = C.id) WHERE C.status = 3 AND current_balance <> 0 GROUP BY C.id ORDER BY C.id */ $table = $this->getDbTable(); $select = $table->select(); $select->setIntegrityCheck(false); $select->from(array('cqu' => 'cheques'), array('id as cheque_id', 'date as cheque_date', 'check_n', 'amount as cheque_amount', 'balance', 'liquidacion_id', 'status as cheque_status')); $select->joinInner(array('ops' => 'operations'), 'cqu.operation_id = ops.id', array('amount as operation_amount')); $select->joinLeft(array('cls' => 'clients'), 'ops.client_id = cls.id', array('id as client_id', 'first_name', 'last_name', 'business')); //$select->joinLeft(array('cps' => 'credit_providers'), 'cqu.credit_provider_id = cps.id', array('name as sup_name')); $select->joinLeft(array('cps' => 'providers'), 'cqu.provider_id = cps.id', array('name as prov_name')); $select->joinLeft(array('chqs' => 'cheques_status'), 'cqu.status = chqs.id', array('status_list')); $select->joinLeft(array('add' => 'addresses'), 'cls.id = add.client_id', array('zip_code', 'city')); //$select->joinLeft(array('cob' => 'cobranzas'), 'cqu.id = cob.cheque_id', array('current_balance', 'paid_amount', 'previous_balance')); $select->joinLeft(array('cob' => 'cobranzas'), 'cqu.id = cob.cheque_id AND cob.current_balance = (select min(current_balance) from cobranzas where cheque_id = cqu.id)', array('current_balance', 'paid_amount', 'previous_balance')); $select->where('cqu.status = 3'); $select->where('cqu.balance > 0.00'); //$select->where('cob.current_balance <> 0 OR cob.current_balance IS NULL'); $select->group('cqu.id'); $select->order('cqu.id ASC'); $resultSet = $table->fetchAll($select); $entries = array(); foreach ($resultSet as $row) { $entry = new Gyuser_Model_Cheques(); $entry->setId($row->cheque_id); $entry->setClient_id($row->client_id); $entry->setFirst_name($row->first_name); $entry->setLast_name($row->last_name); $entry->setEmpresa($row->business); $entry->setZip($row->zip_code); $entry->setLocalidad($row->city); $entry->setDate(date("d/m/Y", strtotime($row->cheque_date))); $entry->setCheck_n($row->check_n); $entry->setAmount($row->cheque_amount); $entry->setBalance($row->balance); $entry->setStatus($row->cheque_status); $entry->setStatus_name($row->status_list); //$entry->setRejected_balance($row->current_balance); if ($row->liquidacion_id) { //the check was passed to a prov $entry->setLiquidacion_id($row->liquidacion_id); $entry->setCave_name($row->prov_name); /* if($row->prov_name) //it was passed to a credit provider $entry->setCave_name($row->sup_name); else //otherwise if name is null it means it was passed to lavalle $entry->setCave_name('Lavalle'); */ } $entries[] = $entry; } return $entries; }