Any Suggestions?

[hkroeger]

Just wondering what people think of this.

I've included a screen shot of the report (note: look at IE's scroll bars in the screen).

It's already fast, loads in under 3 seconds for the most part. Some take longer to display only because of slow redrawing.

<?php
include("lasso_includes.php");
include("auth_main.php");
include("reports-inc.php");
error_reporting(E_ALL);

$building_info = getBuildingInfo($projectID, "report_customize_v3.php?");
$building_query = ($building_info['current_building'] != "")?" AND building='".$building_info['current_building']."'":"";

$start_date = mysql_result(mysql_query("SELECT cust_date FROM unit_custom LEFT JOIN units USING(unitID) WHERE projectID='".$projectID."' ".$building_query." ORDER BY cust_date ASC  LIMIT 1"), 0, "cust_date");
$end_date   = mysql_result(mysql_query("SELECT cust_date FROM unit_custom LEFT JOIN units USING(unitID) WHERE projectID='".$projectID."' ".$building_query." ORDER BY cust_date DESC LIMIT 1"), 0, "cust_date");

list($y, $m, $d) = explode("-", $start_date);
$start_date_php  = mktime(0,0,0,$m,$d,$y);
list($y, $m, $d) = explode("-", $end_date);
$end_date_php    = mktime(0,0,0,$m,$d,$y);
$loop_date = $start_date;
$loop_end_date = $y."-".$m."-31";
unset($y);unset($m);unset($d);

$query = "SELECT * FROM units WHERE projectID='".$projectID."' ".$building_query;
$units = mysql_query($query);

$month_col_span = 6;
$monthly_report_headers = "";
$monthly_revenue_cost_headers = "";
$month_spacers = "";

###################### HEADERS AND TOTALS SETUP #############################
$loop_date = $start_date;

$agg_monthly_totals = "";
$month_totals = "";
while($loop_date < $loop_end_date) {
	### LOOP DATE SETUP ######
	list($y, $m, $d) = explode("-", $loop_date);
	$loop_date_php   = mktime(0,0,0,$m,$d,$y);
	$monthly_report_headers .= "<td colspan=2 align='center'>".date("M Y", $loop_date_php)."</td>\r\n";
	$monthly_revenue_cost_headers .= "<td width=50 align=right>Revenue</td><td width=50 align=right>Cost</td>";
	$month_spacers .= "<td align=right>&nbsp;</td><td align=right>&nbsp;</td>";
	$loop_month_start = date("Y-m-d", mktime(0,0,0, $m,1,$y));
	$loop_month_end   = date("Y-m-d", mktime(0,0,0, $m+1,0,$y));
	### FINISH ######

$rev_agg  = mysql_result(mysql_query("SELECT SUM(cust_amt)  AS custom_amount FROM unit_custom LEFT JOIN units USING(unitID) WHERE projectID='".$projectID."' AND cust_date <= '".$loop_month_end."' ".$building_query), 0, "custom_amount");
$cost_agg = mysql_result(mysql_query("SELECT SUM(cust_cost) AS custom_amount FROM unit_custom LEFT JOIN units USING(unitID) WHERE projectID='".$projectID."' AND cust_date <= '".$loop_month_end."' ".$building_query), 0, "custom_amount");	
$rev_agg = $rev_agg==0?"":'$'.number_format($rev_agg);
$cost_agg = $cost_agg==0?"":'$'.number_format($cost_agg);
$agg_monthly_totals .= "<td align=right>".$rev_agg."</td><td align=right>".$cost_agg."</td>";

$month_rev_totals  = mysql_result(mysql_query("SELECT SUM(cust_amt)  AS custom_amount FROM unit_custom LEFT JOIN units USING(unitID) WHERE projectID='".$projectID."' AND cust_date BETWEEN '".$loop_month_start."' AND '".$loop_month_end."' ".$building_query), 0, "custom_amount");
$month_cost_totals = mysql_result(mysql_query("SELECT SUM(cust_cost) AS custom_amount FROM unit_custom LEFT JOIN units USING(unitID) WHERE projectID='".$projectID."' AND cust_date BETWEEN '".$loop_month_start."' AND '".$loop_month_end."' ".$building_query), 0, "custom_amount");	
$month_rev_totals = $month_rev_totals==0?"":'$'.number_format($month_rev_totals);
$month_cost_totals = $month_cost_totals==0?"":'$'.number_format($month_cost_totals);
$month_totals .= "<td align=right>".$month_rev_totals."</td><td align=right>".$month_cost_totals."</td>";


### LOOP FINISH SCRIPT ######
$m++;
if($m == 13) {
	$m = 1;
	$y++;
}
if($m < 10) $m = "0".$m;
if($d < 10) $d = "0".$d;

$month_col_span += 2;
$loop_date = $y."-".$m."-".$d;
### FINISH ######
}
#############################################################################

