Overview: Outstanding Balance Question

evolvewh · July 14, 2016

Does the 'Outstanding Balance' on the Billing -->> Overview screen include 'Pending' invoices? I'm looking at ours and the balance is way to high for what is currently invoiced. I did go in and create a few pending invoices for future dates yesterday and I think they are being included in this total.

Anyone else seen this?

Michael · July 14, 2016

It does as far as I know cancelling pending orders decreases the outstanding balance.

Paul · July 14, 2016

Are we talking about pending orders, or pending invoices? Pending invoices have a bill date in the future. Pending orders have services that are in the in-review status, and may have open invoices.

evolvewh · July 14, 2016

I am talking about Pending Invoices. I have not added up the totals but the difference appears to come from the Pending Invoices in the system. I'll do the math to be more exact today and confirm.

Paul · July 14, 2016

I am talking about Pending Invoices. I have not added up the totals but the difference appears to come from the Pending Invoices in the system. I'll do the math to be more exact today and confirm.

Pending invoices are not created automatically, so for these you would have had to manually create the invoice and select a future bill date.

If pending invoices are included in the total, I'll create a task so we can address this in a future release.

evolvewh · July 14, 2016

I have already created pending invoices with future dates.

I have just added up the totals and 'Overview' does include 'Open Invoices' and 'Pending Invoices' to create the 'Outstanding Balance' total.

This is with v3.6.1 and I would guess it effects other 3.x versions.

evolvewh · July 18, 2016

Can this get a task to fix the bug? Hopefully it's not that difficult to fix.

Paul · July 29, 2016

CORE-2230, thanks!

Hix · October 13, 2016

On 29/07/2016 at 7:28 PM, Paul said:

CORE-2230, thanks!

@Paul hey look.. here is the patch. It was quite an annoying bug, so I patched it. Me not being a true developer though, so I'm not sure if this is a proper way how to compare a date :wacko:

--- /root/billing_overview_statistics.php   2016-10-13 17:16:04.000000000 +0200
+++ ./plugins/billing_overview/models/billing_overview_statistics.php   2016-10-13 17:35:21.000000000 +0200
@@ -148,6 +148,7 @@
            innerJoin("client_groups", "client_groups.id", "=", "clients.client_group_id", false)->
            where("client_groups.company_id", "=", $company_id)->
            where("invoices.status", "in", array("active", "proforma"))->
+           where("invoices.date_billed", "<", date( 'Y-m-d H:i:s', strtotime("now") ) )->
            where("invoices.currency", "=", $currency)->
            where("invoices.date_closed", "=", null)->
            fetch();
@@ -262,4 +263,4 @@
            numResults();
    }
}
-?>
\ No newline at end of file
+?>

billing_overview_statistics.patch

turner2f · October 28, 2016

This happened to me with an "Abandoned Cart".

The "Abandoned Cart" created an official invoice, that then said that I have "outstanding balances" that are due and also said that my "services" would be interrupted if I did not pay.

What is really funny is that what the "Abandoned Item" in the cart was a product. Not a service.

================

Bottom line is that ...

"Abandoned Carts" should NOT create "official" invoices that are "DUE".

Because it is DEMANDING payment for an item that technically does not yet exist

Abandoned carts should either be emptied once the browser closes,
or should just send a reminder asking IF the customer is still interested

=============

Will this be fixed in version 4.0 ?

evolvewh · October 28, 2016

11 hours ago, turner2f said:

This happened to me with an "Abandoned Cart".

The "Abandoned Cart" created an official invoice, that then said that I have "outstanding balances" that are due and also said that my "services" would be interrupted if I did not pay.

What is really funny is that what the "Abandoned Item" in the cart was a product. Not a service.

================

Bottom line is that ...

"Abandoned Carts" should NOT create "official" invoices that are "DUE".

Because it is DEMANDING payment for an item that technically does not yet exist

Abandoned carts should either be emptied once the browser closes,
or should just send a reminder asking IF the customer is still interested

=============

Will this be fixed in version 4.0 ?

I've never seen this happen. Which version are you using?

turner2f · October 28, 2016

Version 3.2.6

It's a know, documented issue.

Please see...

https://dev.blesta.com/browse/CORE-2230

Paul · October 28, 2016

CORE-2230 has nothing to do with invoices generated by the order system. It has to do with pending invoices. When someone places an order, an invoice is created, not a pending invoice.

Blesta Addons · October 28, 2016

@Paul

This we call it proforma in europe market . and it should not calculated as well with invoices .

What i can see here is blesta should not include invoices for pending services or for renewals that is created auto . .

turner2f · October 29, 2016

What NajjHost said is what I have been trying to say too.

Because these invoices are billing for "pending" services or products that do NOT technically exist .

They should be "considered" as "abandoned carts" that should ask if the person via an email reminder is still interested or not, and then empty the cart after a couple hours or days.

