late fees in rental app

[serverman]

I am working on a application to keep track of  rent for multiple houses. Making this for a friend to use but once I have it working I'm going to put it out open source for anyone to use.

 

So the only thing I'm stumped on is keeping track of late fees. I'm not sure where to start on this. late fees start if you haven't payed  by the 5th of the month but is also waveable. I feel like I need to keep track of something else in the tables to do this right now I have 2 tables for it. Maybe add a balance table? one has the property info the other has payment info it looks like this:

 

(propery)

Id | title | address | rent | late | tenant | occupancy

(payment)

id | parent | date | note

 

Any ideas would help math isn't my thing ha.

 

 

[benanamen]

In payments you could add a "type" column. Types could be rent, late fee, pet deposit, repair reimbursement etc. They are all payments. You would need a types table and use the unique key to reference it in your payments table. It would help you a lot to look at some of the property management software that is out there. I haven't tried it but here is a free one. https://www.tenantcloud.com/

 

* Edit. I just checked it out. I am pretty impressed with what they have done.

Edited February 12, 2017 by benanamen

[serverman]

benanamen, I like that idea will be adding that table. the plan for this is just to do the basic accounting for now. So far most of the software I have found is either an attempt to be your own personal zillow for doing listings or complex CMS. I will be checking out tenant cloud.

[mac_gyver]

the tables that hold 'defining' data should contain the unique/one-time and generally static information for what they define. a property table should only contain information about properties. a property can have a succession of tenants, each with different rent amounts, so the information about each instance of a tenancy needs to be stored in a second table. the payment table holds information related to the instances of tenancy. the actual tenant/renter personal information is separate from this data. see the following list of tables i would start with -

 

property - id | title | address

tenancy - id | property_id | tenant_id | rent | start_date | end_date

tenant - id | first_name | last_name | email | phone

payment - id | tenancy_id | amount | amount_type_id | date | note

amount_type - id | name

 

most of this should be self-explanatory. the amount_type would be things like full rent, partial rent, late fee, waived late fee, rent refund, ... the payment table would hold actual payment amounts. plus amounts are payment in and minus amounts are payment out.

 

the code that calculates the late fees would add row(s) to the payment table, with the amount_type_id for a late fee. if a specific late fee is waived, the row that gets added to the payment table would have a negative amount equal to the late fee amount and an amount_type_id for a waived late fee.

[serverman]

mac_g that makes since. I was planning originally  to have it where you just edit the property when someone else moves in but this works better because then you still have a record of the last person and such. thanks for the suggestion.

[serverman]

So I'm about caught up in my coding to get to the point of calculating rent, fees and such. tables now look like:

(payment)id | tenancy_id | date | amount | amount_type_id | note

(payment_type) id | type

(property)id | title | address | occupied

(tenancy)id | property_id | tent_id | rent | late | start_date | end_date | current

(tenant)id | first_name | last_name | email | phone | current

 

both currents and occupied are a tiny int for 1/0 if they are currently occupied or living somewhere updated with move out script

 

so still thinking about the math I think I need a another table for storing fee amounts.

I think math wise want to do it like this

$rawrent = $months * $rent 

$rdue =  $rawrent - $paid

$totaldue = $rdue + $fees

does that make since or is there a better way to do this

[mac_gyver]

this is/should be the same as a bank or credit card account. you are inserting rows that represent positive and negative amounts for an account, the tenancy_id in this case. you can SUM the amounts for each account/tenancy_id to determine what the balance is on any date.

 

when the rent becomes due in a month, you would add a row to the payment table, with a negative amount and the appropriate amount_type_id for rent due, along with the correct  tenancy_id and date. this will result in a negative balance (amount due) when you SUM() the amount column for each tenancy_id (you would GROUP BY tenancy_id in the sql statement.) if payment(s) are made (row(s) with a positive amount) they offset the rent due, when you SUM() the amount column on the 'late fee date' you will get a zero. if you get a negative result, it would mean that the (whole) rent has not been paid and you would insert a row in the payment table for the late fee (a negative amount with the appropriate values for the other columns.)

[serverman]

OK makes sense so instead of making another table for keeping track of fees just add them into payment just make them negative. I'm sure it's obvious I'm not a real programmer and don't pretend to be but I'm trying to do this right and not just hack it together. Just a hobby for me. Lots a tutorials for me haha. Can you point me in the right direction to have it add rent at the first of the month I've never done anything like that.

[mac_gyver]

actually, i see a mis-statement -

 

the code that calculates the late fees would add row(s) to the payment table, with the amount_type_id for a late fee. if a specific late fee is waived, the row that gets added to the payment table would have a negative amount equal to the late fee amount and an amount_type_id for a waived late fee.

 

the late fee would be a negative amount (an amount due), waiving of the late fee would be row with a positive amount (a credit made to the account.)

Edited February 14, 2017 by mac_gyver

[mac_gyver]

Can you point me in the right direction to have it add rent at the first of the month I've never done anything like that. 

 

 

if that question is about doing things automatically, you would set up a cron job/scheduled task to run a php script, once a day. the php script would check if there are any operations to perform on the particular date or day of the month.

 

for the add rent operation, it would query for the active tenancies (where the current date is between the start date and the end date of the tenancy), and add 'rent due' records with the appropriate field values to the payment table.

[serverman]

The question was about doing things automatically. So I'll look I to chron jobs.

[serverman]

Ok this should be the very last thing. So I'm working on the code for the auto update of the rent. I'm  not sure how to start the loop. Here is the code so far

<?php 
include_once 'includes/db_connect.php';
//before the loop
$date = date(Y.m.d);
$dt2 = new DateTime($date);

//during
$results = $mysqli->query("SELECT rent, start_date FROM tenancy WHERE tent_id ='$tent_id'");
while($row = $results->fetch_array()) {
$rent = $row["rent"];
$movein = $row["start_date"];
}
$results->free();
	$dt1 = new DateTime($movein);
	$occupancy = $dt1->diff($dt2);
	$time = $occupancy->format('%m');
		$updaterent = $time * -abs($rent);
$mysqli->query("UPDATE payment SET amount='$updaterent' date'$date' WHERE tenancy_id='$tent_id'");


?>

[gizmola]

Serverman,

 

The purpose of threads is to have a topic per question. This seems to be turning into a personal topic for you, so I'm going to close it now.

 

Please create a new Topic for your current question, titled as best you can. Something like "Calculate rent payment schedule" or whatever is appropriate. You can link back to this thread in your post, and those that want to check out the backstory can.