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 <rsimmo...@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 <cleanoutmys...@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@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@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.
