Context Navigation

← Previous Version
View Latest Version
Next Version →

Version 28 (modified by Don Wilson, 31 hours ago) ( diff )
--

PHP

The 825 Gen2 includes PHP https://en.wikipedia.org/wiki/PHP which combined with the Apache Web Server allows for powerful web features.

From a terminal php -v will show the PHP version.

card825gen2:~$ php -v
PHP 8.1.10 (cli) (built: Aug 30 2022 16:09:36) (NTS)
Copyright (c) The PHP Group
Zend Engine v4.1.10, Copyright (c) Zend Technologies

Accessing MySQL database from php:

Make sure MySQL is configured first. Refer to the MySQL page MySQL (MariaDB)

Create a simple test php file to read from the database

cd /usr/share/apache2/default-site/htdocs
nano readmysql.php

<!DOCTYPE html>
<html lang="en">
<head>
  <title>825 MySQL Test</title>
</head>
<body>

<?php
$conn = mysqli_connect('127.0.0.1', 'dbuser', '81440', 'test');
if(!$conn) {
   echo 'Connection error: ' . mysqli_connect_error();
} else {
   $sql = 'select * from test_tbl;';
   $result = $conn->query($sql);
   if($result->num_rows > 0) {
      echo '<table><tr><th>Title</th><th>Description</th></tr>';
      while($row = $result->fetch_assoc()) {
          $title = $row['title'];
          $description = $row['description'];
          echo '<tr><td>' . $title . '</td><td>' . $description . '</td></tr>';
      }
      echo '</table>';
   }
}
?>

</body>
</html>

Test with PC on same network as 825.

It may also be helpful to use browser "Developer tools" selection to view the generated html code.

PHP can also be used to read SQLite databases.

card825gen2:/usr/share/apache2/default-site/htdocs$ cat testsqlite.php

<!DOCTYPE html>
<html lang="en">
<head>
  <title>825 SQLite Test</title>
</head>
<body>

<?php
$db = new SQLite3('/mnt/nand/apps/ids/ids.db3');

$results = $db->query('SELECT truck_name,tare FROM trucks');
echo '<table><tr><th>Truck ID</th><th>Tare</th></tr>';
while ($row = $results->fetchArray()) {
   $truckid = $row['truck_name'];
   $tare = $row['tare'];
   echo '<tr><td>' . $truckid . '</td><td>' . $tare . '</td></tr>';
}
echo '</table>';
?>

</body>
</html>

Following is a more advanced demo. This reads from an ID storage MySQL transaction table and also displays vehicle images associated with transactions.

ids.php

<!DOCTYPE html>
<html lang="en">
<head>
  <title>825 ID Storage</title>
  <style type="text/css" media="screen">
  table tr td {
    text-align: right;
    padding: 0 15px;
  }
  table td:nth-child(3) { text-align: left; }
</style>
</head>
<body>
<?php
$conn = mysqli_connect('127.0.0.1', 'dbuser', '81440', 'test');
if(!$conn) {
  echo 'Connection error: ' . mysqli_connect_error();
} else {
  $sql = 'select * from trans;';
  $result = $conn->query($sql);
  if($result->num_rows > 0) {
    echo '<table><tr><th>Tran Num</th><th>date/time</th><th>Vehicle<br/>Product<br/>Customer</th><th>Gross Wt</th><th>Tare Wt</th><th>Net Wt</th><th>Image</th><th>First pass</th></tr>';
    while($row = $result->fetch_assoc()) {
      $tkt_num = $row['tkt_num'];
      $datetime = $row['date'] . ' ' . $row['time'];
          $veh = $row['truck'];
          $prod = $row['prod'];
          $cust = $row['cust'];
          $job = $row['job'];
      $gross = $row['gross'];
      $tare = $row['tare'];
      $net = $row['net'];
      echo '<tr><td>' . $tkt_num . '</td><td>' . $datetime . '</td><td>' . $veh . '<br/>' . $prod . '<br/>' . $cust . '</td><td>' . $gross . '</td><td>' . $tare . '</td><td>'. $net  . '</td>';
      $imgfile = sprintf("v%06d_1.jpg", $tkt_num);
      $img = 'images/' . $imgfile;
      if(file_exists($img)) {
        echo '<td><a href="' . $img . '"><img src="' . $img .'" width="128" height="96" ></a></td>';
      } else {
        echo '<td></td>';
      }
      $imgfile = sprintf("v%06da_1.jpg", $tkt_num);
      $img = 'images/' . $imgfile;
      if(file_exists($img)) {
        echo '<td><a href="' . $img . '"><img src="' . $img .'" width="128" height="96" ></a></td>';
      } else {
        echo '<td></td>';
      }
          echo '</tr>';
    }
    echo '</table>';
  }
}
?>
</body>
</html>

