code-boxx/0-PHP-SIGNAGE.MD

## 0-PHP-SIGNAGE.MD

      
    Raw
  

              0-PHP-SIGNAGE.MD
            
          
    PHP MYSQL DIGITAL SIGNAGE

https://code-boxx.com/digital-signage-php-mysql/
NOTES


Create a test database and import 1-database.sql.
Change the database settings in 2-lib-pg.php to your own.
Access 3a-admin.html to update the pages, 4a-signage.html to show them.
Run unpack.bat (Windows) unpack.sh (Mac/Linux). This will create an assets folder and save the images below.

IMAGES


LICENSE

Copyright by Code Boxx
Permission is hereby granted, free of charge, to any person obtaining a copy
of this software and associated documentation files (the "Software"), to deal
in the Software without restriction, including without limitation the rights
to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
copies of the Software, and to permit persons to whom the Software is
furnished to do so, subject to the following conditions:
The above copyright notice and this permission notice shall be included in all
copies or substantial portions of the Software.
THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
SOFTWARE.

  
## 1-database.sql
-- (A) PAGES "VERSION"
CREATE TABLE `page_ver` (
  `pg_date` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `page_ver`
  ADD PRIMARY KEY (`pg_date`);

INSERT INTO `page_ver` (`pg_date`) VALUES ("2012-12-12 00:00:00");

-- (B) PAGES
CREATE TABLE `pages` (
  `pg_id` bigint(20) NOT NULL,
  `pg_title` varchar(255) NOT NULL,
  `pg_txt` text NOT NULL,
  `pg_sort` bigint(20) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `pages`
  ADD PRIMARY KEY (`pg_id`),
  ADD KEY `pg_sort` (`pg_sort`);

ALTER TABLE `pages`
  MODIFY `pg_id` bigint(20) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

INSERT INTO `pages` (`pg_id`, `pg_title`, `pg_txt`, `pg_sort`) VALUES
(1, 'Leh Mon', '<div class=\"center\">\r\n<div><img style=\"display: block; margin-left: auto; margin-right: auto;\" src=\"assets/img-a.png\" alt=\"\"></div>\r\n<div style=\"font-size: 48px; text-align: center;\"><span style=\"color: rgb(241, 196, 15); font-family: impact, sans-serif; font-size: 36pt;\">LEH MON</span></div>\r\n<div style=\"font-size: 48px; text-align: center;\"><span style=\"color: rgb(0, 0, 0); font-family: impact, sans-serif; font-size: 24pt;\">$999.99</span></div>\r\n</div>', 0),
(2, 'Kukumba', '<div class=\"center\">\r\n<div><img style=\"display: block; margin-left: auto; margin-right: auto;\" src=\"assets/img-b.png\" alt=\"\"></div>\r\n<div style=\"font-size: 48px; text-align: center;\"><span style=\"color: rgb(45, 194, 107); font-family: \'arial black\', sans-serif; font-size: 36pt;\">Koo Kum Bah</span></div>\r\n<div style=\"font-size: 48px; text-align: center;\"><span style=\"color: rgb(0, 0, 0); font-family: impact, sans-serif; font-size: 24pt;\">$123.45</span></div>\r\n</div>', 1),
(3, 'Appor', '<div class=\"center\">\r\n<div><img style=\"display: block; margin-left: auto; margin-right: auto;\" src=\"assets/img-c.png\" alt=\"\"></div>\r\n<div style=\"font-size: 48px; text-align: center;\"><strong><span style=\"color: rgb(224, 62, 45); font-family: \'comic sans ms\', sans-serif; font-size: 36pt;\">AH. PORU.</span></strong></div>\r\n<div style=\"font-size: 48px; text-align: center;\"><span style=\"color: rgb(0, 0, 0); font-family: impact, sans-serif; font-size: 24pt;\">$321.45</span></div>\r\n</div>', 2);

## 2-lib-pg.php
<?php
class Pages {
  // (A) CONSTRUCTOR - CONNECT TO DATABASE
  private $pdo = null;
  private $stmt = null;
  public $error = null;
  function __construct () {
    $this->pdo = new PDO(
      "mysql:host=".DB_HOST.";dbname=".DB_NAME.";charset=".DB_CHARSET,
      DB_USER, DB_PASSWORD, [
      PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
      PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
    ]);
  }

  // (B) DESTRUCTOR - CLOSE DATABASE CONNECTION
  function __destruct () {
    if ($this->stmt !== null) { $this->stmt = null; }
    if ($this->pdo !== null) { $this->pdo = null; }
  }

  // (C) HELPER FUNCTION - RUN SQL QUERY
  function query ($sql, $data=null) : void {
    $this->stmt = $this->pdo->prepare($sql);
    $this->stmt->execute($data);
  }

  // (D) GET ALL PAGES (MINUS TEXT)
  function getAll () {
    $this->query("SELECT `pg_id`, `pg_title` FROM `pages` ORDER BY `pg_sort` ASC");
    return $this->stmt->fetchAll();
  }

  // (E) GET PAGE
  function get ($id) {
    $this->query("SELECT * FROM `pages` WHERE `pg_id`=?", [$id]);
    return $this->stmt->fetch();
  }

  // (F) CHECK PAGES (GET PAGE ORDER FOR SIGNAGE)
  function check () {
    // (F1) "VERSION" TIMESTAMP
    $this->query("SELECT UNIX_TIMESTAMP(`pg_date`) FROM `page_ver`");
    $check = ["last"=>$this->stmt->fetchColumn(), "pages"=>[]];

    // (F2) PAGES ORDER
    $this->query("SELECT `pg_id` FROM `pages` ORDER BY `pg_sort` ASC");
    while ($r = $this->stmt->fetch()) { $check["pages"][] = $r["pg_id"]; }

    // (F3) DONE
    return $check;
  }

  // (G) UPDATE TIMESTAMP
  function touch () {
    $this->query("UPDATE `page_ver` SET `pg_date`=?", [date("Y-m-d H:i:s")]);
  }

  // (H) SAVE PAGE
  function save ($title, $txt, $id=null) {
    // (H1) NEW PAGE
    if ($id==null) {
      $this->query("UPDATE `pages` SET `pg_sort`=`pg_sort`+1");
      $this->query("INSERT INTO `pages` (`pg_title`, `pg_txt`) VALUES (?,?)", [$title, $txt]);
    }

    // (H2) UPDATE PAGE
    else {
      $this->query("UPDATE `pages` SET `pg_title`=?, `pg_txt`=? WHERE `pg_id`=?", [$title, $txt, $id]);
    }

    // (H3) TOUCH & DONE
    $this->touch();
    return true;
  }

  // (I) DELETE PAGE
  function del ($id) {
    $this->query("DELETE FROM `pages` WHERE `pg_id`=?", [$id]);
    $this->touch();
    return true;
  }

  // (J) SAVE SORT ORDER
  function order ($order) {
    foreach (json_decode($order) as $sort=>$id) {
      $this->query("UPDATE `pages` SET `pg_sort`=? WHERE `pg_id`=?", [$sort, $id]);
    }
    $this->touch();
    return true;
  }
}

// (K) DATABASE SETTINGS - CHANGE TO YOUR OWN!
define("DB_HOST", "localhost");
define("DB_NAME", "test");
define("DB_CHARSET", "utf8mb4");
define("DB_USER", "root");
define("DB_PASSWORD", "");

// (L) NEW PAGES OBJECT
$_PG = new Pages();

## 3a-admin.html
<!DOCTYPE html>
<html>
  <head>
    <title>Pages Admin</title>
    <meta charset="utf-8">
    <!-- https://cdnjs.com/libraries/tinymce -->
    <!-- https://www.tiny.cloud/docs/configure/ -->
    <link rel="stylesheet" href="3b-admin.css">
    <script src="https://cdnjs.cloudflare.com/ajax/libs/tinymce/6.4.2/tinymce.min.js"></script>
    <script src="3b-admin.js"></script>
  </head>
  <body>
    <!-- (A) LIST OF PAGES -->
    <div id="pgA">
      <div id="pgAdd" class="flex" onclick="adm.show()">
        &#43; ADD PAGE
      </div>
      <div id="pgList"></div>
    </div>

    <!-- (B) ADD/EDIT PAGE -->
    <form id="pgB" class="hide" onsubmit="return adm.save()"></form>
  </body>
</html>

## 3b-admin.css
/* (A) UNIVERSAL */
/* (A1) WHOLE PAGE */
* {
  font-family: Arial, Helvetica, sans-serif;
  box-sizing: border-box;
}
body {
  padding: 20px;
  max-width: 600px;
  margin: 0 auto;
  background: #f7f7f7;
}

/* (A2) SHARED */
.flex {
  display: flex;
  align-items: center;
}
.flexGrow { flex-grow: 1; }
.hide { display: none !important; }

/* (B) PAGES LIST */
/* (B1) ADD PAGE */
#pgAdd {
  justify-content: center;
  padding: 15px 0;
  color: #4f45ef;
  border: 2px dashed #9497ff;
  background: #fff;
  font-weight: 700;
  cursor: pointer;
}

/* (B2) PAGES WRAPPER & ROWS */
#pgList { margin-top: 10px; }
#pgList .row {
  margin-bottom: 10px;
  border: 1px solid #d1d1d1;
  background: #efefef;
}
#pgList .row:nth-child(even) { background: #f9f9f9; }
#pgList .row small { color: #514fd3; }
#pgList .row .ico {
  color: #d71f1f;
  font-size: 24px;
  padding: 10px;
  cursor: pointer;
}

