Search code examples
phphtmlmysqlreportfpdf

Calculate SUM of profits in FPDF

I have a report which has a profit column. I need to add all the values of profit column and get the total profit for the day. Appreciate any help, since I have gone through SO, and found no solution for this.

$result = mysqli_query($connection, "SELECT S.sales_id, I.item_name, ST.stock_code, 
  S.date, S.unit_price, SUM(S.qty), (SUM(S.qty)*S.unit_price), 
  ((SUM(S.qty)*S.unit_price)-(SUM(S.qty)*P.unit_cost)) FROM sales S 
  INNER JOIN items I ON S.item_id=I.item_id 
  INNER JOIN stock ST ON S.stock_id=ST.stock_id 
  INNER JOIN purchase_items P ON S.purchase_id=P.purchase_id 
  WHERE S.date BETWEEN '$date 00:00:00' AND '$date 23:59:59' GROUP BY S.item_id");

$qty = $row['SUM(S.qty)'];
$unit_price = $row['unit_price'];
$amount = $row['(SUM(S.qty)*S.unit_price)'];    
$profit = $row['((SUM(S.qty)*S.unit_price)-(SUM(S.qty)*P.unit_cost))']; 
$dailyProfit = $row['(SUM(SUM(S.qty)*S.unit_price)-(SUM(S.qty)*P.unit_cost)))'];
$pdf->Cell(25,10,$dailyProfit,'B',0,'J',1);

This retrieves only one record with last item's profit, how should this be corrected to get the daily sum(profit)?

enter image description here

Updated code

$result = mysqli_query($connection, "SELECT S.sales_id, I.item_name, ST.stock_code, S.date,
  S.unit_price, SUM(S.qty) AS line_qty, (SUM(S.qty)*S.unit_price) AS line_total, 
  ((SUM(S.qty)*S.unit_price)-(SUM(S.qty)*P.unit_cost)) AS line_profit
  FROM sales S 
  INNER JOIN items I ON S.item_id=I.item_id 
  INNER JOIN stock ST ON S.stock_id=ST.stock_id 
  INNER JOIN purchase_items P ON S.purchase_id=P.purchase_id 
  WHERE S.date BETWEEN '$date 00:00:00' AND '$date 23:59:59' GROUP BY S.item_id");

$qty = $row['line_qty'];
$unit_price = $row['unit_price'];
$amount = $row['line_total'];    
$profit = $row['line_profit']; 

$aRows = array();
while ($oRow = mysqli_fetch_assoc($result)) $aRows[] = $oRow;
mysqli_free_result($result);

$dTotalProfit = 0;
if (count($aRows) > 0) foreach ($aRows as $aRow) {
  $dTotalProfit += $aRow['line_profit'];
  //$pdf->Cell(25, 10, $aRow['line_qty'], 'B', 0, 'J', 1);
}
$totalProfit = number_format($dTotalProfit, 2);

$pdf->Cell(220, 10, 'Daily Total Profit:', 'B', 0, 'R', 1);
$pdf->Cell(25, 10, $totalProfit, 'B', 0, 'J', 1);

enter image description here

Solution

  • You can't apply SQL functions and expressions to PHP variables, these only work when executed as SQL queries. To help reduce confusion and make your code as readable as possible I would recommend you assign column names or aliases to your calculated values, for example:

    <?php
$sSQL = "SELECT S.sales_id, I.item_name, ST.stock_code, S.date, S.unit_price, 
  SUM(S.qty) AS line_qty, (SUM(S.qty)*S.unit_price) AS line_total, 
  ((SUM(S.qty)*S.unit_price)-(SUM(S.qty)*P.unit_cost)) AS line_profit
  FROM sales S 
  INNER JOIN items I ON S.item_id=I.item_id 
  INNER JOIN stock ST ON S.stock_id=ST.stock_id 
  INNER JOIN purchase_items P ON S.purchase_id=P.purchase_id 
  WHERE S.date BETWEEN '$date 00:00:00' AND '$date 23:59:59' GROUP BY S.item_id";

    Then you can access the values using:

    $qty = $row['line_qty'];
$unit_price = $row['unit_price'];
$amount = $row['line_total'];    
$profit = $row['line_profit'];

    To get the total daily profit you will need to sum up the values from all returned rows, this is easily done within the loop in this context:

    $oConnection = mysqli_connect($sHostname, $sUsername, $sPassword);
mysqli_select_db($oConnection, $sDatabaseName);
$oResult = mysqli_query($oConnection, $sSQL);
$aRows = array();
while ($oRow = mysqli_fetch_assoc($oResult)) $aRows[] = $oRow;
mysqli_free_result($result);

$dTotalProfit = 0;
if (count($aRows) > 0) foreach ($aRows as $aRow) {
  $dTotalProfit += $aRow['line_profit'];
  $pdf->Cell(25, 10, $aRow['line_qty'], 'B', 0, 'J', 1);
  ...
}

    And then at the bottom of your PDF, you can use number_format($dTotalProfit, 2) to output your daily total profit amount.

    To summarise then and apply all techniques above:

    $oQuery = mysqli_query($connection, "SELECT S.sales_id, I.item_name, ST.stock_code, S.date,
  S.unit_price, SUM(S.qty) AS line_qty, (SUM(S.qty)*S.unit_price) AS line_total, 
  ((SUM(S.qty)*S.unit_price)-(SUM(S.qty)*P.unit_cost)) AS line_profit
  FROM sales S 
  INNER JOIN items I ON S.item_id=I.item_id 
  INNER JOIN stock ST ON S.stock_id=ST.stock_id 
  INNER JOIN purchase_items P ON S.purchase_id=P.purchase_id 
  WHERE S.date BETWEEN '$date 00:00:00' AND '$date 23:59:59' GROUP BY S.item_id");

$aRows = array();
while ($oRow = mysqli_fetch_assoc($oQuery)) $aRows[] = $oRow;
mysqli_free_result($oQuery);

$aColumnWidths = array(25, 20, 20, 100, 20, 20, 20, 20);
$dTotalProfit = 0;
if (count($aRows) > 0) foreach ($aRows as $aRow) {
  $dTotalProfit += $aRow['line_profit'];
  $pdf->Cell($aColumnWidths[0], 10, $aRow['date'], 'B', 0, 'J', 1);
  $pdf->Cell($aColumnWidths[1], 10, $aRow['sales_id'], 'B', 0, 'J', 1);
  $pdf->Cell($aColumnWidths[2], 10, $aRow['stock_code'], 'B', 0, 'J', 1);
  $pdf->Cell($aColumnWidths[3], 10, $aRow['item_name'], 'B', 0, 'L', 1);
  $pdf->Cell($aColumnWidths[4], 10, $aRow['line_qty'], 'B', 0, 'R', 1);
  $pdf->Cell($aColumnWidths[5], 10, $aRow['unit_price'], 'B', 0, 'R', 1);
  $pdf->Cell($aColumnWidths[6], 10, $aRow['line_total'], 'B', 0, 'R', 1);
  $pdf->Cell($aColumnWidths[7], 10, $aRow['line_profit'], 'B', 0, 'R', 1);
  $pdf->Ln();
}
$pdf->Ln();
$pdf->Cell($aColumnWidths[0] + $aColumnWidths[1] + $aColumnWidths[2] + $aColumnWidths[3] +
  $aColumnWidths[4] + $aColumnWidths[5] + $aColumnWidths[6], 10, 
  'Daily Total Profit:', 'B', 0, 'R', 1);
$pdf->Cell($aColumnWidths[7], 10, number_format($dTotalProfit, 2), 'B', 0, 'R', 1);

    In this code you'll also notice I've moved the date column to the beginning as typically on financial reports the date would be shown on the left and then the profit column on the far right would have the total directly beneath. I've had a guess at the formatting of your $pdf->Cell lines as your included source code wasn't complete in including these.

    Also if you're not familiar with the prefixes, I've used Hungarian Notation in my answer source code, i.e. the o in $oQuery is for object, the a in $aRows is for array, the d in $dTotalProfit is for double, and I might have used i for integer, b for boolean etc should the need arise. This just helps with code readability if you can identify the variable type from the name.