Invoice Importer

[Reuben Cummings]

On Mar 27, 2012, at 11:27 AM, Derek Atkins wrote:

> Reuben Cummings <reubano at gmail.com> writes:
> 
>>>> I figured it out. The invoices from the entries table were never entered
>>>> into the invoices table. I wrote a script to match the invoices with the
>>>> transaction guids and insert them into the invoices table.
>>> 
>>> I don't think I understand this issue.  What do you mean by "The
>>> invoices from the entries table were never entered into the invoices
>>> table"?  
>> 
>> I'm referring to tables in the backend database. There are two tables
>> that relate to invoices, i.e., entries and invoices. The two tables
>> are related by the fields entries.invoice = invoice.guid.
> 
> Umm, let's ignore the database and talk about the GnuCash objects,
> please?  You shouldn't be modifying the database, you should be using
> the GnuCash APIs to enter your data.

I would like to use the API but I haven't gotten the python module to work. See my post at
http://gnucash.1415818.n4.nabble.com/python-binding-error-on-macports-td4441220.html

>>> In particular I don't understand what you mean by "invoices
>>> from the entries table",
>> 
>> This refers to the invoice field of the entries database table, i.e.,
>> entries.invoice.
> 
> So you mean the gncEntryGetInvoice() result?

I'll have to assume we are talking about the same thing since I'm not familiar with the objects.

>>> and I don't understand what you mean by
>>> "[entries] were never entered into the invoices table."
>> 
>> I guess 'record' is more appropriate. The invoice field of each record
>> in the entries table should have a corresponding record in the
>> invoices table (joined at the guid field).
> 
> Again, please consider using the object model instead of table.  So you
> are saying that the gncEntryGetInvoice() is NULL?

Yes, that sounds right.

> Is this a customer Invoice or a Vendor Bill? 

Customer invoice. 

> What about gncEntryGetBill()?


I don't know since I've only been entering invoices.

>>> Are you saying that the Entries on an invoice were never added to the
>>> Invoice?
>> 
>> So, I am saying that the importer created records in the entries table
>> that had no corresponding record in the invoices table. You can view
>> them with the following sql statement.
> 
> What API did the Importer use to add the entry to the Invoice (or Bill)?

I have no idea, I guess Mike Evans would know the answer though.

>> SELECT
>> 	entries.invoice
>> 
>> 	FROM entries
>> 	LEFT OUTER JOIN invoices ON entries.invoice = invoices.guid
>> 	WHERE invoices.guid IS NULL
>> 	GROUP BY entries.invoice
> 
> Do you really need the join here?

Yes. Since the orphan entries are identical to the non-orphan entries, the only way to filter them out is to join on the invoices table and exclude all the entries that match. That leaves entries with non-matching invoices... the orphan entries.

> What about the entry bill?

You mean vendor bills? I don't have any bills so I only need to look for invoices.

>> What I did was match these orphan invoices with the appropriate
>> transactions in the transactions table (the invoices and transaction
>> tables are related by the transaction guid) to create the records that
>> needed to be inserted into the invoices table.
> 
> I still don't understand what you mean by "Orphan Invoices" here.  I
> think it's a terminology issue here.  It sounds to me like you have
> "Orphan Entries", not "Orphan Invoices", and I'm trying to understand
> how these could be orphaned unless there is a bug in the importer.

Yes, "Orphan Entries" is what I mean. And yes, there was a bug.

>From above in this thread.

> Mar 04, 2012; 1:29pm
>> The patch has now been applied to 2.4 branch and will be in the next 
>> release. 
>> Mike Evans 

However, I needed a fix now to use until the next release comes out... and since i don't have access to the api (see above) I had no choice but to modify the database directly.

> -derek