This example report has date range selections and produces a material summary or detail:

<?php
session_start();
if(!isset($_SESSION['user']) || empty($_SESSION['user'])) {
    header("location: ../login.php"); exit();
}
$type = $_GET['rpttype'];
if(!$type) {
    $type = "sum";
}
?>
<!DOCTYPE html>
<html lang="en">
<script>
function toggleInputs() {
    const selectElement = document.getElementById("rangeSelect");
    const selectedValue = selectElement.value;

    const customInputs = document.getElementById("customInputs");

    // Hide all input containers initially
    customInputs.style.display = "none";

    // Show the relevant input container based on selection
    if (selectedValue === "custom") {
        customInputs.style.display = "block";
    }
}
</script>
<head>
<?php
if($type == 'det') {
    echo '<title>Product Detail</title>';
} else {
    echo '<title>Product Summary</title>';
}
?>
<style type="text/css" media="screen">
#tblSum {
 tr td,th {
    text-align: right;
    padding: 0 15px;
}
 td:nth-child(1) { text-align: left; }
 th:nth-child(1) { text-align: left; }
}
#tblDet {
tr td,th {
    text-align: left;
    padding: 0 15px;
}
td:nth-child(1) { text-align: right; }
td:nth-child(7) { text-align: right; }
th:nth-child(4) { text-align: right; }
}
</style>
</head>
<body>

<?php

$ranges = [
['id' => 'today', 'name' => 'Today'],
['id' => 'tweek', 'name' => 'This week'],
['id' => 'tmonth', 'name' => 'This month'],
['id' => 'tyear', 'name' => 'This year'],
['id' => 'lweek', 'name' => 'Last week'],
['id' => 'lmonth', 'name' => 'Last month'],
['id' => 'lyear', 'name' => 'Last year'],
['id' => 'custom', 'name' => 'Custom'],
];

$types = [
['id' => 'sum', 'name' => 'Summary'],
['id' => 'det', 'name' => 'Detail'],
];


