How each feature is built

Type words; every word must appear in the name, code, brand, model, serial, location or description.

Each word becomes one WHERE clause with LIKE '%word%' over several columns. Words are ANDed. Wildcards typed by the user (% _ [) are escaped, and values are sent as parameters, never glued into the SQL.

WHERE (a.name LIKE :p1 ESCAPE '\' OR a.description LIKE :p1 ESCAPE '\' OR ...)
  AND (a.name LIKE :p2 ESCAPE '\' OR ...)

Build conditions such as Price > 30000 AND Purchase date between two dates, matched with ALL or ANY.

Each row is FIELD~OPERATOR~VALUE in the URL (adv=PRICE~GT~30000). The builder maps the field to a whitelisted column and the operator to SQL, then joins the rows with AND or OR inside brackets.

AND (a.price > :p5 OR a.purchased_on BETWEEN :p6 AND :p7)

Ranked word search with prefix*, -exclude, "exact phrase" and OR.

SQL Server Express here has no Full-Text engine, so the app keeps its own inverted index (table search_token: word, asset, field, weight). Words found in the name weigh 10, in the description 1, and the sum is the rank. When a real full-text index exists (docs/full-text-catalog.sql) the app uses CONTAINSTABLE instead.

JOIN (SELECT asset_id, SUM(score) AS score FROM (
  SELECT asset_id, 1 AS term_no, SUM(weight * hits) AS score
  FROM search_token WHERE token IN (:p1) GROUP BY asset_id ...
) x GROUP BY asset_id HAVING COUNT(*) >= :p2) ft ON ft.asset_id = a.id
ORDER BY score DESC

Pick From / To or a preset (last 7 days, this month, this year ...) on the purchase or last-update date.

The range is half-open: date >= From AND date < To + 1 day, so the whole last day is included even for DATETIME2 columns, and an index on the column can be used (no function around the column).

WHERE a.purchased_on >= :p1 AND a.purchased_on < :p2   -- :p2 = To + 1 day

Tick one or more statuses. Each checkbox shows how many results it would give.

Checked values become an IN (...) list. Unknown values from the URL are dropped by the enum parser.

WHERE a.status IN (:p1)   -- expanded by NamedParameterJdbcTemplate to (?, ?)

Narrow down to laptops, monitors, vehicles ...

Same IN (...) technique on the category_id foreign key, which has its own index.

WHERE a.category_id IN (:p1)

Choose tags and whether an asset needs ANY of them or ALL of them.

Tags are many-to-many (asset_tag). ANY = EXISTS sub-query. ALL = GROUP BY asset HAVING COUNT(*) = number of tags (relational division).

-- ALL
WHERE a.id IN (SELECT asset_id FROM asset_tag WHERE tag_id IN (:p1)
               GROUP BY asset_id HAVING COUNT(*) = :p2)

Show the assets owned by one or more people. The list can be searched.

IN (...) on owner_id. The owner list is sorted by how many results each person has.

WHERE a.owner_id IN (:p1)

Show the assets of one or more departments.

IN (...) on department_id.

WHERE a.department_id IN (:p1)

Combine keyword, status, category, tags, user, department, dates and conditions at once. Counts update live.

Every filter adds its own WHERE clause, tagged with its dimension. Facet counts run the same query GROUP BY one column while leaving that column's own filter out, so you still see the other options.

SELECT a.status AS k, COUNT(*) FROM asset a
WHERE <all filters except status>
GROUP BY a.status

Save keyword + filters + sort under a name and run it again with one click.

All criteria live in the URL, so saving a search = saving its query string (table saved_search). Running it redirects to /search?<saved query string>.

INSERT INTO saved_search (user_id, name, query_string, summary) VALUES (?, ?, ?, ?)

Every search you run is listed with its result count and time; run it again or delete it.

After each search a row is written to search_history (skipped when it equals the previous one, e.g. paging). The page uses Spring Data paging (Pageable) and shows your most frequent and zero-result searches.

SELECT TOP (10) keyword, COUNT(*) FROM search_history
WHERE user_id = :u GROUP BY keyword ORDER BY COUNT(*) DESC

Matching assets and word completions appear while you type (arrow keys + Enter work).

The browser waits 150 ms after the last key (debounce), then calls /api/suggest. Codes and names that start with the text rank first; the last word is completed from the vocabulary table.

SELECT TOP 6 ... ORDER BY CASE WHEN asset_code LIKE 'abc%' THEN 0
  WHEN name LIKE 'abc%' THEN 1 WHEN name LIKE '% abc%' THEN 2 ELSE 3 END

Finds results even with typos: "labtop", "thinkpda", "microscpe", "fone".

Each word is compared with every indexed word using edit distance (Damerau-Levenshtein, 1 typo for short words, 2 for longer) and with SQL Server SOUNDEX (same sound). The look-alikes are searched too; the typed word scores double.

SELECT TOP 20 token FROM search_term
WHERE sound_code = SOUNDEX(:w) AND ABS(LEN(token) - LEN(:w)) <= 2

"Did you mean ...", popular searches, and one-click category / tag / department filters.

When a search finds nothing, each unknown word is replaced by its closest known word and the search runs again ("Showing results for printer"). Popular = most frequent keywords in everybody's history of the last 30 days.

SELECT TOP (6) keyword, COUNT(*) FROM search_history
WHERE searched_at >= DATEADD(DAY, -30, SYSDATETIME()) AND result_count > 0
GROUP BY keyword ORDER BY COUNT(*) DESC

Click the empty search box to see your latest searches.

Your distinct keywords ordered by their latest use (GROUP BY keyword ORDER BY MAX(searched_at) DESC).

SELECT TOP (8) keyword, MAX(searched_at) FROM search_history
WHERE user_id = :u GROUP BY keyword ORDER BY MAX(searched_at) DESC

Type or scan an EAN-13 barcode, upload a photo of it, or hold it up to the webcam.

A USB scanner types digits + Enter, so a normal input works. Photos and webcam frames are sent to the server and decoded with ZXing in Java. The 13th digit is a check digit, which is verified.

SELECT * FROM asset WHERE barcode = :code   -- unique index UQ_asset_barcode

Each asset has a QR code (SFL:AST-000123). Scan it from a picture or the webcam to open the asset.

The QR image is generated by ZXing (QRCodeWriter). Reading accepts our SFL: text, a link to /assets/{id}, an asset code or a serial number.

SELECT * FROM asset WHERE qr_payload = :text

Remove one filter with its × chip, or everything with "Clear all".

Each chip's × is a link to the same search without that one value, built by copying the criteria and removing it.

-- no SQL: the link simply leaves the parameter out

Store a set of filters (without keyword) as a coloured quick button above the results.

Only the filter part of the query string is saved (table saved_filter). Applying it keeps your current keyword and replaces the filters, so one saved filter works with any search.

INSERT INTO saved_filter (user_id, name, color, query_string, summary) VALUES (?, ?, ?, ?, ?)