Owing Column reports amounts owing for Quotes/ Estimates

[GoogleCodeExporter]

1. Make sure the Quote Invoice Type has the Status set to Draft 
(Settings->Invoice Preferences-[Edit]->Status=Draft)
2. Create a new Quote (Money->New Invoice->Invoice Preference=Quote), add some 
items and save the Quote.
3. Return to Money->Invoices

Note that the amount for the quote is showing in the "Owing" column.  Since 
this Status of this Invoice Type is Draft, there should be NO money "Owing" on 
the Quote - this should only appear AFTER it has been changed to an Invoice 
(with Status of Real).

More importantly, when viewing Customers (People->Customers) the "Owing" column 
here also shows any and all Quoted amounts as "Owing".

In my world - issuing a Quote DOES NOT mean that someone owes you money!  That 
doesn't happen until you issue the invoice.

I believe that any report/screen that shows amounts "Owing" should exclude any 
amount associated with an Invoice (Quote) with Status of "Draft"

Tested on Version 2011.1

Footnote: I find this whole "Real" and "Draft" concept very confusing.  But 
once it is set up (and if it worked properly) it would be OK

Original issue reported on code.google.com by rednectar.chris on 8 Feb 2012 at 5:21

[GoogleCodeExporter]

Thanks for reporting this Chris.
I can confirm that there is a bug here and we'll do our best to get it fixed 
soon.

I agree that the concept of having 'real' and 'draft' entries can be confusing, 
however it was a feature that a number of people had requested.

Original comment by MattAntW...@gmail.com on 8 Feb 2012 at 10:52

• Changed state: Open-Accepted
• Added labels: Component-Logic, Milestone-NR, Priority-High, Type-Defect

[GoogleCodeExporter]

Any Progress on this one? It has driven me nuts looking for solution.
Thanks

Original comment by danstout...@gmail.com on 5 Dec 2012 at 8:59

[GoogleCodeExporter]

I've moved this over to the GitHub issue tracker.
I'll try and get this fixed for the next release.

Original comment by MattAntW...@gmail.com on 7 Dec 2012 at 9:41

[GoogleCodeExporter]

I made the following change in the modules/customers/xml.php in line 70 the 
result is not showing in People->Customers owing results

from this 
SELECT 
   coalesce(sum(ii.total),  0) AS total 
FROM
  ".TB_PREFIX."invoice_items ii INNER JOIN
  ".TB_PREFIX."invoices iv ON (iv.id = ii.invoice_id)
WHERE  
   iv.customer_id  = CID) as customer_total,

to this

SELECT 
   coalesce(sum(ii.total),  0) AS total 
FROM
  ".TB_PREFIX."invoice_items ii INNER JOIN
  ".TB_PREFIX."invoices iv ON (iv.id = ii.invoice_id)
   INNER JOIN ".TB_PREFIX."preferences ON ( preference_id = pref_id )
WHERE  
   iv.customer_id  = CID and status = 1) as customer_total,

Original comment by manthos....@gmail.com on 8 Jan 2013 at 7:02

[GoogleCodeExporter]

Hi Manthos,
Does your fix also sort out the Money->Invoices bug?
Its really driving me nuts!

Original comment by adeda...@gmail.com on 30 Jan 2013 at 10:24

[GoogleCodeExporter]

I found where it is but i canot fix it 
it is in include/class/invoice.php
Line 349
ROUND( (SELECT (coalesce(invoice_total,0) - coalesce(INV_PAID,0))) ,2) As owing,

must be change to something like this but dont work
if status = 1
ROUND( (SELECT (coalesce(invoice_total,0) - coalesce(INV_PAID,0))) ,2) As owing,
else
0 As owing,

Original comment by manthos....@gmail.com on 1 Feb 2013 at 8:03

[GoogleCodeExporter]

I found the solution 
it is in include/class/invoice.php
Line 349
remove line
ROUND( (SELECT (coalesce(invoice_total,0) - coalesce(INV_PAID,0))) ,2) As owing,

replace with

ROUND( (SELECT (CASE   WHEN status = 0 THEN '0' 
        ELSE (coalesce(invoice_total,0) - coalesce(INV_PAID,0))  END)) ,2) As owing,

Original comment by manthos....@gmail.com on 1 Feb 2013 at 8:38

[GoogleCodeExporter]

IT worked like magic! Thanks manthos!

Original comment by adeda...@gmail.com on 6 Feb 2013 at 4:02

[GoogleCodeExporter]

I also made the following changes:
FILE:: include/functions.php

