Initial Import

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

1 files changed, 60 insertions, 0 deletions

diff --git a/sql/Pg-upgrade-2.6.1-2.6.2.sql b/sql/Pg-upgrade-2.6.1-2.6.2.sql
new file mode 100755
index 00000000..39136600
--- /dev/null
+++ b/sql/Pg-upgrade-2.6.1-2.6.2.sql
@@ -0,0 +1,60 @@
+--
+drop function avgcost(int);
+CREATE FUNCTION avgcost(int) RETURNS FLOAT AS '
+
+DECLARE
+
+v_cost float;
+v_qty float;
+v_parts_id alias for $1;
+
+BEGIN
+
+  SELECT INTO v_cost, v_qty SUM(i.sellprice * i.qty), SUM(i.qty)
+  FROM invoice i
+  JOIN ap a ON (a.id = i.trans_id)
+  WHERE i.parts_id = v_parts_id;
+
+  IF v_cost IS NULL THEN
+    v_cost := 0;
+  END IF;
+
+  IF NOT v_qty IS NULL THEN
+    IF v_qty = 0 THEN
+      v_cost := 0;
+    ELSE
+      v_cost := v_cost/v_qty;
+    END IF;
+  END IF;
+
+RETURN v_cost;
+END;
+' language 'plpgsql';
+-- end function
+--
+drop function lastcost(int);
+CREATE FUNCTION lastcost(int) RETURNS FLOAT AS '
+ 
+DECLARE 
+  
+v_cost float; 
+v_parts_id alias for $1; 
+   
+BEGIN 
+    
+  SELECT INTO v_cost sellprice FROM invoice i
+  JOIN ap a ON (a.id = i.trans_id) 
+  WHERE i.parts_id = v_parts_id 
+  ORDER BY a.transdate desc, a.id desc 
+  LIMIT 1; 
+ 
+  IF v_cost IS NULL THEN 
+    v_cost := 0; 
+  END IF; 
+
+RETURN v_cost; 
+END; 
+' language 'plpgsql'; 
+-- end function
+--
+update defaults set version = '2.6.2';