I have a SQl table with multiple columns in it, In this table there ia a pageNr column. I need to display a table on the Web interface that has the table as an output but grouped by the pageNr and after each pageNr group I need to display totals for this pageNr.
My code currently looks something like this:
$getItemsTable = "SELECT *
FROM [DB].[dbo].[Table]
WHERE [Month1]='$month1' AND [Year1]='$year1'
AND Page != '0' AND (Flag != 'C' AND OfferType != '')
GROUP BY Page, PseudoNum, PBillDate,PBillMonth,PBillYear, PNum,Pseudo, Flag,Position,Code,Descrip,OfferType,BrandCode,Brand,Segment,Category,SubCategory,NewCategory,Disc,ListPrice,RSP,NamPerc,NamPric,UnitCost,Discount,Qty,NoOfProd,QtyTot,GSV,NSV,GPVal,GPPerc,UnitsCont,GSVCont,Micro,Highlighted,ModelWear,ModelEthnic,FUnitsPerOrd,FUnitsPerAdv,Month1,Year1,CostPerc,ForecastOrders,ForecastAdv,ForecastUnits,GSVTotal,Comments,Markest
Order by CAST(Page AS integer) ASC,[Code]
";
$execgetItemsTable = sqlsrv_query($conn,$getItemsTable);
// echo "$getItemsTable";
$checkPgNum = "SELECT
SUM(CAST([Qty] AS float)) AS TotQty
,SUM(CAST([QtyTot] AS float)) AS TotQtyTot
,SUM(CAST([ListPrice] AS float)) AS TotListPrice
,SUM(CAST([GSV] AS float)) AS TotGSV
,SUM(CAST([NSV] AS float)) AS TotNSV
,SUM(CAST([GPVal] AS float)) AS TotGPVal
,SUM(CAST([GPPerc] AS float)) AS TotGPPerc
,SUM(CAST([GSVCont] AS float)) AS TotGSVCont
,SUM(CAST([NoOfProd] AS float)) AS TotNoOfProd2
,Page
FROM [DB].[dbo].[Table]
WHERE [Month1]='$month1' AND [Year1]='$year1'
AND (Flag != 'C' AND OfferType != '') AND Page != '0'
GROUP BY Page";
//echo $checkPgNum;
$execcheckPgNum = sqlsrv_query($conn,$checkPgNum);
$CountTimes = 0;
while($a_rows = sqlsrv_fetch_object($execgetItemsTable))
{
//$PageTot ='';
while($b_rows = sqlsrv_fetch_object($execcheckPgNum))
{
echo"<pre>";
print_r($b_rows);
echo"</pre>";
$PageTot = $b_rows->TotNoOfProd2;
}
echo "##########".$PageTot."<br>";
$PgN = '';
if($PgN != $a_rows->Page)
{
$PageCount = 0;
$OldPage = $PgN;
$PgN = $a_rows->Page;
Echo "PageCount ".$PageCount."<br>";
Echo "PageTot ".$PageTot."<br>";
if($PageCount <= $PageTot)
{
$Pack2Fin .= "<tr id='row$PCode' style='display:show' bgcolor='#BFBFBF'>";
$Pack2Fin .= "<td align='left' colspan='8' bgcolor='#BFBFBF'><B>$OldPage Total1</B></td>";
$Pack2Fin .= "<td align='right' bgcolor='#BFBFBF'><B>$PLQuantTotL</B></td>";
$Pack2Fin .= "<td align='right' bgcolor='#BFBFBF'><B>$QtyTotL</B></td>";
$GSVTotL = number_format($GSVTotL,0,'.',' ');
$Pack2Fin .= "<td align='right' bgcolor='#BFBFBF' nowrap><B>R $GSVTotL</B></td>";
$NSVTotL = number_format($NSVTotL,0,'.',' ');
$Pack2Fin .= "<td align='right' bgcolor='#BFBFBF'><B>R $NSVTotL</B></td>";
$GPValTotL = number_format($GPValTotL,0,'.',' ');
$Pack2Fin .= "<td align='right' bgcolor='#BFBFBF'><B>R $GPValTotL</B></td>";
$GPPercTotL = number_format($GPPercTotL,0,'.',' ');
$Pack2Fin .= "<td align='right' bgcolor='#BFBFBF'><B>$GPPercTotL%</B></td>";
$PCostTotL = number_format($PCostTotL,2,'.',' ');
$Pack2Fin .= "<td align='right' bgcolor='#BFBFBF'><B>R $PCostTotL</B></td>";
$SSCostTotL = number_format($SSCostTotL,2,'.',' ');
$Pack2Fin .= "<td align='right' bgcolor='#BFBFBF'><B>R $SSCostTotL</B></td>";
$Pack2Fin .= "<td align='right' bgcolor='#BFBFBF'><B>$NoOfProdTotL</B></td>";
$UnitsContTotL = number_format($UnitsContTotL,0,'.',' ');
$Pack2Fin .= "<td align='right' bgcolor='#BFBFBF'><B>$UnitsContTotL %</B></td>";
$GSVContTotL = number_format($GSVContTotL,0,'.',' ');
$Pack2Fin .= "<td align='right' bgcolor='#BFBFBF'><B>$GSVContTotL %</B></td>";
$Pack2Fin .= "</tr>";
$GranTotQuant = $GranTotQuant + $PLQuantTotL;
$GranQtyTotL = $GranQtyTotL + $QtyTotL;
$GranGSVTotL = $GranGSVTotL + $GSVTotL;
$GranNSVTotL = $GranNSVTotL + $NSVTotL;
$GranNSVTotL = $GranNSVTotL + $NSVTotL;
$GranGPValTotL = $GranGPValTotL + $GPValTotL;
//$GranGPPercTotL = $GranGPPercTotL + $GPPercTotL;
$GranGPPercTotL = ($GranGPValTotL/$GranNSVTotL)*100;
$GranPCostTotL = $GranPCostTotL + $PCostTotL;
$GranSSCostTotL = $GranSSCostTotL + $SSCostTotL;
$GranNoOfProdTotL = $GranNoOfProdTotL + $NoOfProdTotL;
$GranUnitsContTotL = $GranUnitsContTotL + $UnitsContTotL;
$GranGSVContTotL + $GranGSVContTotL + $GSVContTotL;
}
$Pack2Fin .= "<tr id='row$PCode' style='display:show' >";
$Pack2Fin .= "<td align='center'>$PgN</td>";
/*if($LineNo == 1)
$QtyTotL = 1;
else*/
//$PageTot = 0;
$PLQuantTotL = 0;
$QtyTotL = 0;
$GSVTotL = 0;
$NSVTotL = 0;
$GPValTotL = 0;
$GPPercTotL = 0;
$PCostTotL = 0;
$SSCostTotL = 0;
$NoOfProdTotL = 0;
$UnitsContTotL = 0;
$GSVContTotL = 0;
$PageCount = $PageCount + 1;
}
else
{
$Pack2Fin .= "<tr id='row$PCode' style='display:show' >";
$Pack2Fin .= "<td align='center'></td>";
}
Here is my Current output Current output
As you can see, where there is no pageNr, he gives me the correct totals, but where there is a page number it gives me a total for every row.
