[GNC] Linked Documents on Import
john
jralls at ceridwen.us
Mon Jul 25 12:05:15 EDT 2022
Piecash isn't supported either, because it also doesn't use the GnuCash API to write to the database.
The supported way--that works with either the XML or SQL backend--is to use the Guile or Python bindings to the GnuCash libraries. You can also write your program in any compiled language that can link to C libraries. The API documentation can be found at https://code.gnucash.org/docs/MAINT.
Regards,
John Ralls
> On Jul 25, 2022, at 6:54 AM, Robert Simmons <rsimmons0 at gmail.com> wrote:
>
> 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.
>>>
>>
>>
> _______________________________________________
> 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