Jump to content
  • 0

Overview: Outstanding Balance Question


evolvewh

Question

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?

Link to comment
Share on other sites

22 answers to this question

Recommended Posts

  • 0

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.

Link to comment
Share on other sites

  • 0

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.

Link to comment
Share on other sites

  • 0
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

Link to comment
Share on other sites

  • 0

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 ?

Link to comment
Share on other sites

  • 0
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?

Link to comment
Share on other sites

  • 0

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.

Link to comment
Share on other sites

  • 0
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.

Link to comment
Share on other sites

  • 0
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.

Link to comment
Share on other sites

  • 0

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 .

Link to comment
Share on other sites

  • 0

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
 

Link to comment
Share on other sites

  • 0
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

img.png

 

 

Link to comment
Share on other sites

  • 0

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

Link to comment
Share on other sites

  • 0

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();			
	}

 

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...