Help Me Create a Report Please...

Ask about general coding issues or problems here.

Moderators: egami, macek, gesf

Post Reply
acellec
php-forum Active User
php-forum Active User
Posts: 26
Joined: Thu May 17, 2012 5:44 pm

Wed Aug 08, 2012 4:59 pm

can you help me create a report that will look like below?
Dates are dynamic, it can display date ranges from jan. to december or it can display only january or february.

Code: Select all

Project Name		Amount Allocated	                 No. of Jobs
                  Total	Jan.       Feb.	  Mar.	      Total	   Jan.	Feb.	  Mar.
Project 1       900.00                                    2,250
Location 1	   500.00	100.00    200.00	  200.00      1,000   500       250      250
Location 2	   250.00	200.00    25.00	   25.00       750     100       400      200
Location 3	   150.00	50.00     50.00	   50.00       500     50         500     400


my tables look like below

for the amount allocated

tblamountalloc

Code: Select all

Project Name	Location 1	   Location 2	Location 3	Date
Project 1	        100.00	    200.00	50.00		01-14-2012
Project 1	        200.00		25.00		50.00		02-14-2012
Project 1	        200.00		25.00		50.00		03-14-2012
for the no. of jobs

tblnojobs

Code: Select all

Project Name	Location 1	Location 2	Location 3	Date
Project 1	       500		100		         50		01-14-2012
Project 1	       250		400		         200		02-14-2012
Project 1	       250		200		         400		03-14-2012
acellec
php-forum Active User
php-forum Active User
Posts: 26
Joined: Thu May 17, 2012 5:44 pm

Wed Aug 15, 2012 12:02 am

i Have this code for a start.

Code: Select all

$query="Select tblamountalloc.agencycode,tblamountalloc.nproject,tblamountalloc.projectname,
tblamountalloc.Location1,tblamountalloc.Location2,tblamountalloc.Location3,tblnojobs.Location1,
tblnojobs.Location2,tblnojobs.Location3 from tblamountalloc Inner join tblnojobs on 
tblamountalloc.projectname=tblnojobs.projectname
where tblheader.starperiod between '2012-06-01' and '2012-08-01'";

Code: Select all

while ($record = mysql_fetch_object($query)) {
  $set[$record->agencycode][$record->nproject[0]][$record->projectname][] = $record;
}
ksort($set);

print "<table>\n";
foreach ($set as $agency => $agencycodes) {
 print "<h2>{$agency}</h2>\n";
foreach ($agencycodes as $agencycode => $procodes) {
  foreach ($procodes as $procode => $qrtcodes) {
  print "<tr>";
  print "<th>{$procode}</th><th>&nbsp;</th>";

  foreach ($qrtcodes as $records) {
  	
  	$mono=$records->qrtcode;
        $sno= remove_non_numeric($mono);   
        $monumber=substr($sno,0,-4);
       
 	$ncr[$monumber]=$records->ncra;	 	
 	
  }

 unset($qrtcodes);
	  
print "<th>".array_sum($ncr)."</th>"; 	
  foreach ($ncr as $ncrrecords) {  	 
        print "<th>{$ncrrecords}</th>";
  }
  unset($ncr);
    
  
  print "<tr>";
  }
}
}
print "</table>\n";
the code above have the result of like this.

Code: Select all

Project III (ARISP III)	         900	100.00	300.00	500.00
Support Project III (ARISP III)	 200	200.00
Project II (ARCP II) (Infra)	 	 600	600.00
my problem is my display should be like this

Code: Select all

  
                                    Total   June         July           August
Project III (ARISP III)	         900	    100.00	  300.00	   500.00
Support Project III (ARISP III)	 200	  200.00          0.00           0.00
Project II (ARCP II) (Infra)	 	 600	   0.00          0.00        600.00
I want to insert 0.00 in an array where the project doesnt have a record within a date of a date range.
Post Reply