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

Eric MulovhedziChief Engineer
Assuming MySQL Tables called "invoices" and "latefees" with the following columns:
                        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 ;

Open in new window


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

Open in new window


1. This is the MySQL Code that produces the results:
                      	((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`
                      	`invoices` AS `A`
                      LEFT JOIN 
                      	`latefees` AS `B` ON `B`.id=`A`.`latefee`
                      	`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)

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"]

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.

Comments (3)

Eric MulovhedziChief Engineer



Is the 300 Words including the code snippets?
Eric MulovhedziChief Engineer


With all due respect that's not what i had in mind and that's not what i would like to do in the future. What you are advising me to do is to find a business model first before i contribute an article on your network.

I am a GENIUS but only on Coding and Software Development, i do lots of research and development stuff for Enterprises.

I also have my own business network portal where people can use ERP and CRM for free ( )

Two, i recently developed an Open Source server configuration utility tool "OVH Server Basement" ( and whole lots of other genius commercial stuff.

The moral of the story is i have lots and lots of knowledge to share more especially on Programming and Development, i just do not have time to think of business models etc. Most of these articles are solutions i derived for problems that i experienced when i develop my own stuff or even clients stuff and i believe that there might be at-least one person out there who is still yet to go through what i went through and perhaps this article might just help them.

ALSO, i selected a tag called MySQL so automatically this article is targeted to someone who is already trying to write an SQL query for what ever reason that prompted them to do so.

Those are the people i was honestly having in mind when i was writing the article,  people who already have a problem or business model and are just searching for solution
Eric MulovhedziChief Engineer


This is also one of the articles i contributed recently:

It was published but to me i do not think has a business model to it because i just went straight to the point

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.