$total_rev  = 0;
$total_cost = 0;
$loop_output = "";
$h = 0 ;
while ($unit = mysql_fetch_assoc($units)) {	
	//if($h++ == 20) break;
	$unitID = $unit['unitID'];
	$status = getUnitStatus($unitID);
	$udrow  = getUnitFullInfo($unitID);

### SUITE TOTALS #######
$suite_total_rev  = mysql_result(mysql_query("SELECT SUM(cust_amt)  AS custom_amount FROM unit_custom WHERE unitID='".$unit['unitID']."' AND cust_date <= '".$loop_end_date."'"), 0, "custom_amount");
$suite_total_cost = mysql_result(mysql_query("SELECT SUM(cust_cost) AS custom_amount FROM unit_custom WHERE unitID='".$unit['unitID']."' AND cust_date <= '".$loop_end_date."'"), 0, "custom_amount");	
$total_rev  += $suite_total_rev;
$total_cost += $suite_total_cost;		
$suite_total_rev  = $suite_total_rev ==0?"":'$'.number_format($suite_total_rev);
$suite_total_cost = $suite_total_cost==0?"":'$'.number_format($suite_total_cost);
### FINISH ######	

//$customizations = mysql_query("SELECT * FROM unit_custom WHERE unitID='".$unit['unitID']."' AND cust_date BETWEEN '".$loop_month_start."' AND '".$loop_month_end."'");
$customizations = mysql_query("SELECT * FROM unit_custom WHERE unitID='".$unit['unitID']."' AND cust_date BETWEEN '".$start_date."' AND '".$end_date."'");
$loop_custom_count = mysql_num_rows($customizations);

$loop_output .= "
	<tr bgcolor=#FBFBFB valign=top>
		<td rowspan='".($loop_custom_count+1)."' bgcolor=#F1F1F1 style=font-weight:bold>".$status."</td>
		<td rowspan='".($loop_custom_count+1)."' bgcolor=#F1F1F1 style=font-weight:bold>".$udrow['unit_num']."</td>
		<td rowspan='".($loop_custom_count+1)."' bgcolor=#F1F1F1 style=font-weight:bold>".$udrow['plan_type']."</td>\r\n";

$first_customization = true;
$suite_month_rev_total  = 0;
$suite_month_cost_total = 0;

$suite_monthly_totals = "";
if($loop_custom_count > 0) {
	$suite_monthly_totals .= "<tr bgcolor=#FBFBFB valign=top style='font-weight:bold'><td width=320>Suite Total</td>";	
	$first_date_loop = true;
	while ($crow = mysql_fetch_assoc($customizations)){
		$customization_output = "";
		if ($crow['cust_descr']){			
			if(!$first_customization) $customization_output .= "<tr bgcolor=#FBFBFB valign=top>";
			$first_customization = false;
			$customization_output .= "<td width=320>".$crow['cust_descr']."&nbsp;</td>\r\n";

			$loop_date = $start_date;
			while($loop_date < $loop_end_date) {
				### LOOP DATE SETUP ######
				list($y, $m, $d) = explode("-", $loop_date);
				$loop_date_php   = mktime(0,0,0,$m,$d,$y);
				$loop_month_start = date("Y-m-d", mktime(0,0,0, $m,1,$y));
				$loop_month_end   = date("Y-m-d", mktime(0,0,0, $m+1,0,$y));
				### FINISH ######
				$custom_info = mysql_fetch_assoc(mysql_query("SELECT * FROM unit_custom WHERE ucID='".$crow['ucID']."' AND cust_date BETWEEN '".$loop_month_start."' AND '".$loop_month_end."'"));	

				if ($custom_info['cust_amt'] != "0.00"){
					$cust_amt  = $custom_info['cust_amt'];
					$cust_cost = $custom_info['cust_cost'];
					$cust_amt  = ($cust_amt==0)?"":'$'.number_format($cust_amt);
					$cust_cost = ($cust_cost==0)?"":'$'.number_format($cust_cost);
				}
				else{
					$cust_amt  = "";
					$cust_cost = "";
				}

				$customization_output .= "
					<td title='Revenue\r\n".$unit['unit_num']."\r\n".date("M Y", $loop_date_php)."'>".$cust_amt."&nbsp;</td>
					<td title='Cost\r\n".$unit['unit_num']."\r\n".date("M Y", $loop_date_php)."'>".$cust_cost."&nbsp;</td>\r\n";

				if($first_date_loop) {
					$suite_month_rev_total  = mysql_result(mysql_query("SELECT SUM(cust_amt)  AS custom_amount FROM unit_custom WHERE unitID='".$unit['unitID']."' AND cust_date BETWEEN '".$loop_month_start."' AND '".$loop_month_end."'"), 0, "custom_amount");
					$suite_month_cost_total = mysql_result(mysql_query("SELECT SUM(cust_cost) AS custom_amount FROM unit_custom WHERE unitID='".$unit['unitID']."' AND cust_date BETWEEN '".$loop_month_start."' AND '".$loop_month_end."'"), 0, "custom_amount");	
					$suite_month_rev_total 	  = $suite_month_rev_total==0?"":'$'.number_format($suite_month_rev_total);
					$suite_month_cost_total	  = $suite_month_cost_total==0?"":'$'.number_format($suite_month_cost_total);
					$suite_monthly_totals .= "<td>".$suite_month_rev_total."&nbsp;</td><td>".$suite_month_cost_total."&nbsp;</td>";			
				}


				### LOOP FINISH SCRIPT ######
				$m++;
				if($m == 13) {
					$m = 1;
					$y++;
				}
				if($m < 10) $m = "0".$m;
				if($d < 10) $d = "0".$d;

				//$month_col_span += 2;
				$loop_date = $y."-".$m."-".$d;
				### FINISH ######
			}
			$first_date_loop = false;
			$customization_output .= "<td>&nbsp;</td><td>&nbsp;</td></tr>\r\n";
			$loop_output .= $customization_output;

		}

	}
	$suite_monthly_totals .= "<td>".$suite_total_rev."&nbsp;</td><td>".$suite_total_cost."&nbsp;</td></tr>";	
	$loop_output .= $suite_monthly_totals;
} else {
	if($suite_total_rev!="" || $suite_total_cost!="") {	
		$loop_output .= "
			<td width=320><b>Suite Total</b></td>
			".$month_spacers."
			<td><b>".$suite_total_rev."</b>&nbsp;</td>
			<td><b>".$suite_total_cost."</b>&nbsp;</td>
		</tr>";
	} else {
		$loop_output .= "
			<td>&nbsp;</td>
			".$month_spacers."
			<td>&nbsp;</td>
			<td>&nbsp;</td>
		</tr>";
	}
}
}
?>

[hkroeger]

I guess I should say what its purpose is.

What it does is, get all of the units for a building and all of the customizations for that unit, finds out the total life span of the building and displays all the units and customizations in the appropriate month.

The screen shot I've included also shows the totaling for the report. Along the sides it totals all of the customizations for a unit, and along the bottom it shows the monthly totals and the aggregate monthly totals.