SF.net SVN: ledger-smb:[5324] trunk

[..hidden..]

Revision: 5324
          http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=5324&view=rev
Author:   einhverfr
Date:     2012-12-08 15:31:00 +0000 (Sat, 08 Dec 2012)
Log Message:
-----------
Timecard class, and tweaks to timecard sql routines, still have UI to do, plus timecard reporting.

Modified Paths:
--------------
    trunk/Changelog
    trunk/sql/Pg-database.sql
    trunk/sql/modules/Timecards.sql

Modified: trunk/Changelog
===================================================================
--- trunk/Changelog	2012-12-08 03:38:07 UTC (rev 5323)
+++ trunk/Changelog	2012-12-08 15:31:00 UTC (rev 5324)
@@ -12,6 +12,7 @@
 * Removed ability to repost/delete transactions (Chris T)
 * Partsgroups can now be nested though this is not yet used by LSMB (Chris T)
 * Session expiration now with configurable behavior (Chris T)
+* Timecard system generalized to allow labor and materials cards too (Chris T)
 
 Enhancing data integrity
 * No longer allow NULL amounts in acc_trans (Chris T)

Modified: trunk/sql/Pg-database.sql
===================================================================
--- trunk/sql/Pg-database.sql	2012-12-08 03:38:07 UTC (rev 5323)
+++ trunk/sql/Pg-database.sql	2012-12-08 15:31:00 UTC (rev 5324)
@@ -2262,6 +2262,14 @@
 COMMENT ON TABLE recurringprint IS
 $$ Template, printer etc. to print to when recurring transaction posts.$$;
 --
+CREATE TABLE jctype (
+  id int not null unique, -- hand assigned
+  label text primary key,
+  description text not null,
+  is_service bool default true,
+  is_timecard bool default true
+);
+
 CREATE TABLE jcitems (
   id serial PRIMARY KEY,
   business_unit_id int references business_unit(id),
@@ -2277,7 +2285,9 @@
   person_id integer references person(id) not null,
   notes text,
   total numeric not null,
-  non_billable numeric not null default 0
+  non_billable numeric not null default 0,
+  jctype int not null,
+  curr char(3) not null
 );
 
 COMMENT ON TABLE jcitems IS $$ Time and materials cards. 

Modified: trunk/sql/modules/Timecards.sql
===================================================================
--- trunk/sql/modules/Timecards.sql	2012-12-08 03:38:07 UTC (rev 5323)
+++ trunk/sql/modules/Timecards.sql	2012-12-08 15:31:00 UTC (rev 5324)
@@ -19,7 +19,8 @@
   in_person_id integer,
   in_notes text,
   in_total numeric,
-  in_non_billable numeric
+  in_non_billable numeric,
+  in_jctype int
 ) 
 RETURNS jcitems LANGUAGE PLPGSQL AS
 $$
@@ -48,11 +49,11 @@
 INSERT INTO jcitems 
 (business_unit_id, parts_id, description, qty, allocated, sellprice,
   fxsellprice, serialnumber, checkedin, checkedout, person_id, notes,
-  total, non_billable)
+  total, non_billable, jctype)
 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);
+  in_person_id, in_notes, in_total, in_non_billable, in_jctype);
 
 SELECT * INTO retval WHERE id = currval('jcitems_id_seq')::int;
 
@@ -61,7 +62,8 @@
 END;
 $$;
 
-CREATE OR REPLACE FUNCTION timecard__parts(in_timecard bool, in_service bool)
+CREATE OR REPLACE FUNCTION timecard__parts
+(in_timecard bool, in_service bool, in_partnumber text)
 RETURNS SETOF parts LANGUAGE SQL AS
 $$
 SELECT * 
@@ -70,6 +72,7 @@
        AND ($1 OR inventory_accno_id IS NULL)
        AND ($2 OR (income_accno_id IS NOT NULL 
              AND inventory_accno_id IS NULL))
+       AND ($3 IS NULL OF partnumber like $3 || '%')
  ORDER BY partnumber;
 $$;
 
@@ -129,4 +132,29 @@
        AND (j.qty <= j.allocated AND $7);
 $$;
 
+CREATE OR REPLACE FUNCTION timecard__allocate(in_id int, in_amount numeric)
+returns jcitems
+LANGUAGE PLPGSQL AS $$
+
+DECLARE retval jcitems;
+
+BEGIN
+
+UPDATE jcitems SET allocated = allocated + in_amount WHERE id = in_id;
+
+IF NOT FOUND THEN
+   RAISE EXCEPTION 'timecard not found';
+END IF;
+
+SELECT * INTO retval FROM jcitems WHERE id = in_id;
+
+IF allocated > qty THEN
+   RAISE EXCEPTION 'Too many allocated';
+END IF;
+
+RETURN retval;
+
+END;
+$$;
+
 COMMIT;

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