/* (B3) PAGES LIST DRAG ORDER */
#pgList .row.hint {
  border: 1px dashed #aa0f0f;
  background: #feffc5;
}
#pgList .row.active {
  border: 1px dashed #ff0000;
  background: #fcff00;
}

/* (C) PAGE FORM */
/* (C1) FORM WRAPPER */
#pgB {
  padding: 20px;
  border: 1px solid #d1d1d1;
  background: #f9f9f9;
}

/* (C2) FORM ELEMENTS */
#pgB label, #pgB input[type=text], #pgB textarea {
  display: block;
  width: 100%;
}
#pgB label {
  font-weight: 700;
  padding: 10px 0;
}
#pgB input[type=text] {
  padding: 10px;
  border: 1px solid #9f9f9f;
}
#pgB input[type=text]:read-only { background: #f1fbff; }
#pgB input[type=button] {
  margin-top: 20px;
  font-weight: 700;
  color: #fff;
  background: #b10000;
  border: 0;
  padding: 10px 20px;
  cursor: pointer;
}

## 3b-admin.js
var adm = {
  // (A) INIT
  pgA : null,  // html page a
  pgB : null,  // html page b
  pgL : null,  // html pages list
  pgS : [],    // html list of pages
  pgD : null, // current page being dragged
  init : () => {
    // (A1) GET HTML ELEMENTS
    adm.pgA = document.getElementById("pgA");
    adm.pgB = document.getElementById("pgB");
    adm.pgL = document.getElementById("pgList");

    // (A2) INIT LOAD PAGES LIST
    adm.list();
  },

  // (B) SUPPORT FUNCTION - AJAX FETCH
  fetch : (data, load) => {
    // (B1) FORM DATA
    let form = new FormData();
    for (let [k,v] of Object.entries(data)) { form.append(k,v); }

    // (B2) FETCH
    fetch("3c-admin-ajax.php", { method:"POST", body:form })
    .then(res => res.text())
    .then(txt => load(txt))
    .catch(err => console.error(err));
  },

  // (C) SUPPORT FUNCTION - TOGGLE PAGE
  toggle : pg => {
    adm.pgA.classList.add("hide");
    adm.pgB.classList.add("hide");
    document.getElementById("pg"+pg).classList.remove("hide");
  },

  // (D) LIST ALL PAGES
  list : () => {
    // (D1) AJAX FETCH
    adm.fetch({ req : "list" },
    txt => {
      // (D2) PUT INTO HTML CONTAINER
      adm.pgL.innerHTML = txt;
      adm.toggle("A");

      // (D3) SORTABLE
      adm.pgS = document.querySelectorAll("#pgList .row");
      if (adm.pgS.length>0) { for (let div of adm.pgS) {
        // (D3-1) ON DRAG START - ADD DROPPABLE HINTS
        div.ondragstart = e => {
          adm.pgD = e.target;
          for (let p of adm.pgS) {
            p.classList.add("drag");
            if (p != adm.pgD) { p.classList.add("hint"); }
          }
        };

        // (D3-2) ON DRAG ENTER - HIGHLIGHT DROPZONE
        div.counter = 0;
        div.ondragenter = e => {
          div.counter++;
          if (div != adm.pgD ) { div.classList.add("active"); }
        };

        // (D3-3) DRAG LEAVE - REMOVE HIGHLIGHT DROPZONE
        div.ondragleave = e => {
          div.counter--;
          if (div.counter==0) { div.classList.remove("active"); }
        };

        // (D3-4) DRAG END - REMOVE ALL HIGHLIGHTS
        div.ondragend = e => { for (let p of adm.pgS) {
          p.counter = 0;
          p.classList.remove("drag");
          p.classList.remove("hint");
          p.classList.remove("active");
        }};

        // (D3-5) DRAG OVER - PREVENT DEFAULT "DROP", SO WE CAN DO OUR OWN
        div.ondragover = e => e.preventDefault();

        // (D3-6) ON DROP - REORDER NOTES & SAVE
        div.ondrop = e => {
          // (D3-6-1) PREVENT DEFAULT BROWSER DROP ACTION
          e.preventDefault();

          if (div != adm.pgD) {
            // (D3-6-2) GET CURRENT & DROPPED POSITIONS
            let idrag = 0, // index of currently dragged
                idrop = 0; // index of dropped location
            for (let i=0; i<adm.pgS.length; i++) {
              if (adm.pgD == adm.pgS[i]) { idrag = i; }
              if (div == adm.pgS[i]) { idrop = i; }
            }

            // (D3-6-3) REORDER HTML NOTES
            if (idrag > idrop) {
              adm.pgL.insertBefore(adm.pgD, div);
            } else {
              adm.pgL.insertBefore(adm.pgD, div.nextSibling);
            }

            // (D3-6-4) GET NEW ORDER
            adm.pgS = adm.pgL.querySelectorAll(".row");
            let order = [];
            for (let n of adm.pgS) { order.push(n.dataset.id); }

            // (D3-6-5) AJAX SAVE ORDER
            adm.fetch({
              req : "order",
              order : JSON.stringify(order)
            }, txt => {
              if (txt != "OK") { alert(txt); }
            });
          }
        };
      }}
    });
  },

  // (E) SHOW PAGE - ADD OR EDIT
  show : id => {
    // (E1) AJAX FETCH FORM
    adm.fetch({
      req : "show",
      id : (id==undefined ? "" : id)
    }, txt => {
      // (E2) CONTENTS INTO <DIV ID="PGB">
      document.getElementById("pgB").innerHTML = txt;
      adm.toggle("B");

      // (E3) TINYMCE
      // https://www.tiny.cloud/docs/advanced/available-menu-items/
      tinymce.remove();
      tinymce.init({
        selector : "#pgTxt",
        menubar : false,
        plugins : "image textcolor lists code",
        toolbar: "fontfamily fontsize | bold italic underline | backcolor forecolor | alignleft aligncenter alignright alignjustify | image | code"
      });
    });
  },

  // (F) SAVE PAGE
  save : () => {
    // (F1) GET FORM
    var data = {
      req : "save",
      title : document.getElementById("pgTitle").value,
      txt : tinymce.get("pgTxt").getContent(),
      id : document.getElementById("pgId").value
    };

    // (F2) CHECKS
    if (data.title=="") {
      alert("Please enter the title.");
      return false;
    }
    if (data.txt=="") {
      alert("Please fill in the page content.");
      return false;
    }

    // (F3) AJAX SAVE
    adm.fetch(data, txt => {
      if (txt=="OK") { adm.list(); alert("Page saved"); }
      else { alert(txt); }
    });
    return false;
  },

  // (G) DELETE PAGE
  del : id => { if (confirm("Delete page?")) {
    adm.fetch({
      req : "del",
      id : id
    }, txt => {
      if (txt=="OK") { adm.list(); alert("Page deleted"); }
      else { alert(txt); }
    });
  }}
};
window.onload = adm.init;