Michael · October 29, 2016

6 hours ago, turner2f said:

What NajjHost said is what I have been trying to say too.

Because these invoices are billing for "pending" services or products that do NOT technically exist .

They should be "considered" as "abandoned carts" that should ask if the person via an email reminder is still interested or not, and then empty the cart after a couple hours or days.

Right then so let's order some hosting and forget about it, I don't really want it just ordering for the sake of it. Got to love it. You don't checkout an order if you don't want it. Well not a human being anyway, you checkout orders if you want to buy something. If I get so far and change my mind I leave it in the order form and don't CHECKOUT. Unless in the United Kingdom we do things different because the EU is shit and well not sure how America works but it's common sense.

Another reason, you don't go into a shop on the highstreet, buy an apple or a chocolate bar, get to the checkout and go sorry I don't want it now, and walk out? even then you don't go out of the shop after you paid for it and chuck it on the floor as if you don't want it.

Paul · October 31, 2016

On 10/28/2016 at 4:49 PM, naja7host said:

@Paul

This we call it proforma in europe market . and it should not calculated as well with invoices .

What i can see here is blesta should not include invoices for pending services or for renewals that is created auto . .

So, Proforma invoices should not be calculated in the outstanding balance, the same as planned for "Pending" invoices, aka invoices with future bill dates? Please confirm, and I'll modify the task, assuming this is a standard requirement. It seems to make sense, since it's not forcibly due.

Blesta Addons · October 31, 2016

for me is ok ,

1 hour ago, Paul said:

So, Proforma invoices should not be calculated in the outstanding balance, the same as planned for "Pending" invoices, aka invoices with future bill dates? Please confirm, and I'll modify the task, assuming this is a standard requirement. It seems to make sense, since it's not forcibly due.

but i don't know what other think about it .

turner2f · October 31, 2016

Yes.

Please do.

Chris van der Westhuizen · August 15, 2018

Is this the correct approach towards calculating the credit from database on a single currency?

select `clients`.`id` as 'client_id', `clients`.`id_value` as 'id_value'

, ifnull(inv.invoices_total,0) as 'invoices_total'
, ifnull(trans.transactions_total,0) as 'transactions_total'
, ifnull(transrev.transreverse_total,0) as 'transreverse_total'
, ifnull(-inv.invoices_total,0) + ifnull(trans.transactions_total,0) as 'credit'
from clients
left join (select `client_id`, ifnull(sum(`total`),0) as 'invoices_total' from `invoices` where `invoices`.`status` = 'active' group by `invoices`.`client_id`) as inv on `clients`.`id` = inv.client_id
left join (select `client_id`, ifnull(sum(`amount`),0) as 'transactions_total' from `transactions` where `transactions`.`status` = 'approved' group by `transactions`.`client_id`) as trans on `clients`.id = trans.client_id
left join (select `client_id`, ifnull(sum(`amount`),0) as 'transreverse_total' from `transactions` where `transactions`.`status` in('refunded','returned') group by `transactions`.`client_id`) as transrev on clients.id = transrev.client_id

Blesta Addons · August 16, 2018

On 8/15/2018 at 6:52 AM, Chris van der Westhuizen said:

Is this the correct approach towards calculating the credit from database on a single currency?

select `clients`.`id` as 'client_id', `clients`.`id_value` as 'id_value'

, ifnull(inv.invoices_total,0) as 'invoices_total'
, ifnull(trans.transactions_total,0) as 'transactions_total'
, ifnull(transrev.transreverse_total,0) as 'transreverse_total'
, ifnull(-inv.invoices_total,0) + ifnull(trans.transactions_total,0) as 'credit'
from clients
left join (select `client_id`, ifnull(sum(`total`),0) as 'invoices_total' from `invoices` where `invoices`.`status` = 'active' group by `invoices`.`client_id`) as inv on `clients`.`id` = inv.client_id
left join (select `client_id`, ifnull(sum(`amount`),0) as 'transactions_total' from `transactions` where `transactions`.`status` = 'approved' group by `transactions`.`client_id`) as trans on `clients`.id = trans.client_id
left join (select `client_id`, ifnull(sum(`amount`),0) as 'transreverse_total' from `transactions` where `transactions`.`status` in('refunded','returned') group by `transactions`.`client_id`) as transrev on clients.id = transrev.client_id

You need it for making a statement for clients? if yes, i think already a member has posted a plugin that do this .

if you have subscription in our website, look at the stats plugin, it has some sort of this request, as i see you want a list of client's credit ?

is this what you want? i can share with you the code if you don't have access to our addons

Chris van der Westhuizen · August 16, 2018

