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

[..hidden..]

Revision: 5328
          http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=5328&view=rev
Author:   einhverfr
Date:     2012-12-09 09:29:18 +0000 (Sun, 09 Dec 2012)
Log Message:
-----------
beginnings of timecard report module

Modified Paths:
--------------
    trunk/sql/modules/Fixes.sql
    trunk/sql/modules/Roles.sql
    trunk/sql/modules/Timecards.sql

Modified: trunk/sql/modules/Fixes.sql
===================================================================
--- trunk/sql/modules/Fixes.sql	2012-12-09 03:59:41 UTC (rev 5327)
+++ trunk/sql/modules/Fixes.sql	2012-12-09 09:29:18 UTC (rev 5328)
@@ -113,3 +113,24 @@
 ALTER TABLE payrol_deduction_class ADD stored_proc_name name not null;
 
 COMMIT;
+
+BEGIN; -- Timecard types
+
+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
+);
+
+INSERT INTO jctype (id, label, description, is_service, is_timecard)
+VALUES (1, 'time', 'Timecards for project services', true, true);
+
+INSERT INTO jctype (id, label, description, is_service, is_timecard)
+VALUES (2, 'materials', 'Materials for projects', false, false);
+
+INSERT INTO jctype (id, label, description, is_service, is_timecard)
+VALUES (3, 'overhead', 'Time/Overhead for payroll, manufacturing, etc', false, true);
+
+COMMIT;

Modified: trunk/sql/modules/Roles.sql
===================================================================
--- trunk/sql/modules/Roles.sql	2012-12-09 03:59:41 UTC (rev 5327)
+++ trunk/sql/modules/Roles.sql	2012-12-09 09:29:18 UTC (rev 5328)
@@ -1580,6 +1580,7 @@
 GRANT SELECT ON asset_report_class, asset_rl_to_disposal_method,
                 asset_disposal_method TO PUBLIC;
 GRANT SELECT ON mime_type, file_class TO PUBLIC;
+GRANT SELECT ON jctype TO PUBLIC;
 
 GRANT EXECUTE ON FUNCTION user__get_all_users() TO public;
 

Modified: trunk/sql/modules/Timecards.sql
===================================================================
--- trunk/sql/modules/Timecards.sql	2012-12-09 03:59:41 UTC (rev 5327)
+++ trunk/sql/modules/Timecards.sql	2012-12-09 09:29:18 UTC (rev 5328)
@@ -35,7 +35,7 @@
        serialnumber = in_serialnumber,
        checkedin = in_checkedin,
        checkedout = in_checkedout,
-       person_id = person__get_my_entity_id(),
+       person_id = coalesce(in_person_id, person__get_my_entity_id()),
        notes = in_notes,
        total = in_total,
        non_billable = in_non_billable
@@ -53,7 +53,8 @@
 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_jctype);
+  coalesce(in_person_id, person__get_my_entity_id()), in_notes, in_total, 
+  in_non_billable, in_jctype);
 
 SELECT * INTO retval WHERE id = currval('jcitems_id_seq')::int;
 
@@ -87,6 +88,7 @@
    transdate date,
    weekday double precision,
    workweek double precision,
+   weekstarting date,
    partnumber text,
    business_unit_code text,
    business_unit_description text,
@@ -98,7 +100,8 @@
 
 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)
+in_date_from date, in_date_to date, in_open bool, in_closed bool, 
+in_jctype int)
 RETURNS SETOF timecard_report_line
 LANGUAGE SQL AS
 $$
@@ -115,6 +118,7 @@
        j.checkedout::time as checkedout, j.checkedin::date as transdate,
        extract('dow' from j.checkedin) as weekday, 
        extract('week' from j.checkedin) as workweek,
+       date_trunc('week', j.checkedin) as weekstarting,
        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
@@ -129,7 +133,8 @@
        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);
+       AND (j.qty <= j.allocated AND $7)
+       AND (j.jctype = $8 OR $8 is null);
 $$;
 
 CREATE OR REPLACE FUNCTION timecard__allocate(in_id int, in_amount numeric)

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