## 3c-admin-ajax.php
<?php
if (isset($_POST["req"])) {
  require "2-lib-pg.php";
  switch ($_POST["req"]) {
  // (A) LIST ALL PAGES
  case "list":
    $pages = $_PG->getAll();
    foreach ($pages as $p) { printf(
      "<div class='row flex' draggable='true' data-id='%u'>
        <div class='ico' onclick='adm.del(%u)'>&#10006;</div>
        <strong class='flexGrow'>%s</strong>
        <div class='ico' onclick='adm.show(%u)'>&#9998;</div>
      </div>",
        $p["pg_id"], $p["pg_id"], $p["pg_title"], $p["pg_id"]
      );
    }
    break;

  // (B) SHOW PAGE - ADD/EDIT
  case "show":
    // (B1) GET PAGE
    if (is_numeric($_POST["id"])) { $pg = $_PG->get($_POST["id"]); }

    // (B2) PAGE FORM ?>
    <label>Page Title</label>
    <input type="text" id="pgTitle" value="<?=isset($pg)?$pg["pg_title"]:""?>">

    <label>Page Content</label>
    <textarea id="pgTxt"><?=isset($pg)?$pg["pg_txt"]:""?></textarea>

    <input type="hidden" id="pgId" value="<?=isset($pg)?$pg["pg_id"]:""?>" required>
    <input type="button" value="Back" onclick="adm.toggle('A')">
    <input type="button" value="Save" onclick="adm.save()">
    <?php break;

  // (C) SAVE PAGE
  case "save":
    $_PG->save($_POST["title"], $_POST["txt"], $_POST["id"]);
    echo "OK";
    break;

  // (D) DELETE PAGE
  case "del":
    $_PG->del($_POST["id"]);
    echo "OK";
    break;

  // (E) SORT PAGES
  case "order":
    $_PG->order($_POST["order"]);
    echo "OK";
    break;
}}

