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 <rsimmo...@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 <de...@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 <rsimmo...@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 <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. >>> >> >> > _______________________________________________ > 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.
