[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: How functional is 1.3?

Subject: Re: How functional is 1.3?
From: John Locke <..hidden..>
Date: Mon, 25 Oct 2010 10:44:47 -0700

Hi,

My notes are summarized here:

http://sourceforge.net/mailarchive/forum.php?thread_name=4CB49532.9000905%40freelock.com&forum_name=ledger-smb-devel

... since then, I've had to grant privileges on several sequences,
functions, and tables, and I did have to go through and change the
account/chart id on all of the individual parts I had imported...

Also, we had to do some refactoring on our check templates -- it's now
split into 3 files, and hard-coded to use the templates/demo templates.

I'm attaching the sql script Chris sent me, with a bunch of edits I did
as I worked through it...

We have reached the point where it's mostly usable. At the moment, we're
issuing invoices, receiving cash, printing checks, running some reports,
and starting to use the voucher and reconciliation systems. It's
starting to feel pretty solid. It took a long time getting here, and
I've been sending a steady stream of patches back to Chris. Next up on
the list: sending invoices by email. And then I think we're going to
bolt on a REST interface to facilitate some integration with our other
systems...

Cheers,
John Locke
http://freelock.com

-------- Original Message  --------
Subject: Re: [Ledger-smb-users] How functional is 1.3?
From: Philip Rhoades <..hidden..>
To: John Locke <..hidden..>
Cc: John Locke <..hidden..>
Date: Sun 24 Oct 2010 03:25:30 AM PST
> John,
>
> Do you have notes of things you have had to do to get 1.3 going?
>
> I am finding a number of things too - your notes might help me resolve
> some of these?
>
> Thanks,
>
> Phil.
>
>
> On 2010-10-15 07:53, John Locke wrote:
>>   I'm currently in the midst of migrating to 1.3 just this week.
>>
>> I'm finding tons of little issues, have been working with Chris T on
>> getting my patches back up stream...
>>
>> If you're willing to work with some growing pains, and accept a bit of a
>> step down in stability, it's probably workable (or will be come hell or
>> high water by next week...). We're moving forward primarily because we
>> have to fix the reconciliation system -- and this is vastly improved in
>> 1.3.
>>
>> initiate.pl failed for me, didn't install everything necessary. The
>> manual process does work, and there's a SQL upgrade script floating
>> around if you're looking to import from LSMB 1.2 or SQL-Ledger... not
>> really something that's usable if you're not a developer, you will hit
>> issues.
>>
>> I wrote a post to the Devel list earlier this week about our upgrade
>> process so far, if you can't find it in the archives, I can forward to
>> this list...
>>
>> Today I got reconciliation actually working, fixed a really crazy bug in
>> GL that was causing heading accounts to show up as additional lines and
>> making transactions out of balance, and am working on getting proper
>> shipto, customer, and vendor views that will hopefully fix most of AP
>> and AR screens. Reports mostly seem to work ok.
>>
>>
>> Cheers,
>> John Locke
>> http://freelock.com
>>
>> Philip Rhoades wrote:
>>> Turtle,
>>>
>>> I too was about to ask if there was any progress because I hadn't seen
>>> any posts re progress for a while.  Should I download the latest code
>>> and try again (64-bit)?
>>>
>>> Thanks,
>>>
>>> Phil.
>>>
>>>
>>> On 2010-10-15 02:13, Schmiechen wrote:
>>>> I just checked ledgersmb 1.3 Trunk Revision: 3091.
>>>> Just to see where things are at.
>>>> I installed the few extra dependency's and set it up in Apache on
>>>> my testing
>>>> Laptop.
>>>> How useable is 1.3? are folks here using it for daily use?
>>>> I checked out the initiate.pl in my webbrowser and browsed the code
>>>> a bit.
>>>> I got to initiate.pl not letting me log in as a db user but I did
>>>> not set up
>>>> any lsmb_db first.
>>>> Is initiate.pl supposed to edit ledgersmb.conf and set everything
>>>> up or is it
>>>> like a new admin.pl?
>>>> Anyway looks like allot of progress has been made.
>>>>
>>>> Cheers
>>>> Turtle
>>>>
>>>> ------------------------------------------------------------------------------
>>>>
>>>> Beautiful is writing same markup. Internet Explorer 9 supports
>>>> standards for HTML5, CSS3, SVG 1.1,  ECMAScript5, and DOM L2&   L3.
>>>> Spend less time writing and  rewriting code and more time creating
>>>> great
>>>> experiences on the web. Be a part of the beta today.
>>>> http://p.sf.net/sfu/beautyoftheweb
>>>> _______________________________________________
>>>> Ledger-smb-users mailing list
>>>> ..hidden..
>>>> https://lists.sourceforge.net/lists/listinfo/ledger-smb-users
>>
>>
>> ------------------------------------------------------------------------------
>>
>> Beautiful is writing same markup. Internet Explorer 9 supports
>> standards for HTML5, CSS3, SVG 1.1,  ECMAScript5, and DOM L2&  L3.
>> Spend less time writing and  rewriting code and more time creating great
>> experiences on the web. Be a part of the beta today.
>> http://p.sf.net/sfu/beautyoftheweb
>> _______________________________________________
>> Ledger-smb-users mailing list
>> ..hidden..
>> https://lists.sourceforge.net/lists/listinfo/ledger-smb-users
>


 
-- 
John Locke
Manager, Freelock Computing
The Open Source for Business Solutions
Follow me on Twitter: http://twitter.com/freelock
http://www.freelock.com
..hidden..  206-577-0540 x20


