FiveTech Support Forums

CREATE TABLE IF NOT EXISTS `voucher` (
  `vch_id` int(11) NOT NULL AUTO_INCREMENT,
  `voucher_id` int(11) DEFAULT NULL,
  `ledger_id` int(11) DEFAULT NULL,
  `order_id` int(11) DEFAULT NULL,
  `sales_id` int(11) DEFAULT NULL,
  `Purchase_id` int(11) DEFAULT NULL,
  `amount` decimal(15,2) DEFAULT 0.00,
  `gold_amount` decimal(15,2) DEFAULT 0.00,
  `type` enum('credit','debit') NOT NULL,
  `vtype` enum('RECEIVED','PAYMENT','SALES','PURCHASE','JOURNAL','DEBIT NOTE','CREDIT NOTE') DEFAULT NULL,
  narration VARCHAR(255),
  PRIMARY KEY (`vch_id`),
  KEY `ledger_id` (`ledger_id`),
  CONSTRAINT `voucher_details_ibfk_1` FOREIGN KEY (`ledger_id`) REFERENCES `ledger` (`ledger_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;




DELIMITER //
CREATE TRIGGER after_voucher_insert
AFTER INSERT ON voucher
FOR EACH ROW
BEGIN
    IF NEW.type = 'debit' THEN
        UPDATE ledger
        SET ledger_cash_bal = ledger_cash_bal + NEW.amount,
            ledger_gold_bal = ledger_gold_bal + NEW.gold_amount
        WHERE ledger_id = NEW.ledger_id;
    ELSE
        UPDATE ledger
        SET ledger_cash_bal = ledger_cash_bal - NEW.amount,
            ledger_gold_bal = ledger_gold_bal - NEW.gold_amount
        WHERE ledger_id = NEW.ledger_id;
    END IF;
END //
DELIMITER ;


BEGIN
    -- Revert old values
    IF OLD.type = 'debit' THEN
        UPDATE ledger
        SET ledger_cash_bal = ledger_cash_bal - OLD.amount,
            ledger_gold_bal = ledger_gold_bal - OLD.gold_amount
        WHERE ledger_id = OLD.ledger_id;
    ELSE
        UPDATE ledger
        SET ledger_cash_bal = ledger_cash_bal + OLD.amount,
            ledger_gold_bal = ledger_gold_bal + OLD.gold_amount
        WHERE ledger_id = OLD.ledger_id;
    END IF;

    -- Apply new values
    IF NEW.type = 'debit' THEN
        UPDATE ledger
        SET ledger_cash_bal = ledger_cash_bal + NEW.amount,
            ledger_gold_bal = ledger_gold_bal + NEW.gold_amount
        WHERE ledger_id = NEW.ledger_id;
    ELSE
        UPDATE ledger
        SET ledger_cash_bal = ledger_cash_bal - NEW.amount,
            ledger_gold_bal = ledger_gold_bal - NEW.gold_amount
        WHERE ledger_id = NEW.ledger_id;
    END IF;
END


DELIMITER //
CREATE TRIGGER after_voucher_delete
AFTER DELETE ON voucher
FOR EACH ROW
BEGIN
    IF OLD.type = 'debit' THEN
        UPDATE ledger
        SET ledger_cash_bal = ledger_cash_bal - OLD.amount,
            ledger_gold_bal = ledger_gold_bal - OLD.gold_amount
        WHERE ledger_id = OLD.ledger_id;
    ELSE
        UPDATE ledger
        SET ledger_cash_bal = ledger_cash_bal + OLD.amount,
            ledger_gold_bal = ledger_gold_bal + OLD.gold_amount
        WHERE ledger_id = OLD.ledger_id;
    END IF;
END //
DELIMITER ;