CREATE TABLE timereportbreak
(
timereportid int4 NOT NULL DEFAULT 0,
starttime time NOT NULL DEFAULT '00:00:00'::time without time zone,
endtime time NOT NULL DEFAULT '00:00:00'::time without time zone,
indexnumber int4 NOT NULL DEFAULT 0
)
WITH OIDS;
ALTER TABLE timereportbreak OWNER TO postgres;

CREATE TABLE timereport
(
employeesignature varchar(6) NOT NULL DEFAULT ''::character varying,
startdate date,
workedtimetotal time NOT NULL DEFAULT '00:00:00'::time without time zone,
starttime time NOT NULL DEFAULT '00:00:00'::time without time zone,
endtime time NOT NULL DEFAULT '00:00:00'::time without time zone,
statuscode int2 NOT NULL DEFAULT 0,
note text NOT NULL DEFAULT ''::text,
internalinfo varchar(80) NOT NULL DEFAULT ''::character varying,
id int4 NOT NULL DEFAULT nextval('timereport_id_seq'::text),
CONSTRAINT "TimeReport_pkey" PRIMARY KEY (id)
)
WITH OIDS;
ALTER TABLE timereport OWNER TO postgres;

CREATE OR REPLACE FUNCTION timereport_getbydateandemployee("varchar", date,
date, "varchar")
RETURNS SETOF timereportandbreaks AS
$BODY$
SELECT DISTINCT
timereport.employeesignature,
timereport.startdate,
timereport.workedtimetotal,
timereport.starttime,
timereport.endtime,
timereport.statuscode,
timereport.note,
timereport.internalinfo,
timereport.id
,(SELECT timereportbreak.starttime FROM timereportbreak, timereport,
employee
WHERE
(timereport.startdate BETWEEN $2 AND $3)
AND
(upper(timereport.employeesignature) = upper($4))
AND
upper(timereport.employeesignature) = upper(employee.signature)
AND
employee.companyid = $1
AND
timereport.id = timereportbreak.timereportid
AND
timereportbreak.indexnumber = '0'
LIMIT '1'
) AS break1start
,(SELECT timereportbreak.endtime FROM timereportbreak, timereport,
employee
WHERE
(timereport.startdate BETWEEN $2 AND $3)
AND
(upper(timereport.employeesignature) = upper($4))
AND
upper(timereport.employeesignature) = upper(employee.signature)
AND
employee.companyid = $1
AND
timereport.id = timereportbreak.timereportid
AND
timereportbreak.indexnumber = '0'
LIMIT '1'
) AS break1end
,(SELECT timereportbreak.starttime FROM timereportbreak, timereport,
employee
WHERE
(timereport.startdate BETWEEN $2 AND $3)
AND
(upper(timereport.employeesignature) = upper($4))
AND
upper(timereport.employeesignature) = upper(employee.signature)
AND
employee.companyid = $1
AND
timereport.id = timereportbreak.timereportid
AND
timereportbreak.indexnumber = '1'
LIMIT '1'
) AS break2start
,(SELECT timereportbreak.endtime FROM timereportbreak, timereport,
employee
WHERE
(timereport.startdate BETWEEN $2 AND $3)
AND
(upper(timereport.employeesignature) = upper($4))
AND
upper(timereport.employeesignature) = upper(employee.signature)
AND
employee.companyid = $1
AND
timereport.id = timereportbreak.timereportid
AND
timereportbreak.indexnumber = '1'
LIMIT '1'
) AS break2end
,(SELECT timereportbreak.starttime FROM timereportbreak, timereport,
employee
WHERE
(timereport.startdate BETWEEN $2 AND $3)
AND
(upper(timereport.employeesignature) = upper($4))
AND
upper(timereport.employeesignature) = upper(employee.signature)
AND
employee.companyid = $1
AND
timereport.id = timereportbreak.timereportid
AND
timereportbreak.indexnumber = '2'
LIMIT '1'
) AS break3start
,(SELECT timereportbreak.endtime FROM timereportbreak, timereport,
employee
WHERE
(timereport.startdate BETWEEN $2 AND $3)
AND
(upper(timereport.employeesignature) = upper($4))
AND
upper(timereport.employeesignature) = upper(employee.signature)
AND
employee.companyid = $1
AND
timereport.id = timereportbreak.timereportid
AND
timereportbreak.indexnumber = '2'
LIMIT '1'
) AS break3end
,(SELECT timereportbreak.starttime FROM timereportbreak, timereport,
employee
WHERE
(timereport.startdate BETWEEN $2 AND $3)
AND
(upper(timereport.employeesignature) = upper($4))
AND
upper(timereport.employeesignature) = upper(employee.signature)
AND
employee.companyid = $1
AND
timereport.id = timereportbreak.timereportid
AND
timereportbreak.indexnumber = '3'
LIMIT '1'
) AS break4start
,(SELECT timereportbreak.endtime FROM timereportbreak, timereport,
employee
WHERE
(timereport.startdate BETWEEN $2 AND $3)
AND
(upper(timereport.employeesignature) = upper($4))
AND
upper(timereport.employeesignature) = upper(employee.signature)
AND
employee.companyid = $1
AND
timereport.id = timereportbreak.timereportid
AND
timereportbreak.indexnumber = '3'
LIMIT '1'
) AS break4end
FROM
timereport, timereportbreak, employee
WHERE
(timereport.startdate BETWEEN $2 AND $3)
AND
(upper(timereport.employeesignature) = upper($4))
AND
(upper(timereport.employeesignature) = upper(employee.signature))
AND
(employee.companyid = $1)
AND
(timereportbreak.timereportid = timereport.id)
ORDER BY
timereport.startdate,
timereport.starttime
$BODY$
LANGUAGE 'sql' VOLATILE;
ALTER FUNCTION timereport_getbydateandemployee("varchar", date, date,
"varchar") OWNER TO postgres;