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;
}
