SET client_encoding = 'SQL_ASCII';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;
SET search_path = public, pg_catalog;
SET default_tablespace = '';
SET default_with_oids = true;
CREATE TABLE currentcall (
    cadc_taskno integer DEFAULT nextval(('currentcall_cadc_taskno_seq'::text)::regclass) NOT NULL,
    coversight character varying(4),
    cprojman character varying(4),
    cbarcode integer,
    clink integer,
    cdatemod date,
    cdatein date DEFAULT date(now()) NOT NULL,
    ctimein smallint DEFAULT ((date_part('hour'::text, now()) * (100)::double precision) + date_part('minute'::text, now())),
    cloggedby character varying(4),
    clogging_id character varying(4) NOT NULL,
    clogging_id2 character varying(5) NOT NULL,
    ccustname character varying(6) NOT NULL,
    cbilling_id character varying(6) NOT NULL,
    csubcont_to character varying(6),
    ccustomer character varying(44),
    caddress character varying(44),
    csuburb character varying(20),
    cfloor character varying(2),
    cequipmod character varying(30) NOT NULL,
    cextras character varying(40),
    cserial character varying(20) NOT NULL,
    cfprior smallint,
    cfrespond_time smallint NOT NULL,
    cjobtype character varying(1) NOT NULL,
    cjob_loc character varying(1) NOT NULL,
    cspecial character varying(1),
    cexinclusions character varying(1),
    cloanunit character varying(1),
    clock character varying(1) NOT NULL,
    cpercent double precision,
    ccallback character varying(1),
    cname2 character varying(22),
    cphone2 character varying(14),
    cfault character varying(120),
    cfcode character varying(2),
    ccustcomment character varying(64),
    cdatealloc date,
    ctimemod smallint,
    ctechalloc character varying(4),
    callocby character varying(4),
    cdateclear date,
    ctimeclear smallint,
    cfollowup character varying(1),
    cfollupdate date,
    cfollowwho character varying(4),
    cstatus character varying(1) NOT NULL,
    ctimeoutst integer DEFAULT 0,
    cresptime numeric(10,5),
    ctaxexempt character varying(1),
    ctaxnumber character varying(12),
    ccustorder character varying(20),
    cequipcode character varying(8),
    csubchrg numeric(16,4),
    cneg_cost numeric(16,4),
    ctimevalue numeric(16,4),
    cpartvalue numeric(16,4),
    ctax_total numeric(16,4),
    cfreight numeric(16,4),
    cmileage numeric(16,4),
    ctotvalue numeric(16,4),
    csubcost numeric(16,4),
    cfixcost numeric(16,4),
    ctimecost numeric(16,4),
    cpartcost numeric(16,4),
    ctaxcost numeric(16,4),
    cmilecost numeric(16,4),
    cfrtcost numeric(16,4),
    ctotcost numeric(16,4),
    cfixcode character varying(4),
    cest_complete smallint,
    cpmdone character varying(1),
    clastinit character varying(3),
    cloanser character varying(15),
    cloanused character varying(1),
    ctot_time numeric(6,1),
    cchrg_time numeric(6,1),
    cext_num character varying(4),
    cequip_type character varying(1),
    cmrate numeric(16,4),
    carate numeric(16,4),
    cbrate numeric(16,4),
    ccrate numeric(16,4),
    cdrate numeric(16,4),
    ctrate numeric(16,4),
    ctmacost numeric(16,4),
    ctmbcost numeric(16,4),
    ctmccost numeric(16,4),
    ctmtcost numeric(16,4),
    cmlcost numeric(16,4),
    cminhrsa double precision,
    cminhrsb double precision,
    cminhrsc double precision,
    cfax_no character varying(30),
    cfax_stat character varying(1),
    cucredit character varying(1),
    cuclass character varying(1) NOT NULL,
    cusubinfo character varying(80),
    csubjobno character varying(15),
    cstate character varying(4),
    cpostcode character varying(4),
    cwarinv character varying(20),
    cwardate date,
    cwarsup character varying(6),
    copfnum integer,
    cestcomplete date,
    cnodeid character varying(64),
    cpttaxin numeric(16,4),
    cpttaxout numeric(16,4),
    cwdtaxin numeric(16,4),
    cwdtaxout numeric(16,4),
    cctmonwday smallint,
    cctmoffwday smallint,
    cctmonhol smallint,
    cctmoffhol smallint,
    cchrgmdesc character varying(64),
    cchrgmval numeric(16,4),
    cchrgmtax numeric(16,4),
    cnegtax numeric(16,4),
    cfixtax numeric(16,4),
    csalesper character varying(4),
    cusubcond character varying(40),
    cexp_time numeric(6,1),
    cmob_num character varying(14),
    cexp_chrg numeric(16,4),
    cdatefinished date,
    cbudget_time numeric(6,1),
    cnewtask character(1) NOT NULL,
    cequiploc character varying(6),
    ccredit numeric(16,4),
    caxaptacode character(4),
    cprojectid integer,
    csource character(1),
    cjobclass character(1),
    caxproject character varying(20),
    caxcategoryid character varying(10),
    timezone character varying(10) DEFAULT 'CST'::character varying,
    updated timestamp(0) with time zone DEFAULT now(),
    dateloggedtz timestamp(0) with time zone DEFAULT now(),
    firstrealworkdone timestamp(0) with time zone,
    clegalentity character varying(5) DEFAULT 'TEC'::character varying,
    cemailnotify text,
    csorm integer,
    csorp integer,
    csendnotifyemail boolean DEFAULT false,
    CONSTRAINT currentcall_fixcode_notnull CHECK ((((cstatus)::text <> ALL ((ARRAY['C'::character varying, 'I'::character varying, 'F'::character varying])::text[])) OR (cfixcode IS NOT NULL))),
    CONSTRAINT currentcall_newtask_check CHECK (((cnewtask IS NULL) OR (cnewtask = ANY (ARRAY['N'::bpchar, 'Y'::bpchar]))))
);
ALTER TABLE ONLY currentcall ALTER COLUMN cstatus SET STATISTICS 300;
ALTER TABLE ONLY currentcall ALTER COLUMN cjobclass SET STATISTICS 300;
ALTER TABLE public.currentcall OWNER TO ian;
COMMENT ON COLUMN currentcall.cfault IS 'This is a REQUIRED field and MUST be accurate and to the point. Fault contains 2 fields which are 60 characters long. Where possible the main thrust of the fault should be entered in the top field. these details are printed on the Customer Invoice,and also used for equipment history.';
COMMENT ON COLUMN currentcall.updated IS 'Last time this task was updated in the database';
COMMENT ON COLUMN currentcall.dateloggedtz IS 'Time this task actually made it into the table';
COMMENT ON COLUMN currentcall.firstrealworkdone IS 'First workdone record that has actual work in it.';
COMMENT ON COLUMN currentcall.cemailnotify IS 'EMail address to notify on events';
COMMENT ON COLUMN currentcall.csorm IS 'Sale Order Maint/Labour';
COMMENT ON COLUMN currentcall.csorp IS 'Sale Order Product';
COMMENT ON COLUMN currentcall.csendnotifyemail IS 'Set this to true during a update to cause the backend to send a notify email to all listed in the cemailnotify field';
ALTER TABLE ONLY currentcall
    ADD CONSTRAINT currentcall_pkey PRIMARY KEY (cadc_taskno);