## 4a-signage.html
<!DOCTYPE html>
<html>
  <head>
    <!-- TITLE + CHARSET + DESCRIPTION + VIEWPORT + FAVICON -->
    <title>Signage</title>
    <meta charset="utf-8">
    <meta name="description" content="Signage">
    <meta name="viewport" content="width=device-width, initial-scale=1.0, maximum-scale=1.5">
    <link rel="icon" href="assets/favicon.png" type="image/png">

    <!-- WEB APP MANIFEST -->
    <!-- https://web.dev/add-manifest/ -->
    <link rel="manifest" href="5-manifest.json">

    <!-- ANDROID + CHROME + APPLE + WINDOWS APP -->
    <meta name="mobile-web-app-capable" content="yes">
    <meta name="theme-color" content="white">
    <link rel="apple-touch-icon" href="assets/icon-512.png">
    <meta name="apple-mobile-web-app-capable" content="yes">
    <meta name="apple-mobile-web-app-status-bar-style" content="black">
    <meta name="apple-mobile-web-app-title" content="Signage">
    <meta name="msapplication-TileImage" content="assets/icon-512.png">
    <meta name="msapplication-TileColor" content="#ffffff">

    <!-- STYLESHEET + JAVASCRIPT -->
    <link rel="stylesheet" href="4b-signage.css">
    <script src="4b-signage.js"></script>
  </head>
  <body>
    <div id="pages"></div>
  </body>
</html>

## 4b-signage.css
/* (A) ENTIRE PAGE */
* {
  font-family: Arial, Helvetica, sans-serif;
  box-sizing: border-box;
}
body {
  padding: 0;
  margin: 0;
}

/* (B) PAGES */
#pages, .page {
  width: 100vw;
  height: 100vh;
}
#pages { position: relative; }
.page {
  position: absolute;
  top: 0; left: 0;
  opacity: 0;
  visibility: hidden;
  transition: opacity 0.5s;
}
.show {
  opacity: 1;
  visibility: visible;
}

## 4b-signage.js
var sign = {
  // (A) SUPPORT FUNCTION - AJAX FETCH
  fetch : (data, load) => {
    // (A1) DATA
    let query = new URLSearchParams();
    for (let [k,v] of Object.entries(data)) { query.append(k,v); }

    // (A2) FETCH
    fetch("4c-signage-ajax.php?" + query.toString())
    .then(res => res.text())
    .then(txt => load(txt))
    .catch(err => console.error(err));
  },

  // (B) PAGE INIT
  hWrap : null, // html wrapper
  version : null, // last updated pages data
  pages : [], // loaded pages
  delay : 5000, // delay between each page (ms)
  init : () => {
    // (B1) GET HTML WRAPPER
    sign.hWrap = document.getElementById("pages");

    // (B2) RESTORE PAGES DATA
    sign.version = localStorage.getItem("version");
    if (sign.version != null) { sign.version = JSON.parse(sign.version); }

    // (B3) REGISTER SERVICE WORKER
    if ("serviceWorker" in navigator) {
      navigator.serviceWorker.register("5-worker.js", {scope: "/"});
    }

    // (B4) IF ONLINE - CHECK FOR POSSIBLE UPDATES
    // (B4) ELSE - JUST LOAD FROM CACHE
    if (navigator.onLine) {
      sign.fetch({ req : "check" }, res => {
        res = JSON.parse(res);
        if (sign.version==null || sign.version.last < res.last) {
          sign.version = res;
          localStorage.setItem("version", JSON.stringify(sign.version));
          sign.cache();
        }
        sign.load();
      });
    } else { sign.load(); }
  },

  // (C) LOAD PAGES
  load : () => { if (sign.version != null) {
    for (let id of sign.version.pages) {
      sign.fetch({ req : "get", id : id }, res => {
        let div = document.createElement("div");
        div.className = "page";
        div.innerHTML = res;
        sign.hWrap.appendChild(div);
        sign.pages.push(div);
        if (sign.pages.length >= sign.version.pages.length) {
          sign.run();
          setInterval(sign.run, sign.delay);
        }
      });
    }
  }},

  // (D) UPDATE CACHE
  cache : async () => {
    // (D1) REMOVE OLD CACHE
    await caches.delete("PAGES");

    // (D2) LIST OF PAGES TO CACHE
    let updater = [];
    for (let id of sign.version.pages) {
      updater.push("4c-signage-ajax.php?req=get&id=" + id);
    }

    // (D3) GO!
    caches.open("PAGES")
    .then(cache => cache.addAll(updater))
    .catch(err => console.error(err));
  },

  // (E) ROTATE PAGES
  current : -1, // current page
  run : () => {
    // (E1) HIDE ALL
    for (let p of sign.pages) { p.classList.remove("show"); }

    // (E2) SHOW NEXT PAGE
    sign.current++;
    if (sign.current >= sign.pages.length) { sign.current = 0; }
    sign.pages[sign.current].classList.add("show");
  }
};
window.onload = sign.init;

## 4c-signage-ajax.php
<?php
if (isset($_GET["req"])) {
  require "2-lib-pg.php";
  switch ($_GET["req"]) {
    // (A) CHECK PAGE "VERSION"
    case "check":
      echo json_encode($_PG->check());
      break;

    // (B) GET PAGE
    case "get":
      $pg = $_PG->get($_GET["id"]);
      if (isset($pg["pg_txt"])) { echo $pg["pg_txt"]; }
      else { echo "Invalid page"; }
      break;
}}

## 5-manifest.json
{
  "short_name": "Signage",
  "name": "Signage",
  "icons": [{
    "src": "assets/favicon.png",
    "sizes": "64x64",
    "type": "image/png"
  }, {
    "src": "assets/icon-512.png",
    "sizes": "512x512",
    "type": "image/png"
  }],
  "start_url": "4a-signage.html",
  "scope": "/",
  "background_color": "white",
  "theme_color": "white",
  "display": "standalone"
}

