-- ===================================================================== -- WMS Web | MySQL 8 Schema -- Run this once on your cPanel MySQL database. -- ===================================================================== CREATE TABLE tblCompany ( company_id INT AUTO_INCREMENT PRIMARY KEY, company_name VARCHAR(150) NOT NULL, status ENUM('Active','Suspended') DEFAULT 'Active', created_at DATETIME DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB; CREATE TABLE tblSite ( site_id INT AUTO_INCREMENT PRIMARY KEY, company_id INT NOT NULL, site_name VARCHAR(150) NOT NULL, address VARCHAR(255), status ENUM('Active','Suspended') DEFAULT 'Active', FOREIGN KEY (company_id) REFERENCES tblCompany(company_id) ) ENGINE=InnoDB; CREATE TABLE tblWeighbridge ( device_id VARCHAR(50) PRIMARY KEY, site_id INT NOT NULL, wb_name VARCHAR(100), device_key VARCHAR(255) NOT NULL, -- bcrypt hash of the device key, never the raw value status ENUM('Active','Inactive') DEFAULT 'Active', FOREIGN KEY (site_id) REFERENCES tblSite(site_id) ) ENGINE=InnoDB; CREATE TABLE tblLive_Weight ( device_id VARCHAR(50) PRIMARY KEY, weight VARCHAR(20) DEFAULT '0', stable TINYINT(1) DEFAULT 0, updated_at DATETIME, FOREIGN KEY (device_id) REFERENCES tblWeighbridge(device_id) ) ENGINE=InnoDB; CREATE TABLE tblRole ( role_id INT AUTO_INCREMENT PRIMARY KEY, role_name VARCHAR(30) UNIQUE NOT NULL ) ENGINE=InnoDB; INSERT INTO tblRole (role_name) VALUES ('ISVAdmin'), ('CompanyAdmin'), ('Supervisor'), ('Operator'); CREATE TABLE tblUser ( user_id INT AUTO_INCREMENT PRIMARY KEY, company_id INT NULL, role_id INT NOT NULL, username VARCHAR(50) UNIQUE NOT NULL, password_hash VARCHAR(255) NOT NULL, full_name VARCHAR(100), status ENUM('Active','Disabled') DEFAULT 'Active', failed_attempts INT DEFAULT 0, locked_until DATETIME NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (company_id) REFERENCES tblCompany(company_id), FOREIGN KEY (role_id) REFERENCES tblRole(role_id) ) ENGINE=InnoDB; CREATE TABLE tblUser_Site_Access ( user_id INT NOT NULL, site_id INT NOT NULL, PRIMARY KEY (user_id, site_id), FOREIGN KEY (user_id) REFERENCES tblUser(user_id) ON DELETE CASCADE, FOREIGN KEY (site_id) REFERENCES tblSite(site_id) ON DELETE CASCADE ) ENGINE=InnoDB; CREATE TABLE tblPermission ( permission_key VARCHAR(100) PRIMARY KEY, description VARCHAR(255) ) ENGINE=InnoDB; INSERT INTO tblPermission (permission_key, description) VALUES ('void_transaction','Void or edit a saved weighing transaction'), ('edit_master','Add/edit/delete master data (truck, product, customer...)'), ('view_report','View transaction / weighbridge reports'), ('export_report','Export reports to Excel/PDF'), ('manage_users','Create/edit users within own company'); CREATE TABLE tblRole_Permission ( role_id INT NOT NULL, permission_key VARCHAR(100) NOT NULL, PRIMARY KEY (role_id, permission_key), FOREIGN KEY (role_id) REFERENCES tblRole(role_id), FOREIGN KEY (permission_key) REFERENCES tblPermission(permission_key) ) ENGINE=InnoDB; INSERT INTO tblRole_Permission (role_id, permission_key) SELECT r.role_id, p.permission_key FROM tblRole r CROSS JOIN tblPermission p WHERE r.role_name IN ('ISVAdmin','CompanyAdmin','Supervisor'); INSERT INTO tblRole_Permission (role_id, permission_key) SELECT role_id, 'view_report' FROM tblRole WHERE role_name = 'Operator'; CREATE TABLE tblField_Setting ( field_setting_id INT AUTO_INCREMENT PRIMARY KEY, company_id INT NOT NULL, form_name VARCHAR(100) NOT NULL, field_key VARCHAR(100) NOT NULL, display_label VARCHAR(100) NOT NULL, is_enabled TINYINT(1) DEFAULT 1, sort_order INT DEFAULT 0, UNIQUE (company_id, form_name, field_key), FOREIGN KEY (company_id) REFERENCES tblCompany(company_id) ) ENGINE=InnoDB; CREATE TABLE tblTicket_Format ( format_id INT AUTO_INCREMENT PRIMARY KEY, site_id INT NOT NULL, template_html MEDIUMTEXT, logo_path VARCHAR(255), paper_size VARCHAR(20) DEFAULT 'A4', FOREIGN KEY (site_id) REFERENCES tblSite(site_id) ) ENGINE=InnoDB; CREATE TABLE tblAudit_Log ( audit_id BIGINT AUTO_INCREMENT PRIMARY KEY, user_id INT NULL, action VARCHAR(100), target_type VARCHAR(50), target_id VARCHAR(50), detail TEXT, ip_address VARCHAR(45), created_at DATETIME DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (user_id) REFERENCES tblUser(user_id) ) ENGINE=InnoDB; CREATE TABLE tblTruck_Master ( truck_id INT AUTO_INCREMENT PRIMARY KEY, company_id INT NOT NULL, truck_no VARCHAR(30) NOT NULL, driver_name VARCHAR(100), status ENUM('Active','Inactive') DEFAULT 'Active', created_by INT NULL, updated_by INT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NULL, UNIQUE (company_id, truck_no), FOREIGN KEY (company_id) REFERENCES tblCompany(company_id) ) ENGINE=InnoDB; CREATE TABLE tblProduct_Master ( product_id INT AUTO_INCREMENT PRIMARY KEY, company_id INT NOT NULL, product_name VARCHAR(100) NOT NULL, uom VARCHAR(20) DEFAULT 'KG', status ENUM('Active','Inactive') DEFAULT 'Active', created_by INT NULL, updated_by INT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NULL, FOREIGN KEY (company_id) REFERENCES tblCompany(company_id) ) ENGINE=InnoDB; CREATE TABLE tblCustomer_Master ( customer_id INT AUTO_INCREMENT PRIMARY KEY, company_id INT NOT NULL, customer_name VARCHAR(150) NOT NULL, contact_no VARCHAR(30), status ENUM('Active','Inactive') DEFAULT 'Active', created_by INT NULL, updated_by INT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NULL, FOREIGN KEY (company_id) REFERENCES tblCompany(company_id) ) ENGINE=InnoDB; CREATE TABLE tblSupplier_Master ( supplier_id INT AUTO_INCREMENT PRIMARY KEY, company_id INT NOT NULL, supplier_name VARCHAR(150) NOT NULL, contact_no VARCHAR(30), status ENUM('Active','Inactive') DEFAULT 'Active', created_by INT NULL, updated_by INT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NULL, FOREIGN KEY (company_id) REFERENCES tblCompany(company_id) ) ENGINE=InnoDB; CREATE TABLE tblTransaction ( transaction_id BIGINT AUTO_INCREMENT PRIMARY KEY, site_id INT NOT NULL, device_id VARCHAR(50) NOT NULL, ticket_no VARCHAR(30) NOT NULL, truck_id INT NULL, product_id INT NULL, customer_id INT NULL, supplier_id INT NULL, gross_weight DOUBLE, tare_weight DOUBLE, net_weight DOUBLE, gross_time DATETIME NULL, tare_time DATETIME NULL, status ENUM('Open','Completed','Void') DEFAULT 'Open', remarks VARCHAR(255), created_by INT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, UNIQUE (site_id, ticket_no), FOREIGN KEY (site_id) REFERENCES tblSite(site_id), FOREIGN KEY (device_id) REFERENCES tblWeighbridge(device_id) ) ENGINE=InnoDB; -- Seed ISVAdmin. Generate a real hash with: php -r "echo password_hash('YourPassword', PASSWORD_BCRYPT);" INSERT INTO tblUser (company_id, role_id, username, password_hash, full_name) VALUES (NULL, (SELECT role_id FROM tblRole WHERE role_name='ISVAdmin'), 'isvadmin', '$2y$10$REPLACE.WITH.REAL.BCRYPT.HASH.OUTPUT..........', 'ISVASIA Admin'); -- ===================================================================== -- Section 1: Masters - run once in phpMyAdmin against wbcorp_webapp -- Extends Customer/Supplier with payment terms, adds Driver/Vehicle(extends -- Truck)/Transporter/Item(extends Product) masters, stock ledger, and an -- optional photo on audit log entries. -- ===================================================================== ALTER TABLE tblCustomer_Master ADD COLUMN payment_terms ENUM('Cash','Credit') DEFAULT 'Cash' AFTER contact_no, ADD COLUMN credit_limit DOUBLE NULL AFTER payment_terms, ADD COLUMN remarks VARCHAR(255) NULL AFTER credit_limit; ALTER TABLE tblSupplier_Master ADD COLUMN payment_terms ENUM('Cash','Credit') DEFAULT 'Cash' AFTER contact_no, ADD COLUMN credit_limit DOUBLE NULL AFTER payment_terms, ADD COLUMN remarks VARCHAR(255) NULL AFTER credit_limit; -- Vehicle Master = tblTruck_Master extended. tare_weight 0 or NULL means -- "no known tare" - the transaction engine (Section 2) then behaves like a -- normal first+second weighing instead of pulling tare from this record. ALTER TABLE tblTruck_Master ADD COLUMN tare_weight DOUBLE NULL DEFAULT NULL AFTER driver_name, ADD COLUMN transporter_id INT NULL AFTER tare_weight, ADD COLUMN driver_id INT NULL AFTER transporter_id, ADD COLUMN remarks VARCHAR(255) NULL AFTER driver_id; -- Item Master = tblProduct_Master extended with pricing, running stock, and -- a DEFAULT deduction rule (operator can still key a manual value per -- transaction in Section 2 - this is just the master-level default). ALTER TABLE tblProduct_Master ADD COLUMN purchase_price DOUBLE DEFAULT 0 AFTER uom, ADD COLUMN sales_price DOUBLE DEFAULT 0 AFTER purchase_price, ADD COLUMN current_stock DOUBLE DEFAULT 0 AFTER sales_price, ADD COLUMN default_deduction_type ENUM('None','Percent','Fixed') DEFAULT 'None' AFTER current_stock, ADD COLUMN default_deduction_value DOUBLE DEFAULT 0 AFTER default_deduction_type, ADD COLUMN remarks VARCHAR(255) NULL AFTER default_deduction_value; CREATE TABLE tblDriver_Master ( driver_id INT AUTO_INCREMENT PRIMARY KEY, company_id INT NOT NULL, driver_name VARCHAR(100) NOT NULL, license_no VARCHAR(50), contact_no VARCHAR(30), signature_path VARCHAR(255) NULL, remarks VARCHAR(255), status ENUM('Active','Inactive') DEFAULT 'Active', created_by INT NULL, updated_by INT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NULL, FOREIGN KEY (company_id) REFERENCES tblCompany(company_id) ) ENGINE=InnoDB; CREATE TABLE tblTransporter_Master ( transporter_id INT AUTO_INCREMENT PRIMARY KEY, company_id INT NOT NULL, transporter_name VARCHAR(150) NOT NULL, contact_no VARCHAR(30), remarks VARCHAR(255), status ENUM('Active','Inactive') DEFAULT 'Active', created_by INT NULL, updated_by INT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NULL, FOREIGN KEY (company_id) REFERENCES tblCompany(company_id) ) ENGINE=InnoDB; ALTER TABLE tblTruck_Master ADD CONSTRAINT fk_truck_transporter FOREIGN KEY (transporter_id) REFERENCES tblTransporter_Master(transporter_id), ADD CONSTRAINT fk_truck_driver FOREIGN KEY (driver_id) REFERENCES tblDriver_Master(driver_id); -- Stock ledger: every IN/OUT movement, with a running balance for history/ -- audit. tblProduct_Master.current_stock is the fast-path cache for display. CREATE TABLE tblStock_Ledger ( ledger_id BIGINT AUTO_INCREMENT PRIMARY KEY, company_id INT NOT NULL, item_id INT NOT NULL, movement_type ENUM('In','Out') NOT NULL, quantity DOUBLE NOT NULL, reference_type VARCHAR(50), -- e.g. 'Transaction', 'Manual Adjustment', 'Opening Stock' reference_id VARCHAR(50), balance_after DOUBLE NOT NULL, remarks VARCHAR(255), created_by INT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (company_id) REFERENCES tblCompany(company_id), FOREIGN KEY (item_id) REFERENCES tblProduct_Master(product_id) ) ENGINE=InnoDB; -- Audit trail: optional webcam photo attached to a sensitive action (manual -- weight entry, override, edit, void, price change) when the operator -- grants camera permission in the browser (Section 2/6). ALTER TABLE tblAudit_Log ADD COLUMN photo_path VARCHAR(255) NULL AFTER detail; -- ===================================================================== -- Section 2a: Transaction Engine - data model + settings -- Run once in phpMyAdmin against wbcorp_webapp. -- ===================================================================== -- Transaction Type is now a proper master, not hardcoded Purchase/Sales - -- rename-able, extendable (Local Sales, Inter-Plant Transfer, etc). -- weight_logic drives order validation, using your exact definition: -- Purchase = DECREASING (1st weighing higher than 2nd, 2nd higher than 3rd...) -- - truck arrives loaded/gross, leaves empty/tare -- Sales = INCREASING (1st weighing lower than 2nd, 2nd lower than 3rd...) -- - truck arrives empty/tare, leaves loaded/gross -- Named 'Increasing'/'Decreasing' (the literal direction the number must -- move) rather than "ascending/descending" to avoid any future ambiguity -- between "the weight value" and "goods in vs out". CREATE TABLE tblTransaction_Type ( type_id INT AUTO_INCREMENT PRIMARY KEY, company_id INT NOT NULL, type_name VARCHAR(100) NOT NULL, weight_logic ENUM('Increasing','Decreasing','None') DEFAULT 'None', is_purchase_like TINYINT(1) DEFAULT 0, -- used for auto-detect: which type to assign when weights increase is_sales_like TINYINT(1) DEFAULT 0, -- used for auto-detect: which type to assign when weights decrease sort_order INT DEFAULT 0, status ENUM('Active','Inactive') DEFAULT 'Active', created_by INT NULL, updated_by INT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NULL, UNIQUE (company_id, type_name), FOREIGN KEY (company_id) REFERENCES tblCompany(company_id) ) ENGINE=InnoDB; -- One row per company - every toggle you listed, controlled by ISVAdmin -- (consistent with Field Settings being ISVAdmin-only, since these govern -- core transaction integrity, not day-to-day data entry). CREATE TABLE tblTransaction_Setting ( company_id INT PRIMARY KEY, enforce_weight_sequence TINYINT(1) DEFAULT 1, -- always validates order per type's weight_logic auto_detect_type TINYINT(1) DEFAULT 1, -- auto-assign Purchase/Sales-like type from observed direction when operator didn't pick one max_items_per_job INT NULL, -- NULL = unlimited vehicle_tare_enabled TINYINT(1) DEFAULT 1, -- pull tare from Vehicle Master when available direct_weighing_enabled TINYINT(1) DEFAULT 1, -- allow single net-weight-only entry, no sequence allow_manual_weight TINYINT(1) DEFAULT 0, manual_weight_requires_password TINYINT(1) DEFAULT 1, manual_weight_password_hash VARCHAR(255) NULL, -- separate from any user's login password allow_reprint TINYINT(1) DEFAULT 1, updated_by INT NULL, updated_at DATETIME NULL, FOREIGN KEY (company_id) REFERENCES tblCompany(company_id) ) ENGINE=InnoDB; -- Job = one ticket, can contain several items (sequential weighings). CREATE TABLE tblTransaction_Job ( job_id BIGINT AUTO_INCREMENT PRIMARY KEY, site_id INT NOT NULL, device_id VARCHAR(50) NOT NULL, ticket_no VARCHAR(30) NOT NULL, type_id INT NULL, -- NULL until chosen or auto-detected type_auto_detected TINYINT(1) DEFAULT 0, -- audit: was this picked by the system, not the operator customer_id INT NULL, supplier_id INT NULL, truck_id INT NULL, driver_id INT NULL, transporter_id INT NULL, is_direct_weighing TINYINT(1) DEFAULT 0, -- true = single net-weight-only job, no sequence status ENUM('Open','Completed','Void') DEFAULT 'Open', void_reason VARCHAR(255) NULL, remarks VARCHAR(255), created_by INT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, completed_at DATETIME NULL, UNIQUE (site_id, ticket_no), FOREIGN KEY (site_id) REFERENCES tblSite(site_id), FOREIGN KEY (device_id) REFERENCES tblWeighbridge(device_id), FOREIGN KEY (type_id) REFERENCES tblTransaction_Type(type_id) ) ENGINE=InnoDB; -- Raw weighing events within a job, in order. This is the immutable audit -- trail - who captured it, whether it was live from the indicator or keyed -- in manually, and why if manual. Item lines (below) are DERIVED from -- consecutive pairs of these, never edited directly. CREATE TABLE tblTransaction_Weighing ( weighing_id BIGINT AUTO_INCREMENT PRIMARY KEY, job_id BIGINT NOT NULL, seq_no INT NOT NULL, -- 1, 2, 3... item_id INT NULL, -- the item this weighing closes out (NULL for seq 1 baseline) weight VARCHAR(20) NOT NULL, -- exact string, same philosophy as tblLive_Weight is_manual TINYINT(1) DEFAULT 0, manual_reason VARCHAR(255) NULL, captured_by INT NULL, captured_at DATETIME DEFAULT CURRENT_TIMESTAMP, UNIQUE (job_id, seq_no), FOREIGN KEY (job_id) REFERENCES tblTransaction_Job(job_id), FOREIGN KEY (item_id) REFERENCES tblProduct_Master(product_id) ) ENGINE=InnoDB; -- Computed line items: one row per item in the job, derived from the -- difference between two consecutive weighings. This is what pricing, -- deduction, stock posting, and the printed ticket actually read from. CREATE TABLE tblTransaction_Item_Line ( line_id BIGINT AUTO_INCREMENT PRIMARY KEY, job_id BIGINT NOT NULL, item_id INT NOT NULL, from_weighing_id BIGINT NOT NULL, to_weighing_id BIGINT NOT NULL, gross_weight DOUBLE NOT NULL, -- absolute difference between the two weighings deduction_type ENUM('None','Percent','Fixed') DEFAULT 'None', deduction_value DOUBLE DEFAULT 0, net_weight DOUBLE NOT NULL, -- gross_weight after deduction unit_price DOUBLE NOT NULL, -- snapshot of purchase_price or sales_price at time of transaction amount DOUBLE NOT NULL, -- net_weight * unit_price created_at DATETIME DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (job_id) REFERENCES tblTransaction_Job(job_id), FOREIGN KEY (item_id) REFERENCES tblProduct_Master(product_id), FOREIGN KEY (from_weighing_id) REFERENCES tblTransaction_Weighing(weighing_id), FOREIGN KEY (to_weighing_id) REFERENCES tblTransaction_Weighing(weighing_id) ) ENGINE=InnoDB; -- Seed a sensible Purchase/Sales default for every existing company so the -- app isn't empty on first use - these are fully rename-able/editable after. INSERT INTO tblTransaction_Type (company_id, type_name, weight_logic, is_purchase_like, sort_order) SELECT company_id, 'Purchase', 'Decreasing', 1, 1 FROM tblCompany; INSERT INTO tblTransaction_Type (company_id, type_name, weight_logic, is_sales_like, sort_order) SELECT company_id, 'Sales', 'Increasing', 1, 2 FROM tblCompany; INSERT INTO tblTransaction_Setting (company_id) SELECT company_id FROM tblCompany; -- Separate permission for editing a completed transaction (distinct from -- void_transaction, and from edit_master which governs master data, not jobs). INSERT INTO tblPermission (permission_key, description) VALUES ('edit_transaction', 'Edit a completed weighing transaction (not just void it)'); INSERT INTO tblRole_Permission (role_id, permission_key) SELECT r.role_id, 'edit_transaction' FROM tblRole r WHERE r.role_name IN ('ISVAdmin','CompanyAdmin','Supervisor'); -- ===================================================================== -- Section 2b+: capture engine, signatures, CCTV scaffold, scheduled reports -- Run once in phpMyAdmin against wbcorp_webapp. -- ===================================================================== -- Operator signature, captured once at user creation or added later. ALTER TABLE tblUser ADD COLUMN signature_path VARCHAR(255) NULL AFTER full_name; -- Per-transaction captured driver signature (distinct from the master's -- default - a driver can sign fresh each time even if a default exists). ALTER TABLE tblTransaction_Job ADD COLUMN driver_signature_path VARCHAR(255) NULL AFTER remarks; -- CCTV: up to 4 RTSP cameras per site, configured by ISVAdmin. Actual -- frame capture happens via a local agent extension (same pattern as the -- weight agent) - this is the settings + storage side only. CREATE TABLE tblCamera_Setting ( camera_id INT AUTO_INCREMENT PRIMARY KEY, site_id INT NOT NULL, camera_name VARCHAR(100) NOT NULL, rtsp_url VARCHAR(255) NOT NULL, position_label VARCHAR(50), -- e.g. 'Front', 'Rear', 'Left', 'Right' status ENUM('Active','Inactive') DEFAULT 'Active', created_at DATETIME DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (site_id) REFERENCES tblSite(site_id) ) ENGINE=InnoDB; CREATE TABLE tblTransaction_Photo ( photo_id BIGINT AUTO_INCREMENT PRIMARY KEY, job_id BIGINT NOT NULL, camera_id INT NULL, photo_path VARCHAR(255) NOT NULL, captured_at DATETIME DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (job_id) REFERENCES tblTransaction_Job(job_id), FOREIGN KEY (camera_id) REFERENCES tblCamera_Setting(camera_id) ) ENGINE=InnoDB; -- Scheduled email reports CREATE TABLE tblReport_Schedule ( schedule_id INT AUTO_INCREMENT PRIMARY KEY, company_id INT NOT NULL, frequency ENUM('Daily','Weekly','Monthly') NOT NULL, recipient_emails TEXT NOT NULL, -- comma-separated report_type VARCHAR(50) DEFAULT 'Transaction Summary', last_run_at DATETIME NULL, status ENUM('Active','Inactive') DEFAULT 'Active', created_by INT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (company_id) REFERENCES tblCompany(company_id) ) ENGINE=InnoDB; -- ===================================================================== -- Section 2c: GST, real CCTV credentials, capture-request queue -- Run once in phpMyAdmin against wbcorp_webapp. -- ===================================================================== ALTER TABLE tblTransaction_Setting ADD COLUMN gst_percent DECIMAL(5,2) DEFAULT 9.00 AFTER allow_reprint; -- Replace the single rtsp_url field with the actual pieces you'll key in per -- camera (IP, username, password), plus an editable path so the exact -- working RTSP endpoint for your camera model can be set/corrected without -- a code change. Password is stored ENCRYPTED (reversible), not hashed like -- login passwords - the local agent needs the real value to authenticate to -- the camera, so one-way hashing isn't usable here. ALTER TABLE tblCamera_Setting ADD COLUMN ip_address VARCHAR(100) NOT NULL AFTER camera_name, ADD COLUMN username VARCHAR(100) NULL AFTER ip_address, ADD COLUMN password_encrypted VARCHAR(255) NULL AFTER username, ADD COLUMN rtsp_path VARCHAR(255) DEFAULT '/stream1' AFTER password_encrypted, ADD COLUMN rtsp_port INT DEFAULT 554 AFTER rtsp_path; -- rtsp_url column, if it exists from the earlier scaffold, is superseded by the above - -- drop it only if your table actually has it (it will if you ran the Section 2b migration): ALTER TABLE tblCamera_Setting DROP COLUMN rtsp_url; -- Polling queue: when a job completes, one row is queued per site. The local -- agent polls this (same philosophy as the weight polling fix - reliable, -- no event-handler flakiness), captures a snapshot from each active camera -- on that site, uploads it, then marks the row Done. CREATE TABLE tblCamera_Capture_Request ( request_id BIGINT AUTO_INCREMENT PRIMARY KEY, job_id BIGINT NOT NULL, site_id INT NOT NULL, status ENUM('Pending','Done','Failed') DEFAULT 'Pending', fail_reason VARCHAR(255) NULL, requested_at DATETIME DEFAULT CURRENT_TIMESTAMP, completed_at DATETIME NULL, FOREIGN KEY (job_id) REFERENCES tblTransaction_Job(job_id), FOREIGN KEY (site_id) REFERENCES tblSite(site_id) ) ENGINE=InnoDB; ALTER TABLE tblCustomer_Master ADD UNIQUE KEY uq_customer_name (company_id, customer_name); ALTER TABLE tblSupplier_Master ADD UNIQUE KEY uq_supplier_name (company_id, supplier_name); ALTER TABLE tblDriver_Master ADD UNIQUE KEY uq_driver_name (company_id, driver_name); ALTER TABLE tblTransporter_Master ADD UNIQUE KEY uq_transporter_name (company_id, transporter_name); ALTER TABLE tblProduct_Master ADD UNIQUE KEY uq_product_name (company_id, product_name); -- ===================================================================== -- Section 4: role permissions + ticket print tracking. -- Run once in phpMyAdmin against wbcorp_webapp. -- ===================================================================== -- Per-company overrides of what Supervisor / Operator may do (set on the Role Permissions -- screen). Anything without a row here uses the built-in defaults. CREATE TABLE tblCompany_Role_Permission ( company_id INT NOT NULL, role_id INT NOT NULL, permission_key VARCHAR(100) NOT NULL, allowed TINYINT(1) NOT NULL, updated_by INT NULL, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (company_id, role_id, permission_key), FOREIGN KEY (company_id) REFERENCES tblCompany(company_id) ON DELETE CASCADE, FOREIGN KEY (role_id) REFERENCES tblRole(role_id) ) ENGINE=InnoDB; -- Print tracking: 0 = never printed. The first print right after completion is the original; -- every later print is a logged Reprint and the ticket is labelled "REPRINT - Copy #n". ALTER TABLE tblTransaction_Job ADD COLUMN print_count INT NOT NULL DEFAULT 0 AFTER void_reason, ADD COLUMN first_printed_at DATETIME NULL AFTER print_count; -- ===================================================================== -- Section 5: custom roles + configurable ticket numbering. -- Run once in phpMyAdmin against wbcorp_webapp. -- ===================================================================== -- ---- Custom roles -------------------------------------------------- -- company_id NULL = a built-in role (ISVAdmin, CompanyAdmin, Supervisor, Operator). -- A custom role belongs to ONE company, so two companies can each have their own "Clerk". ALTER TABLE tblRole ADD COLUMN company_id INT NULL AFTER role_id; -- The old rule "role_name unique across everything" must go (it would stop two companies -- using the same role name). If this line says the index doesn't exist, run -- SHOW INDEX FROM tblRole; and drop whichever UNIQUE index covers only role_name. ALTER TABLE tblRole DROP INDEX role_name; ALTER TABLE tblRole ADD UNIQUE KEY uq_role_company_name (company_id, role_name); ALTER TABLE tblRole ADD CONSTRAINT fk_role_company FOREIGN KEY (company_id) REFERENCES tblCompany(company_id) ON DELETE CASCADE; -- ---- Ticket numbering ---------------------------------------------- -- One row per site (no row = the original format TKT---<0001>). CREATE TABLE tblTicket_Numbering ( site_id INT PRIMARY KEY, prefix VARCHAR(10) NOT NULL DEFAULT '', suffix VARCHAR(10) NOT NULL DEFAULT '', format VARCHAR(40) NOT NULL DEFAULT '{SEQ}', -- tokens: {YYYY} {YY} {MM} {DD} {SITEID} {SEQ} seq_digits INT NOT NULL DEFAULT 5, -- zero-padding of {SEQ} start_no INT NOT NULL DEFAULT 1, -- first number issued when a counter begins reset_cycle ENUM('Never','Yearly','Monthly','Daily') NOT NULL DEFAULT 'Monthly', updated_by INT NULL, updated_at DATETIME NULL, FOREIGN KEY (site_id) REFERENCES tblSite(site_id) ON DELETE CASCADE ) ENGINE=InnoDB; -- The running counter, one row per site per period (e.g. per month when reset_cycle = Monthly). CREATE TABLE tblTicket_Counter ( site_id INT NOT NULL, period_key VARCHAR(12) NOT NULL, last_no INT NOT NULL DEFAULT 0, PRIMARY KEY (site_id, period_key), FOREIGN KEY (site_id) REFERENCES tblSite(site_id) ON DELETE CASCADE ) ENGINE=InnoDB; -- ===================================================================== -- Section 6: public (no-login) ticket view via QR + GST frozen on each ticket. -- Run once in phpMyAdmin against wbcorp_webapp. -- ===================================================================== -- public_token: a random 32-character code that is the ONLY thing the QR link contains. -- Ticket numbers / job ids are sequential and guessable, so they must never be usable as a public link. -- Tokens are created automatically the first time a ticket is shown; older tickets get one then. -- gst_percent: the GST % in force when the ticket was completed, so a later change to the GST setting -- never alters an old ticket on reprint (NULL on old tickets = use the current setting, as before). ALTER TABLE tblTransaction_Job ADD COLUMN public_token CHAR(32) NULL AFTER first_printed_at, ADD COLUMN gst_percent DECIMAL(5,2) NULL AFTER public_token, ADD UNIQUE KEY uq_job_public_token (public_token); -- Run once in phpMyAdmin. -- 1) Direct-weighing lines have no weighings: allow NULL (they were saved as 0, which the foreign key rejects). ALTER TABLE tblTransaction_Item_Line MODIFY from_weighing_id BIGINT NULL, MODIFY to_weighing_id BIGINT NULL; -- 2) SuperAdmin switch: may typing a new name on New Transaction add it to the master data? ALTER TABLE tblTransaction_Setting ADD COLUMN allow_new_master_entries TINYINT(1) NOT NULL DEFAULT 0; -- Run once in phpMyAdmin. -- Names typed on New Transaction that are NOT saved to the master (history recording switched off) are kept on the ticket itself. ALTER TABLE tblTransaction_Job ADD COLUMN truck_text VARCHAR(100) NULL, ADD COLUMN driver_text VARCHAR(100) NULL, ADD COLUMN transporter_text VARCHAR(100) NULL, ADD COLUMN customer_text VARCHAR(100) NULL, ADD COLUMN supplier_text VARCHAR(100) NULL; -- Run once in phpMyAdmin. -- 1) More SuperAdmin "Allow ..." switches (all default ON = nothing changes until you switch one off). ALTER TABLE tblTransaction_Setting ADD COLUMN allow_deduction TINYINT(1) NOT NULL DEFAULT 1, ADD COLUMN allow_pricing TINYINT(1) NOT NULL DEFAULT 1, ADD COLUMN allow_camera TINYINT(1) NOT NULL DEFAULT 1, ADD COLUMN allow_edit_transaction TINYINT(1) NOT NULL DEFAULT 1, ADD COLUMN allow_void_transaction TINYINT(1) NOT NULL DEFAULT 1, ADD COLUMN allow_email_ticket TINYINT(1) NOT NULL DEFAULT 1; -- 2) Items can be typed on the weighing screen: an item that is not in the master is kept as text on the line. ALTER TABLE tblTransaction_Item_Line MODIFY item_id INT NULL, ADD COLUMN item_text VARCHAR(100) NULL; ALTER TABLE tblTransaction_Weighing ADD COLUMN item_text VARCHAR(100) NULL; -- Run once in phpMyAdmin. -- Operator signature chosen/drawn on the completion screen, per ticket. -- NULL = use the operator's saved signature (as before); '' = deliberately left blank; otherwise the drawn image path. ALTER TABLE tblTransaction_Job ADD COLUMN operator_signature_path VARCHAR(255) NULL; -- Run once in phpMyAdmin. -- Default deduction type pre-selected on the weighing screen (SuperAdmin setting). Fixed (kg) by default. -- Values: Fixed | Percent | None | Item (Item = use each item's own default) ALTER TABLE tblTransaction_Setting ADD COLUMN default_deduction_type VARCHAR(10) NOT NULL DEFAULT 'Fixed'; -- Run once in phpMyAdmin. -- SuperAdmin switch: print the Tare weight column on the ticket (1 = show, 0 = hide). ALTER TABLE tblTransaction_Setting ADD COLUMN show_tare_on_ticket TINYINT(1) NOT NULL DEFAULT 1; -- Run once in phpMyAdmin. -- Company details printed in the ticket header (address, contact, registration no.) and the company logo (top-left of the ticket). ALTER TABLE tblCompany ADD COLUMN address TEXT NULL, ADD COLUMN phone VARCHAR(40) NULL, ADD COLUMN email VARCHAR(120) NULL, ADD COLUMN reg_no VARCHAR(60) NULL, ADD COLUMN logo_path VARCHAR(255) NULL; -- Run once in phpMyAdmin (after migrations 12 and 13). -- SuperAdmin setting: is the reason for a manual weight Mandatory (1) or Optional (0)? Default = mandatory. ALTER TABLE tblTransaction_Setting ADD COLUMN manual_reason_required TINYINT(1) NOT NULL DEFAULT 1;