Payment Application

[k_t_d]

I am building program to manage payment/monthly payments. I've ran into an issue that I haven't been able to overcome and need some help. I've been lurking for a while and decided it was time to ask you guys...Here's the issue:

 

I have a table 'payments' that contains scheduled payments that looks like:

 

id

clientid

paymentduedate

balance

expected

pending

367

112233

4/16/2011

1030.00

257.50

Y

 

368

112233

5/16/2011

1030.00

257.50

Y

 

369

112233

6/16/2011

1030.00

257.50

Y

 

370

112233

7/16/2011

1030.00

257.50

Y

 

 

What I need to do that I haven't figured out is to adjust the expected amount according to the payment, update the status to N (not pending) without affecting the later expected payments.

 

For example:

• they make a payment of $257.50, applies that to row 367, sets the status to N, but leaves the other status' to Y (for obvious reasons).
  
• the make a payment of $250.00, leaving the balance at $7.50, so lets add $7.50 to row 368, mark 367 status to N
  
• the make a payment of $300.00, leaving the expected balance at $0.00 for row 367, but adjust the balance for row 368 to $215.00
  

 

Any ideas?

 

Thanks

[micmania1]

You can create a recursive function as so...

 

WARNING! I have not validated any data. You will need to do this, nor have I checked it works.

// This is a recursive function that will find 1 single row 
// from payments table and update payment
// returns true or false, dies on fatal error
function update_payment($payment, $client) {
// Make sure you validate your data before querying

// Query next expected payment for client
$query = "SELECT * FROM payments WHERE clientid='$client' AND pending='Y' ORDER BY paymentduedate ASC LIMIT 0,1";
$result = mysql_query($query) or die("Invalid query 1");
if (mysql_num_rows($result) == 1) {

	$row = mysql_fetch_assoc($result);


	if ($row['expected'] < $payment) {
		// The payment is higher than the expected amount due
		// make this zero and repeat function
		// Set pending to N
		$expected = 0;
		$payment -= $row['expected'];
		$pending = 'N';
	} else if ($row['expected'] > $payment) {
		// Expected amount due is more than the amount paid
		// Update this row and leave pending
		$expected =	$row['expected'] - $payment;
		$payment = 0;
		$pending = 'Y';
	} else {
		// Payment has been matched to expected
		// Set pending to N
		$expected = 0;
		$payment = 0;
		$pending = 'N';
	}


	// Update row
	$query2 = "UPDATE payments SET expected='$expected', pending='$pending' WHERE id='{$row['id']}'";
	$result2 = mysql_query($query2) or die("Invalid query 2");
	if (mysql_affected_rows() == 1) {
		// Payment update has been successful

		// Check to see if full $payment amount has been matched
		if ($payment > 0) {
			// Payment has not fully been matched.
			update_payment($payment, $client);
		} else {
			return true;
		}
	} else {
		die("Unable to update row");
	}
}

return false;
}

 

 

// You call the function like this

// it will keep recalling itself until $payment=0

$payment = '257.5';
$client = '112233';

update_payment($payment, $client);

 

If a lot of transactions are taking place at once, this isn't very server-friendly and you may want to think about restructuring your program/database.

[PFMaBiSmAd]

When you have a payment account, you don't modify the stored values, you enter a row for every payment that is made (as a - number) and for every expected payment (as a + number) and to get the current account balance at any point in time, you perform a query to get a sum of the + and - amounts of the rows with dates less-than or equal to the date you are interested in.

[k_t_d]

When you have a payment account, you don't modify the stored values, you enter a row for every payment that is made (as a - number) and for every expected payment (as a + number) and to get the current account balance at any point in time, you perform a query to get a sum of the + and - amounts of the rows with dates less-than or equal to the date you are interested in.

 

I'm picking up what you are putting down, it's just the madness behind the logic that was making this simple problem have such a complex solution; though my balance is calculated by the SUM(Expected), I also added in a $forwardbalance and added it to the the next pending balance.

 

 

If a lot of transactions are taking place at once, this isn't very server-friendly and you may want to think about restructuring your program/database.

 

With a bit of manipulation, this worked out great! I ended up adding another column 'Paid' so I can monitor the incoming as well.

 

Thanks a lot guys; I really appreciate it!

 

[litebearer]

2 cents...

2 cents...

 

datedue - simply the day of the month if it is always the same for an acct - actually should be in another table with the initial acct info, not in this table.

 

amtdue - regularly scheduled payment - actually should be in another table with the initial acct info not in this  table

 

acctnumber - the unique loan/mortgage/lease/rental id - garnered from  table with the initial acct info

 

datepaid - datetime of activity

 

debit - amount by which acct increases (initial loan amount, late charges, bad check, cc charged back etc)

 

credit - amount by which acct is reduced (usually a payment received)

 

notes - what the amount being debited/credited represents (payment, bad check, bad check charge, late fees etc)

 

Not even close to ALL the proper accting procedures, but a start

[k_t_d]

2 cents...

2 cents...

 

datedue - simply the day of the month if it is always the same for an acct - actually should be in another table with the initial acct info, not in this table.

 

amtdue - regularly scheduled payment - actually should be in another table with the initial acct info not in this  table

 

acctnumber - the unique loan/mortgage/lease/rental id - garnered from  table with the initial acct info

 

datepaid - datetime of activity

 

debit - amount by which acct increases (initial loan amount, late charges, bad check, cc charged back etc)

 

credit - amount by which acct is reduced (usually a payment received)

 

notes - what the amount being debited/credited represents (payment, bad check, bad check charge, late fees etc)

 

Not even close to ALL the proper accting procedures, but a start

 

This is basically a glorified audit trail. What I plan on doing is analyzing the data to design my team to function on/at a more productive time scale.

 

Being as this specific audit is for a patient collections module, I can see who (employee), when (date) and what time the call is made; and whether or not the call was scored. This way, I know what days and what times are best to call to follow up on payments/balances; not to mention the geographical analysis by payment (tied to the patient's zip code).