PHP + MYSQL -- need to make payment for open invoices

Discussion in 'Software' started by pari22, Oct 8, 2011.

  1. pari22

    pari22 Corporal

    Greetings Geeks!
    I've written and have a working web-based(PHP) program using MySQL DB for Invoicing. -- All is working fine here.
    The other day, my client asked me to make a payment option to automatically pay old "open invoices" first when customer makes a payment. When the credit is applied, it should search for the oldest first - pay - if there are any left over credit it searches for the next oldest one and apply remaining credit... and so on. Which makes perfect sense, but he wants this to be automated...
    I'm able to search and list "open invoices" & "oldest first". But I'm having trouble with making code to automatically update the rest... Can anyone help me with this?
     
  2. Mada_Milty

    Mada_Milty MajorGeek

    Well, I'll help with the pseudo code, and maybe that helps with the actual writing of the script to accomplish this.

    In order, we would need to:

    1. Set a variable equal to the received customer payment. We'll also need to track the customer it was received from.

    2. Select the open orders for the customer (not sure what defines an open order here - maybe there's a status, or an unpaid amount) in ascending order of order date.

    3. Loop through the orders. On each iteration, we should be confirming that there are still orders to process, and that there is still credit left to be applied.

    4. If there is still credit left to be applied, and orders to process, we need to compare the leftover credit to the unpaid total on the order. If the leftover credit is greater than the unpaid amount on the current order, we need to update the order to paid. (Change the status, unpaid amount, I'm not sure). We also need to deduct the unpaid amount of the order from our payment received/available credit variable.

    If the unpaid amount on the current order is greater than the leftover credit, you can either:

    - apply a partial payment to that order In this case, we would need to updated the unpaid amount to unpaid amount - available credit, and update the available credit variable to 0.

    - skip the current order, and continue looking for orders the available payment can In this case, we would just skip the current iteration of the loop. We would also need to see if there is any leftover credit after all orders are checked, and handle the balance.

    - not apply any more credit, and issue a refund for the remainder (pretty complicated option)

    It sounds like with your background, you should be able to write the code necessary to do this, but if not, I am glad to help further.
     
  3. pari22

    pari22 Corporal

    Yup. That's exactly what need to be done. I thought that was pretty obvious...
    Anyway, do you think it's better to have seperate table made for the payments only?
     
  4. Mada_Milty

    Mada_Milty MajorGeek

    That might be a good idea. You could create a table to track payments, the customer who made it, and whether it's been applied. Then you could create a script to select the unapplied payments, and then loop through those, performing the steps for applying the payments.
     
  5. pari22

    pari22 Corporal

    Yes... Keeping & tracking records are the core components and idea behind using database with forms... rolleyes
    You could also apply the payments and keep the records in the same "invoice" table. I was talking more about seperating the records into two different tables but same schema - in order to not use the invoices primary id/ or some kind of future complication...
    So, you keep talking about looping script - but haven't seen one. Could you please provide a sample php codes?
    I already have the "pseudo" codes.
     
  6. Mada_Milty

    Mada_Milty MajorGeek

    *sigh*

    Alright, even though you are in the position of asking for help, and have the nerve to talk down to me here:

    AND here:

    I will still write out some sample code for you. Hopefully, this helps you complete YOUR job.

    Okay, keeping in mind I'm making up table names, form fields, etc as I go:

    This is the received payments table I'm using in my hypothetical. Very simple; a column to identify the customer, another to store the payment amount, and a boolean third to track whether the payment has been used. (1 = used, 0 = unused).

    Code:
    CREATE TABLE IF NOT EXISTS `customer_payments` (
        `id` int(11) NOT NULL auto_increment,
        `customer_id` int(11) NOT NULL,
        `payment` decimal(10,2) NOT NULL,
        `payment_applied` tinyint(1) NOT NULL,
        PRIMARY KEY (`id`)
    )
    Assuming that the PHP script has already connected to the database, and the table above has been populated with customer payments, we'd select the unused payments and loop through them:

    Code:
    // Select the unused payments
    $payqry = mysql_query ("SELECT `payment`, `customer_id`
    FROM `customer_payments`
    WHERE `payment_applied` = 0
    ORDER BY `customer_id` ASC;");
    // Loop through selected payments
    while ($payinfo = mysql_fetch_assoc($payqry)) {
        //Set payment variable
        $payment = $payinfo['payment'];
        //Select unpaid invoices
        $invqry = mysql_query ("SELECT `invoice_id`, `payment_due`
        FROM `customer_invoices`
        WHERE `paid_status` = 'unpaid' AND `customer_id` = '".mysql_real_escape_string($payinfo['customer_id'])."'
        ORDER BY `invoice_date` ASC;");
        // Loop through unpaid invoices
        while ($invinfo = mysql_fetch_assoc($invqry)) {
            if ($payment > 0) {
                // If there is credit remaining, compare it to the current invoice total
                if ($payment > $invinfo['payment_due']) {
                    // If the invoice amount is less than the remaining credit, pay the invoice, and deduct the total from the remaining credit
                    $payment -= $invinfo['payment_due'];
                    $payqry = mysql_query ("UPDATE `customer_invoices`
                    SET `payment_due` = '0', 
                    `paid_status` = 'paid'
                    WHERE `invoice_id` = '".mysql_real_escape_string($invinfo['invoice_id'])."';");
                } elseif ($invinfo['payment_due'] > $payment) {
                    // If the invoice amount is greated than the remaining credit, make a partial payment
                    $newamt = $invinfo['payment_due'] - $payment;
                    $partqry = mysql_query ("UPDATE `customer_invoices`
                    SET `payment_due` = '".mysql_real_escape_string($newamt)."'
                    WHERE `invoice_id` = ''.mysql_real_escape_string($invinfo['invoice_id'])."';");
                    $payment = 0;
                }
            } else {break;} // No more credit for this payment, move to the next
        }
    }
    
    How you execute this script is up to you. You could navigate to a page with this embedded in it as part of your application, or run it as a crontab job, whatever fits the bill for you.
     
  7. Mada_Milty

    Mada_Milty MajorGeek

    Oops.. neglected to handle the condition that the invoice amount is exactly equal to the remaining credit.

    Either the payment > invoice or invoice > payment conditions can be updated to handle this condition, and it should still work.

    ie payment >= invoice, or invoice >= payment should handle the condition fine.
     

MajorGeeks.Com Menu

Downloads All In One Tweaks \ Android \ Anti-Malware \ Anti-Virus \ Appearance \ Backup \ Browsers \ CD\DVD\Blu-Ray \ Covert Ops \ Drive Utilities \ Drivers \ Graphics \ Internet Tools \ Multimedia \ Networking \ Office Tools \ PC Games \ System Tools \ Mac/Apple/Ipad Downloads

Other News: Top Downloads \ News (Tech) \ Off Base (Other Websites News) \ Way Off Base (Offbeat Stories and Pics)

Social: Facebook \ YouTube \ Twitter \ Tumblr \ Pintrest \ RSS Feeds