SF.net SVN: ledger-smb:[5317] trunk/sql/modules

[..hidden..]

Revision: 5317
          http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=5317&view=rev
Author:   einhverfr
Date:     2012-12-07 08:40:18 +0000 (Fri, 07 Dec 2012)
Log Message:
-----------
beginning on timecard rewrite.

Modified Paths:
--------------
    trunk/sql/modules/Util.sql

Added Paths:
-----------
    trunk/sql/modules/Timecards.sql

Added: trunk/sql/modules/Timecards.sql
===================================================================
--- trunk/sql/modules/Timecards.sql	                        (rev 0)
+++ trunk/sql/modules/Timecards.sql	2012-12-07 08:40:18 UTC (rev 5317)
@@ -0,0 +1,67 @@
+BEGIN;
+
+CREATE OR REPLACE FUNCTION timecard__get(id int) RETURNS jcitems
+LANGUAGE SQL AS
+$$ SELECT * FROM jcitems WHERE id = $1; $$;
+
+CREATE OR REPLACE FUNCTION timecard__save
+(in_id int,
+  in_business_unit_id int,
+  in_parts_id int,
+  in_description text,
+  in_qty numeric,
+  in_allocated numeric,
+  in_sellprice NUMERIC,
+  in_fxsellprice NUMERIC,
+  in_serialnumber text,
+  in_checkedin timestamp with time zone,
+  in_checkedout timestamp with time zone,
+  in_person_id integer,
+  in_notes text,
+  in_total numeric,
+  in_non_billable numeric,
+) 
+RETURNS jcitems LANGUAGE PLPGSQL AS
+$$
+DECLARE retval jcitems;
+
+BEGIN;
+
+UPDATE jcitems 
+   SET description = in_description,
+       qty = in_qty,
+       allocated = in_allocated,
+       serialnumber = in_serialnumber,
+       checkedin = in_checkedin,
+       checkedout = in_checkedout,
+       person_id = person__get_my_entity_id(),
+       notes = in_notes,
+       total = in_total,
+       non_billable = in_non_billable
+ WHERE id = in_id;
+
+IF FOUND THEN
+  SELECT * INTO retval WHERE id = in_id;
+  return retval;
+END IF;
+
+INSERT INTO jcitems 
+(business_unit_id, parts_id, description, qty, allocated, sellprice,
+  fxsellprice, serialnumber, checkedin, checkedout, person_id, notes,
+  total, non_billable)
+VALUES
+(in_business_unit_id, in_parts_id, in_description, in_qty, in_allocated, 
+  in_sellprice, in_fxsellprice, in_serialnumber, in_checkedin, in_checkedout, 
+  in_person_id, in_notes, in_total, in_non_billable);
+
+SELECT * INTO retval WHERE id = currval('jcitems_id_seq')::int;
+
+RETURN retval;
+
+$$;
+
+CREATE OR REPLACE FUNCTION timecard__report(...)
+RETURNS ...
+LANGUAGE SQL AS
+$$ ... 
+$$;

Modified: trunk/sql/modules/Util.sql
===================================================================
--- trunk/sql/modules/Util.sql	2012-12-07 06:51:23 UTC (rev 5316)
+++ trunk/sql/modules/Util.sql	2012-12-07 08:40:18 UTC (rev 5317)
@@ -1,5 +1,44 @@
 BEGIN;
 
+DROP TYPE IF EXISTS lsmb_date_fields;
+
+CREATE TYPE lsmb_date_fields AS (
+    century double precision,
+    decade double precision,
+    year double precision,
+    month double precision,
+    day double precision, 
+    hour double precision,
+    minute double precision,
+    second double precision,
+    quarter double precision,
+    doy double precision,
+    dow double precision,
+    epoch double precision,
+    as_date date,
+    as_time time
+);
+
+CREATE OR REPLACE FUNCTION lsmb__decompose_timestamp 
+(in_timestamp timestamptz)
+RETURNS lsmb_date_fields language sql AS
+$$
+SELECT extract('century' from $1) as century, 
+       extract('decade' from $1) as decade,
+       extract('year' from $1) as year,
+       extract('month' from $1) as month,
+       extract('day' from $1) as day,
+       extract('hour' from $1) as hour,
+       extract('minute' from $1) as minute,
+       extract('second' from $1) as second,
+       extract('quarter' from $1) as quarter,
+       extract('doy' from $1) as doy,
+       extract('dow' from $1) as dow,
+       extract('epoch' from $1) as epoch,
+       $1::date as as_date,
+       $1::time as as_time;
+$$;
+
 CREATE OR REPLACE FUNCTION parse_date(in_date date) returns date AS
 $$ select $1; $$ language sql;
 

This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.