-- 
John Locke
"Open Source Solutions for Small Business Problems"
published by Charles River Media, June 2004
Follow me on Twitter: http://twitter.com/freelock
http://www.freelock.com

-- should be *_contact_read, *_contact_edit
GRANT EXECUTE ON FUNCTION eca__list_notes(int) TO lsmb_freelockco__contact_read;
GRANT INSERT ON eca_note TO lsmb_freelockco__contact_edit;

ALTER TABLE orig.vendor ADD COLUMN entity_id int;
ALTER TABLE orig.vendor ADD COLUMN company_id int;
ALTER TABLE orig.vendor ADD COLUMN credit_id int;

ALTER TABLE orig.customer ADD COLUMN entity_id int;
ALTER TABLE orig.customer ADD COLUMN company_id int;
ALTER TABLE orig.customer ADD COLUMN credit_id int;

INSERT INTO chart SELECT * FROM orig.chart;
-- The blank entities are tests and not used for anything anyway.

INSERT INTO business SELECT * FROM orig.business;

-- Importing vendors

-- add country id for US, renumber duplicates
INSERT INTO entity (name, control_code, entity_class, country_id)
SELECT name,vendornumber, 1, 232
FROM orig.vendor
GROUP BY name, vendornumber;  

-- add country id for us, renumber duplicates
INSERT INTO entity (name, control_code, entity_class, country_id)
SELECT name,customernumber, 2, 232
FROM orig.customer
GROUP BY name, customernumber;  

-- add entity class to subselect
UPDATE orig.vendor SET entity_id = (select id from entity where vendornumber = control_code AND entity_class = 1);
UPDATE orig.customer SET entity_id = (select id from entity where customernumber = control_code AND entity_class = 2);

-- no arap_accno_id or payment_accno_id or threshold in orig.vendor, orig.customer
-- set accno_id to accounts payable account.id
INSERT INTO entity_credit_account
(entity_id, meta_number, business_id, creditlimit, ar_ap_account_id,
	 startdate, enddate, entity_class)
SELECT entity_id, vendornumber, business_id, creditlimit, 19,
	 startdate, enddate, 1
FROM orig.vendor WHERE entity_id is not null;

-- in addition, set entity_class to customer
INSERT INTO entity_credit_account
(entity_id, meta_number, business_id, creditlimit,  ar_ap_account_id,
	startdate, enddate,  entity_class)
SELECT entity_id, customernumber, business_id, creditlimit,  6,
	startdate, enddate,  2
FROM orig.customer WHERE entity_id is not null;

UPDATE orig.vendor SET credit_id = 
	(SELECT id FROM entity_credit_account e 
	WHERE e.meta_number = vendornumber AND vendor.entity_id = e.entity_id);

UPDATE orig.customer SET credit_id = 
	(SELECT id FROM entity_credit_account e 
	WHERE e.meta_number = customernumber AND customer.entity_id = e.entity_id);


-- companies

INSERT INTO company (entity_id, legal_name, tax_id)
SELECT entity_id, name, taxnumber FROM orig.vendor
union 
SELECT entity_id, name, taxnumber FROM orig.customer;

UPDATE orig.vendor SET company_id = (select id from company c where entity_id = vendor.entity_id );
UPDATE orig.customer SET company_id = (select id from company c where entity_id = customer.entity_id );


CREATE VIEW orig.eca AS
select id, name, address1, address2, city, state, zipcode, country, contact, 
phone, fax, email, cc, bcc, taxincluded, terms, taxnumber, curr, startdate, enddate, 
credit_id, entity_id, company_id
from orig.customer
union
select id, name, address1, address2, city, state, zipcode, country, contact, 
phone, fax, email, cc, bcc, taxincluded, terms, taxnumber, curr, startdate, enddate, 
credit_id, entity_id, company_id
from orig.vendor;

