Maintenance

This page is used for database backup and restore, direct database table viewing and editing, running maintenance SQL scripts, cleaning selected data, reposting inventory cost, and opening the cut-off workspace.

Use this menu only for technical administrators or senior finance users. Many actions on this page work directly on the live database and can change transaction history, balances, journals, stock, or report results.

What Is Available on the Maintenance Page

Backup Tab

Create database backup files, view the backup list, download backups, delete backup files, and run restore.

Edit Database Tab

View database table content directly, move through pages of data, then update specific rows and save them.

SQL Script Tab

Run a single maintenance SQL statement on the live database and review query results or affected row counts.

Others Tab

Open the cut-off page, delete attendance photos before a selected date, empty transactions, and repost inventory or journal cost data.

When to Use It

Before Using Maintenance

  1. Make sure you understand the purpose of the change and which tables or transactions will be touched.
  2. Create a fresh backup first, especially before restore, table editing, running INSERT / UPDATE / DELETE, empty transaction, repost cost, or cut-off.
  3. Make sure other users are not actively entering data in the same area when you plan to repair the database directly.
  4. Record what was changed: table name, transaction ID, date, reason for the change, and who requested it.

Choose the Right Action

Need Recommended Menu Note
Only want to view data Edit Database or SQL SELECT Start with a method that does not change data.
Need to correct one simple row Edit Database Useful when the row and field to change are already clear.
Need filtering or joins to find data SQL Script More flexible for audit and troubleshooting.
Need to update many rows at once SQL Script A backup is mandatory before execution.
Need to return to the state of a selected backup Restore Database Use it only when you are sure the selected backup file is correct.
Need to move old transactions into opening balances Cut Off Do not replace this process with manual table editing.

Database Backup

The Backup tab is used to create a database backup file that can be stored outside the server, downloaded, or used later for restore.

How to Create a Backup

  1. Open the Backup tab.
  2. Click Backup Database.
  3. Enter the backup file name. Use only letters, numbers, dots, underscores, or dashes.
  4. Wait for the process to finish. The system shows progress and then the file appears in the list.
  5. Download the backup if you want to store it elsewhere.

Important Notes

  • A good backup name is easy to identify, for example by using date, time, and the purpose of the change.
  • Keep a copy outside the application server when the backup may be needed for rollback.
  • Do not delete old backup files carelessly if they may still be needed for audit or rollback.

Database Restore

Restore overwrites the live database using the selected backup file, so it is a high-risk action.

How to Restore

  1. Select a backup file from the backup list.
  2. Click Restore on that row.
  3. Read the warning, then confirm by typing RESTORE.
  4. Wait until the process is complete and make sure the application works normally again.

Restore Risks

  • All changes made after the backup time will be lost from the live database.
  • Transactions, master data, journals, balances, and history created after the backup will not be included unless there is a newer backup.
  • If the wrong backup file is selected, users may lose recent working data.

Backup and Restore Examples

Before a Major Update or Fix

Create a clearly named backup such as before-fix-sales-2026-04-26 so the team can quickly identify the rollback point if a problem appears.

After a Wrong Mass Update

If the wrong change already touched many tables and is difficult to trace, restore is often safer than layered manual corrections that may miss side effects.

Viewing Data in Database Tables

The Edit Database tab is useful when you need to view database table content directly from the application. It is helpful when you want to inspect selected data without opening phpMyAdmin or another database tool.

How to View Data

  1. Open the Edit Database tab.
  2. Select the table name in Table Name.
  3. The system loads the column structure and table content.
  4. Use pagination to move through pages if the table has many rows.
  5. Review the column names, row values, and field relationships before deciding to change anything.

When This Tab Is Useful

  • Viewing original field values that are not visible on application forms.
  • Finding transaction IDs, statuses, flags, or data relationships for troubleshooting.
  • Verifying whether a transaction is really stored in a selected table.
If you only need to inspect data with more specific filters, the SQL Script tab is often safer than editing rows directly.

How to Edit Data Directly in a Table

The Edit Database tab can be used to change field values on a selected row and save it using the save button on the left side of that row.

Editing Steps

  1. Select the correct table.
  2. Find the row that must be corrected.
  3. Click the editable field and change the value.
  4. Review the entire row so no other field changes accidentally.
  5. Click the Save icon on that row.
  6. Make sure a success notification appears, then recheck the effect in the related form or report.