CREATE INDEX cdatefin_index ON currentcall USING btree (cdatefinished);
CREATE INDEX currentcall_cbarcode ON currentcall USING btree (cbarcode);
CREATE INDEX currentcall_cbilling_id ON currentcall USING btree (upper((cbilling_id)::text));
CREATE INDEX currentcall_ccustname ON currentcall USING btree (upper((ccustname)::text));
CREATE INDEX currentcall_ccustomer ON currentcall USING btree (upper((ccustomer)::text));
CREATE INDEX currentcall_cdateclear ON currentcall USING btree (cdateclear);
CREATE INDEX currentcall_cfollupdate ON currentcall USING btree (cfollupdate);
CREATE INDEX currentcall_classstatus ON currentcall USING btree (cjobclass, cstatus) WHERE ((((cstatus)::text <> 'C'::text) AND ((cstatus)::text <> 'I'::text)) AND ((cstatus)::text <> 'F'::text));
CREATE INDEX currentcall_clink ON currentcall USING btree (clink);
CREATE INDEX currentcall_clogging_id ON currentcall USING btree (upper((clogging_id)::text));
CREATE UNIQUE INDEX currentcall_closed ON currentcall USING btree (cadc_taskno) WHERE (((cstatus)::text = 'C'::text) OR ((cstatus)::text = 'I'::text));
CREATE INDEX currentcall_cnodeid ON currentcall USING btree (upper((cnodeid)::text));
CREATE INDEX currentcall_copfnum ON currentcall USING btree (copfnum);
CREATE INDEX currentcall_csalesper ON currentcall USING btree (upper((csalesper)::text));
CREATE INDEX currentcall_cstatus ON currentcall USING btree (upper((cstatus)::text)) WHERE (((((((((((upper((cstatus)::text) = 'A'::text) OR (upper((cstatus)::text) = 'W'::text)) OR (upper((cstatus)::text) = 'X'::text)) OR (upper((cstatus)::text) = 'T'::text)) OR (upper((cstatus)::text) = 'Q'::text)) OR (upper((cstatus)::text) = 'F'::text)) OR (upper((cstatus)::text) = 'H'::text)) OR (upper((cstatus)::text) = 'O'::text)) OR (upper((cstatus)::text) = 'P'::text)) OR (upper((cstatus)::text) = 'E'::text)) OR (upper((cstatus)::text) = 'S'::text));
CREATE INDEX currentcall_csubjobno ON currentcall USING btree (upper((csubjobno)::text));
CREATE INDEX currentcall_ctechalloc ON currentcall USING btree (upper((ctechalloc)::text));
CREATE INDEX currentcall_datein ON currentcall USING btree (cdatein, ctimein);
CREATE INDEX currentcall_datein_class_tech ON currentcall USING btree (cdatein, cjobclass, cloggedby);
CREATE UNIQUE INDEX currentcall_oid ON currentcall USING btree (oid);
CREATE INDEX currentcall_project ON currentcall USING btree (cprojectid);
CREATE INDEX currentcall_status ON currentcall USING btree (cstatus) WHERE (((cstatus)::text <> 'C'::text) AND ((cstatus)::text <> 'I'::text));
CREATE INDEX currentcall_status_alloc ON currentcall USING btree (upper((ctechalloc)::text)) WHERE ((((cstatus)::text <> 'C'::text) AND ((cstatus)::text <> 'I'::text)) AND ((cstatus)::text <> 'F'::text));
CREATE INDEX currentcall_updated ON currentcall USING btree (updated) WHERE (updated IS NOT NULL);
CREATE RULE currentcall_change AS ON UPDATE TO currentcall WHERE ((((((((new.cstatus)::text <> (old.cstatus)::text) OR ((new.ctechalloc)::text <> (old.ctechalloc)::text)) OR ((new.ccustcomment)::text <> (old.ccustcomment)::text)) OR (old.cfprior <> new.cfprior)) OR (old.cestcomplete <> new.cestcomplete)) OR (old.cjobclass <> new.cjobclass)) OR (old.cnewtask <> new.cnewtask)) DO INSERT INTO taskchangelog (tasknum, oldstatus, newstatus, oldtech, newtech, status, oldpriority, newpriority, oldestcomplete, newestcomplete, oldclass, newclass, newnewtask, oldnewtask) VALUES (new.cadc_taskno, old.cstatus, new.cstatus, old.ctechalloc, new.ctechalloc, new.ccustcomment, old.cfprior, new.cfprior, old.cestcomplete, new.cestcomplete, old.cjobclass, new.cjobclass, (new.cnewtask = 'Y'::bpchar), (old.cnewtask = 'Y'::bpchar));
CREATE RULE currentcall_insert AS ON INSERT TO currentcall DO INSERT INTO taskchangelog (tasknum, newstatus, newtech, status, newpriority, newestcomplete, newclass, newnewtask) VALUES (new.cadc_taskno, new.cstatus, new.ctechalloc, new.ccustcomment, new.cfprior, new.cestcomplete, new.cjobclass, (new.cnewtask = 'Y'::bpchar));
CREATE TRIGGER currentcall_priorityshuffle
    AFTER INSERT OR UPDATE ON currentcall
    FOR EACH ROW
    EXECUTE PROCEDURE currentcall_priorityshuffle();
