[GNC] Linked Documents on Import
Derek Atkins
derek at ihtfp.com
Mon Jul 25 08:57:21 EDT 2022
You are definitely doing something terrible.
You are modifying the database from under GnuCash.
That is completely not supported and you may destroy your data.
-derek
Sent using my mobile device. Please excuse any typos.
On July 25, 2022 7:42:04 AM Robert Simmons <rsimmons0 at gmail.com> wrote:
> 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.
>>>
>>
> _______________________________________________
> 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