Adding inventory adjustment stored procedures

git-svn-id: https://ledger-smb.svn.sourceforge.net/svnroot/ledger-smb/trunk@2018 4979c152-3d1c-0410-bac9-87ea11338e46

2 files changed, 73 insertions, 3 deletions

diff --git a/sql/Pg-database.sql b/sql/Pg-database.sql
index efa585c3..bcb4ff8d 100644
--- a/sql/Pg-database.sql
+++ b/sql/Pg-database.sql
@@ -47,8 +47,6 @@ CREATE TABLE entity_class_to_entity (
 
 COMMENT ON TABLE entity_class_to_entity IS $$ Relation builder for classes to entity $$;
 
-
-
 -- USERS stuff --
 CREATE TABLE users (
     id serial UNIQUE, 
@@ -477,8 +475,28 @@ CREATE TABLE entity_credit_account (
     primary_contact int references person(id),
     ar_ap_account_id int references chart(id),
     cash_account_id int references chart(id),
-    PRIMARY KEY(entity_id, meta_number)
+    PRIMARY KEY(entity_id, meta_number, entity_class),
 );
+
+CREATE UNIQUE INDEX entity_credit_ar_accno_idx_u 
+ON entity_credit_account(meta_number)
+WHERE entity_class = 2;
+
+COMMENT ON INDEX entity_credit_ar_accno_idx_u IS
+$$This index is used to ensure that AR accounts are not reused.$$;
+
+-- THe following credit accounts are used for inventory adjustments.
+INSERT INTO entity (name, entity_class) values ('Inventory Entity', 1);
+
+INSERT INTO company (legal_name, entity_id) 
+values ('Inventory Entity', currval('entity_id_seq');
+
+INSERT INTO entity_credit_account (entity_id, meta_number, entity_class)
+VALUES 
+(currval('entity_id_seq'), '00000', 1),
+(currval('entity_id_seq'), '00000', 2);
+
+
 -- notes are from entity_note
 -- ssn, iban and bic are from entity_credit_account
 -- 
diff --git a/sql/modules/Inventory.sql b/sql/modules/Inventory.sql
new file mode 100644
index 00000000..fa4d6d0e
--- /dev/null
+++ b/sql/modules/Inventory.sql
@@ -0,0 +1,52 @@
+CREATE OR REPLACE FUNCTION inventory_get_item_at_day
+(in_transdate date, in_partnumber text)
+RETURNS parts AS
+$$
+DECLARE out_row parts%ROWTYPE;
+BEGIN
+        SELECT p.id, p.partnumber, p.description, p.unit, p.listprice, 
+		p.sellprice, p.lastcost, p.priceupdate, p.weight, 
+                p.onhand - sum(coalesce(i.qty, 0))
+                AS onhand, p.notes, p.makemodel, p.assembly, p.alternate, 
+		p.rop, p.inventory_accno_id, p.income_accno_id, p.expense_accno_id,
+		p.bin, p.obsolete, p.bom, p.image, p.microfiche, p.partsgroup_id, 
+		p.project_id, p.avgcost
+	INTO out_row;
+        FROM parts p
+        LEFT JOIN invoice i ON (i.parts_id = p.id
+                AND i.trans_id IN 
+                        (select id FROM ar WHERE transdate > in_trans_date
+                        UNION 
+                        SELECT id FROM ap WHERE transdate > in_trans_date))
+        WHERE p.partnumber = in_partnumber
+                AND assembly IS FALSE AND obsolete IS NOT TRUE
+        GROUP BY p.id, p.partnumber, p.description, p.unit, p.listprice,
+                p.sellprice, p.lastcost, p.priceupdate, p.weight,
+                p.onhand, p.notes, p.makemodel, p.assembly, p.alternate,
+                p.rop, p.inventory_accno_id, p.income_accno_id, p.expense_accno_id,
+                p.bin, p.obsolete, p.bom, p.image, p.microfiche, p.partsgroup_id,
+                p.project_id, p.avgcost;
+
+	RETURN out_row;
+END;
+$$ LANGUAGE PLPGSQL;
+
+CREATE OR REPLACE FUNCTION inventory_create_report(in_transdate date) RETURNS int
+AS
+$$
+BEGIN
+	INSERT INTO inventory_report(entry_date) values (in_transdate);
+	RETURN currval('inventory_report_id_seq');
+END;
+$$ language plpgsql;
+
+CREATE OR REPLACE FUNCTION inventory_report__add_line
+(in_report_id int, in_parts_id int, in_onhand int, in_counted int)
+RETURNS int AS
+$$
+BEGIN
+	INSERT INTO inventory_report_line(report_id, parts_id, onhand, counted)
+	VALUES (in_report_id, in_parts_id, in_onhand, in_counted);
+
+	RETURN currval('inventory_report_line_id_seq');
+$$ LANGUAGE plpgsql;