Jump to content

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

kat35601

By kat35601
in PHP Coding Help

 Share

Recommended Posts

kat35601 Advanced Member

kat35601

Posted
Posted

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 comment
Share on other sites
Barand Prolific Member

Barand

Posted
Posted

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 comment
Share on other sites
kat35601 Advanced Member

kat35601

Posted
  • Author
Posted

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 comment
Share on other sites
kat35601 Advanced Member

kat35601

Posted
  • Author
Posted

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 comment
Share on other sites
Barand Prolific Member

Barand

Posted
Posted

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 comment
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

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.

×
 Share
Go to topic listing
×
×
  • 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.