CREATE TRIGGER currentcall_refresh
    AFTER UPDATE ON currentcall
    FOR EACH ROW
    EXECUTE PROCEDURE currentcall_refresh();
CREATE TRIGGER currentcall_updated
    BEFORE INSERT OR UPDATE ON currentcall
    FOR EACH ROW
    EXECUTE PROCEDURE set_updated();
ALTER TABLE ONLY currentcall
    ADD CONSTRAINT currentcall_project FOREIGN KEY (cprojectid) REFERENCES projects(p_serial) MATCH FULL;
REVOKE ALL ON TABLE currentcall FROM PUBLIC;
REVOKE ALL ON TABLE currentcall FROM ian;
GRANT ALL ON TABLE currentcall TO ian;
GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE currentcall TO sales;
GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE currentcall TO maint;
GRANT SELECT ON TABLE currentcall TO report;
GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE currentcall TO admin;

CREATE OR REPLACE FUNCTION "public"."set_updated" () RETURNS trigger AS
$body$
DECLARE
	email text = NULL;
    emailsubject text = 'ADC#';
    sendto text = '';
    emp record;
    sendemail bool = false;
BEGIN
	NEW.updated:=current_timestamp;
    
	IF NEW.cestcomplete - now() > '1year'::interval THEN
    	RAISE EXCEPTION 'stupid est complete try again...';
    END IF;
    
    IF TG_OP = 'UPDATE' THEN
    	IF OLD.cstatus not in ('C','I') THEN
    	  	IF NEW.ctechalloc <> OLD.ctechalloc THEN
    			IF NEW.cstatus = 'H' THEN NEW.cstatus = 'A'; END IF;
                SELECT INTO sendto emp_logname FROM employees WHERE emp_function = 'MC' 
                AND emp_init != 'MC';
			    IF FOUND THEN
    				sendto = sendto || '@apdata.com.au';
                ELSE
                	sendto = '';
    			END IF;
                IF initalstologin(NEW.ctechalloc) != current_user THEN
                	sendto = sendto || ' ' || initalstologin(NEW.ctechalloc) || '@apdata.com.au';
       				NEW.cnewtask = 'Y';
                END IF;
                IF initalstologin(OLD.ctechalloc) != current_user THEN
                	sendto = sendto || ' ' || initalstologin(OLD.ctechalloc) || '@apdata.com.au';
                END IF;
               	sendemail = true;
      		END IF;
      		IF NEW.cstatus = 'F' THEN NEW.cexp_time = NEW.ctot_time; END IF;
    	END IF;
    ELSEIF NEW.cfprior IS NOT NULL THEN
    	sendemail = true;
        sendto = 'mc@apdata.com.au';
        IF NEW.ctechalloc != 'MC' THEN
        	sendto = sendto || ' ' || initalstologin(NEW.ctechalloc) || '@apdata.com.au';
        END IF;
    END IF;
    
    IF NEW.csendnotifyemail THEN
    	IF NEW.cemailnotify IS NOT NULL THEN -- Only add if there is something to add ;)
    		sendto = sendto || ' ' || NEW.cemailnotify;
        END IF;
    	NEW.csendnotifyemail = false;
        sendemail = true;
    END IF;
    
    IF sendemail THEN
        SELECT INTO emp * FROM employees WHERE emp_logname = current_user;
        
        emailsubject = emailsubject || NEW.cadc_taskno || ' ' || NEW.ccustomer || ' - ' || NEW.cfault;
        
        email = 'Hello,<br>\n';
		email = email || 'Your email has been received and a new task has been logged<br>\n';
		email = email || 'Here are the task details:<br><br>\n';
        
        email = email || '<table>\n';
        email = email || '<tr><th>Task</th><td>' || NEW.cadc_taskno ||
         '</td><th>Target Date</th><td>' || NEW.cestcomplete || '</td><th>Status</th><td>' || NEW.
         cstatus || '</td></tr>\n';
         IF TG_OP = 'UPDATE' THEN
         	IF NEW.ctechalloc <> OLD.ctechalloc AND OLD.cfprior IS NOT NULL THEN
        		email = email || '<tr><th></th><td colspan="5">Task reallocated from ' ||
         initalstodisplayname(OLD.ctechalloc) || ' to ' || initalstodisplayname(NEW.ctechalloc) ||
          ' by ' || emp.emp_rname || '</td></tr>\n';
          	ELSE
            	email = email || '<tr><th></th><td colspan="5">Task allocated to ' || initalstodisplayname(NEW.ctechalloc) ||
          ' by ' || emp.emp_rname || '</td></tr>\n';
          	END IF;
          ELSE
          	email = email || '<tr><th></th><td colspan="5">Task allocated to ' || initalstodisplayname(NEW.ctechalloc) ||
          ' by ' || emp.emp_rname || '</td></tr>\n';
          END IF;
        email = email || '<tr><th>Agreement</th><td>' || NEW.clogging_id || ' ' || NEW.clogging_id2
         || '</td><th>Node</th><td colspan="3">' || COALESCE(NEW.cnodeid,'') || '</td></tr>\n';
        email = email || '<tr><th>Customer</th><td colspan="5">' || NEW.ccustomer || '</td></tr>\n';
           email = email || '<tr><th>Fault</th><td colspan="5">' || NEW.cfault || '</td></tr>\n';
           email = email || '<tr><th>Contact</th><td>' || NEW.cname2 || '</td><th>Phone</th><td>' ||
            COALESCE(NEW.cphone2,'') || '</td><th>Mobile</th><td>' || COALESCE(NEW.cmob_num,'') ||
             '</td></tr>\n';
        email = email || '<tr><th>Project</th><td>' || COALESCE(NEW.cprojectid::TEXT,'') ||
         '</td><th>AXProject</th><td colspan="3">' || COALESCE(NEW.caxproject) || '</tr>\n';
        email = email || '<tr><th>Status</th><td colspan="5">' || NEW.ccustcomment || '</td></tr>\n';
        email = email || '</table>\n<br>\n<br>';
        
        email = email || 'Please include ADC#' || NEW.cadc_taskno || ' in the subject field when requesting or replying to emails specific to this task<br><br>\n';

		email = email || 'Regards<br>\n';
        
        email = email || emp.emp_rname || '<br>\n';
        email = email || emp.emp_title;
        
        sendto = sendto || ' davidv@apdata.com.au';
        
        INSERT INTO notifyemails ( "to", subject, message )
        VALUES ( sendto, emailsubject, email );
    END IF;
    
  	NOTIFY tasksupdated;
  	RETURN NEW;
