Jump to content

Hey PhpFreaks I need some help creating a row at the bottom of each UOMPTRUCKNUMBER to show a total for $loadTotal


Recommended Posts

I always have trouble formatting my HTML in PHP.

I want to create a total at the bottom of each uomptrucknumber to show the total for all the orders on that truck. I have included a screen shot of the output.

 

So I want to put a row that adds up the order totals and show that total for every truck number. before the next truck number.

<html>
<head>
<title>Loads Between Dates</title>
</head>
<body>
	<style>


table {
	border: 1px solid #B0CBEF;
	border-width: 1px 0px 0px 1px;
	font-size: 14pt;
	font-family: Calibri;
	font-weight: 100;
	border-spacing: 0px;
	border-collapse: collapse;
    
}

 TH {
	background-image: url(excel-2007-header-bg.gif);
	background-repeat: repeat-x;
	font-weight: normal;
	font-size: 17px;
	border: 1px solid #9EB6CE;
	border-width: 0px 1px 1px 0px;
	height: 17px;
}

 TD {
	border: 0px;
	padding: 0px 4px 0px 2px;
	border: 1px solid #D0D7E5;
	border-width: 0px 1px 1px 0px;
    width: 100px;
}

 TD B {
	border: 0px;
	background-color: white;
	font-weight: bold;
}

 TD.heading {
	background-color: #E4ECF7;
	text-align: center;
	border: 1px solid #9EB6CE;
	border-width: 0px 1px 1px 0px;
}



</style>
<?php
$loadid = '0';
$loadTotal=0;
$beginDate=$_POST["begin"];
$endDate=$_POST["end"];

$connect =odbc_connect("removed");
if(!$connect) {
	exit("Connection Failed: " . $connect);
}
$gr_total = 0;
$sql="select * from 

(select convert(varchar,ompRequestedShipDate,101) as ShipDate,UOMPTRUCKNUMBER,UOMPDROPSEQUENCE ,ompSalesOrderID ,
 ompCustomerOrganizationID,
 sum(omlOrderQuantity) as QTY,
 UOMPVOLUMETOTAL,
 sum(omlExtendedPriceBase) as total

from m1_kf.dbo.SalesOrders
left outer  join m1_kf.dbo.SalesOrderLines on omlSalesOrderID=ompSalesOrderID
where ompClosed !=-1 
and ompRequestedShipDate >= '$beginDate' and  ompRequestedShipDate <= '$endDate' and ompCustomerPO not like '%void%' and UOMPTRUCKNUMBER !=''
group by ompRequestedShipDate,UOMPTRUCKNUMBER,UOMPDROPSEQUENCE ,ompSalesOrderID,ompCustomerOrganizationID,UOMPVOLUMETOTAL) as cte
order by UOMPTRUCKNUMBER ,UOMPDROPSEQUENCE,ompSalesOrderID,ompCustomerOrganizationID" ;

$result =odbc_exec($connect,$sql);
if(!$result){
exit("Error in SQL");
}


	
	function ShowDetail($row){
       
        
		echo "<tr><td>".$row['ShipDate']."</td>";
        echo "<td>" .$row['UOMPDROPSEQUENCE']."</td>";
        echo "<td>" .$row['ompSalesOrderID']."</td>";
        echo "<td>" .$row['ompCustomerOrganizationID']."</td>";
        echo "<td>" .round($row['QTY'],0)."</td>";
        echo "<td>" .($row['UOMPVOLUMETOTAL'])."</td>";
        
        echo "<td align = 'right'>" .$row['total']."</td>";
              }
//****************************************************************
 while ($row = odbc_fetch_array($result))
{
    echo     "<table>"; 
    echo "<tr style='background:#82fbfd'>";
    if($row['UOMPTRUCKNUMBER'] != $loadid) {
        echo '<th>'.$row['UOMPTRUCKNUMBER']."</th>";
        echo "<th>Drop</th>";
        echo "<th>OrderID</th>";
        echo "<th>CustID</th>";
        echo "<th>QTY</th>";
        echo "<th>Cubes</th>";
		echo "<th>Total</th></tr>";
		$TotalPcs=0;
		$loadTotal=0;
    }

    

       
	ShowDetail($row);
    $loadid = $row['UOMPTRUCKNUMBER'];
	$TotalPcs+=round($row['QTY'],0);
    $loadTotal +=$row['total'];
	
   
}
echo     "</table>"; 
//******************************************************************



?>

 

 

 

screenshot.jpg

Link to post
Share on other sites

Not much modification required. I've commented the changes needed.

