How to create a Late Fee Payment Query that automatically bills unpaid invoices

Assuming MySQL Tables called "invoices" and "latefees" with the following columns:
 

CREATE TABLE IF NOT EXISTS `invoices`
                      (
                        id BIGINT (255) NOT NULL AUTO-INCREAMENT,
                        customer BIGINT (255),
                        invoiceno VARCHAR (255),
                        startdate DATE,
                        isPaidStatus ENUM ("YES,NO") DEFAULT 'NO',
                        payment_terms INT (5),
                        latefee INT (10),
                        subtotal DECIMAL ("6,2"),
                        vat DECIMAL ("6,2"),
                        total DECIMAL ("6,2"),
                        total_addtional DECIMAL ("6,2"),
                        PRIMARY KEY ( id)
                      ) ENGINE = InnoDB ;

Select all Open in new window

 

CREATE TABLE IF NOT EXISTS `latefees`
                      (
                        id BIGINT (255) NOT NULL AUTO-INCREAMENT,
                        type ENUM("%,Flat Fee") DEFAULT '%',
                        frequency INT (5),
                        amount DECIMAL ("6,2"),
                        PRIMARY KEY (`id`)
                      ) ENGINE = InnoDB ;

Select all Open in new window

respectively.

1. This is the MySQL Code that produces the results:
 

SELECT 
                      	`A`.customer,`A`.id,`A`.invoiceno,`A`.total,`A`._total,`A`.`latefee`,`A`.startdate,`A`.payment_terms,
                      	`B`.name,`B`.type,`B`.frequency,`B`.amount,
                      	((TO_DAYS(Current_Date()) - TO_DAYS(`A`.startdate)) - `A`.payment_terms) AS daysElasped,
                      	MOD(((TO_DAYS(Current_Date()) - TO_DAYS(`A`.startdate)) - `A`.payment_terms), `B`.frequency) AS `isBillableDay`,
                      	IF(`B`.type=1,(`A`.total + `A`.total_addtional)*(`B`.amount / 100),`B`.amount) AS `new_amount`
                      FROM 
                      	`invoices` AS `A`
                      LEFT JOIN 
                      	`latefees` AS `B` ON `B`.id=`A`.`latefee`
                      WHERE 
                      	`latefee` > 0 AND 
                      	`isPaidStatus` = 'NO' AND
                      	((TO_DAYS(Current_Date()) - TO_DAYS(`A`.startdate)) > `A`.payment_terms) AND 
                      	(MOD(((TO_DAYS(Current_Date()) - TO_DAYS(`A`.startdate)) -`A`.payment_terms),`B`.frequency)=0)

Select all Open in new window

This query basically does the following:
 
    a) Firstly checks if the invoice status is on "not paid yet"

    b) Calculates the number days ellapsed "daysElapsed" by checking invoice start date against the current date and then,

    c) Checks if the resultant "daysElapsed" exceeds the selected invoice terms and these could be "7 => Net 7 Days, 15 => Net 15 Days, 25 => Net 25 Days, 45 => Net 45 Days, 60 => Net 60 Days", and then 

    d) Checks if the remainder of our dividend "daysElapsed" minus invoice terms against our divisor latefee frequency is 0. This tells us that, today is the suitable day for us to pernalize this particular invoice by adding they appropriate latefee amount.

    e) If the invoice late fee selected is set to increament in percentages (%), then our new amount will be the  x % of the invoice's current amount.

    f) Else if the invoice late fee selectedd is set to be a Flate Rate then, our new amount will just be the latefee amount

2. Update the invoice with the new amount just calculated. Lets assume that we have saved our resultant new amount from the query above

i.e. say you are using PHP as your scripting language FIELDS[]"new_amount"]; ?>

 

UPDATE `invoices` SET `status`='1',`total_addtional`=`total_addtional`+ {SQL-RESULTS}->FIELDS["new_amount"] WHERE id={SQL-RESULTS}->FIELDS["new_amount"]

Select all Open in new window

3. After executing the above UPDATE query successfully then, notify the customer (i.e. {SQL-RESULTS}->FIELDS[]"customer"]) 
of the additional amount they have been penalized for. This could be via Email and even SMS.

The Email text could be something like th fllowing:

Hi Mrs Smith, 

Note that the following invoice # INV001 is 10 day (s) overdue, this serves as a formal notification that an additional amount of R 24.89 has been added to your invoice INV001. Please, make sure you make paymen arangements soon in order to avoid further penalties.

Thank you


4. Set a CronJob in your server to run this script everyday

NB: {SQL-RESULTS}->FIELDS[]"isBillableDay"], this variable will always make sure that the query only returns items for that particular payment termed day on that frequency.