Jump to content

GrandTotal for the field Price


kat35601
Go to solution Solved by Barand,

Recommended Posts

I want to get a grand total for the results of price. I do not want to do this in sql I would like to know how to do it in PHP Please.

<html>
<head>
<title>Production</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;
}

 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>
<h1>Kith Furniture </h1>

<?php

$connect =odbc_connect("removed");
if(!$connect) {
	exit("Connection Failed: " . $connect);
}

$sql="	SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, tdate)) AS DATE
	,ISNULL(trans, 'NON') AS trans
	,ISNULL(Transactions.item, Snumbers.item) AS item
	,count(serial) AS qty
	,tuser
	,convert(varchar,cast(sum(M1_KF.dbo.PartUnitSalePrices.imhUnitSalePrice) as money),1) as TotalPrice
	FROM Orbedata.dbo.SNumbers
  	LEFT OUTER JOIN OrbeData.dbo.Transactions ON snum = serial 
	INNER JOIN M1_KF.dbo.PartUnitSalePrices ON Orbedata.dbo.transactions.item = M1_KF.dbo.PartUnitSalePrices.imhPartID
	WHERE CONVERT(DATE, tdate) = CONVERT(DATE, Getdate())
		AND trans = 'fpr'
	GROUP BY DATEADD(dd, 0, DATEDIFF(dd, 0, tdate))
	,ISNULL(trans, 'NON')
	,ISNULL(Transactions.item, Snumbers.item)
	,tuser
	,Orbedata.dbo.transactions.qty
	order by tuser,item


 ";

$result =odbc_exec($connect,$sql);
if(!$result){
exit("Error in SQL");
}
echo "Produced Today ". date("m-d-Y") ;
echo "<table><tr>";
echo "<th>Line</th>";
 echo "<th>  </th>";
echo "<th>Item</th>";
 echo "<th>  </th>";
echo "<th>Qty</th>";
echo "<th>Price</th></tr>";

while (odbc_fetch_row($result)) {
  $scanner=odbc_result($result,"tuser");
  $Item=odbc_result($result,"item");
  $Qty=odbc_result($result,"qty");
   $Price=odbc_result($result,"TotalPrice");
 
	 
  echo "<tr><td>$scanner</td>";
  echo "<td> ◃ </td>";
  echo "<td>$Item</td>";
   echo "<td> ▹ </td>";
  echo "<td>$Qty</td>";
   echo "<td align='right'>$Price</td></tr>";
   
}

odbc_close($connect);
?>
</body>
</html>
Link to comment
Share on other sites


$grandTotal = 0; // initialise to 0

while (odbc_fetch_row($result)) {
$scanner=odbc_result($result,"tuser");
$Item=odbc_result($result,"item");
$Qty=odbc_result($result,"qty");
$Price=odbc_result($result,"TotalPrice");

$grandTotal += $price; // accumulate price

echo "<tr><td>$scanner</td>";
echo "<td> ◃ </td>";
echo "<td>$Item</td>";
echo "<td> ▹ </td>";
echo "<td>$Qty</td>";
echo "<td align='right'>$Price</td></tr>";

}

echo "Grand Total: $grandTotal"; // output grand total
Link to comment
Share on other sites

Thanks-----The total is not correct. What would be my problem. 

Produced Today 10-22-2015
Grand Total: 6371.88
Line		Item		Qty	Price
UL2	◃	110-03	▹	4	520.00
UL2	◃	115-02	▹	194	9,310.06
UL2	◃	115-04	▹	101	6,361.99
UL2	◃	115-05	▹	101	7,371.99
UL2	◃	115-12	▹	8	824.00
UL2	◃	171-05	▹	3	254.97
UL2	◃	175-02	▹	1	54.99
UL2	◃	175-05	▹	2	169.98
UL2	◃	175-12	▹	48	4,560.00
UL2	◃	190-03	▹	9	1,170.00
UL2	◃	190-12	▹	6	618.00
UL2	◃	193-02	▹	59	2,831.41
UL2	◃	193-04	▹	37	1,849.63
UL2	◃	197-04	▹	150	7,498.50
UL2	◃	197-05	▹	136	8,158.64
UL2	◃	269-05	▹	4	419.96
UL2	◃	269-73	▹	1	95.00
UL2	◃	275-02	▹	59	3,539.41
UL2	◃	275-05	▹	101	10,098.99
UL2	◃	275-12	▹	86	9,890.00
UL2	◃	405-02	▹	2	139.98
UL2	◃	485-12	▹	2	280.00
UL3	◃	175-33	▹	33	1,155.00
UL3	◃	175-53	▹	35	1,225.00
UL3	◃	230-53	▹	1	42.00
UL3	◃	269-76	▹	17	935.00
UL3	◃	405-50	▹	2	90.00
UL3	◃	405-60	▹	14	770.00
UL3	◃	405-FP	▹	3	90.00
UL3	◃	485-01	▹	96	4,800.00
UL3	◃	485-50	▹	81	3,645.00
UL3	◃	485-51	▹	43	1,720.00
UL3	◃	485-52	▹	74	1,850.00
UL3	◃	485-60	▹	18	990.00
<html>
<head>
<title>Production</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;
}

 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>
<h1>Kith Furniture </h1>

<?php
$grandTotal = 0;
$connect =odbc_connect("Removed");
if(!$connect) {
	exit("Connection Failed: " . $connect);
}

$sql="	SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, tdate)) AS DATE
	,ISNULL(trans, 'NON') AS trans
	,ISNULL(Transactions.item, Snumbers.item) AS item
	,count(serial) AS qty
	,tuser
	,convert(varchar,cast(sum(M1_KF.dbo.PartUnitSalePrices.imhUnitSalePrice) as money),1) as TotalPrice
	FROM Orbedata.dbo.SNumbers
  	LEFT OUTER JOIN OrbeData.dbo.Transactions ON snum = serial 
	INNER JOIN M1_KF.dbo.PartUnitSalePrices ON Orbedata.dbo.transactions.item = M1_KF.dbo.PartUnitSalePrices.imhPartID
	WHERE CONVERT(DATE, tdate) = CONVERT(DATE, Getdate())
		AND trans = 'fpr'
	GROUP BY DATEADD(dd, 0, DATEDIFF(dd, 0, tdate))
	,ISNULL(trans, 'NON')
	,ISNULL(Transactions.item, Snumbers.item)
	,tuser
	,Orbedata.dbo.transactions.qty
	order by tuser,item


 ";

$result =odbc_exec($connect,$sql);
if(!$result){
exit("Error in SQL");
}
echo "Produced Today ". date("m-d-Y") ;
echo "<table><tr>";
echo "<th>Line</th>";
 echo "<th>  </th>";
echo "<th>Item</th>";
 echo "<th>  </th>";
echo "<th>Qty</th>";
echo "<th>Price</th></tr>";

while (odbc_fetch_row($result)) {
  $scanner=odbc_result($result,"tuser");
  $Item=odbc_result($result,"item");
  $Qty=odbc_result($result,"qty");
   $Price=odbc_result($result,"TotalPrice");
 
 $grandTotal += $Price; 
	 
  echo "<tr><td>$scanner</td>";
  echo "<td> ◃ </td>";
  echo "<td>$Item</td>";
   echo "<td> ▹ </td>";
  echo "<td>$Qty</td>";
   echo "<td align='right'>$Price</td></tr>";
   
}

echo "Grand Total: $grandTotal";

odbc_close($connect);
?>
</body>
</html>
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.

×
×
  • 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.