-- Moving to a UNION query
insert into eca_to_contact (credit_id, contact_class_id, contact,description) 
select v.credit_id, 1, v.phone, 'Primary phone: '||max(v.contact) as description
from orig.eca v 
where v.company_id is not null and v.phone is not null 
       and v.phone ~ '[[:alnum:]_]'::text 
group by v.credit_id, v.phone
UNION
select v.credit_id, 12, v.email, 
       'email address: '||max(v.contact) as description 
from orig.eca v 
where v.company_id is not null and v.email is not null 
       and v.email ~ '[[:alnum:]_]'::text 
group by v.credit_id, v.email
UNION
select v.credit_id, 12, v.cc, 'Carbon Copy email address' as description 
from orig.eca v 
where v.company_id is not null and v.cc is not null 
      and v.cc ~ '[[:alnum:]_]'::text 
group by v.credit_id, v.cc
UNION 
select v.credit_id, 12, v.bcc, 'Blind Carbon Copy email address' as description 
from orig.eca v 
where v.company_id is not null and v.bcc is not null 
       and v.bcc ~ '[[:alnum:]_]'::text 
group by v.credit_id, v.bcc
UNION
    select v.credit_id, 9, v.fax, 'Fax number' as description 
from orig.eca v 
where v.company_id is not null and v.fax is not null 
      and v.fax ~ '[[:alnum:]_]'::text 
group by v.credit_id, v.fax;
-- addresses

INSERT INTO public.country (id, name, short_name) VALUES (-1, 'Invalid Country', 'XX');

-- TODO -- Stuck on this one!
INSERT INTO eca_to_location(credit_id, location_class, location_id)
SELECT eca.id, 1,
    min(location_save(NULL,

    case 
        when ov.address1 = '' then 'Null' 
        when ov.address1 is null then 'Null'
        else ov.address1 
    end,
    ov.address2, 
    NULL,
    case 
        when ov.city !~ '[[:alnum:]_]' then 'Invalid' 
        when ov.city is null then 'Null' 
        else ov.city 
    end,
    case 
        when ov.state !~ '[[:alnum:]_]' then 'Invalid' 
        when ov.state is null then 'Null' 
        else ov.state 
    end,
    case 
        when ov.zipcode !~ '[[:alnum:]_]' then 'Invalid' 
        when ov.zipcode is null then 'Null' 
        else ov.zipcode 
    end,
    coalesce(c.id, -1)
    ))
FROM orig.eca ov left join country c on ov.country = c.name
JOIN entity_credit_account eca ON (ov.credit_id = eca.id)
GROUP BY eca.id;


 -- notes 

INSERT INTO eca_note(note_class, ref_key, note, vector)
SELECT 3, credit_id, notes, ''::tsvector FROM orig.vendor 
WHERE notes IS NOT NULL
union
SELECT 3, credit_id, notes, ''::tsvector FROM orig.customer
WHERE notes IS NOT NULL;

-- if you set  up custom groups, this comes in handy during migration
CREATE OR REPLACE FUNCTION enter_user(in_first text, in_last text, 
in_username text, in_role text, in_empnum text)
RETURNS int AS
$$
BEGIN
	INSERT INTO entity (name, entity_class, country_id)
	VALUES (in_first || ' ' || in_last, 3, 232);

	INSERT INTO person (first_name, last_name, entity_id)
	VALUES (in_first, in_last, currval('entity_id_seq'));
	
	INSERT INTO entity_employee( entity_id, role, employeenumber ) VALUES 
	(currval('entity_id_seq'), in_role, in_empnum);

	IF in_username NOT IN (select rolname FROM pg_roles) THEN
		EXECUTE 'CREATE USER ' || quote_ident(in_username);
	END IF;

	EXECUTE 'GRANT ' || quote_ident(in_role) || ' TO ' ||
		quote_ident(in_username);

	INSERT INTO users (username, entity_id) 
	VALUES (in_username, currval('entity_id_seq'));

	INSERT INTO user_preference (id, dateformat)
	VALUES (currval('users_id_seq'), 'mm/dd/yy');
	
	execute 'alter user ' || quote_ident(in_username)  || ' set search_path='||quote_ident('public');

	RETURN currval('entity_id_seq');
END;
$$ language plpgsql;

alter table orig.employee add entity_id int;

update orig.employee set entity_id = 
	(select entity_id from person where first_name = employee.name 
	UNION 
	select entity_id from users where username = lower(employee.login));


