-- ===================================================================== -- 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');