## 5-worker.js
// (A) CREATE/INSTALL CACHE
self.addEventListener("install", evt => {
  self.skipWaiting();
  evt.waitUntil(
    caches.open("BASE")
    .then(cache => cache.addAll([
      "4a-signage.html",
      "4b-signage.css",
      "4b-signage.js",
      "5-manifest.json"
    ]))
    .catch(err => console.error(err))
  );
});

// (B) CLAIM CONTROL INSTANTLY
self.addEventListener("activate", evt => self.clients.claim());

// (C) LOAD FROM CACHE FIRST, FALLBACK TO NETWORK IF NOT FOUND
self.addEventListener("fetch", evt => evt.respondWith(
  caches.match(evt.request).then(res => res || fetch(evt.request))
));

## unpack.bat
md assets
curl https://user-images.githubusercontent.com/11156244/282664989-2619dc64-5c72-40f7-8da2-479587edf241.png --ssl-no-revoke --output assets/img-a.png
curl https://user-images.githubusercontent.com/11156244/282664998-16d3ce48-d70b-4a00-8788-3edfad1c9cba.png --ssl-no-revoke --output assets/img-b.png
curl https://user-images.githubusercontent.com/11156244/282665000-0feceb5d-a83e-4347-939d-f3df62e423d5.png --ssl-no-revoke --output assets/img-c.png
curl https://user-images.githubusercontent.com/11156244/239427970-cc8e62c0-3d44-48e1-9e20-d1a6f25b52e4.png --ssl-no-revoke --output assets/icon-512.png
curl https://user-images.githubusercontent.com/11156244/239427974-4622c4ea-3cfd-491b-8c32-a2fd01874621.png --ssl-no-revoke --output assets/favicon.png

