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

[..hidden..]

Revision: 5319
          http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=5319&view=rev
Author:   einhverfr
Date:     2012-12-08 03:08:18 +0000 (Sat, 08 Dec 2012)
Log Message:
-----------
More timecards work.  Working on ensuring that time cards can also be material cards, or labor cards, for future addons.

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

Modified: trunk/sql/modules/Timecards.sql
===================================================================
--- trunk/sql/modules/Timecards.sql	2012-12-08 02:04:06 UTC (rev 5318)
+++ trunk/sql/modules/Timecards.sql	2012-12-08 03:08:18 UTC (rev 5319)
@@ -60,8 +60,70 @@
 
 $$;
 
-CREATE OR REPLACE FUNCTION timecard__report(...)
-RETURNS ...
+CREATE OR REPLACE FUNCTION timecard__parts(in_timecard bool, in_service bool)
+RETURNS SETOF parts AS
+$$
+SELECT * 
+  FROM parts
+ WHERE not obsolete
+       AND ($1 OR inventory_accno_id IS NULL)
+       AND ($2 OR (income_accno_id IS NOT NULL 
+             AND inventory_accno_id IS NULL))
+ ORDER BY partnumber;
+$$;
+
+DROP TYPE IF EXISTS timecard_report_line;
+CREATE TYPE timecard_report_line AS (
+   id int,
+   description text,
+   qty numeric,
+   allocated numeric,
+   checkedin time,
+   checkedout time,
+   transdate date,
+   weekday double,
+   workweek text,
+   partnumber text,
+   business_unit_code text,
+   business_unit_description text,
+   employeenumber text, 
+   employee text,
+   parts_id int,
+   sellprice numeric
+); 
+
+CREATE OR REPLACE FUNCTION timecard__report
+(in_business_units int[], in_partnumber text, in_person_id int, 
+in_date_from date, in_date_to date, in_open bool, in_closed bool)
+RETURNS SETOF timecard_report_line
 LANGUAGE SQL AS
-$$ ... 
+$$
+WITH RECURSIVE bu_tree (id, path) AS (
+     SELECT id, id::text AS path, control_code, description
+       FROM business_unit
+      WHERE id = any(in_business_units)
+      UNION
+     SELECT bu.id, bu_tree.path || ',' || bu.id, control_code, description
+       FROM business_unit bu
+       JOIN bu_tree ON bu_tree.id = bu.parent_id
+)
+SELECT j.id, j.description, j.qty, j.allocated, j.checkedin::time as checkedin,
+       j.checkedout::time as checkedout, j.checkedin::date as transdate,
+       extract('dow' from j.checkedin) as weekday, 
+       extract('week' from j.checkedin) as workweek,
+       p.partnumber, bu.control_code as business_unit_code, 
+       bu.description AS businessunit_description,
+       ee.employeenumber, e.name AS employee, j.parts_id, j.sellprice
+  FROM jcitems j
+  JOIN parts p ON p.id = jc.parts_id
+  JOIN person ON person.id = jc.person_id
+  JOIN employee_entity ee ON ee.entity_id = person.entity_id
+  JOIN entity e ON ee.entity_id = e.id
+  JOIN bu_tree bu ON bu.id = j.business_unit_id
+ WHERE (p.partnumber = $2 OR $2 IS NULL)
+       AND (ee.entity_id = $3 OR $3 IS NULL)
+       AND (j.checkedin::date <= $4 OR $4 IS NULL)
+       AND (j.checkedin::date >= $5 OR $5 IS NULL)
+       AND (j.qty > j.allocated AND $6)
+       AND (j.qty <= j.allocated AND $7);
 $$;

Modified: trunk/sql/modules/Util.sql
===================================================================
--- trunk/sql/modules/Util.sql	2012-12-08 02:04:06 UTC (rev 5318)
+++ trunk/sql/modules/Util.sql	2012-12-08 03:08:18 UTC (rev 5319)
@@ -14,6 +14,7 @@
     quarter double precision,
     doy double precision,
     dow double precision,
+    week double precision,
     epoch double precision,
     as_date date,
     as_time time
@@ -34,6 +35,7 @@
        extract('quarter' from $1) as quarter,
        extract('doy' from $1) as doy,
        extract('dow' from $1) as dow,
+       extract('week' from $1) as week,
        extract('epoch' from $1) as epoch,
        $1::date as as_date,
        $1::time as as_time;

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