[GNC] Linked Documents on Import
Robert Simmons
rsimmons0 at gmail.com
Mon Jul 25 09:54:34 EDT 2022
I definitely wouldn't expect this to be supported. I did take a look at
PieCash and see how it handles working with the "slots" table. There
doesn't appear to be any other housekeeping that is done other than the
INSERT commands. As long as the INSERTS are properly formed and I'm not
doing this with gnucash running at the same time, the outcome so far
appears to satisfy my requirement to add linked documents programmatically.
Thanks to the people who suggested PieCash.
Here is one of the stretches of code in PieCash that deals with that
particular table:
https://github.com/sdementen/piecash/blob/fcf36d8e315bd13183b166ff928b691c622f1360/gnucash_books/reference/3_0/default_3_0_0_full_options.gnucash.sql#L252-L329
On Mon, Jul 25, 2022 at 8:57 AM Derek Atkins <derek at ihtfp.com> wrote:
> 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