Joined: Sun Mar 19, 2006 3:13 pm Posts: 123 Location: English Indiana, USA
|
In many (if not most billing) applications there are always a running report that shows the current balance before or after a credit or debit transaction. It is extremely important to be able to see every debit or credit transaction on a client’s account as it occurs. Sometimes this is done just by simply putting a “previous_bal” field in every table containing money transaction logs. As long as the record is logged before the clients balance is updated in cc_card table a simple DB trigger can do this. Even if the balance in the CC_CARD table is updated first it still shows what the balance is at the exact time of the credit or debit.
Record edited on Sun Dec 27, 2009 I have not used the A2billing invoicing as of yet and always did my own reports and interfaces to view or edit the data in A2b. This was because so many features I needed weren’t in older versions of a2billing. I was so used to of doing this I recently overlooked some new features in a2billing. So maybe there is a running Balance or something just as good and I posted to forum too soon.
Record edited on Sun Dec 27, 2009
To demonstrate this I put a new field inside of the cc_call table named “previous_bal” and wrote a trigger inside of MYSQ for table “cc_call” to update this field with the cleints current balance with each new call record. This is a crude way of doing it but least now there are some balance records with timestamps that shows what a clients balance is at any giving day as long as they are making calls every day. It’s really easy and fast to find errors or explain to clients they are being billed correctly when there is a before or after balance record for each debit or credit which occurs. This can be done for other tables as well so it’s easy to see or explain billing issues to clients.
DELIMITER $$ USE `mya2billing`$$ DROP TRIGGER /*!50032 IF EXISTS */ `cc_call_prev_bal_ins`$$ CREATE /*!50017 DEFINER = 'root'@'localhost' */ TRIGGER `cc_call_prev_bal_ins` BEFORE INSERT ON `cc_call` FOR EACH ROW BEGIN DECLARE strprev_bal DOUBLE DEFAULT '';
SET strprev_bal = (SELECT credit FROM cc_card WHERE id = new.card_id); SET New.previous_bal = strprev_bal; END; $$ DELIMITER ;
Last edited by Sam on Sun Dec 27, 2009 1:43 pm, edited 1 time in total.
|
|