-- skipped next chunk, no batches
-- batches, transactions, and vouchers
insert into batch (id, control_code, description, approved_on, approved_by, 
	created_on, created_by, batch_class_id, default_date)
select b.id, b.batchnumber, b.description, b.apprdate, me.entity_id, transdate, 
	ee.entity_id, bc.id, b.transdate
FROM orig.br b
LEFT JOIN orig.employee me ON (b.managerid = me.id)
LEFT JOIN orig.employee ee ON (ee.id = b.employee_id)
JOIN public.batch_class bc ON (b.batch = bc.class);
-- end skipped section


insert into ap 
(entity_credit_account, person_id,
	id, invnumber, transdate, taxincluded, amount, netamount, paid, 
	datepaid, duedate, invoice, ordnumber, curr, notes, quonumber, intnotes,
	department_id, shipvia, language_code, ponumber, shippingpoint, 
	on_hold, reverse, terms, description)
SELECT 
	vendor.credit_id,
	(select entity_id from orig.employee 
		WHERE id = ap.employee_id),
	ap.id, invnumber, transdate, ap.taxincluded, amount, netamount, paid, 
	datepaid, duedate, invoice, ordnumber, ap.curr, ap.notes, quonumber, 
	intnotes,
	department_id, shipvia, ap.language_code, ponumber, shippingpoint, 
	false, case when amount < 0 then true else false end,
	ap.terms, ''
FROM orig.ap JOIN orig.vendor ON (ap.vendor_id = vendor.id) ;

insert into ar
(entity_credit_account, person_id,
	id, invnumber, transdate, taxincluded, amount, netamount, paid, 
	datepaid, duedate, invoice, ordnumber, curr, notes, quonumber, intnotes,
	department_id, shipvia, language_code, ponumber, shippingpoint, 
	on_hold, reverse, terms, description)
SELECT 
	customer.credit_id,
	1,
	ar.id, invnumber, transdate, ar.taxincluded, amount, netamount, paid, 
	datepaid, duedate, invoice, ordnumber, ar.curr, ar.notes, quonumber, 
	intnotes,
	department_id, shipvia, ar.language_code, ponumber, shippingpoint, 
	false, case when amount < 0 then true else false end,
	ar.terms, ''
FROM orig.ar JOIN orig.customer ON (ar.customer_id = customer.id) ;

INSERT INTO gl
(id, reference, description, transdate, person_id, notes, approved, department_id)
SELECT id, reference, description, transdate, 
	(select max(id) from person where entity_id = (select entity_id from orig.employee
          WHERE id = gl.employee_id)),
	notes, true, department_id FROM orig.gl;

insert into voucher(trans_id, batch_id, id, batch_class)
select min(v.trans_id), v.br_id, v.id, bc.id
from orig.vr v
JOIN orig.br b ON (v.br_id = b.id)
JOIN public.batch_class bc ON (b.batch = bc.class)
JOIN public.transactions t ON (t.id = v.trans_id)
where v.id <> 185355
group by v.br_id, bc.id, v.id;

-- We may need to comment this line out if we ever do this migration again
-- Apparently an AP transaction lost its vendor, which should never happen.

INSERT INTO gl (id, reference, description, transdate)
SELECT id, invnumber, notes || ' (lost ap transaction)', transdate
FROM orig.ap WHERE id = 135077;

-- acc_trans entries/financial line items

INSERT INTO acc_trans
(trans_id, chart_id, amount, transdate, source, cleared, fx_transaction, 
	project_id, memo, approved
	)
SELECT trans_id, a.id , amount, transdate, source,
	CASE WHEN cleared IS NOT NULL THEN TRUE ELSE FALSE END, fx_transaction,
	project_id, memo, true
        FROM orig.acc_trans JOIN orig.chart ON orig.acc_trans.chart_id = orig.chart.id
        JOIN account a ON a.accno = orig.chart.accno WHERE trans_id NOT IN (11036, 12257, 12280);

-- sequences

SELECT setval('id', max(id)) FROM transactions;

INSERT INTO defaults (setting_key, value)
SELECT fldname, fldvalue FROM orig.defaults
WHERE fldname = 'currencies';

References:
- How functional is 1.3?
  - From: Schmiechen
- Re: How functional is 1.3?
  - From: Philip Rhoades
- Re: How functional is 1.3?
  - From: John Locke

Prev by Date: Re: recursivity, or in other words: departments in a department (in a department etc....)
Next by Date: Printing issue in Windows
Previous by thread: Re: How functional is 1.3?
Next by thread: Restoring from backup - help!
Index(es):
- Date
- Thread