while ($row = odbc_fetch_array($result))
{
    echo     "<table>"; 
    echo "<tr style='background:#82fbfd'>";
    
    if($row['UOMPTRUCKNUMBER'] != $loadid) {
        
        if ($loadid != 0) {                                                                           # ADD
            // OUTPUT PREVIOUS TRUCK TOTAL HERE                                                       # ADD
        }                                                                                             # ADD
        echo '<th>'.$row['UOMPTRUCKNUMBER']."</th>";
        echo "<th>Drop</th>";
        echo "<th>OrderID</th>";
        echo "<th>CustID</th>";
        echo "<th>QTY</th>";
        echo "<th>Cubes</th>";
        echo "<th>Total</th></tr>";
        $TotalPcs=0;
        $loadTotal=0;
    }

    ShowDetail($row);
    $loadid = $row['UOMPTRUCKNUMBER'];
    $TotalPcs+=round($row['QTY'],0);
    $loadTotal +=$row['total'];
    
   
}    
// OUTPUT TOTALS FOR LAST TRUCK HERE                                                                   # ADD

 

Link to post
Share on other sites

SHow the first and last totals

$result =odbc_exec($connect,$sql);
if(!$result){
exit("Error in SQL");
}


	
	function ShowDetail($row){
       
        
		echo "<tr><td>".$row['ShipDate']."</td>";
        echo "<td>" .$row['UOMPDROPSEQUENCE']."</td>";
        echo "<td>" .$row['ompSalesOrderID']."</td>";
        echo "<td>" .$row['ompCustomerOrganizationID']."</td>";
        echo "<td>" .round($row['QTY'],0)."</td>";
        echo "<td>" .($row['UOMPVOLUMETOTAL'])."</td>";
        
		echo "<td align = 'right'>" .$row['total']."</td>";
	
              }
//****************************************************************
 while ($row = odbc_fetch_array($result))
{
    echo     "<table>"; 
	echo "<tr style='background:#82fbfd'>";
                                                                                 


    if($row['UOMPTRUCKNUMBER'] != $loadid) {
		if ($loadid != 0) {                                                                           
			echo "<th>$loadTotal</th>";                                                  
		}    
	       
        echo '<th>'.$row['UOMPTRUCKNUMBER']."</th>";
        echo "<th>Drop</th>";
        echo "<th>OrderID</th>";
        echo "<th>CustID</th>";
        echo "<th>QTY</th>";
        echo "<th>Cubes</th>";
		echo "<th>Total</th></tr>";
		$TotalPcs=0;
		
		
		$loadTotal=0;
    }


       
	ShowDetail($row);

    $loadid = $row['UOMPTRUCKNUMBER'];
	$TotalPcs+=round($row['QTY'],0);
    $loadTotal +=$row['total'];
	
   
}
echo     "</table>"; 
//******************************************************************
echo "<td>$loadTotal</td>"; 


?>

 

screenshot.png

Link to post
Share on other sites

I did get the totals on there now how would I move them to the bottom of each truck and put them under the totals. See attached

 while ($row = odbc_fetch_array($result))
{
    echo     "<table>"; 
	echo "<tr style='background:#82fbfd'>";
                                                                                 


    if($row['UOMPTRUCKNUMBER'] != $loadid) {
		                                                                         
	

	
        echo '<th>'.$row['UOMPTRUCKNUMBER']."</th>";
        echo "<th>Drop</th>";
        echo "<th>OrderID</th>";
        echo "<th>CustID</th>";
        echo "<th>QTY</th>";
        echo "<th>Cubes</th>";
		echo "<th>Total</th>";
		
		echo "<td><br>$loadTotal</td></tr>";
		$TotalPcs=0;
		
		
		$loadTotal=0;
    }


       
	ShowDetail($row);

    $loadid = $row['UOMPTRUCKNUMBER'];
	$TotalPcs+=round($row['QTY'],0);
    $loadTotal +=$row['total'];
	
   
}echo "<td>$loadTotal</td>"; 

echo     "</table>"; 

 

screenshot.png

Link to post
Share on other sites

try

echo "<table>";
while ($row = odbc_fetch_array($result))
{
     
    
    if($row['UOMPTRUCKNUMBER'] != $loadid) {
        
        if ($loadid != 0) {
            echo "<tr style='background:#eee'>
                     <td colspan='4'>TOTAL</td>
                     <td>$TotalPcs</td>
                     <td>&nbsp;</td>
                     <td>$LoadTotal</td>
                  </tr>";
        }
        
        echo "<tr style='background:#82fbfd'>";
        echo '<th>'.$row['UOMPTRUCKNUMBER']."</th>";
        echo "<th>Drop</th>";
        echo "<th>OrderID</th>";
        echo "<th>CustID</th>";
        echo "<th>QTY</th>";
        echo "<th>Cubes</th>";
        echo "<th>Total</th></tr>";
        $TotalPcs=0;
        $loadTotal=0;
    }

    ShowDetail($row);
    $loadid = $row['UOMPTRUCKNUMBER'];
    $TotalPcs+=round($row['QTY'],0);
    $loadTotal +=$row['total'];
    
   
}    
echo "<tr style='background:#eee'>
         <td colspan='4'>TOTAL</td>
         <td>$TotalPcs</td>
         <td>&nbsp;</td>
         <td>$LoadTotal</td>
      </tr>
      </table>
      ";

 

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.