Re: Questions about changes to schema 1.2/1.3 -> 1.4

Hi,

I have some perl scripts I wrote 4 or 5 years ago to do imports of data from MT4 into LedgerSMB. At the time there was essentially no usable perl-level API to interact with the LedgerSMB database, so I simply wrote my own.

Now we have LedgerSMB 1.4, and I see there are some significant changes to the database schema. I'm wondering if there's now a decent perl module to directly interact with the database (not the "construct a CGI request" hokiness that existed in past versions, I really want something fairly concise and clean, I'll attach what I have, which you can use yourselves, with the priviso that it currently doesn't work with 1.4).

Since you're working with a direct database connection, yes, there are modules you can use to do what you want (= import data into LedgerSMB). From your example code, I see you want the following kinds of data inserted:

- add vendors/customers

- import GL data

- import AP items (invoices? transactions?) [transactions never have inventory impact; invoices *can* have inventory impact]

LedgerSMB 1.4 offers a very clean interface to add vendors (I think), when acting with a direct database connection available: LedgerSMB::Entity::Company (https://github.com/ledgersmb/LedgerSMB/blob/1.4/LedgerSMB/Entity/Company.pm) and LedgerSMB::Entity:Credit_Account (https://github.com/ledgersmb/LedgerSMB/blob/1.4/LedgerSMB/Entity/Credit_Account.pm).

In order to use the above classes, you need to:

- create a direct database connection ($dbh)

- assign the value of $dbh to $LedgerSMB::App_State::DBH

- create a company as follows(typed into the mail here, untested):

my $company = LedgerSMB::Entity::Company->new(

legal_name => 'the legal name',

name => 'the name',

country_id => {integer selected from the Id's in the country-table},

control_code => 'if you want to manually set one',

entity_class => 1 # vendor

);

$company->save();

my $vendor_eca = LedgerSMB::Entity::Credit_Account->new(

entity_id => $company->entity_id,

entity_class => 1, # vendor

description => 'the way you see the vendor in the UI',

curr => 'USD', # default currency to be used for this vendor

ar_ap_account_id => {integer selected from the IDs in the account-table} # default AP account used for the vendor

);

$vendor_eca->save();

Barring that, what's the best way to get up to speed on the changes? For instance I see that the vendors table is totally gone, replaced apparently with entities and various relations. I don't see a stored procedure or updatable view that allows a simple SQL INSERT/UPDATE/SELECT of that data. I'm sure I can write one, but why reinvent the wheel?

Since you're expecting a 'customer' and 'vendor' database, your prior import must have been to 1.2. There's a 1.2 conversion script at https://github.com/ledgersmb/LedgerSMB/blob/master/sql/upgrade/1.2-1.4.sql which might help you identify the differences in the database schema.

The problem in older 1.2 code is that employees can both be an employee and a customer of a business, or that multiple departments of a single company can all be customers at the same company. These accounts can be managed independently, but for the purpose of managing credit risk, you still want to register the different "roles". That's what "credit accounts" are about: they are the different roles a single person/legal entity can assume. E.g.: Employee, vendor, customer, prospect, ...

You're right that there's not a full Perl interface to every functionality in the system like the one for the Company/Eca above. It's underway, but definitely not there yet. Areas where this is particularly the case (the API being absent, that is) are AR, AP and GL. Most of your 1.2-based code should still work there, except that the vendor and customer have been replaced by references to ECAs.

I'm sure I'll stumble across a bunch of other things, but maybe someone else already has...

No problem. Put your questions out here and we'll try to help you through them. It offers a great opportunity to build on the collection of example code!

Bye,

Erik.

http://efficito.com -- Hosted accounting and ERP.

Robust and Flexible. No vendor lock-in.