Thank you Blesta Addons for your reply. We are writing a plugin to sync balance with another API of program on our network. that is why I try to calculate the credit, that is actually the balance credit or outstanding. thus outstanding debit less the credit shown, but I do not know where to get it properly, that is why I did the query but are not sure if I get all values correctly to get the credit (balance). because current result works good for 99% of the clients, but there is a few, that is out by the value of a service, and I cannot pick it up in db

Blesta Addons · August 16, 2018

this is how we do it

	/**
     * Returns the amount available as a credit for each client by currency
	 */
	public function getCredits()
	{
        $fields = [
			'transactions.currency', 'transactions.client_id', 'transactions.amount',
			'REPLACE(clients.id_format, ?, clients.id_value)' => 'client_id_code',
            'SUM(IFNULL(transaction_applied.amount,?))' => 'applied_amount',
            'contacts.first_name' => 'first_name',
            'contacts.last_name' => 'last_name',
            'contacts.company' => 'client_company'
        ];
        $this->Record->select($fields)
            ->appendValues(
                [
                    $this->replacement_keys['clients']['ID_VALUE_TAG'],
					0
                ]
            )
            ->from('transactions')
            ->leftJoin('transaction_applied', 'transaction_applied.transaction_id', '=', 'transactions.id', false)
            ->leftJoin('contacts', 'contacts.client_id', '=', 'transactions.client_id', false)			
            ->innerJoin('clients', 'clients.id', '=', 'transactions.client_id', false)
            ->innerJoin('client_groups', 'client_groups.id', '=', 'clients.client_group_id', false)
            ->where('transactions.status', '=', 'approved');

        // Filter by company
        $this->Record->where('client_groups.company_id', '=', Configure::get('Blesta.company_id'));
		
        // return 
		$transactions = $this->Record
            ->group(['transactions.id', 'transactions.client_id', 'transactions.currency'])
            ->having('applied_amount', '<', 'transactions.amount', false)
            ->fetchAll();
		
        $total_credits = [];
		
        foreach ($transactions as $transaction) {
			$transaction->total_amount = round($transaction->amount - $transaction->applied_amount, 4);
			
			if (isset($total_credits[$transaction->client_id]) 
				&& $total_credits[$transaction->client_id]->currency == $transaction->currency
			) {
				$total_credits[$transaction->client_id]->total_transactions++;
				$total_credits[$transaction->client_id]->total_amount += $transaction->total_amount;
				continue;
			}
			
			$total_credits[$transaction->client_id] = $transaction;
			$total_credits[$transaction->client_id]->total_transactions = 1;
        }
		
		usort(
			$total_credits, function($a, $b) {
				return $b->total_amount - $a->total_amount; 
			}
		);
		
		return $total_credits;
	}

for amout due

	/**
     * Returns the amount due for each client by currency
     * 
	 */
	public function getAmountDue()
	{
        $fields = [
			'invoices.total', 
			'invoices.currency', 
			'invoices.client_id',
			'COUNT(invoices.id)' => 'total_invoices',
			'REPLACE(clients.id_format, ?, clients.id_value)' => 'client_id_code',
            'contacts.first_name' => 'first_name',
            'contacts.last_name' => 'last_name',
            'contacts.company' => 'client_company'
        ];
        $this->Record->select($fields)
			->select(['SUM(IFNULL(invoices.total,0))-SUM(IFNULL(invoices.paid,0))' => 'total_amount'], false)
            ->from('invoices')		
            ->appendValues(
                [
                    $this->replacement_keys['clients']['ID_VALUE_TAG']
                ]
            )
            ->innerJoin('clients', 'clients.id', '=', 'invoices.client_id', false)
            ->innerJoin('client_groups', 'client_groups.id', '=', 'clients.client_group_id', false)
            ->on('contacts.contact_type', '=', 'primary')
            ->innerJoin('contacts', 'contacts.client_id', '=', 'clients.id', false)		
			->where('invoices.status', 'in', ['active', 'proforma'])
			->where('invoices.date_closed', '=', null);

        // Filter by company
        $this->Record->where('client_groups.company_id', '=', Configure::get('Blesta.company_id'));
				
        return $this->Record
            ->group(['invoices.client_id', 'invoices.currency'])
			->order(['total_amount' => 'DESC'])
            // ->having('total_amount', '>', 0, false)			
			->limit($this->getPerPage(), (max(1, 1) - 1) * $this->getPerPage())
            ->fetchAll();			
	}

Sign In

Overview: Outstanding Balance Question

Question

evolvewh

22 answers to this question

Recommended Posts

Michael

Paul

evolvewh

Paul

evolvewh

evolvewh

Paul

Hix

turner2f

evolvewh

turner2f

Paul

Blesta Addons

turner2f

Michael

Paul

Blesta Addons

turner2f

Chris van der Westhuizen

Blesta Addons

Chris van der Westhuizen

Blesta Addons

Join the conversation

Browse

Activity