php - 计算FPDF中的利润总和

我有一份报告有一个profit栏。我需要把利润栏的所有值加起来，得到当天的利润总额。感谢你的帮助，因为我经历了这些，但没有找到解决办法。

$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);

这只检索最后一项profit的一条记录，应该如何更正以获取每日sum(profit)？

更新的代码

$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);

最佳答案

不能对PHP变量应用SQL函数和表达式，这些函数和表达式只能作为SQL查询执行。为了帮助减少混淆并使代码尽可能可读，我建议您为计算值指定列名或别名，例如：

<?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";

然后可以使用以下方法访问值：

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

要获得每日总利润，您需要对所有返回行的值进行汇总，这在以下上下文中的循环中很容易完成：

$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);
  ...
}

然后在PDF的底部，您可以使用number_format($dTotalProfit, 2)输出您的每日利润总额。
总结并应用上述所有技术：

$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);

在这段代码中，您还将注意到我已经将日期列移到了开头，因为在财务报告中，日期通常会显示在左侧，然后最右侧的利润列会在正下方显示总计。我猜过您的$pdf->Cell行的格式，因为您包含的源代码在包含这些行时不完整。
另外，如果您不熟悉前缀，我在应答源代码中使用了匈牙利符号，即$oQuery中的o表示对象，$aRows中的a表示数组，$dTotalProfit中的d表示double，如果需要，我可能使用I表示整数，b表示布尔值等。如果可以从名称中标识变量类型，这只会有助于提高代码的可读性。

关于php - 计算FPDF中的利润总和，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/41059761/