## unpack.sh
mkdir -m 777 assets
curl https://user-images.githubusercontent.com/11156244/282664989-2619dc64-5c72-40f7-8da2-479587edf241.png --ssl-no-revoke --output ./assets/img-a.png
curl https://user-images.githubusercontent.com/11156244/282664998-16d3ce48-d70b-4a00-8788-3edfad1c9cba.png --ssl-no-revoke --output ./assets/img-b.png
curl https://user-images.githubusercontent.com/11156244/282665000-0feceb5d-a83e-4347-939d-f3df62e423d5.png --ssl-no-revoke --output ./assets/img-c.png
curl https://user-images.githubusercontent.com/11156244/239427970-cc8e62c0-3d44-48e1-9e20-d1a6f25b52e4.png --ssl-no-revoke --output ./assets/icon-512.png
curl https://user-images.githubusercontent.com/11156244/239427974-4622c4ea-3cfd-491b-8c32-a2fd01874621.png --ssl-no-revoke --output ./assets/favicon.png
	-- (A) PAGES "VERSION"
	CREATE TABLE `page_ver` (
	`pg_date` datetime NOT NULL DEFAULT current_timestamp()
	) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

	ALTER TABLE `page_ver`
	ADD PRIMARY KEY (`pg_date`);

	INSERT INTO `page_ver` (`pg_date`) VALUES ("2012-12-12 00:00:00");

	-- (B) PAGES
	CREATE TABLE `pages` (
	`pg_id` bigint(20) NOT NULL,
	`pg_title` varchar(255) NOT NULL,
	`pg_txt` text NOT NULL,
	`pg_sort` bigint(20) NOT NULL DEFAULT 0
	) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

	ALTER TABLE `pages`
	ADD PRIMARY KEY (`pg_id`),
	ADD KEY `pg_sort` (`pg_sort`);

	ALTER TABLE `pages`
	MODIFY `pg_id` bigint(20) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

	INSERT INTO `pages` (`pg_id`, `pg_title`, `pg_txt`, `pg_sort`) VALUES
	(1, 'Leh Mon', '<div class=\"center\">\r\n<div><img style=\"display: block; margin-left: auto; margin-right: auto;\" src=\"assets/img-a.png\" alt=\"\"></div>\r\n<div style=\"font-size: 48px; text-align: center;\"><span style=\"color: rgb(241, 196, 15); font-family: impact, sans-serif; font-size: 36pt;\">LEH MON</span></div>\r\n<div style=\"font-size: 48px; text-align: center;\"><span style=\"color: rgb(0, 0, 0); font-family: impact, sans-serif; font-size: 24pt;\">$999.99</span></div>\r\n</div>', 0),
	(2, 'Kukumba', '<div class=\"center\">\r\n<div><img style=\"display: block; margin-left: auto; margin-right: auto;\" src=\"assets/img-b.png\" alt=\"\"></div>\r\n<div style=\"font-size: 48px; text-align: center;\"><span style=\"color: rgb(45, 194, 107); font-family: \'arial black\', sans-serif; font-size: 36pt;\">Koo Kum Bah</span></div>\r\n<div style=\"font-size: 48px; text-align: center;\"><span style=\"color: rgb(0, 0, 0); font-family: impact, sans-serif; font-size: 24pt;\">$123.45</span></div>\r\n</div>', 1),
	(3, 'Appor', '<div class=\"center\">\r\n<div><img style=\"display: block; margin-left: auto; margin-right: auto;\" src=\"assets/img-c.png\" alt=\"\"></div>\r\n<div style=\"font-size: 48px; text-align: center;\"><strong><span style=\"color: rgb(224, 62, 45); font-family: \'comic sans ms\', sans-serif; font-size: 36pt;\">AH. PORU.</span></strong></div>\r\n<div style=\"font-size: 48px; text-align: center;\"><span style=\"color: rgb(0, 0, 0); font-family: impact, sans-serif; font-size: 24pt;\">$321.45</span></div>\r\n</div>', 2);
	<?php
	class Pages {
	// (A) CONSTRUCTOR - CONNECT TO DATABASE
	private $pdo = null;
	private $stmt = null;
	public $error = null;
	function __construct () {
	$this->pdo = new PDO(
	"mysql:host=".DB_HOST.";dbname=".DB_NAME.";charset=".DB_CHARSET,
	DB_USER, DB_PASSWORD, [
	PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
	PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
	]);
	}

	// (B) DESTRUCTOR - CLOSE DATABASE CONNECTION
	function __destruct () {
	if ($this->stmt !== null) { $this->stmt = null; }
	if ($this->pdo !== null) { $this->pdo = null; }
	}

	// (C) HELPER FUNCTION - RUN SQL QUERY
	function query ($sql, $data=null) : void {
	$this->stmt = $this->pdo->prepare($sql);
	$this->stmt->execute($data);
	}

	// (D) GET ALL PAGES (MINUS TEXT)
	function getAll () {
	$this->query("SELECT `pg_id`, `pg_title` FROM `pages` ORDER BY `pg_sort` ASC");
	return $this->stmt->fetchAll();
	}

	// (E) GET PAGE
	function get ($id) {
	$this->query("SELECT * FROM `pages` WHERE `pg_id`=?", [$id]);
	return $this->stmt->fetch();
	}

	// (F) CHECK PAGES (GET PAGE ORDER FOR SIGNAGE)
	function check () {
	// (F1) "VERSION" TIMESTAMP
	$this->query("SELECT UNIX_TIMESTAMP(`pg_date`) FROM `page_ver`");
	$check = ["last"=>$this->stmt->fetchColumn(), "pages"=>[]];

	// (F2) PAGES ORDER
	$this->query("SELECT `pg_id` FROM `pages` ORDER BY `pg_sort` ASC");
	while ($r = $this->stmt->fetch()) { $check["pages"][] = $r["pg_id"]; }

	// (F3) DONE
	return $check;
	}

	// (G) UPDATE TIMESTAMP
	function touch () {
	$this->query("UPDATE `page_ver` SET `pg_date`=?", [date("Y-m-d H:i:s")]);
	}

	// (H) SAVE PAGE
	function save ($title, $txt, $id=null) {
	// (H1) NEW PAGE
	if ($id==null) {
	$this->query("UPDATE `pages` SET `pg_sort`=`pg_sort`+1");
	$this->query("INSERT INTO `pages` (`pg_title`, `pg_txt`) VALUES (?,?)", [$title, $txt]);
	}

	// (H2) UPDATE PAGE
	else {
	$this->query("UPDATE `pages` SET `pg_title`=?, `pg_txt`=? WHERE `pg_id`=?", [$title, $txt, $id]);
	}

	// (H3) TOUCH & DONE
	$this->touch();
	return true;
	}

	// (I) DELETE PAGE
	function del ($id) {
	$this->query("DELETE FROM `pages` WHERE `pg_id`=?", [$id]);
	$this->touch();
	return true;
	}

	// (J) SAVE SORT ORDER
	function order ($order) {
	foreach (json_decode($order) as $sort=>$id) {
	$this->query("UPDATE `pages` SET `pg_sort`=? WHERE `pg_id`=?", [$sort, $id]);
	}
	$this->touch();
	return true;
	}
	}

	// (K) DATABASE SETTINGS - CHANGE TO YOUR OWN!
	define("DB_HOST", "localhost");
	define("DB_NAME", "test");
	define("DB_CHARSET", "utf8mb4");
	define("DB_USER", "root");
	define("DB_PASSWORD", "");

	// (L) NEW PAGES OBJECT
	$_PG = new Pages();
	<!DOCTYPE html>
	<html>
	<head>
	<title>Pages Admin</title>
	<meta charset="utf-8">
	<!-- https://cdnjs.com/libraries/tinymce -->
	<!-- https://www.tiny.cloud/docs/configure/ -->
	<link rel="stylesheet" href="3b-admin.css">
	<script src="https://cdnjs.cloudflare.com/ajax/libs/tinymce/6.4.2/tinymce.min.js"></script>
	<script src="3b-admin.js"></script>
	</head>
	<body>
	<!-- (A) LIST OF PAGES -->
	<div id="pgA">
	<div id="pgAdd" class="flex" onclick="adm.show()">
	+ ADD PAGE
	</div>
	<div id="pgList"></div>
	</div>

	<!-- (B) ADD/EDIT PAGE -->
	<form id="pgB" class="hide" onsubmit="return adm.save()"></form>
	</body>
	</html>
	/* (A) UNIVERSAL */
	/* (A1) WHOLE PAGE */
	* {
	font-family: Arial, Helvetica, sans-serif;
	box-sizing: border-box;
	}
	body {
	padding: 20px;
	max-width: 600px;
	margin: 0 auto;
	background: #f7f7f7;
	}

	/* (A2) SHARED */
	.flex {
	display: flex;
	align-items: center;
	}
	.flexGrow { flex-grow: 1; }
	.hide { display: none !important; }

	/* (B) PAGES LIST */
	/* (B1) ADD PAGE */
	#pgAdd {
	justify-content: center;
	padding: 15px 0;
	color: #4f45ef;
	border: 2px dashed #9497ff;
	background: #fff;
	font-weight: 700;
	cursor: pointer;
	}

	/* (B2) PAGES WRAPPER & ROWS */
	#pgList { margin-top: 10px; }
	#pgList .row {
	margin-bottom: 10px;
	border: 1px solid #d1d1d1;
	background: #efefef;
	}
	#pgList .row:nth-child(even) { background: #f9f9f9; }
	#pgList .row small { color: #514fd3; }
	#pgList .row .ico {
	color: #d71f1f;
	font-size: 24px;
	padding: 10px;
	cursor: pointer;
	}

	/* (B3) PAGES LIST DRAG ORDER */
	#pgList .row.hint {
	border: 1px dashed #aa0f0f;
	background: #feffc5;
	}
	#pgList .row.active {
	border: 1px dashed #ff0000;
	background: #fcff00;
	}

	/* (C) PAGE FORM */
	/* (C1) FORM WRAPPER */
	#pgB {
	padding: 20px;
	border: 1px solid #d1d1d1;
	background: #f9f9f9;
	}

	/* (C2) FORM ELEMENTS */
	#pgB label, #pgB input[type=text], #pgB textarea {
	display: block;
	width: 100%;
	}
	#pgB label {
	font-weight: 700;
	padding: 10px 0;
	}
	#pgB input[type=text] {
	padding: 10px;
	border: 1px solid #9f9f9f;
	}
	#pgB input[type=text]:read-only { background: #f1fbff; }
	#pgB input[type=button] {
	margin-top: 20px;
	font-weight: 700;
	color: #fff;
	background: #b10000;
	border: 0;
	padding: 10px 20px;
	cursor: pointer;
	}
	var adm = {
	// (A) INIT
	pgA : null, // html page a
	pgB : null, // html page b
	pgL : null, // html pages list
	pgS : [], // html list of pages
	pgD : null, // current page being dragged
	init : () => {
	// (A1) GET HTML ELEMENTS
	adm.pgA = document.getElementById("pgA");
	adm.pgB = document.getElementById("pgB");
	adm.pgL = document.getElementById("pgList");

	// (A2) INIT LOAD PAGES LIST
	adm.list();
	},

	// (B) SUPPORT FUNCTION - AJAX FETCH
	fetch : (data, load) => {
	// (B1) FORM DATA
	let form = new FormData();
	for (let [k,v] of Object.entries(data)) { form.append(k,v); }

	// (B2) FETCH
	fetch("3c-admin-ajax.php", { method:"POST", body:form })
	.then(res => res.text())
	.then(txt => load(txt))
	.catch(err => console.error(err));
	},

	// (C) SUPPORT FUNCTION - TOGGLE PAGE
	toggle : pg => {
	adm.pgA.classList.add("hide");
	adm.pgB.classList.add("hide");
	document.getElementById("pg"+pg).classList.remove("hide");
	},

	// (D) LIST ALL PAGES
	list : () => {
	// (D1) AJAX FETCH
	adm.fetch({ req : "list" },
	txt => {
	// (D2) PUT INTO HTML CONTAINER
	adm.pgL.innerHTML = txt;
	adm.toggle("A");

	// (D3) SORTABLE
	adm.pgS = document.querySelectorAll("#pgList .row");
	if (adm.pgS.length>0) { for (let div of adm.pgS) {
	// (D3-1) ON DRAG START - ADD DROPPABLE HINTS
	div.ondragstart = e => {
	adm.pgD = e.target;
	for (let p of adm.pgS) {
	p.classList.add("drag");
	if (p != adm.pgD) { p.classList.add("hint"); }
	}
	};

	// (D3-2) ON DRAG ENTER - HIGHLIGHT DROPZONE
	div.counter = 0;
	div.ondragenter = e => {
	div.counter++;
	if (div != adm.pgD ) { div.classList.add("active"); }
	};

	// (D3-3) DRAG LEAVE - REMOVE HIGHLIGHT DROPZONE
	div.ondragleave = e => {
	div.counter--;
	if (div.counter==0) { div.classList.remove("active"); }
	};

	// (D3-4) DRAG END - REMOVE ALL HIGHLIGHTS
	div.ondragend = e => { for (let p of adm.pgS) {
	p.counter = 0;
	p.classList.remove("drag");
	p.classList.remove("hint");
	p.classList.remove("active");
	}};

	// (D3-5) DRAG OVER - PREVENT DEFAULT "DROP", SO WE CAN DO OUR OWN
	div.ondragover = e => e.preventDefault();

	// (D3-6) ON DROP - REORDER NOTES & SAVE
	div.ondrop = e => {
	// (D3-6-1) PREVENT DEFAULT BROWSER DROP ACTION
	e.preventDefault();

	if (div != adm.pgD) {
	// (D3-6-2) GET CURRENT & DROPPED POSITIONS
	let idrag = 0, // index of currently dragged
	idrop = 0; // index of dropped location
	for (let i=0; i<adm.pgS.length; i++) {
	if (adm.pgD == adm.pgS[i]) { idrag = i; }
	if (div == adm.pgS[i]) { idrop = i; }
	}

	// (D3-6-3) REORDER HTML NOTES
	if (idrag > idrop) {
	adm.pgL.insertBefore(adm.pgD, div);
	} else {
	adm.pgL.insertBefore(adm.pgD, div.nextSibling);
	}

	// (D3-6-4) GET NEW ORDER
	adm.pgS = adm.pgL.querySelectorAll(".row");
	let order = [];
	for (let n of adm.pgS) { order.push(n.dataset.id); }

	// (D3-6-5) AJAX SAVE ORDER
	adm.fetch({
	req : "order",
	order : JSON.stringify(order)
	}, txt => {
	if (txt != "OK") { alert(txt); }
	});
	}
	};
	}}
	});
	},

	// (E) SHOW PAGE - ADD OR EDIT
	show : id => {
	// (E1) AJAX FETCH FORM
	adm.fetch({
	req : "show",
	id : (id==undefined ? "" : id)
	}, txt => {
	// (E2) CONTENTS INTO <DIV ID="PGB">
	document.getElementById("pgB").innerHTML = txt;
	adm.toggle("B");

	// (E3) TINYMCE
	// https://www.tiny.cloud/docs/advanced/available-menu-items/
	tinymce.remove();
	tinymce.init({
	selector : "#pgTxt",
	menubar : false,
	plugins : "image textcolor lists code",
	toolbar: "fontfamily fontsize \| bold italic underline \| backcolor forecolor \| alignleft aligncenter alignright alignjustify \| image \| code"
	});
	});
	},

	// (F) SAVE PAGE
	save : () => {
	// (F1) GET FORM
	var data = {
	req : "save",
	title : document.getElementById("pgTitle").value,
	txt : tinymce.get("pgTxt").getContent(),
	id : document.getElementById("pgId").value
	};

	// (F2) CHECKS
	if (data.title=="") {
	alert("Please enter the title.");
	return false;
	}
	if (data.txt=="") {
	alert("Please fill in the page content.");
	return false;
	}

	// (F3) AJAX SAVE
	adm.fetch(data, txt => {
	if (txt=="OK") { adm.list(); alert("Page saved"); }
	else { alert(txt); }
	});
	return false;
	},

	// (G) DELETE PAGE
	del : id => { if (confirm("Delete page?")) {
	adm.fetch({
	req : "del",
	id : id
	}, txt => {
	if (txt=="OK") { adm.list(); alert("Page deleted"); }
	else { alert(txt); }
	});
	}}
	};
	window.onload = adm.init;
	<?php
	if (isset($_POST["req"])) {
	require "2-lib-pg.php";
	switch ($_POST["req"]) {
	// (A) LIST ALL PAGES
	case "list":
	$pages = $_PG->getAll();
	foreach ($pages as $p) { printf(
	"<div class='row flex' draggable='true' data-id='%u'>
	<div class='ico' onclick='adm.del(%u)'>✖</div>
	<strong class='flexGrow'>%s</strong>
	<div class='ico' onclick='adm.show(%u)'>✎</div>
	</div>",
	$p["pg_id"], $p["pg_id"], $p["pg_title"], $p["pg_id"]
	);
	}
	break;

	// (B) SHOW PAGE - ADD/EDIT
	case "show":
	// (B1) GET PAGE
	if (is_numeric($_POST["id"])) { $pg = $_PG->get($_POST["id"]); }

	// (B2) PAGE FORM ?>
	<label>Page Title</label>
	<input type="text" id="pgTitle" value="<?=isset($pg)?$pg["pg_title"]:""?>">

	<label>Page Content</label>
	<textarea id="pgTxt"><?=isset($pg)?$pg["pg_txt"]:""?></textarea>

	<input type="hidden" id="pgId" value="<?=isset($pg)?$pg["pg_id"]:""?>" required>
	<input type="button" value="Back" onclick="adm.toggle('A')">
	<input type="button" value="Save" onclick="adm.save()">
	<?php break;

	// (C) SAVE PAGE
	case "save":
	$_PG->save($_POST["title"], $_POST["txt"], $_POST["id"]);
	echo "OK";
	break;

	// (D) DELETE PAGE
	case "del":
	$_PG->del($_POST["id"]);
	echo "OK";
	break;

	// (E) SORT PAGES
	case "order":
	$_PG->order($_POST["order"]);
	echo "OK";
	break;
	}}
	/* (A) ENTIRE PAGE */
	* {
	font-family: Arial, Helvetica, sans-serif;
	box-sizing: border-box;
	}
	body {
	padding: 0;
	margin: 0;
	}

	/* (B) PAGES */
	#pages, .page {
	width: 100vw;
	height: 100vh;
	}
	#pages { position: relative; }
	.page {
	position: absolute;
	top: 0; left: 0;
	opacity: 0;
	visibility: hidden;
	transition: opacity 0.5s;
	}
	.show {
	opacity: 1;
	visibility: visible;
	}
	var sign = {
	// (A) SUPPORT FUNCTION - AJAX FETCH
	fetch : (data, load) => {
	// (A1) DATA
	let query = new URLSearchParams();
	for (let [k,v] of Object.entries(data)) { query.append(k,v); }

	// (A2) FETCH
	fetch("4c-signage-ajax.php?" + query.toString())
	.then(res => res.text())
	.then(txt => load(txt))
	.catch(err => console.error(err));
	},

	// (B) PAGE INIT
	hWrap : null, // html wrapper
	version : null, // last updated pages data
	pages : [], // loaded pages
	delay : 5000, // delay between each page (ms)
	init : () => {
	// (B1) GET HTML WRAPPER
	sign.hWrap = document.getElementById("pages");

	// (B2) RESTORE PAGES DATA
	sign.version = localStorage.getItem("version");
	if (sign.version != null) { sign.version = JSON.parse(sign.version); }

	// (B3) REGISTER SERVICE WORKER
	if ("serviceWorker" in navigator) {
	navigator.serviceWorker.register("5-worker.js", {scope: "/"});
	}

	// (B4) IF ONLINE - CHECK FOR POSSIBLE UPDATES
	// (B4) ELSE - JUST LOAD FROM CACHE
	if (navigator.onLine) {
	sign.fetch({ req : "check" }, res => {
	res = JSON.parse(res);
	if (sign.version==null \|\| sign.version.last < res.last) {
	sign.version = res;
	localStorage.setItem("version", JSON.stringify(sign.version));
	sign.cache();
	}
	sign.load();
	});
	} else { sign.load(); }
	},

	// (C) LOAD PAGES
	load : () => { if (sign.version != null) {
	for (let id of sign.version.pages) {
	sign.fetch({ req : "get", id : id }, res => {
	let div = document.createElement("div");
	div.className = "page";
	div.innerHTML = res;
	sign.hWrap.appendChild(div);
	sign.pages.push(div);
	if (sign.pages.length >= sign.version.pages.length) {
	sign.run();
	setInterval(sign.run, sign.delay);
	}
	});
	}
	}},

	// (D) UPDATE CACHE
	cache : async () => {
	// (D1) REMOVE OLD CACHE
	await caches.delete("PAGES");

	// (D2) LIST OF PAGES TO CACHE
	let updater = [];
	for (let id of sign.version.pages) {
	updater.push("4c-signage-ajax.php?req=get&id=" + id);
	}

	// (D3) GO!
	caches.open("PAGES")
	.then(cache => cache.addAll(updater))
	.catch(err => console.error(err));
	},

	// (E) ROTATE PAGES
	current : -1, // current page
	run : () => {
	// (E1) HIDE ALL
	for (let p of sign.pages) { p.classList.remove("show"); }

	// (E2) SHOW NEXT PAGE
	sign.current++;
	if (sign.current >= sign.pages.length) { sign.current = 0; }
	sign.pages[sign.current].classList.add("show");
	}
	};
	window.onload = sign.init;
	<?php
	if (isset($_GET["req"])) {
	require "2-lib-pg.php";
	switch ($_GET["req"]) {
	// (A) CHECK PAGE "VERSION"
	case "check":
	echo json_encode($_PG->check());
	break;

	// (B) GET PAGE
	case "get":
	$pg = $_PG->get($_GET["id"]);
	if (isset($pg["pg_txt"])) { echo $pg["pg_txt"]; }
	else { echo "Invalid page"; }
	break;
	}}
	{
	"short_name": "Signage",
	"name": "Signage",
	"icons": [{
	"src": "assets/favicon.png",
	"sizes": "64x64",
	"type": "image/png"
	}, {
	"src": "assets/icon-512.png",
	"sizes": "512x512",
	"type": "image/png"
	}],
	"start_url": "4a-signage.html",
	"scope": "/",
	"background_color": "white",
	"theme_color": "white",
	"display": "standalone"
	}