END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;



CREATE OR REPLACE FUNCTION "public"."currentcall_refresh" () RETURNS trigger AS
$body$
DECLARE
BEGIN
	IF NEW.cjobtype <> OLD.cjobtype THEN 
    	PERFORM fixtaskrates ( NEW.cadc_taskno ); 
    	UPDATE workdone SET wtype = wtype WHERE wadctask_no = NEW.cadc_taskno;
    END IF;
	RETURN NEW;
END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;




CREATE OR REPLACE FUNCTION "public"."currentcall_priorityshuffle" () RETURNS trigger AS
$body$
DECLARE
BEGIN
  IF TG_OP = 'INSERT' THEN
  	LOCK TABLE currentcall IN SHARE UPDATE EXCLUSIVE MODE;
  	UPDATE currentcall SET cfprior=cfprior+1 WHERE ctechalloc = NEW.ctechalloc AND 
    	cstatus NOT IN ('C','I','F') AND cadc_taskno != NEW.cadc_taskno AND cfprior = NEW.cfprior;
  ELSIF NEW.cfprior != OLD.cfprior or NEW.ctechalloc != OLD.ctechalloc THEN
  	LOCK TABLE currentcall IN SHARE UPDATE EXCLUSIVE MODE;
  	UPDATE currentcall SET cfprior=cfprior+1 WHERE ctechalloc = NEW.ctechalloc AND 
    	cstatus NOT IN ('C','I','F') AND cadc_taskno != NEW.cadc_taskno AND cfprior = NEW.cfprior;
  END IF;
  RETURN NULL;
END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY DEFINER
COST 100;