[GNC] Linked Documents on Import
Robert Simmons
rsimmons0 at gmail.com
Mon Jul 25 08:40:38 EDT 2022
This seems to do the trick. Not sure if I'm doing something terrible here.
I don't recommend you do this unless you know what you're doing (I don't
lol).
https://gist.github.com/utkonos/1dad74716828c591c9839711fdb12f22
import pathlib
import sqlite3
bills = [{'id': '000001',
'date_opened': '12/31/2018',
'owner_id': '000001',
'billingid': '123123123123',
'notes': '',
'date': '',
'desc': 'Widget',
'action': 'Material',
'account': 'Expenses:Office Supplies',
'quantity': 1,
'price': '1000.00',
'disc_type': '',
'disc_how': '',
'discount': '',
'taxable': '',
'taxincluded': '',
'tax_table': '',
'date_posted': '12/31/2018',
'due_date': '12/31/2018',
'account_posted': 'Liabilities:Accounts Payable',
'memo_posted': '',
'accu_splits': ''},
{'id': '000001',
'date_opened': '01/31/2019',
'owner_id': '000001',
'billingid': '123123123126',
'notes': '',
'date': '',
'desc': 'Widget',
'action': 'Material',
'account': 'Expenses:Office Supplies',
'quantity': 1,
'price': '1000.00',
'disc_type': '',
'disc_how': '',
'discount': '',
'taxable': '',
'taxincluded': '',
'tax_table': '',
'date_posted': '01/31/2019',
'due_date': '01/31/2019',
'account_posted': 'Liabilities:Accounts Payable',
'memo_posted': '',
'accu_splits': ''}]
doclinks = {
'000001': {
'doclink': 'Path/To/Some1.pdf'
},
'000002': {
'doclink': 'Path/To/Some2.pdf'
}
}
db_path = pathlib.Path('testfile.gnucash')
con = .sqlite3connect(db_path)
cur = con.cursor()
for bill in bills:
rows = cur.execute('SELECT * FROM invoices WHERE owner_type = 4 AND id
= "{}"'.format(bill['id']))
for row in rows:
doclinks[bill['id']]['obj_guid'] = row[0]
rows = cur.execute('SELECT max(id) FROM slots')
slotid = rows.fetchone()[0] + 1
for doclink in doclinks.values():
cur.execute("INSERT INTO slots VALUES
({},'{}','assoc_uri',4,0,'{}',NULL,'1970-01-01
00:00:00',NULL,0,1,NULL)".format(slotid, doclink['obj_guid'],
doclink['doclink']))
con.commit()
slotid += 1
con.close()
On Sun, Jul 24, 2022 at 8:12 PM Robert Simmons <rsimmons0 at gmail.com> wrote:
> I don't see anything specific to linked documents in PieCash.
>
> I see that the IU menu item "Manage Document Link...." calls
> "gnc_plugin_page_invoice_cmd_link" in
> gnucash/gnome/gnc-plugin-page-invoice.c
>
> This is here:
>
> https://github.com/Gnucash/gnucash/blob/bbb4113a5a996dcd7bb3494e0be900b275b49a4f/gnucash/gnome/gnc-plugin-page-invoice.c#L1342-L1396
>
> On line 1389, there is a call to "gncInvoiceSetDocLink". This function is
> in libgnucash/engine/gncInvoice.c
>
> Here:
>
> https://github.com/Gnucash/gnucash/blob/ce2b89fd8cae4c5c78ba6925e78d18fca6a3df6a/libgnucash/engine/gncInvoice.c#L550-L580
>
> And this specifically is where the DocLink is set:
>
> {
> GValue v = G_VALUE_INIT;
> g_value_init (&v, G_TYPE_STRING);
> g_value_set_string (&v, doclink);
> qof_instance_set_kvp (QOF_INSTANCE (invoice), &v, 1,
> GNC_INVOICE_DOCLINK);
> invoice->doclink = g_strdup (doclink);
> g_value_unset (&v);
> }
>
> I don't see this "gncInvoiceSetDocLink" function in the Python bindings,
> however (or the search function I'm using is not working).
>
> Also, I don't see anything in the log that gnucash creates related to the
> row in the "slots" table that represents the DocLink.
>
> I'm going to experiment with just adding that row to the database myself
> directly.
>
> Using bills as the example (but I can do the same for other objects):
>
> 1. Import the bills from my CSV.
> 2. Query the database in the "invoices" table using the ID field from my
> CSV for the matching row.
> 3. Query the "guid" for the ID from #2.
> 4. Create a new row in the "slots" table using the "guid" from #3 and
> name="assoc_uri", "slot_type"=4, "int64_val"=0, and "string_val" set to the
> relative path to the document. The rest of the fields in that row look
> basically unused, so I'll set them to whatever is in the previous linked
> document rows.
>
> If you can identify something that I'm missing, please let me know.
> Especially "OMG, you're forgetting X and will blow up Y if you try this."
>
> On Sun, Jul 24, 2022 at 7:10 PM Geoff <cleanoutmyshed at gmail.com> wrote:
>
>> Hi Robert
>>
>> > Is there a way to include the document URI in the import CSV?
>>
>> I don't believe so.
>>
>> > could probably reverse engineer the database queries that are
>> > being made by reading the source code.
>>
>> PieCash may be of interest:
>>
>> https://github.com/sdementen/piecash
>>
>>
>> Regards
>>
>> Geoff
>> =====
>>
>> On 25/07/2022 6:58 am, Robert Simmons wrote:
>> > Please forgive me if this is the wrong place to ask this question. I
>> > searched the list and did not see this discussed, so if it was
>> discussed,
>> > also please forgive my searching mistake.
>> >
>> > I have built a Jupyter notebook that parses incoming bills in various
>> forms
>> > (some are PDF, some are CSV, and some are just saved HTML). The output
>> is
>> > the correct CSV for gnucash. After importing the data from this CSV, I
>> have
>> > almost everything I need except for one data point: the linked document.
>> > After the import process, I have to go one by one to each imported bill
>> or
>> > invoice and link the document manually in gnucash's UI.
>> >
>> > I opened the database and looked around and I can see that the linked
>> > documents are stored in the "slots" table with the "name" column set to
>> > "assoc_uri" set to the relative path to the document. The "slot_type"
>> is 4
>> > and the "obj_guid" I assume points to the invoice or whatever the
>> document
>> > is associated with. This is all very straightforward. I absolutely don't
>> > want to make changes from Jupyter directly to the database even though I
>> > could probably reverse engineer the database queries that are being
>> made by
>> > reading the source code.
>> >
>> > I have looked through the Python bindings and I don't see any
>> references to
>> > linking documents.
>> >
>> > Is there a way to include the document URI in the import CSV?
>> >
>> > Or
>> >
>> > Is there a Python binding that I'm missing or isn't documented that will
>> > allow me to link a document?
>> >
>> > Finally:
>> >
>> > Is adding a field for a linked document to the importer UI component a
>> > valid feature request?
>> > _______________________________________________
>> > gnucash-user mailing list
>> > gnucash-user at gnucash.org
>> > To update your subscription preferences or to unsubscribe:
>> > https://lists.gnucash.org/mailman/listinfo/gnucash-user
>> > If you are using Nabble or Gmane, please see
>> https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
>> > -----
>> > Please remember to CC this list on all your replies.
>> > You can do this by using Reply-To-List or Reply-All.
>>
>
More information about the gnucash-user
mailing list