$jsonFile = '/mnt/nand/apps/ids/db.json';
$jsonContent = file_get_contents($jsonFile);
$data = json_decode($jsonContent, true);
if (!$data) {
    echo "ID Storage database not configured";
} else {
   if ($data['dbtype'] != 'mysql') {
      echo "ID Storage database type must by MySQL for reporting";
   } else {

       $range = $_GET['range'];
        if(!$range) {
            $range = "today";
        }

        echo '<form action="/reports/idsprod.php" method="GET">';

        echo '<label>Report Type: </label><select name="rpttype" id="typeSelect">';
        foreach ($types as $t):
            echo '<option value="' . $t['id'] . '"';
        if($t['id'] == $type) {
            echo " selected";
        }
        echo '>' . $t['name'];
        echo '</option>';
        endforeach;
        echo '</select>';

        echo '<label> Date Range: </label><select name="range" id="rangeSelect" onchange="toggleInputs()">';
        foreach ($ranges as $r):
            echo '<option value="' . $r['id'] . '"';
            if($r['id'] == $range) {
                echo " selected";
            }
            echo '>' . $r['name'];
            echo '</option>';
        endforeach;
        echo '</select>';

        $dateFrom = $_GET['from'];
        $dateTo = $_GET['to'];

        if($range != "custom") {
            echo '<div id="customInputs" style="display: none;">';
        } else {
            echo '<div id="customInputs">';
        }
        echo '<label for="from">From: </label>';
        echo '<input type="date" name="from" id="from" value="' . $dateFrom . '">';
        echo '<label for="to"> To: </label>';
        echo '<input type="date" name="to" id="to" value="' . $dateTo . '">';
        echo '</div>';
        echo ' ';
        echo '<input type="submit" value="Submit">';
        echo '</form>';

       $tzpath = realpath("/etc/localtime");
       if(substr($tzpath, 0, 20) == '/usr/share/zoneinfo/') {
           $tz = substr($tzpath, 20);
           date_default_timezone_set($tz);
       }
       // echo "Date/time " . date('Y-m-d H:i:s');

        $conn = mysqli_connect($data['server'], $data['user'], $data['passw'], $data['database']);
        if(mysqli_connect_error()) {
            echo 'Server ' . $data['server'] . ' Connection error: ' . mysqli_connect_error();
        } else {
            //echo "range " . $range;

            $units = "";

            $dateTo = "";
            switch($range) {
                case "today":
                    $dateFrom = date('Y-m-d');
                    $dateTo = date('Y-m-d');
                    break;

                case "tweek":
                    $dayOfWeek = date('w');
                    $d = new DateTime();
                    if($dayOfWeek != 0) {
                        $d->modify('-' . strval($dayOfWeek) . ' days');
                    }
                    $dateFrom = date('Y-m-d', $d->GetTimestamp());
                    break;

                case "tmonth":
                    $dateFrom = date('Y-m') . '-01';
                    break;

                case "tyear":
                    $dateFrom = date('Y') . '-01-01';
                    break;

                case "lweek":
                    $dayOfWeek = date('w');
                    $d = new DateTime();
                    $d->modify('-' . strval($dayOfWeek + 7) . ' days');

                    $dateFrom = date('Y-m-d', $d->GetTimestamp());
                    $d->modify('+ 6 days');
                    $dateTo = date('Y-m-d', $d->GetTimestamp());
                    //echo 'Day of week ' . $dayOfWeek . ' Last week ' . $dateFrom . ' - ' . $dateTo . '<br>';
                    break;

                case "lmonth":
                    $d1 = strtotime("-1 months");
                     $dateFrom = date("Y-m", $d1) . "-01";
                     $dateTo = date("Y-m-t", $d1);
                    break;

                case "lyear":
                    $d1 = strtotime("-1 years");
                    $dateFrom = date("Y", $d1) . "-01-01";
                    $dateTo = date("Y", $d1) . "-12-31";
                    break;

                case "custom":
                    $dateFrom = $_GET['from'];
                    $dateTo = $_GET['to'];
                    break;
            }
            //echo "where ". $where;

            if($dateFrom == $dateTo) {
                $where = "WHERE datetm >= '" . $dateFrom . " 00:00:00' AND datetm <= '". $dateTo . " 23:59:59'";
            } else {
                if($dateTo != "") {
                    $where = "WHERE datetm >= '" . $dateFrom . " 00:00:00' AND datetm <= '". $dateTo . " 23:59:59'";
                } else {
                    $where = "WHERE datetm >= '" . $dateFrom . " 00:00:00'";
                }
            }

            if($type == "sum") {
                echo "<h2>Product Summary</h2>";
            } else {
                echo "<h2>Product Detail</h2>";
            }

        if($dateFrom == $dateTo) {
            echo "Date: " . $dateFrom;
        } else {
            echo "Date Range: " . $dateFrom . " - ";
            if($dateTo != "") {
                echo $dateTo;
            }
        }
         echo "<br><br>";

            if($type == "sum") {
          $sql = 'SELECT prod, units, COUNT(*) AS ProdCount, SUM(net) AS NetTotal FROM trans ' . $where . ' GROUP BY prod;';
            $result = $conn->query($sql);
            if(!$result) {
                echo "query error ";
            }
            if($result->num_rows > 0) {
                $totCount = 0;
                $totNet = 0;
                echo '<table id="tblSum"><tr><th>Product</th><th>Loads</th><th>Net weight total</th></tr>';
                while($row = $result->fetch_assoc()) {
                    $prod = $row['prod'];
                    $units = $row['units'];

                    $count = $row['ProdCount'];
                    $netTotal = $row['NetTotal'];
                    echo '<tr><td>' . $prod . '</td><td>' . $count . '</td><td>' . $netTotal .' ' . $units . '</td></tr>';
                    $totCount += $count;
                    $totNet += $netTotal;
                }
                echo '<tr><th>Total</th><th>' . $totCount . '</th><th>' . $totNet .' ' . $units . '</th></tr>';
                echo '</table>';
            }
        }
         else {

                $sql = 'SELECT * FROM trans ' . $where . ' ORDER BY prod,datetm;';
                $result = $conn->query($sql);
                if(!$result) {
                    echo "query error ";
                }
                if($result->num_rows > 0) {
                    $prodCnt = 0;
                    $rptCnt = 0;
                    $prodNetWt = 0;
                    $rptNetWt = 0;
                    $rowCnt = 0;
                    $prvProd = "";
                    echo '<table id="tblDet"><tr><th>Tran Num</th><th>Date/time</th><th>Vehicle</th><th>Custom</th><th>Job</th><th>Product</th><th>Net Wt</th></tr>';
                    while($row = $result->fetch_assoc()) {
                        $tkt_num = $row['tkt_num'];
                        $datetime = $row['datetm'];
                        $veh = $row['truck'];
                        $prod = $row['prod'];
                        $cust = $row['cust'];
                        $job = $row['job'];
                        //$gross = $row['gross'];
                        //$tare = $row['tare'];
                        $net = $row['net'];
                        $units = $row['units'];

                        if($rowCnt == 0) {
                            $prvProd = $prod;
                        }

                        if($prod != $prvProd) {
                            echo '<tr><th>Subtotal</th><th colspan="4"> Loads ' . $prodCnt . '</th><th>' . $prvProd. '</th><th>' . $prodNetWt .' ' . $units . '</th></tr>';
                           // echo '<tr><th>' . $prvProd. '</th><th colspan="4">Total ' . $prodCnt . ' loads</th><th>' . $prodNetWt . '</th><th></th></tr>';
                            $prodCnt = 0;
                            $prodNetWt = 0;

                            $prvProd = $prod;
                            echo '<tr><td colspan="6"> &nbsp;  </td></tr>';
                        }

                        echo '<tr><td><a href="/reports/idstrannum.php?num=' . $tkt_num . '">' . $tkt_num . '</a></td><td>' . $datetime . '</td><td>'  . $veh . '</td><td>' . $cust .  '</td><td>' . $job . '</td><td>' . $prod . '</td><td>'. $net  .' ' . $units . '</td>';
                        echo '</tr>';

                        $prodCnt++;
                        $rptCnt++;

                        $prodNetWt += $net;
                        $rptNetWt += $net;

                        $rowCnt++;
                    }
                    echo '<tr><th>Subtotal</th><th colspan="4"> Loads ' . $prodCnt . '</th><th>' . $prvProd. '</th><th>' . $prodNetWt . ' ' . $units .'</th></tr>';
                    echo '<tr><td colspan="6">&nbsp; </td></tr>';
                    echo '<tr><th>Total</th><th colspan="4">Loads ' . $rptCnt . '</th><th></th><th>' . $rptNetWt .' ' . $units . '</th><th></th></tr>';
                    echo '</table>';

                }

            }
        }
    }
}

?>
</body>
</html>

Attachments (6)

825webpagephp.png (9.5 KB ) - added by Don Wilson 2 years ago.
825webdevtools.png (18.8 KB ) - added by Don Wilson 2 years ago.
825webphpsqlite.png (10.7 KB ) - added by Don Wilson 2 years ago.
825webpage_ids_captured_images.png (167.7 KB ) - added by Don Wilson 21 months ago.
idsprodsum.png (35.8 KB ) - added by Don Wilson 31 hours ago.
idsproddet.png (105.9 KB ) - added by Don Wilson 31 hours ago.

Download all attachments as: .zip

Note: See TracWiki for help on using the wiki.

Download in other formats:

Plain Text