Hi Armaghan,
Thanks, for looking at this.
On Tue, 20 Dec 2011, Armaghan Saqib wrote:
(1) Please always run sql/Pg-custom_tables.sql on your database when
you upgrade to ledger123. You can run it multiple times without any
side effect.
I have run this in the past but I did not know I needed to rerun it after
a git pull.
As suggested, I reran Pg-custom_tables.sql. Below is the output:
(bugs pts3) $ psql -U sql-ledger testdb < sql/Pg-custom_tables.sql
ERROR: relation "trf" already exists
ERROR: column "department_id" of relation "inventory" already exists
ERROR: column "delivereddate" of relation "trf" already exists
ERROR: column "transdate" of relation "invoice" already exists
ERROR: relation "fifo" already exists
ERROR: column "lastcost" of relation "invoice" already exists
ERROR: relation "invoicetax" already exists
ERROR: relation "invoice_parts_id" already exists
ERROR: relation "fifo_parts_id" already exists
ERROR: relation "build" already exists
ERROR: column "description" of relation "inventory" already exists
ERROR: column "warehouse_id" of relation "fifo" already exists
ERROR: column "warehouse_id" of relation "invoice" already exists
ERROR: column "invoice_id" of relation "fifo" already exists
ERROR: column "invoice_id" of relation "inventory" already exists
ERROR: relation "inventory_invoice_id" already exists
ERROR: column "cogs" of relation "invoice" already exists
ERROR: column "cogs" of relation "inventory" already exists
ERROR: relation "entry_id" already exists
nextval
---------
28329
(1 row)
ERROR: column "entry_id" of relation "acc_trans" already exists
UPDATE 5973
UPDATE 1719
ERROR: relation "fifo_trans_id" already exists
ERROR: relation "invoice_qty" already exists
ERROR: relation "customerloginid" already exists
nextval
---------
2
(1 row)
ERROR: relation "customercart" already exists
ERROR: relation "customerlogin" already exists
ERROR: relation "partsattr" already exists
(bugs pts3) $
If you still have issues after running this sql file on your database,
please report it here or to me directly.
I reran the tax collected report selecting Nov 2011, month, detail,
and cash. All other check boxes are the default. I am still getting errors.
Below is the output:
DBD::Pg::st execute failed: ERROR: column reference "discount" is ambiguous
Error!
SELECT a.id, '0' AS invoice, a.datepaid AS transdate,
a.invnumber, n.name, n.customernumber,
ac.amount * 1 AS netamount,
ac.memo AS description,
a.till, n.id AS vc_id
FROM acc_trans ac
JOIN ar a ON (a.id = ac.trans_id)
JOIN customer n ON (n.id = a.customer_id)
JOIN chart ch ON (ch.id = ac.chart_id)
WHERE a.approved = '1' AND a.transdate >= '20111101' AND a.transdate <=
'20111130'
AND a.invoice = '0'
AND a.netamount = a.amount
AND NOT (ch.link LIKE '%_paid' OR ch.link = 'AR')
AND ac.trans_id IN
(
SELECT trans_id
FROM acc_trans
JOIN chart ON (chart_id = chart.id)
WHERE link LIKE '%AR_paid%'
AND a.approved = '1'
AND a.datepaid <= '20111130'
AND a.paid = a.amount
)
GROUP BY a.id, a.datepaid, a.invnumber, n.name, ac.amount,
ac.memo, a.till, n.id, n.customernumber
UNION ALL
SELECT a.id, '1' AS invoice, a.datepaid AS transdate,
a.invnumber, n.name, n.customernumber,
sum(ac.sellprice * ac.qty * (1 - ac.discount)) * 1 AS netamount,
ac.description,
a.till, n.id AS vc_id
FROM invoice ac
JOIN ar a ON (a.id = ac.trans_id)
JOIN customer n ON (n.id = a.customer_id)
WHERE a.approved = '1' AND a.transdate >= '20111101' AND a.transdate <=
'20111130'
AND a.invoice = '1'
AND (
a.customer_id NOT IN (
SELECT customer_id FROM customertax t (customer_id)
) OR
ac.parts_id NOT IN (
SELECT parts_id FROM partstax p (parts_id)
)
)
AND ac.trans_id IN
(
SELECT trans_id
FROM acc_trans
JOIN chart ON (chart_id = chart.id)
WHERE link LIKE '%AR_paid%'
AND a.approved = '1'
AND a.datepaid <= '20111130'
AND a.paid = a.amount
)
GROUP BY a.id, a.invnumber, a.datepaid, n.name,
ac.description, a.till, n.id, n.customernumber
UNION
SELECT a.id, '0' AS invoice, a.datepaid AS transdate,
a.invnumber, n.name, n.customernumber, a.netamount,
ac.memo AS description,
a.till, n.id AS vc_id
FROM acc_trans ac
JOIN ar a ON (a.id = ac.trans_id)
JOIN customer n ON (n.id = a.customer_id)
JOIN chart ch ON (ch.id = ac.chart_id)
WHERE a.datepaid >= '20111101'
AND a.invoice = '0'
AND a.netamount = a.amount
AND NOT (ch.link LIKE '%_paid' OR ch.link = 'AR')
AND ac.trans_id IN
(
SELECT trans_id
FROM acc_trans
JOIN chart ON (chart_id = chart.id)
WHERE link LIKE '%AR_paid%'
AND a.approved = '1'
AND a.datepaid <= '20111130'
AND a.paid = a.amount
)
GROUP BY a.id, a.datepaid, a.invnumber, n.name, a.netamount,
ac.memo, a.till, n.id, n.customernumber
UNION
SELECT a.id, '1' AS invoice, a.datepaid AS transdate,
a.invnumber, n.name, n.customernumber,
sum(ac.sellprice * ac.qty * (1 - discount)) * 1 AS netamount,
ac.description,
a.till, n.id AS vc_id
FROM invoice ac
JOIN ar a ON (a.id = ac.trans_id)
JOIN customer n ON (n.id = a.customer_id)
WHERE a.datepaid >= '20111101'
AND a.invoice = '1'
AND (
a.customer_id NOT IN (
SELECT customer_id FROM customertax t (customer_id)
) OR
ac.parts_id NOT IN (
SELECT parts_id FROM partstax p (parts_id)
)
)
AND ac.trans_id IN
(
SELECT trans_id
FROM acc_trans
JOIN chart ON (chart_id = chart.id)
WHERE link LIKE '%AR_paid%'
AND a.approved = '1'
AND a.datepaid <= '20111130'
AND a.paid = a.amount
)
GROUP BY a.id, a.invnumber, a.datepaid, n.name,
ac.description, a.till, n.id, n.customernumber
ORDER by 3 ASC,4,5
ERROR: column reference "discount" is ambiguous
(2) Only master branch is guaranteed to be free of known bugs/issues
(unless you have specific requirements and are on support contract)
This should be enough for most users.
(3) With recent changes which allow editing of invoices created from
orders this branch is no longer needed.
I was not aware of that. That is good enough for my purposes.
FWIW, I also ran ledger doctor on the db and the only thing it showed was the
following:
Missing dates in invoice table
There were '238 rows with blank transdate in invoice table. Being corrected now
...'
... corrected.
I reran the above tax report and I am still getting the same errors.
Regards,
--
Tom [email protected] Spamtrap address
[email protected]
_______________________________________________
SQL-Ledger mailing list
[email protected]
http://lists.ledger123.com/mailman/listinfo/sql-ledger