line 173:
$sql ="SELECT
        coalesce(sum(ii.total),  0) AS total 
    FROM
        ".TB_PREFIX."invoice_items ii INNER JOIN
        ".TB_PREFIX."invoices iv ON (iv.id = ii.invoice_id)
        INNER JOIN ".TB_PREFIX."preferences ON ( preference_id = pref_id )
    WHERE  
        status = 1 and iv.customer_id  = :customer
    ";

FILE:: include/class/invoice.php

line 143:
WHERE 
                    status = 1 and 

But I notice the following aspects still factor in the draft values:
1. People->Customers->View->Customer Invoice listing
2. Money-> Invoices->Quick View = the 'Financial status' aspect.

Manthos, can you dig a bit further for these? Thanks

Original comment by adeda...@gmail.com on 6 Feb 2013 at 6:19

[GoogleCodeExporter]

Awesome work Mathis.
Can you please package this up as a patch.

Original comment by MattAntW...@gmail.com on 8 Feb 2013 at 5:33

[GoogleCodeExporter]

Fix People->Customers->View->Customer Invoice listing
Draft problem
/include/sql_queries.php function getCustomerInvoices($id) line 1633

from

(SELECT sum( COALESCE(ii.total, 0)) FROM " . TB_PREFIX . "invoice_items ii 
where ii.invoice_id = i.id) As invd,
(SELECT sum( COALESCE(ap.ac_amount, 0)) FROM " . TB_PREFIX . "payment ap where 
ap.ac_inv_id = i.id) As pmt,
        (SELECT COALESCE(invd, 0)) As total, 
        (SELECT COALESCE(pmt, 0)) As paid, 
        (select (total - paid)) as owing 
    FROM 
        " . TB_PREFIX . "invoices i 
    WHERE 
        i.customer_id = :id
        and
        i.domain_id = :domain_id

to

(SELECT sum( COALESCE(ii.total, 0)) 
         FROM " . TB_PREFIX . "invoice_items ii 
         INNER JOIN ".TB_PREFIX."invoices ON ( invoice_id = ".TB_PREFIX."invoices.id )
         INNER JOIN ".TB_PREFIX."preferences ON ( preference_id = pref_id )
         where ii.invoice_id = i.id and status = 1) As invd,
        (SELECT sum( COALESCE(ap.ac_amount, 0)) FROM " . TB_PREFIX . "payment ap where ap.ac_inv_id = i.id) As pmt,
        (SELECT COALESCE(invd, 0)) As total, 
        (SELECT COALESCE(pmt, 0)) As paid, 
        (select (total - paid)) as owing 
    FROM 
        " . TB_PREFIX . "invoices i 
    WHERE 
        i.customer_id = :id
        and
        i.domain_id = :domain_id
    HAVING 
        total > 0

Original comment by manthos....@gmail.com on 8 Feb 2013 at 8:43

[GoogleCodeExporter]

Hi Matt,

How to package this up as a patch?

Regards
Manthos

Original comment by manthos....@gmail.com on 8 Feb 2013 at 8:45

[GoogleCodeExporter]

Hi Manthos,
Your latest fix was on line 1579 in my script.
Worked like a charm, as usual.
Thank you.
Will search for the last one [Money-> Invoices->Quick View = the 'Financial 
status' aspect].

Hi Matt, could you help us collate all this into the patch you spoke of?

Original comment by adeda...@gmail.com on 8 Feb 2013 at 10:29

[GoogleCodeExporter]

Hi Manthos,
Did the changes affect your print view for the Estimates? Mine just shows date 
now - nothing else. Please respond. Thank you

Original comment by adeda...@gmail.com on 25 Feb 2013 at 12:25

[GoogleCodeExporter]

where is print view for the estimates??? I don't understand

Original comment by manthos....@gmail.com on 25 Feb 2013 at 1:45

[GoogleCodeExporter]

@manthos: when you view an invoice/estimate/quote/receipt, it allows you to do 
the following: print preview, export as doc, export as pdf, export as xls, 
email etc. The very first one [print preview] works well for invoices but now 
for estimates, it shows a semi-blank page. Please try it out in your 
installation. Thank you

Original comment by adeda...@gmail.com on 25 Feb 2013 at 2:51

[GoogleCodeExporter]

Now I understand estimates is invoice preferences.
No I don't have this issue when I used any type of draft invoices.
Make the following test. Make a new type of invoice preferences as draft and 
see what happen. If you still have this problem check the changes you made in 
code, maybe something missed.
I will suggest you to use versions every time you make any changes that work.
I lost a lot of time in the past cause I didn't do that.

Hope help you
George

Original comment by manthos....@gmail.com on 25 Feb 2013 at 3:45