I am trying to create program for voucher entry. I am using one field named type for credit and debit.
How to display Total Credit and Total debit i footer. in one footer
- Code: Select all Expand view RUN
- 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 ;