AppGini Community Forums

Code: Select all

/*Add files that allow the functions to connect
to the database*/
$currDir = dirname(__FILE__);
include("$currDir/lib.php");
include("$currDir/language.php");
include("$currDir/defaultLang.php");
include("$currDir/header.php");


/*Get Database Data*/
$report_from = get_sql_from('TABLE_N1');
if (!$report_from) exit(error_message('Access Denied'));

/*get report*/
$report_ID = intval($_REQUEST['ID']);
if (!$report_ID) exit(error_message('Invalid report ID', false));


/*Retrieve report information*/
$report_fields = get_sql_fields('TABLE_N1');
$res = sql("select {$report_fields} from {$report_from} AND `TABLE_N1`.`ID`={$report_ID}", $eo);
if(!($report = db_fetch_assoc($res))) exit(error_message('Report Not Found', false));
/*var dump on the variables for the report*/
var_dump($report);

/*get the timesheets associated with the Invoice
Iterate through the timesheets for a given Invoice*/

$timesheet = array(); //timesheet array
$timesheet_fields = get_sql_fields('TABLE_N2');
$timesheet_from = get_sql_from('TABLE_N2');
/*Match the timesheet 'invoice_no' field to TABLE_N1*/
$timesheet_res = sql("select {$timesheet_fields} from {$timesheet_from}  AND TABLE_N2.invoice_no={$report_ID}", $eo);
/*use while loop to iterate the results since
the results will be an array*/
while ($row = db_fetch_assoc($timesheet_res)){
    $timesheet[] = $row;
}
/*var dump to check the timesheets*/
var_dump($timesheet);


/* This is the last part that is not working for me, I can only see the results from the first timesheet*/
/*get the time records associated with the time sheet
Iterate through the time records for a given time sheet*/
$timerecord = array(); //findings array
$timerecord_fields = get_sql_fields('TABLE_N3');
$timerecord_from = get_sql_from('TABLE_N3');
$timerecord_res = sql("select {$timerecord_fields} from {$timerecord_from}  AND TABLE_N3.ts_no = {$report_ID}", $eo);
/*use while loop to iterate the results since
the results will be an array*/
while ($row = db_fetch_assoc($timerecord_res)){
    $timerecord[] = $row;
}
/*var dump to check the timerecords*/
var_dump($timerecord);

Code: Select all

/*Add files that allow the functions to connect
to the database*/
$currDir = dirname(__FILE__);
include("$currDir/lib.php");
include("$currDir/language.php");
include("$currDir/defaultLang.php");
include("$currDir/header.php");


/*Get Database Data*/
$report_from = get_sql_from('TABLE_N1');
if (!$report_from) exit(error_message('Access Denied'));

/*get report*/
$report_ID = intval($_REQUEST['ID']);
if (!$report_ID) exit(error_message('Invalid report ID', false));


/*Retrieve report information*/
$report_fields = get_sql_fields('TABLE_N1');
$res = sql("select {$report_fields} from {$report_from} AND `TABLE_N1`.`ID`={$report_ID}", $eo);
if(!($report = db_fetch_assoc($res))) exit(error_message('Report Not Found', false));
/*var dump on the variables for the report*/
var_dump($report);

/*get the timesheets associated with the Invoice
Iterate through the timesheets for a given Invoice*/

$timesheet = array(); //timesheet array
$timesheet_fields = get_sql_fields('TABLE_N2');
$timesheet_from = get_sql_from('TABLE_N2');
/*Match the timesheet 'invoice_no' field to TABLE_N1*/
$timesheet_res = sql("select {$timesheet_fields} from {$timesheet_from}  AND TABLE_N2.invoice_no={$report['id']}", $eo);
/*use while loop to iterate the results since
the results will be an array*/
$pk=getPKFieldName('TABLE_N2');

$timerecord_fields = get_sql_fields('TABLE_N3');
$timerecord_from = get_sql_from('TABLE_N3');
$timerecord = array(); //findings array
while ($row = db_fetch_assoc($timesheet_res)){
    $timesheet[] = $row;

	/*get the time records associated with the time sheet
	Iterate through the time records for a given time sheet*/

	$timerecord_res = sql("select {$timerecord_fields} from {$timerecord_from}  AND TABLE_N3.ts_no = {$row[$pk]}", $eo);

	/*use while loop to iterate the results since
	the results will be an array*/
	while ($row = db_fetch_assoc($timerecord_res)){
		$timerecord[] = $row;
	}
}
/*var dump to check the timesheets*/
var_dump($timesheet);