<

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

Published on
6,962 Points
3,962 Views
Last Modified:
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 ;

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 ;

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)

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.
0
Comment
  • 3
3 Comments
LVL 2

Author Comment

by:Eric Mulovhedzi
Yes,

Is the 300 Words including the code snippets?
0
LVL 2

Author Comment

by:Eric Mulovhedzi
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 ( http://1stbn.co.za )

Two, i recently developed an Open Source server configuration utility tool "OVH Server Basement" (http://ovhserverbasement.co.za/) 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
0
LVL 2

Author Comment

by:Eric Mulovhedzi
This is also one of the articles i contributed recently:

http://www.experts-exchange.com/Networking/Linux_Networking/A_13635-How-to-setup-a-local-Area-Network-between-two-Ubuntu-servers.html

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

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Join & Write a Comment

In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month