Hi Roland,
Sorry for the delay in reply.
Personally, I think the best method for building a list grid that displays all of the data when the tables are joined is to create a custom action that performs the query and displays the results that you want.
You can use the Dataface_Record grid class to help you to output the results in a grid with the same style as the list view:
- Code: Select all
$app =& Dataface_Application::getInstance();
$sql = "
select
l.username,
hours_pending,
hours_approved,
hours_rejected,
hours_paid,
balance_payable,
paid_to_date
from
users l
left join
(select username, sum(hours_declared) as hours_pending from log_entry where status='pending' group by username) as hp
on l.username=hp.username
left join
(select username, sum(hours_declared) as hours_approved from log_entry where status='approved' group by username) as ha
on l.username=ha.username
left join
(select username, sum(hours_declared) as hours_rejected from log_entry where status='rejected' group by username) as hr
on l.username=hr.username
left join
(select username, sum(hours_declared) as hours_paid from log_entry where status='paid' group by username) as hpaid
on l.username=hpaid.username
left join
(select username, sum(ifnull(flat_rate,hourly_rate*hours_declared)) as balance_payable from log_entry where status='approved' group by username) as hbp
on l.username=hbp.username
left join
(select username, sum(ifnull(flat_rate,hourly_rate*hours_declared)) as paid_to_date from log_entry where status='paid' group by username) as hptd
on l.username=hptd.username
";
$res = mysql_query($sql, $app->db());
if ( !$res ) trigger_error(mysql_error($app->db()));
$data = array();
while ( $row = mysql_fetch_assoc($res) ){
$data[] = $row;
}
import('Dataface/SkinTool.php');
import('Dataface/RecordGrid.php');
$grid = new Dataface_RecordGrid($data);
echo $grid->toHtml();
(I just quickly copied that snippet from a custom action in another app of mine so you may have to sift through to make sense. The thing to note is in the last few lines where I use the Dataface_RecordGrid object to display a grid.
As for hiding fields from the ajax drop-down pages, this is something that should be available but is not currently.. In general you should be able to use permissions or set visibility:browse = hidden in your fields.ini file to achieve this result.
When I get a chance i'll post the changes that will allow this to work.
-Steve