Important Notes

  • Not every column should be changed. Audit columns such as id, usercreate, useredit, and updatetimestamp usually should not be edited from this tool.
  • This editor focuses on updating existing data, not on running the full business workflow. A saved change does not always trigger the same validation used by the normal form.
  • After manual editing, recheck the effect on reports, balances, journals, stock, or related transaction status.

What to Check After a Manual Edit

Risks of Editing the Database Directly

Direct database editing should be done only when you fully understand the table relationships and business impact.

What Must Not Be Done

SQL Script

The SQL Script tab is used to run one SQL statement only on the live database. Results are shown in a table for SELECT statements, or as a success message with the number of affected rows for non-SELECT statements.

Allowed Commands

  • SELECT ...
  • INSERT INTO ...
  • UPDATE ...
  • DELETE FROM ...
  • REPLACE INTO ...
  • TRUNCATE TABLE ...
  • CREATE TABLE ...
  • ALTER TABLE ...
  • DROP TABLE ...

SQL Script Limits

  • Only one statement per execution. Multiple statements are not allowed.
  • The script may work only on the active database. Cross-database references such as db.table are rejected.
  • Server-level or global database commands cannot be run from this editor.

Rejected Commands

  • USE ...
  • CREATE DATABASE ...
  • DROP DATABASE ...
  • ALTER DATABASE ...
  • CREATE USER ...
  • DROP USER ...
  • GRANT ... and REVOKE ...
  • SET GLOBAL ...
  • INTO OUTFILE or INTO DUMPFILE
  • LOAD DATA ...

Safer Examples for Checking Data

SELECT * FROM account LIMIT 10
SELECT salesid, salesdate, customerid, salestotal FROM sales ORDER BY salesdate DESC LIMIT 20
SELECT productid, SUM(invin - invout) AS balance FROM inventory GROUP BY productid LIMIT 20
SELECT purchaseid, supplierid, purchasedate FROM purchase WHERE purchasedate >= '2026-01-01' ORDER BY purchasedate DESC LIMIT 50

Examples That Change Data

UPDATE sales SET memoedit='Admin correction' WHERE salesid='SO-001'
DELETE FROM customernote WHERE id='123'
ALTER TABLE customernote ADD COLUMN notepriority INT NOT NULL DEFAULT 0

For statements that change data, make sure a backup already exists and that you fully understand which rows will be affected.

SQL Script Scenarios

Data Audit

Start with SELECT to find the issue. Do not jump directly to UPDATE or DELETE.

Limited Correction

Use UPDATE only when the target row or ID is already certain and the affected scope is controlled.

Structure Change

Use ALTER TABLE only when you understand the effect on forms, reports, and application code.

Safe Order for Running an SQL Script

  1. Start with SELECT to confirm the correct table, rows, and filters.
  2. Count or estimate how many rows will be affected. If the result is larger than expected, stop first.
  3. Create a fresh backup if the script will change data or alter table structure.
  4. Write the final statement using a specific filter, then read it again before execution.
  5. After the script finishes, check the affected forms, reports, or related tables.

Quick Troubleshooting

Make sure the table name and ID are correct. If needed, use SQL Script with SELECT so you can apply filters, ordering, or joins to other tables.

Stop further changes. Record the statement that was run and the number of affected rows, then compare the result with a backup or another trusted reference. If the impact is broad, restore is often safer than fixing each side effect one by one.

Check the selected backup file name again, the backup creation time, and whether new transactions were entered after restore completed. Do not immediately run a second restore before understanding the source of the mismatch.

Others Tab

Delete Attendance Photos

Select the cutoff date, then confirm deletion by typing YES. This feature is used to remove attendance photo files before a selected date.

Empty Transaction

Empties transactions from the live database. This feature is very dangerous and requires the confirmation text IAGREEDELETEALLTRANSACTION. Use it only when you really want to delete transactions.

Repost Inventory Cost / Journal

Used to recalculate inventory cost and journal impact for inventory transactions. Use preview first before running the full repost.

Cut Off

Maintenance provides a button to open the cut-off workspace. Because the process is long and affects opening balances, transaction archives, and batch verification, its guide is separated into a dedicated page.

Open Cut Off Documentation

Safe Checklist Before Leaving the Page

Short FAQ

If you only need to view data, use SELECT or inspect it from Edit Database. If you need to change one simple row, Edit Database is easier. If you need complex filtering or mass updates, SQL Script is more suitable but also riskier.

Not always. Many manual changes only touch one table. Because of that, users still need to check related tables, reports, stock, balances, and journals.

When the error already affects many tables, many transactions, or you are not sure about the impact, restore from the correct backup is often safer than a long chain of manual corrections.

Menu Location