1547 lines
62 KiB
PL/PgSQL
1547 lines
62 KiB
PL/PgSQL
CREATE FUNCTION public.assign_ibh_number() RETURNS trigger
|
|
LANGUAGE plpgsql
|
|
AS $$
|
|
begin
|
|
IF NEW.isinhouse = true and (new.invoice_number is null or new.invoice_number = 'ih') THEN
|
|
UPDATE counters
|
|
SET count = count + 1
|
|
where shopid = (select shopid from jobs where jobs.id = new.jobid)
|
|
AND countertype = 'ihbnum'
|
|
RETURNING concat(prefix,count) into new.invoice_number;
|
|
END IF;
|
|
RETURN NEW;
|
|
END;
|
|
$$;
|
|
CREATE FUNCTION public.assign_payment_number() RETURNS trigger
|
|
LANGUAGE plpgsql
|
|
AS $$
|
|
BEGIN
|
|
IF (new.paymentnum IS NULL
|
|
OR new.paymentnum = 'pnew') THEN
|
|
UPDATE
|
|
counters
|
|
SET
|
|
count = count + 1
|
|
WHERE
|
|
shopid = (
|
|
SELECT
|
|
shopid
|
|
FROM
|
|
jobs
|
|
WHERE
|
|
jobs.id = new.jobid)
|
|
AND countertype = 'paymentnum'
|
|
RETURNING
|
|
concat(prefix, count) INTO new.paymentnum;
|
|
END IF;
|
|
RETURN NEW;
|
|
END;
|
|
$$;
|
|
CREATE FUNCTION public.assign_ro_number() RETURNS trigger
|
|
LANGUAGE plpgsql
|
|
AS $$
|
|
begin
|
|
IF NEW.converted = true and (new.ro_number is null or new.ro_number = '') THEN
|
|
UPDATE counters
|
|
SET count = count + 1 where shopid=new.shopid AND countertype = 'ronum'
|
|
RETURNING concat(prefix,count) into new.ro_number;
|
|
END IF;
|
|
RETURN NEW;
|
|
END;
|
|
$$;
|
|
CREATE FUNCTION public.audit_trigger() RETURNS trigger
|
|
LANGUAGE plpgsql SECURITY DEFINER
|
|
AS $$
|
|
DECLARE
|
|
shopid uuid ;
|
|
email text;
|
|
BEGIN
|
|
select b.id, u.email INTO shopid, email from users u join associations a on u.email = a.useremail join bodyshops b on b.id = a.shopid where u.authid = current_setting('hasura.user', 't')::jsonb->>'x-hasura-user-id' and a.active = true;
|
|
IF TG_OP = 'INSERT'
|
|
THEN
|
|
INSERT INTO public.audit_trail (tabname, schemaname, operation, new_val, recordid, bodyshopid, useremail)
|
|
VALUES (TG_RELNAME, TG_TABLE_SCHEMA, TG_OP, row_to_json(NEW), NEW.id, shopid, email);
|
|
RETURN NEW;
|
|
ELSIF TG_OP = 'UPDATE'
|
|
THEN
|
|
INSERT INTO public.audit_trail (tabname, schemaname, operation, old_val, new_val, recordid, bodyshopid, useremail)
|
|
VALUES (TG_RELNAME, TG_TABLE_SCHEMA, TG_OP,
|
|
json_diff(to_jsonb(OLD), to_jsonb(NEW)) , json_diff(to_jsonb(NEW), to_jsonb(OLD)), OLD.id, shopid, email);
|
|
RETURN NEW;
|
|
ELSIF TG_OP = 'DELETE'
|
|
THEN
|
|
INSERT INTO public.audit_trail (tabname, schemaname, operation, old_val, recordid, bodyshopid, useremail)
|
|
VALUES (TG_RELNAME, TG_TABLE_SCHEMA, TG_OP, row_to_json(OLD), OLD.ID, shopid, email);
|
|
RETURN OLD;
|
|
END IF;
|
|
END;
|
|
$$;
|
|
CREATE FUNCTION public.json_diff(l jsonb, r jsonb) RETURNS jsonb
|
|
LANGUAGE sql
|
|
AS $$
|
|
SELECT jsonb_object_agg(a.key, a.value) FROM
|
|
( SELECT key, value FROM jsonb_each(l) ) a LEFT OUTER JOIN
|
|
( SELECT key, value FROM jsonb_each(r) ) b ON a.key = b.key
|
|
WHERE a.value != b.value OR b.key IS NULL;
|
|
$$;
|
|
CREATE TABLE public.bills (
|
|
id uuid DEFAULT public.gen_random_uuid() NOT NULL,
|
|
created_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
updated_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
vendorid uuid NOT NULL,
|
|
jobid uuid NOT NULL,
|
|
date date DEFAULT now() NOT NULL,
|
|
due_date date,
|
|
exported boolean DEFAULT false NOT NULL,
|
|
exported_at timestamp with time zone,
|
|
is_credit_memo boolean DEFAULT false NOT NULL,
|
|
total numeric DEFAULT 0 NOT NULL,
|
|
invoice_number text NOT NULL,
|
|
federal_tax_rate numeric DEFAULT 0 NOT NULL,
|
|
state_tax_rate numeric DEFAULT 0 NOT NULL,
|
|
local_tax_rate numeric DEFAULT 0 NOT NULL,
|
|
isinhouse boolean DEFAULT false NOT NULL
|
|
);
|
|
CREATE FUNCTION public.search_bills(search text) RETURNS SETOF public.bills
|
|
LANGUAGE plpgsql STABLE
|
|
AS $$
|
|
BEGIN
|
|
if search = '' then
|
|
return query select * from bills ;
|
|
else
|
|
return query SELECT
|
|
bills.*
|
|
FROM
|
|
bills, vendors
|
|
WHERE
|
|
bills.vendorid= vendors.id and
|
|
(search <% (invoice_number)
|
|
OR
|
|
search <% (name)
|
|
)
|
|
;
|
|
end if;
|
|
END
|
|
$$;
|
|
CREATE TABLE public.cccontracts (
|
|
id uuid DEFAULT public.gen_random_uuid() NOT NULL,
|
|
created_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
updated_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
agreementnumber integer NOT NULL,
|
|
courtesycarid uuid NOT NULL,
|
|
jobid uuid NOT NULL,
|
|
status text DEFAULT 'Reserved'::text NOT NULL,
|
|
start timestamp with time zone,
|
|
scheduledreturn timestamp with time zone,
|
|
actualreturn timestamp with time zone,
|
|
kmstart numeric NOT NULL,
|
|
kmend numeric,
|
|
driver_dlnumber text,
|
|
driver_dlexpiry date,
|
|
driver_dlst text,
|
|
driver_fn text NOT NULL,
|
|
driver_ln text NOT NULL,
|
|
driver_addr1 text,
|
|
driver_addr2 text,
|
|
driver_city text,
|
|
driver_state text,
|
|
driver_zip text,
|
|
driver_ph1 text,
|
|
driver_dob date,
|
|
contract_date date DEFAULT now() NOT NULL,
|
|
dailyrate numeric,
|
|
actax numeric,
|
|
dailyfreekm integer,
|
|
refuelcharge numeric,
|
|
excesskmrate numeric,
|
|
cleanupcharge numeric,
|
|
damagewaiver numeric,
|
|
federaltax numeric,
|
|
statetax numeric,
|
|
localtax numeric,
|
|
coverage numeric,
|
|
fuelout numeric DEFAULT 100 NOT NULL,
|
|
fuelin numeric DEFAULT 0 NOT NULL,
|
|
damage text
|
|
);
|
|
CREATE FUNCTION public.search_cccontracts(search text) RETURNS SETOF public.cccontracts
|
|
LANGUAGE plpgsql STABLE
|
|
AS $$
|
|
BEGIN
|
|
IF search = '' THEN
|
|
RETURN query
|
|
SELECT
|
|
*
|
|
FROM
|
|
cccontracts c;
|
|
ELSE
|
|
RETURN query
|
|
SELECT
|
|
contracts.*
|
|
FROM
|
|
courtesycars cars,
|
|
jobs jobs,
|
|
cccontracts contracts
|
|
WHERE (jobs.ro_number ILIKE '%' || search || '%'
|
|
OR jobs.ownr_fn ILIKE '%' || search || '%'
|
|
OR jobs.ownr_ln ILIKE '%' || search || '%'
|
|
OR jobs.ownr_co_nm ILIKE '%' || search || '%'
|
|
OR (cast(contracts.agreementnumber as text)) ILIKE '%' || search || '%'
|
|
OR contracts.driver_fn ILIKE '%' || search || '%'
|
|
OR contracts.driver_ln ILIKE '%' || search || '%'
|
|
OR cars.fleetnumber ILIKE '%' || search || '%'
|
|
OR cars.make ILIKE '%' || search || '%'
|
|
OR cars.model ILIKE '%' || search || '%')
|
|
AND contracts.jobid = jobs.id
|
|
AND contracts.courtesycarid = cars.id;
|
|
END IF;
|
|
END
|
|
$$;
|
|
CREATE TABLE public.exportlog (
|
|
id uuid DEFAULT public.gen_random_uuid() NOT NULL,
|
|
created_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
updated_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
jobid uuid,
|
|
billid uuid,
|
|
paymentid uuid,
|
|
successful boolean DEFAULT false NOT NULL,
|
|
message text,
|
|
bodyshopid uuid NOT NULL,
|
|
useremail text NOT NULL
|
|
);
|
|
CREATE FUNCTION public.search_exportlog(search text) RETURNS SETOF public.exportlog
|
|
LANGUAGE plpgsql STABLE
|
|
AS $$ BEGIN IF search = '' THEN RETURN query
|
|
SELECT
|
|
*
|
|
FROM
|
|
exportlog e;
|
|
ELSE RETURN query
|
|
SELECT
|
|
e.*
|
|
FROM
|
|
exportlog e
|
|
LEFT JOIN jobs j on j.id = e.jobid
|
|
LEFT JOIN payments p
|
|
ON p.id = e.paymentid
|
|
LEFT JOIN bills b
|
|
ON e.billid = b.id
|
|
WHERE
|
|
(
|
|
j.ro_number ILIKE '%' || search || '%'
|
|
OR b.invoice_number ILIKE '%' || search || '%'
|
|
OR p.paymentnum ILIKE '%' || search || '%'
|
|
OR e.useremail ILIKE '%' || search || '%'
|
|
)
|
|
AND (e.jobid = j.id
|
|
or e.paymentid = p.id
|
|
or e.billid = b.id)
|
|
;
|
|
END IF;
|
|
END $$;
|
|
CREATE TABLE public.jobs (
|
|
id uuid DEFAULT public.gen_random_uuid() NOT NULL,
|
|
created_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
updated_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
shopid uuid NOT NULL,
|
|
ro_number text,
|
|
ownerid uuid,
|
|
vehicleid uuid,
|
|
labor_rate_id text,
|
|
labor_rate_desc text,
|
|
rate_lab numeric,
|
|
rate_lad numeric,
|
|
rate_lae numeric,
|
|
rate_lar numeric,
|
|
rate_las numeric,
|
|
rate_laf numeric,
|
|
rate_lam numeric,
|
|
rate_lag numeric,
|
|
rate_lau numeric,
|
|
rate_la1 numeric,
|
|
rate_la2 numeric,
|
|
rate_la3 numeric,
|
|
rate_la4 numeric,
|
|
rate_mapa numeric,
|
|
rate_mash numeric,
|
|
rate_mahw numeric,
|
|
rate_ma2s numeric,
|
|
rate_ma3s numeric,
|
|
rate_ma2t numeric,
|
|
rate_mabl numeric,
|
|
rate_macs numeric,
|
|
rate_matd numeric,
|
|
federal_tax_rate numeric,
|
|
state_tax_rate numeric,
|
|
local_tax_rate numeric,
|
|
est_co_nm text,
|
|
est_addr1 text,
|
|
est_addr2 text,
|
|
est_city text,
|
|
est_st text,
|
|
est_zip text,
|
|
est_ctry text,
|
|
est_ph1 text,
|
|
est_ea text,
|
|
est_ct_ln text,
|
|
est_ct_fn text,
|
|
scheduled_in timestamp with time zone,
|
|
actual_in timestamp with time zone,
|
|
scheduled_completion timestamp with time zone,
|
|
actual_completion timestamp with time zone,
|
|
scheduled_delivery timestamp with time zone,
|
|
actual_delivery timestamp with time zone,
|
|
regie_number text,
|
|
invoice_date date,
|
|
inproduction boolean DEFAULT false NOT NULL,
|
|
ins_co_id text,
|
|
ins_co_nm text,
|
|
ins_addr1 text,
|
|
ins_addr2 text,
|
|
ins_city text,
|
|
ins_st text,
|
|
ins_zip text,
|
|
ins_ctry text,
|
|
ins_ph1 text,
|
|
ins_ph1x text,
|
|
ins_ph2 text,
|
|
ins_ph2x text,
|
|
ins_fax text,
|
|
ins_faxx text,
|
|
ins_ct_ln text,
|
|
ins_ct_fn text,
|
|
ins_title text,
|
|
ins_ct_ph text,
|
|
ins_ct_phx text,
|
|
ins_ea text,
|
|
ins_memo text,
|
|
policy_no text,
|
|
ded_amt numeric,
|
|
ded_status text,
|
|
asgn_no text,
|
|
asgn_date date,
|
|
asgn_type text,
|
|
clm_no text,
|
|
clm_ofc_id text,
|
|
date_estimated date,
|
|
date_open timestamp with time zone,
|
|
date_scheduled timestamp with time zone,
|
|
date_invoiced timestamp with time zone,
|
|
date_exported timestamp with time zone,
|
|
clm_total numeric DEFAULT 0,
|
|
owner_owing numeric,
|
|
converted boolean DEFAULT false NOT NULL,
|
|
ciecaid text,
|
|
loss_date date,
|
|
clm_ofc_nm text,
|
|
clm_addr1 text,
|
|
clm_addr2 text,
|
|
clm_city text,
|
|
clm_st text,
|
|
clm_zip text,
|
|
clm_ctry text,
|
|
clm_ph1 text,
|
|
clm_ph1x text,
|
|
clm_ph2 text,
|
|
clm_ph2x text,
|
|
clm_fax text,
|
|
clm_faxx text,
|
|
clm_ct_ln text,
|
|
clm_ct_fn text,
|
|
clm_title text,
|
|
clm_ct_ph text,
|
|
clm_ct_phx text,
|
|
clm_ea text,
|
|
payee_nms text,
|
|
pay_type text,
|
|
pay_date date,
|
|
pay_chknm text,
|
|
pay_amt numeric,
|
|
agt_co_id text,
|
|
agt_co_nm text,
|
|
agt_addr1 text,
|
|
agt_addr2 text,
|
|
agt_city text,
|
|
agt_st text,
|
|
agt_zip text,
|
|
agt_ctry text,
|
|
agt_ph1 text,
|
|
agt_ph1x text,
|
|
agt_ph2 text,
|
|
agt_ph2x text,
|
|
agt_fax text,
|
|
agt_faxx text,
|
|
agt_ct_ln text,
|
|
agt_ct_fn text,
|
|
agt_ct_ph text,
|
|
agt_ct_phx text,
|
|
agt_ea text,
|
|
agt_lic_no text,
|
|
loss_type text,
|
|
loss_desc text,
|
|
theft_ind boolean DEFAULT false,
|
|
cat_no text,
|
|
tlos_ind boolean DEFAULT false,
|
|
cust_pr text,
|
|
insd_ln text,
|
|
insd_fn text,
|
|
insd_title text,
|
|
insd_co_nm text,
|
|
insd_addr1 text,
|
|
insd_addr2 text,
|
|
insd_city text,
|
|
insd_st text,
|
|
insd_zip text,
|
|
insd_ctry text,
|
|
insd_ph1 text,
|
|
insd_ph1x text,
|
|
insd_ph2 text,
|
|
insd_ph2x text,
|
|
insd_fax text,
|
|
insd_faxx text,
|
|
insd_ea text,
|
|
ownr_ln text,
|
|
ownr_fn text,
|
|
ownr_title text,
|
|
ownr_co_nm text,
|
|
ownr_addr1 text,
|
|
ownr_addr2 text,
|
|
ownr_city text,
|
|
ownr_st text,
|
|
ownr_zip text,
|
|
ownr_ctry text,
|
|
ownr_ph1 text,
|
|
ownr_ph1x text,
|
|
ownr_ph2 text,
|
|
ownr_ph2x text,
|
|
ownr_fax text,
|
|
ownr_faxx text,
|
|
ownr_ea text,
|
|
area_of_damage jsonb,
|
|
loss_cat text,
|
|
special_coverage_policy boolean DEFAULT false NOT NULL,
|
|
po_number text,
|
|
unit_number text,
|
|
kmin integer,
|
|
kmout integer,
|
|
referral_source text,
|
|
selling_dealer text,
|
|
servicing_dealer text,
|
|
servicing_dealer_contact text,
|
|
selling_dealer_contact text,
|
|
depreciation_taxes numeric,
|
|
other_amount_payable numeric,
|
|
towing_payable numeric,
|
|
storage_payable numeric,
|
|
adjustment_bottom_line numeric,
|
|
tax_pstthr numeric,
|
|
tax_tow_rt numeric,
|
|
tax_sub_rt numeric,
|
|
tax_paint_mat_rt numeric,
|
|
tax_levies_rt numeric,
|
|
tax_prethr numeric,
|
|
tax_thramt numeric,
|
|
tax_str_rt numeric,
|
|
tax_lbr_rt numeric,
|
|
adj_g_disc numeric,
|
|
adj_towdis numeric,
|
|
adj_strdis numeric,
|
|
tax_predis numeric,
|
|
rate_laa numeric,
|
|
status text DEFAULT 'Open'::text NOT NULL,
|
|
cieca_stl jsonb,
|
|
g_bett_amt numeric DEFAULT 0,
|
|
cieca_ttl jsonb,
|
|
plate_no text,
|
|
plate_st text,
|
|
v_vin text,
|
|
v_model_yr text,
|
|
v_model_desc text,
|
|
v_make_desc text,
|
|
v_color text,
|
|
parts_tax_rates jsonb,
|
|
job_totals jsonb,
|
|
production_vars jsonb,
|
|
intakechecklist jsonb,
|
|
invoice_allocation jsonb,
|
|
kanbanparent text,
|
|
employee_body uuid,
|
|
employee_refinish uuid,
|
|
employee_prep uuid,
|
|
tax_registration_number text,
|
|
class text,
|
|
category text,
|
|
deliverchecklist jsonb,
|
|
tax_shop_mat_rt numeric,
|
|
queued_for_parts boolean DEFAULT false NOT NULL,
|
|
ca_gst_registrant boolean DEFAULT false,
|
|
alt_transport text,
|
|
lbr_adjustments jsonb DEFAULT jsonb_build_object() NOT NULL,
|
|
voided boolean DEFAULT false NOT NULL,
|
|
towin boolean DEFAULT false NOT NULL,
|
|
driveable boolean DEFAULT false NOT NULL,
|
|
ca_bc_pvrt numeric,
|
|
ca_customer_gst numeric DEFAULT 0,
|
|
employee_csr uuid
|
|
);
|
|
COMMENT ON COLUMN public.jobs.tax_pstthr IS 'Parts Tax Rate';
|
|
COMMENT ON COLUMN public.jobs.tax_tow_rt IS 'Towing Tax Rate';
|
|
COMMENT ON COLUMN public.jobs.tax_sub_rt IS 'Sublet Tax Rate';
|
|
CREATE FUNCTION public.search_jobs(search text) RETURNS SETOF public.jobs
|
|
LANGUAGE plpgsql STABLE
|
|
AS $$ BEGIN if search = '' then return query
|
|
select *
|
|
from jobs j;
|
|
else return query
|
|
SELECT *
|
|
FROM jobs j2
|
|
WHERE ro_number ILIKE '%' || search || '%'
|
|
or ownr_fn ILIKE '%' || search || '%'
|
|
or ownr_ln ILIKE '%' || search || '%'
|
|
or ownr_co_nm ILIKE '%' || search || '%'
|
|
or clm_no ILIKE '%' || search || '%'
|
|
or v_make_desc ILIKE '%' || search || '%'
|
|
or v_model_desc ILIKE '%' || search || '%'
|
|
OR plate_no ILIKE '%' || search || '%'
|
|
ORDER BY ro_number ILIKE '%' || search || '%'
|
|
OR NULL,
|
|
ownr_ln ILIKE '%' || search || '%'
|
|
OR null,
|
|
ownr_co_nm ILIKE '%' || search || '%'
|
|
OR NULL,
|
|
ownr_fn ILIKE '%' || search || '%'
|
|
OR NULL,
|
|
clm_no ILIKE '%' || search || '%'
|
|
OR NULL,
|
|
v_make_desc ILIKE '%' || search || '%'
|
|
OR NULL,
|
|
v_model_desc ILIKE '%' || search || '%'
|
|
OR NULL,
|
|
plate_no ILIKE '%' || search || '%'
|
|
OR NULL;
|
|
end if;
|
|
END $$;
|
|
CREATE TABLE public.owners (
|
|
id uuid DEFAULT public.gen_random_uuid() NOT NULL,
|
|
created_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
updated_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
ownr_fn text,
|
|
ownr_ln text,
|
|
ownr_addr1 text,
|
|
ownr_addr2 text,
|
|
ownr_city text,
|
|
ownr_st text,
|
|
ownr_zip text,
|
|
ownr_ctry text,
|
|
ownr_ea text,
|
|
ownr_ph1 text,
|
|
preferred_contact text,
|
|
allow_text_message boolean DEFAULT false NOT NULL,
|
|
shopid uuid NOT NULL,
|
|
ownr_ph2 text,
|
|
ownr_co_nm text,
|
|
ownr_title text,
|
|
accountingid bigint NOT NULL
|
|
);
|
|
CREATE FUNCTION public.search_owners(search text) RETURNS SETOF public.owners
|
|
LANGUAGE plpgsql STABLE
|
|
AS $$
|
|
BEGIN
|
|
IF search = '' THEN
|
|
RETURN query
|
|
SELECT
|
|
*
|
|
FROM
|
|
owners;
|
|
ELSE
|
|
RETURN query
|
|
SELECT
|
|
*
|
|
FROM
|
|
owners
|
|
WHERE (ownr_fn || ' ' || ownr_ln)
|
|
ILIKE '%' || search || '%'
|
|
OR ownr_ln ILIKE '%' || search || '%'
|
|
OR ownr_fn ILIKE '%' || search || '%'
|
|
OR ownr_fn ILIKE '%' || search || '%'
|
|
OR ownr_co_nm ILIKE '%' || search || '%'
|
|
OR ownr_ph1 ILIKE '%' || search || '%'
|
|
OR ownr_addr1 ILIKE '%' || search || '%'
|
|
ORDER BY
|
|
(ownr_fn || ' ' || ownr_ln)
|
|
ILIKE '%' || search || '%'
|
|
OR NULL,
|
|
ownr_ln ILIKE '%' || search || '%'
|
|
OR NULL,
|
|
ownr_fn ILIKE '%' || search || '%'
|
|
OR NULL,
|
|
ownr_co_nm ILIKE '%' || search || '%'
|
|
OR NULL,
|
|
ownr_fn ILIKE '%' || search || '%'
|
|
OR NULL,
|
|
ownr_ph1 ILIKE '%' || search || '%'
|
|
OR NULL,
|
|
ownr_addr1 ILIKE '%' || search || '%'
|
|
OR NULL;
|
|
END IF;
|
|
END
|
|
$$;
|
|
CREATE TABLE public.payments (
|
|
id uuid DEFAULT public.gen_random_uuid() NOT NULL,
|
|
created_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
updated_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
jobid uuid NOT NULL,
|
|
amount numeric NOT NULL,
|
|
transactionid text,
|
|
memo text,
|
|
stripeid text,
|
|
payer text,
|
|
exportedat timestamp with time zone,
|
|
type text,
|
|
paymentnum text,
|
|
date date
|
|
);
|
|
CREATE FUNCTION public.search_payments(search text) RETURNS SETOF public.payments
|
|
LANGUAGE plpgsql STABLE
|
|
AS $$
|
|
BEGIN
|
|
if search = '' then
|
|
return query select * from payments ;
|
|
else
|
|
return query SELECT
|
|
p.*
|
|
FROM
|
|
payments p, jobs j
|
|
WHERE
|
|
p.jobid = j.id AND
|
|
(
|
|
search <% j.ownr_fn OR
|
|
search <% j.ownr_ln OR
|
|
search <% j.ownr_co_nm OR
|
|
search <% j.ro_number OR
|
|
search <% (p.payer) OR
|
|
search <% (p.transactionid) OR
|
|
search <% (p.memo));
|
|
end if;
|
|
END
|
|
$$;
|
|
CREATE TABLE public.phonebook (
|
|
id uuid DEFAULT public.gen_random_uuid() NOT NULL,
|
|
created_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
updated_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
bodyshopid uuid NOT NULL,
|
|
firstname text,
|
|
lastname text,
|
|
phone1 text,
|
|
email text,
|
|
address1 text,
|
|
address2 text,
|
|
city text,
|
|
state text,
|
|
zip text,
|
|
country text,
|
|
company text,
|
|
phone2 text,
|
|
fax text,
|
|
category text
|
|
);
|
|
CREATE FUNCTION public.search_phonebook(search text) RETURNS SETOF public.phonebook
|
|
LANGUAGE plpgsql STABLE
|
|
AS $$
|
|
BEGIN
|
|
IF search = '' THEN
|
|
RETURN query
|
|
SELECT
|
|
*
|
|
FROM
|
|
phonebook;
|
|
ELSE
|
|
RETURN query
|
|
SELECT
|
|
phonebook.*
|
|
FROM
|
|
phonebook
|
|
WHERE firstname ILIKE '%' || search || '%'
|
|
OR lastname ILIKE '%' || search || '%'
|
|
OR company ILIKE '%' || search || '%'
|
|
OR address1 ILIKE '%' || search || '%'
|
|
OR phone1 ILIKE '%' || search || '%'
|
|
OR phone2 ILIKE '%' || search || '%'
|
|
OR email ILIKE '%' || search || '%'
|
|
OR category ILIKE '%' || search || '%'
|
|
ORDER BY
|
|
firstname ILIKE '%' || search || '%'
|
|
OR NULL,
|
|
lastname ILIKE '%' || search || '%'
|
|
OR NULL,
|
|
company ILIKE '%' || search || '%'
|
|
OR NULL,
|
|
address1 ILIKE '%' || search || '%'
|
|
OR NULL,
|
|
phone1 ILIKE '%' || search || '%'
|
|
OR NULL,
|
|
phone2 ILIKE '%' || search || '%'
|
|
OR NULL,
|
|
email ILIKE '%' || search || '%'
|
|
OR NULL,
|
|
category ILIKE '%' || search || '%'
|
|
OR NULL;
|
|
END IF;
|
|
END
|
|
$$;
|
|
CREATE TABLE public.vehicles (
|
|
id uuid DEFAULT public.gen_random_uuid() NOT NULL,
|
|
created_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
updated_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
v_vin text NOT NULL,
|
|
v_make_desc text,
|
|
v_model_desc text,
|
|
v_model_yr text,
|
|
v_color text,
|
|
v_paint_codes jsonb,
|
|
v_bstyle text,
|
|
v_engine text,
|
|
shopid uuid NOT NULL,
|
|
db_v_code text,
|
|
plate_no text,
|
|
plate_st text,
|
|
v_cond text,
|
|
v_prod_dt text,
|
|
v_type text,
|
|
v_trimcode text,
|
|
trim_color text,
|
|
v_mldgcode text,
|
|
v_options text,
|
|
v_tone text,
|
|
v_stage text,
|
|
v_makecode text
|
|
);
|
|
CREATE FUNCTION public.search_vehicles(search text) RETURNS SETOF public.vehicles
|
|
LANGUAGE plpgsql STABLE
|
|
AS $$
|
|
BEGIN
|
|
if search = '' then
|
|
return query select * from vehicles ;
|
|
else
|
|
return query
|
|
SELECT *
|
|
FROM vehicles
|
|
WHERE v_vin ILIKE '%' || search || '%'
|
|
or plate_no ILIKE '%' || search || '%'
|
|
or v_make_desc ILIKE '%' || search || '%'
|
|
or v_model_desc ILIKE '%' || search || '%'
|
|
ORDER BY v_vin ILIKE '%' || search || '%'
|
|
OR NULL,
|
|
plate_no ILIKE '%' || search || '%'
|
|
OR NULL,
|
|
v_model_desc ILIKE '%' || search || '%'
|
|
OR NULL,
|
|
v_make_desc ILIKE '%' || search || '%'
|
|
OR null;
|
|
end if;
|
|
END
|
|
$$;
|
|
CREATE FUNCTION public.set_current_timestamp_updated_at() RETURNS trigger
|
|
LANGUAGE plpgsql
|
|
AS $$
|
|
DECLARE
|
|
_new record;
|
|
BEGIN
|
|
_new := NEW;
|
|
_new."updated_at" = NOW();
|
|
RETURN _new;
|
|
END;
|
|
$$;
|
|
CREATE FUNCTION public.update_conversation_on_message() RETURNS trigger
|
|
LANGUAGE plpgsql
|
|
AS $$
|
|
BEGIN
|
|
UPDATE conversations SET updated_at = now() WHERE id = NEW."conversationid";
|
|
RETURN NEW;
|
|
END;
|
|
$$;
|
|
CREATE TABLE public.allocations (
|
|
id uuid DEFAULT public.gen_random_uuid() NOT NULL,
|
|
created_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
updated_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
joblineid uuid NOT NULL,
|
|
employeeid uuid NOT NULL,
|
|
hours numeric DEFAULT 0 NOT NULL
|
|
);
|
|
CREATE TABLE public.appointments (
|
|
id uuid DEFAULT public.gen_random_uuid() NOT NULL,
|
|
created_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
updated_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
jobid uuid,
|
|
start timestamp with time zone NOT NULL,
|
|
"end" timestamp with time zone NOT NULL,
|
|
canceled boolean DEFAULT false NOT NULL,
|
|
arrived boolean DEFAULT false NOT NULL,
|
|
isintake boolean DEFAULT true NOT NULL,
|
|
bodyshopid uuid NOT NULL,
|
|
title text,
|
|
block boolean DEFAULT false NOT NULL,
|
|
color text
|
|
);
|
|
CREATE TABLE public.associations (
|
|
id uuid DEFAULT public.gen_random_uuid() NOT NULL,
|
|
shopid uuid NOT NULL,
|
|
useremail text NOT NULL,
|
|
active boolean DEFAULT true NOT NULL,
|
|
authlevel integer DEFAULT 0 NOT NULL,
|
|
default_prod_list_view text
|
|
);
|
|
CREATE TABLE public.audit_trail (
|
|
id integer NOT NULL,
|
|
created timestamp without time zone DEFAULT now(),
|
|
schemaname text,
|
|
tabname text,
|
|
operation text,
|
|
recordid uuid,
|
|
new_val json,
|
|
old_val json,
|
|
useremail text,
|
|
bodyshopid uuid
|
|
);
|
|
CREATE SEQUENCE public.audit_trail_id_seq
|
|
AS integer
|
|
START WITH 1
|
|
INCREMENT BY 1
|
|
NO MINVALUE
|
|
NO MAXVALUE
|
|
CACHE 1;
|
|
ALTER SEQUENCE public.audit_trail_id_seq OWNED BY public.audit_trail.id;
|
|
CREATE TABLE public.available_jobs (
|
|
id uuid DEFAULT public.gen_random_uuid() NOT NULL,
|
|
created_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
updated_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
uploaded_by text NOT NULL,
|
|
cieca_id text NOT NULL,
|
|
bodyshopid uuid NOT NULL,
|
|
est_data jsonb NOT NULL,
|
|
issupplement boolean DEFAULT false NOT NULL,
|
|
jobid uuid,
|
|
supplement_number integer,
|
|
ownr_name text,
|
|
vehicle_info text,
|
|
clm_amt numeric,
|
|
clm_no text,
|
|
source_system text,
|
|
ins_co_nm text
|
|
);
|
|
CREATE TABLE public.billlines (
|
|
id uuid DEFAULT public.gen_random_uuid() NOT NULL,
|
|
created_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
updated_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
billid uuid NOT NULL,
|
|
line_desc text,
|
|
actual_price numeric DEFAULT 0 NOT NULL,
|
|
actual_cost numeric DEFAULT 0 NOT NULL,
|
|
cost_center text NOT NULL,
|
|
joblineid uuid,
|
|
applicable_taxes jsonb,
|
|
quantity integer DEFAULT 1 NOT NULL,
|
|
deductedfromlbr boolean DEFAULT false NOT NULL,
|
|
lbr_adjustment jsonb
|
|
);
|
|
CREATE TABLE public.bodyshops (
|
|
id uuid DEFAULT public.gen_random_uuid() NOT NULL,
|
|
shopname text NOT NULL,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
updated_at timestamp with time zone DEFAULT now(),
|
|
address1 text,
|
|
address2 text,
|
|
city text,
|
|
state text,
|
|
zip_post text,
|
|
country text,
|
|
email text,
|
|
federal_tax_id text,
|
|
insurance_vendor_id text,
|
|
state_tax_id text,
|
|
md_ro_statuses jsonb,
|
|
region_config text DEFAULT 'CA_BC'::text NOT NULL,
|
|
md_order_statuses jsonb,
|
|
md_responsibility_centers jsonb,
|
|
messagingservicesid text,
|
|
shoprates jsonb,
|
|
template_header text,
|
|
textid text,
|
|
production_config jsonb,
|
|
bill_tax_rates jsonb,
|
|
inhousevendorid uuid,
|
|
intakechecklist jsonb DEFAULT jsonb_build_object(),
|
|
accountingconfig jsonb,
|
|
appt_length integer DEFAULT 60 NOT NULL,
|
|
stripe_acct_id text,
|
|
ssbuckets jsonb DEFAULT jsonb_build_array(),
|
|
scoreboard_target jsonb DEFAULT jsonb_build_object(),
|
|
md_referral_sources jsonb DEFAULT jsonb_build_array() NOT NULL,
|
|
md_messaging_presets jsonb DEFAULT jsonb_build_array() NOT NULL,
|
|
speedprint jsonb DEFAULT jsonb_build_array(),
|
|
md_parts_locations jsonb DEFAULT jsonb_build_array(),
|
|
md_notes_presets jsonb DEFAULT jsonb_build_array() NOT NULL,
|
|
md_rbac jsonb,
|
|
prodtargethrs numeric DEFAULT 1 NOT NULL,
|
|
md_classes jsonb DEFAULT jsonb_build_array(),
|
|
md_categories jsonb DEFAULT jsonb_build_array(),
|
|
md_ins_cos jsonb DEFAULT jsonb_build_array(),
|
|
enforce_class boolean DEFAULT false NOT NULL,
|
|
md_labor_rates jsonb DEFAULT jsonb_build_array(),
|
|
deliverchecklist jsonb DEFAULT jsonb_build_object(),
|
|
phone text,
|
|
target_touchtime numeric DEFAULT 3.5,
|
|
appt_colors jsonb DEFAULT jsonb_build_array(),
|
|
appt_alt_transport jsonb DEFAULT jsonb_build_array(),
|
|
schedule_start_time timestamp with time zone,
|
|
schedule_end_time timestamp with time zone,
|
|
imexshopid text,
|
|
default_adjustment_rate numeric DEFAULT 0,
|
|
workingdays jsonb DEFAULT jsonb_build_object() NOT NULL,
|
|
use_fippa boolean DEFAULT false NOT NULL,
|
|
md_payment_types jsonb DEFAULT jsonb_build_array() NOT NULL,
|
|
md_hour_split jsonb DEFAULT jsonb_build_object() NOT NULL,
|
|
autohouseid text,
|
|
logo_img_path jsonb DEFAULT jsonb_build_object(),
|
|
sub_status text DEFAULT 'active'::text NOT NULL,
|
|
jobsizelimit integer DEFAULT 26214400 NOT NULL,
|
|
md_ccc_rates jsonb DEFAULT jsonb_build_array(),
|
|
enforce_referral boolean DEFAULT false NOT NULL
|
|
);
|
|
CREATE SEQUENCE public.cccontract_agreementnumber_seq
|
|
AS integer
|
|
START WITH 1
|
|
INCREMENT BY 1
|
|
NO MINVALUE
|
|
NO MAXVALUE
|
|
CACHE 1;
|
|
ALTER SEQUENCE public.cccontract_agreementnumber_seq OWNED BY public.cccontracts.agreementnumber;
|
|
CREATE TABLE public.conversations (
|
|
id uuid DEFAULT public.gen_random_uuid() NOT NULL,
|
|
created_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
updated_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
bodyshopid uuid NOT NULL,
|
|
phone_num text NOT NULL
|
|
);
|
|
CREATE TABLE public.counters (
|
|
id uuid DEFAULT public.gen_random_uuid() NOT NULL,
|
|
updated_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
shopid uuid NOT NULL,
|
|
countertype text NOT NULL,
|
|
prefix text,
|
|
count integer DEFAULT 1 NOT NULL
|
|
);
|
|
CREATE TABLE public.courtesycars (
|
|
id uuid DEFAULT public.gen_random_uuid() NOT NULL,
|
|
created_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
updated_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
bodyshopid uuid NOT NULL,
|
|
make text NOT NULL,
|
|
model text NOT NULL,
|
|
year text NOT NULL,
|
|
plate text NOT NULL,
|
|
color text NOT NULL,
|
|
vin text NOT NULL,
|
|
fleetnumber text,
|
|
purchasedate date,
|
|
servicestartdate date,
|
|
serviceenddate date,
|
|
leaseenddate date,
|
|
status text DEFAULT 'Available'::text NOT NULL,
|
|
nextservicekm numeric DEFAULT 0 NOT NULL,
|
|
nextservicedate date,
|
|
damage text,
|
|
notes text,
|
|
fuel numeric DEFAULT 100 NOT NULL,
|
|
registrationexpires date,
|
|
insuranceexpires date,
|
|
dailycost numeric DEFAULT 0 NOT NULL,
|
|
mileage numeric DEFAULT 0 NOT NULL
|
|
);
|
|
CREATE TABLE public.csi (
|
|
id uuid DEFAULT public.gen_random_uuid() NOT NULL,
|
|
created_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
updated_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
jobid uuid NOT NULL,
|
|
valid boolean DEFAULT true NOT NULL,
|
|
relateddata jsonb,
|
|
bodyshopid uuid NOT NULL,
|
|
validuntil date,
|
|
response jsonb,
|
|
questionset uuid,
|
|
completedon timestamp with time zone
|
|
);
|
|
CREATE TABLE public.csiquestions (
|
|
id uuid DEFAULT public.gen_random_uuid() NOT NULL,
|
|
created_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
updated_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
bodyshopid uuid NOT NULL,
|
|
current boolean DEFAULT true NOT NULL,
|
|
config jsonb
|
|
);
|
|
CREATE TABLE public.documents (
|
|
id uuid DEFAULT public.gen_random_uuid() NOT NULL,
|
|
created_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
updated_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
uploaded_by text NOT NULL,
|
|
jobid uuid,
|
|
name text,
|
|
key text DEFAULT '0'::text NOT NULL,
|
|
billid uuid,
|
|
type text,
|
|
extension text,
|
|
bodyshopid uuid,
|
|
size integer DEFAULT 0 NOT NULL
|
|
);
|
|
CREATE TABLE public.employees (
|
|
id uuid DEFAULT public.gen_random_uuid() NOT NULL,
|
|
created_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
updated_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
first_name text NOT NULL,
|
|
last_name text NOT NULL,
|
|
employee_number text,
|
|
shopid uuid NOT NULL,
|
|
active boolean DEFAULT true NOT NULL,
|
|
hire_date date,
|
|
termination_date date,
|
|
base_rate numeric DEFAULT 0 NOT NULL,
|
|
flat_rate boolean DEFAULT false NOT NULL,
|
|
pin text,
|
|
user_email text,
|
|
rates jsonb DEFAULT jsonb_build_array() NOT NULL
|
|
);
|
|
CREATE TABLE public.ioevents (
|
|
id bigint NOT NULL,
|
|
created_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
operationname text NOT NULL,
|
|
"time" numeric,
|
|
variables jsonb,
|
|
dbevent boolean DEFAULT false NOT NULL
|
|
);
|
|
CREATE SEQUENCE public.ioevents_id_seq
|
|
START WITH 1
|
|
INCREMENT BY 1
|
|
NO MINVALUE
|
|
NO MAXVALUE
|
|
CACHE 1;
|
|
ALTER SEQUENCE public.ioevents_id_seq OWNED BY public.ioevents.id;
|
|
CREATE TABLE public.job_conversations (
|
|
jobid uuid NOT NULL,
|
|
conversationid uuid NOT NULL,
|
|
id uuid DEFAULT public.gen_random_uuid() NOT NULL
|
|
);
|
|
CREATE TABLE public.joblines (
|
|
id uuid DEFAULT public.gen_random_uuid() NOT NULL,
|
|
created_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
updated_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
jobid uuid NOT NULL,
|
|
unq_seq integer,
|
|
line_ind text,
|
|
line_desc text,
|
|
part_type text,
|
|
oem_partno text,
|
|
est_seq integer,
|
|
db_ref text,
|
|
line_ref numeric,
|
|
tax_part boolean DEFAULT true NOT NULL,
|
|
db_price numeric,
|
|
act_price numeric,
|
|
part_qty integer,
|
|
alt_partno text,
|
|
mod_lbr_ty text,
|
|
db_hrs numeric,
|
|
mod_lb_hrs numeric,
|
|
lbr_op text,
|
|
lbr_amt numeric,
|
|
glass_flag boolean,
|
|
price_inc boolean,
|
|
alt_part_i boolean,
|
|
price_j boolean,
|
|
cert_part boolean,
|
|
alt_co_id text,
|
|
alt_overrd boolean,
|
|
alt_partm text,
|
|
prt_dsmk_p numeric,
|
|
prt_dsmk_m numeric,
|
|
lbr_inc boolean,
|
|
lbr_hrs_j boolean,
|
|
lbr_typ_j boolean,
|
|
lbr_op_j boolean,
|
|
paint_stg integer,
|
|
paint_tone integer,
|
|
lbr_tax boolean,
|
|
misc_amt numeric,
|
|
misc_sublt boolean,
|
|
misc_tax boolean,
|
|
bett_type text,
|
|
bett_pctg numeric,
|
|
bett_amt numeric,
|
|
bett_tax boolean,
|
|
op_code_desc text,
|
|
status text,
|
|
removed boolean DEFAULT false NOT NULL,
|
|
line_no integer,
|
|
notes text,
|
|
location text,
|
|
profitcenter_labor text,
|
|
profitcenter_part text,
|
|
sublet_ignored boolean DEFAULT false NOT NULL,
|
|
sublet_completed boolean DEFAULT false NOT NULL,
|
|
manual_line boolean DEFAULT false NOT NULL
|
|
);
|
|
CREATE VIEW public.joblines_status AS
|
|
SELECT j.jobid,
|
|
j.status,
|
|
count(1) AS count,
|
|
j.part_type
|
|
FROM public.joblines j
|
|
WHERE ((j.part_type IS NOT NULL) AND (j.part_type <> 'PAE'::text))
|
|
GROUP BY j.jobid, j.status, j.part_type;
|
|
CREATE TABLE public.masterdata (
|
|
key text NOT NULL,
|
|
value text NOT NULL
|
|
);
|
|
CREATE TABLE public.messages (
|
|
id uuid DEFAULT public.gen_random_uuid() NOT NULL,
|
|
created_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
updated_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
msid text NOT NULL,
|
|
conversationid uuid NOT NULL,
|
|
text text,
|
|
image boolean DEFAULT false NOT NULL,
|
|
isoutbound boolean DEFAULT false NOT NULL,
|
|
status text DEFAULT 'posted'::text NOT NULL,
|
|
read boolean DEFAULT false NOT NULL,
|
|
userid text,
|
|
image_path jsonb
|
|
);
|
|
CREATE TABLE public.notes (
|
|
id uuid DEFAULT public.gen_random_uuid() NOT NULL,
|
|
created_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
updated_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
jobid uuid NOT NULL,
|
|
text text,
|
|
created_by text NOT NULL,
|
|
critical boolean DEFAULT false NOT NULL,
|
|
private boolean DEFAULT false NOT NULL,
|
|
audit boolean DEFAULT false NOT NULL
|
|
);
|
|
CREATE SEQUENCE public.owners_accountingid_seq
|
|
START WITH 1
|
|
INCREMENT BY 1
|
|
NO MINVALUE
|
|
NO MAXVALUE
|
|
CACHE 1;
|
|
ALTER SEQUENCE public.owners_accountingid_seq OWNED BY public.owners.accountingid;
|
|
CREATE TABLE public.parts_order_lines (
|
|
id uuid DEFAULT public.gen_random_uuid() NOT NULL,
|
|
created_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
updated_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
orderid uuid NOT NULL,
|
|
job_line_id uuid,
|
|
line_desc text,
|
|
oem_partno text,
|
|
db_price numeric,
|
|
act_price numeric,
|
|
status text DEFAULT 'Ordered'::text NOT NULL,
|
|
line_remarks text,
|
|
quantity numeric DEFAULT 1 NOT NULL,
|
|
backordered_on date,
|
|
backordered_eta date,
|
|
part_type text,
|
|
cost numeric
|
|
);
|
|
CREATE TABLE public.parts_orders (
|
|
id uuid DEFAULT public.gen_random_uuid() NOT NULL,
|
|
created_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
updated_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
jobid uuid NOT NULL,
|
|
vendorid uuid NOT NULL,
|
|
order_date date DEFAULT now() NOT NULL,
|
|
user_email text NOT NULL,
|
|
status text NOT NULL,
|
|
deliver_by date,
|
|
order_number integer NOT NULL,
|
|
return boolean DEFAULT false NOT NULL,
|
|
returnfrombill uuid
|
|
);
|
|
CREATE SEQUENCE public.parts_order_order_number_seq
|
|
AS integer
|
|
START WITH 1
|
|
INCREMENT BY 1
|
|
NO MINVALUE
|
|
NO MAXVALUE
|
|
CACHE 1;
|
|
ALTER SEQUENCE public.parts_order_order_number_seq OWNED BY public.parts_orders.order_number;
|
|
CREATE TABLE public.scoreboard (
|
|
id uuid DEFAULT public.gen_random_uuid() NOT NULL,
|
|
jobid uuid NOT NULL,
|
|
painthrs numeric DEFAULT 0 NOT NULL,
|
|
bodyhrs numeric DEFAULT 0 NOT NULL,
|
|
date date DEFAULT now() NOT NULL
|
|
);
|
|
CREATE TABLE public.timetickets (
|
|
id uuid DEFAULT public.gen_random_uuid() NOT NULL,
|
|
created_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
updated_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
date date DEFAULT now() NOT NULL,
|
|
cost_center text NOT NULL,
|
|
employeeid uuid NOT NULL,
|
|
jobid uuid,
|
|
rate numeric DEFAULT 0 NOT NULL,
|
|
productivehrs numeric DEFAULT 0 NOT NULL,
|
|
actualhrs numeric DEFAULT 0 NOT NULL,
|
|
clockon timestamp with time zone,
|
|
clockoff timestamp with time zone,
|
|
ciecacode text,
|
|
bodyshopid uuid NOT NULL,
|
|
memo text
|
|
);
|
|
CREATE TABLE public.users (
|
|
email text NOT NULL,
|
|
authid text NOT NULL,
|
|
created_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
updated_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
fcmtokens jsonb DEFAULT '{}'::jsonb,
|
|
dashboardlayout jsonb DEFAULT jsonb_build_array() NOT NULL,
|
|
validemail boolean DEFAULT true NOT NULL
|
|
);
|
|
CREATE TABLE public.vendors (
|
|
id uuid DEFAULT public.gen_random_uuid() NOT NULL,
|
|
created_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
updated_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
bodyshopid uuid NOT NULL,
|
|
name text NOT NULL,
|
|
street1 text,
|
|
street2 text,
|
|
city text,
|
|
state text,
|
|
zip text,
|
|
country text,
|
|
email text,
|
|
discount numeric DEFAULT 0 NOT NULL,
|
|
due_date integer,
|
|
cost_center text,
|
|
favorite jsonb DEFAULT jsonb_build_array(),
|
|
phone text,
|
|
active boolean DEFAULT true NOT NULL
|
|
);
|
|
ALTER TABLE ONLY public.audit_trail ALTER COLUMN id SET DEFAULT nextval('public.audit_trail_id_seq'::regclass);
|
|
ALTER TABLE ONLY public.cccontracts ALTER COLUMN agreementnumber SET DEFAULT nextval('public.cccontract_agreementnumber_seq'::regclass);
|
|
ALTER TABLE ONLY public.ioevents ALTER COLUMN id SET DEFAULT nextval('public.ioevents_id_seq'::regclass);
|
|
ALTER TABLE ONLY public.owners ALTER COLUMN accountingid SET DEFAULT nextval('public.owners_accountingid_seq'::regclass);
|
|
ALTER TABLE ONLY public.parts_orders ALTER COLUMN order_number SET DEFAULT nextval('public.parts_order_order_number_seq'::regclass);
|
|
ALTER TABLE ONLY public.allocations
|
|
ADD CONSTRAINT allocations_pkey PRIMARY KEY (id);
|
|
ALTER TABLE ONLY public.appointments
|
|
ADD CONSTRAINT appointments_pkey PRIMARY KEY (id);
|
|
ALTER TABLE ONLY public.associations
|
|
ADD CONSTRAINT associations_pkey PRIMARY KEY (id);
|
|
ALTER TABLE ONLY public.audit_trail
|
|
ADD CONSTRAINT audit_trail_pkey PRIMARY KEY (id);
|
|
ALTER TABLE ONLY public.available_jobs
|
|
ADD CONSTRAINT available_jobs_clm_no_bodyshopid_key UNIQUE (clm_no, bodyshopid);
|
|
ALTER TABLE ONLY public.available_jobs
|
|
ADD CONSTRAINT available_jobs_pkey PRIMARY KEY (id);
|
|
ALTER TABLE ONLY public.bodyshops
|
|
ADD CONSTRAINT bodyshops_autohouseid_key UNIQUE (autohouseid);
|
|
ALTER TABLE ONLY public.bodyshops
|
|
ADD CONSTRAINT bodyshops_imexshopid_key UNIQUE (imexshopid);
|
|
ALTER TABLE ONLY public.bodyshops
|
|
ADD CONSTRAINT bodyshops_pkey PRIMARY KEY (id);
|
|
ALTER TABLE ONLY public.cccontracts
|
|
ADD CONSTRAINT cccontract_pkey PRIMARY KEY (id);
|
|
ALTER TABLE ONLY public.conversations
|
|
ADD CONSTRAINT conversations_pkey PRIMARY KEY (id);
|
|
ALTER TABLE ONLY public.counters
|
|
ADD CONSTRAINT counters_pkey PRIMARY KEY (id);
|
|
ALTER TABLE ONLY public.courtesycars
|
|
ADD CONSTRAINT courtesycars_pkey PRIMARY KEY (id);
|
|
ALTER TABLE ONLY public.csi
|
|
ADD CONSTRAINT csiinvites_pkey PRIMARY KEY (id);
|
|
ALTER TABLE ONLY public.csiquestions
|
|
ADD CONSTRAINT csiquestion_pkey PRIMARY KEY (id);
|
|
ALTER TABLE ONLY public.documents
|
|
ADD CONSTRAINT documents_pkey PRIMARY KEY (id);
|
|
ALTER TABLE ONLY public.employees
|
|
ADD CONSTRAINT employees_pkey PRIMARY KEY (id);
|
|
ALTER TABLE ONLY public.employees
|
|
ADD CONSTRAINT employees_user_email_key UNIQUE (user_email);
|
|
ALTER TABLE ONLY public.exportlog
|
|
ADD CONSTRAINT exportlog_pkey PRIMARY KEY (id);
|
|
ALTER TABLE ONLY public.billlines
|
|
ADD CONSTRAINT invoicelines_pkey PRIMARY KEY (id);
|
|
ALTER TABLE ONLY public.bills
|
|
ADD CONSTRAINT invoices_pkey PRIMARY KEY (id);
|
|
ALTER TABLE ONLY public.ioevents
|
|
ADD CONSTRAINT ioevents_pkey PRIMARY KEY (id);
|
|
ALTER TABLE ONLY public.job_conversations
|
|
ADD CONSTRAINT job_conversations_id_key UNIQUE (id);
|
|
ALTER TABLE ONLY public.job_conversations
|
|
ADD CONSTRAINT job_conversations_pkey PRIMARY KEY (id);
|
|
ALTER TABLE ONLY public.joblines
|
|
ADD CONSTRAINT joblines_pkey PRIMARY KEY (id);
|
|
ALTER TABLE ONLY public.jobs
|
|
ADD CONSTRAINT jobs_pkey PRIMARY KEY (id);
|
|
ALTER TABLE ONLY public.masterdata
|
|
ADD CONSTRAINT masterdata_pkey PRIMARY KEY (key);
|
|
ALTER TABLE ONLY public.messages
|
|
ADD CONSTRAINT messages_msid_key UNIQUE (msid);
|
|
ALTER TABLE ONLY public.messages
|
|
ADD CONSTRAINT messages_pkey PRIMARY KEY (id);
|
|
ALTER TABLE ONLY public.notes
|
|
ADD CONSTRAINT notes_pkey PRIMARY KEY (id);
|
|
ALTER TABLE ONLY public.owners
|
|
ADD CONSTRAINT owners_pkey PRIMARY KEY (id);
|
|
ALTER TABLE ONLY public.parts_order_lines
|
|
ADD CONSTRAINT parts_order_lines_pkey PRIMARY KEY (id);
|
|
ALTER TABLE ONLY public.parts_orders
|
|
ADD CONSTRAINT parts_order_pkey PRIMARY KEY (id);
|
|
ALTER TABLE ONLY public.payments
|
|
ADD CONSTRAINT payments_pkey PRIMARY KEY (id);
|
|
ALTER TABLE ONLY public.phonebook
|
|
ADD CONSTRAINT phonebook_pkey PRIMARY KEY (id);
|
|
ALTER TABLE ONLY public.scoreboard
|
|
ADD CONSTRAINT scoreboard_pkey PRIMARY KEY (id);
|
|
ALTER TABLE ONLY public.timetickets
|
|
ADD CONSTRAINT timetickets_pkey PRIMARY KEY (id);
|
|
ALTER TABLE ONLY public.users
|
|
ADD CONSTRAINT users_pkey PRIMARY KEY (email);
|
|
ALTER TABLE ONLY public.vehicles
|
|
ADD CONSTRAINT vehicles_pkey PRIMARY KEY (id);
|
|
ALTER TABLE ONLY public.vehicles
|
|
ADD CONSTRAINT vehicles_v_vin_shopid_key UNIQUE (v_vin, shopid);
|
|
ALTER TABLE ONLY public.vendors
|
|
ADD CONSTRAINT vendors_pkey PRIMARY KEY (id);
|
|
CREATE INDEX bills_idx_date ON public.bills USING btree (date);
|
|
CREATE INDEX bills_idx_invoice_number ON public.bills USING btree (lower(invoice_number));
|
|
CREATE INDEX cccontracts_idx_actual_return ON public.cccontracts USING btree (actualreturn);
|
|
CREATE INDEX conversations_idx_phone_num ON public.conversations USING btree (phone_num);
|
|
CREATE INDEX conversations_idx_updated_at ON public.conversations USING btree (updated_at);
|
|
CREATE INDEX courtesycars_idx_fleet ON public.courtesycars USING btree (fleetnumber);
|
|
CREATE INDEX employees_idx_employee_number ON public.employees USING btree (employee_number);
|
|
CREATE INDEX idx_invoices_invoicenumber ON public.bills USING gin (invoice_number public.gin_trgm_ops);
|
|
CREATE INDEX idx_jobs_clmno ON public.jobs USING gin (clm_no public.gin_trgm_ops);
|
|
CREATE INDEX idx_jobs_ownrfn ON public.jobs USING gin (ownr_fn public.gin_trgm_ops);
|
|
CREATE INDEX idx_jobs_ownrln ON public.jobs USING gin (ownr_ln public.gin_trgm_ops);
|
|
CREATE INDEX idx_jobs_plateno ON public.jobs USING gin (plate_no public.gin_trgm_ops);
|
|
CREATE INDEX idx_jobs_ronumber ON public.jobs USING gin (ro_number public.gin_trgm_ops);
|
|
CREATE INDEX idx_jobs_vmakedesc ON public.jobs USING gin (v_make_desc public.gin_trgm_ops);
|
|
CREATE INDEX idx_jobs_vmodeldesc ON public.jobs USING gin (v_model_desc public.gin_trgm_ops);
|
|
CREATE INDEX idx_owner_name ON public.owners USING gin ((((((ownr_fn || ' '::text) || ownr_ln) || ' '::text) || ownr_co_nm)) public.gin_trgm_ops);
|
|
CREATE INDEX idx_owners_estnumber ON public.owners USING gin (ownr_co_nm public.gin_trgm_ops);
|
|
CREATE INDEX idx_owners_ownr_addr1 ON public.owners USING gin (ownr_addr1 public.gin_trgm_ops);
|
|
CREATE INDEX idx_owners_ownr_co_nm ON public.owners USING gin (ownr_co_nm public.gin_trgm_ops);
|
|
CREATE INDEX idx_owners_ownr_fn ON public.owners USING gin (ownr_fn public.gin_trgm_ops);
|
|
CREATE INDEX idx_owners_ownr_ln ON public.owners USING gin (ownr_ln public.gin_trgm_ops);
|
|
CREATE INDEX idx_owners_ownr_ph1 ON public.owners USING gin (ownr_ph1 public.gin_trgm_ops);
|
|
CREATE INDEX idx_owners_ownrfn ON public.owners USING gin (ownr_fn public.gin_trgm_ops);
|
|
CREATE INDEX idx_owners_ownrln ON public.owners USING gin (ownr_ln public.gin_trgm_ops);
|
|
CREATE INDEX idx_payments_memo ON public.payments USING gin (memo public.gin_trgm_ops);
|
|
CREATE INDEX idx_payments_payer ON public.payments USING gin (payer public.gin_trgm_ops);
|
|
CREATE INDEX idx_payments_txnid ON public.payments USING gin (transactionid public.gin_trgm_ops);
|
|
CREATE INDEX idx_vehicles_plateno ON public.vehicles USING gin (plate_no public.gin_trgm_ops);
|
|
CREATE INDEX idx_vehicles_vin ON public.vehicles USING gin (v_vin public.gin_trgm_ops);
|
|
CREATE INDEX joblines_idx_line_no ON public.joblines USING btree (line_no, jobid);
|
|
CREATE INDEX joblines_idx_mod_lbr_ty ON public.joblines USING btree (mod_lbr_ty);
|
|
CREATE INDEX joblines_idx_removed ON public.joblines USING btree (removed);
|
|
CREATE INDEX jobs_idx_date_exported ON public.jobs USING btree (date_exported);
|
|
CREATE INDEX jobs_idx_ownr_fn ON public.jobs USING btree (lower(ownr_fn));
|
|
CREATE INDEX jobs_idx_ownr_ln ON public.jobs USING btree (lower(ownr_ln));
|
|
CREATE INDEX jobs_idx_ro_number ON public.jobs USING btree (lower(ro_number));
|
|
CREATE INDEX jobs_idx_status ON public.jobs USING btree (status);
|
|
CREATE INDEX messages_idx_created_at ON public.messages USING btree (created_at);
|
|
CREATE INDEX owners_idx_ownr_co_nm ON public.owners USING btree (lower(ownr_co_nm));
|
|
CREATE INDEX owners_idx_ownr_fn ON public.owners USING btree (lower(ownr_fn));
|
|
CREATE INDEX owners_idx_ownr_ln ON public.owners USING btree (lower(ownr_ln));
|
|
CREATE INDEX parts_orders_idx_order_date ON public.parts_orders USING btree (order_date);
|
|
CREATE INDEX vehicles_idx_v_make_desc ON public.vehicles USING btree (lower(v_make_desc));
|
|
CREATE INDEX vehicles_idx_v_model_desc ON public.vehicles USING btree (lower(v_model_desc));
|
|
CREATE INDEX vendor_idx_active ON public.vendors USING btree (active);
|
|
CREATE INDEX vendor_idx_name ON public.vendors USING btree (name);
|
|
CREATE TRIGGER bills_assign_ihbnum BEFORE INSERT ON public.bills FOR EACH ROW EXECUTE FUNCTION public.assign_ibh_number();
|
|
CREATE TRIGGER jobs_assign_ro BEFORE UPDATE ON public.jobs FOR EACH ROW EXECUTE FUNCTION public.assign_ro_number();
|
|
CREATE TRIGGER jobs_assign_ro_insert BEFORE INSERT ON public.jobs FOR EACH ROW EXECUTE FUNCTION public.assign_ro_number();
|
|
CREATE TRIGGER payments_assign_paymentnum BEFORE INSERT ON public.payments FOR EACH ROW EXECUTE FUNCTION public.assign_payment_number();
|
|
CREATE TRIGGER set_public_allocations_updated_at BEFORE UPDATE ON public.allocations FOR EACH ROW EXECUTE FUNCTION public.set_current_timestamp_updated_at();
|
|
COMMENT ON TRIGGER set_public_allocations_updated_at ON public.allocations IS 'trigger to set value of column "updated_at" to current timestamp on row update';
|
|
CREATE TRIGGER set_public_appointments_updated_at BEFORE UPDATE ON public.appointments FOR EACH ROW EXECUTE FUNCTION public.set_current_timestamp_updated_at();
|
|
COMMENT ON TRIGGER set_public_appointments_updated_at ON public.appointments IS 'trigger to set value of column "updated_at" to current timestamp on row update';
|
|
CREATE TRIGGER set_public_available_jobs_updated_at BEFORE UPDATE ON public.available_jobs FOR EACH ROW EXECUTE FUNCTION public.set_current_timestamp_updated_at();
|
|
COMMENT ON TRIGGER set_public_available_jobs_updated_at ON public.available_jobs IS 'trigger to set value of column "updated_at" to current timestamp on row update';
|
|
CREATE TRIGGER set_public_bodyshops_updated_at BEFORE UPDATE ON public.bodyshops FOR EACH ROW EXECUTE FUNCTION public.set_current_timestamp_updated_at();
|
|
COMMENT ON TRIGGER set_public_bodyshops_updated_at ON public.bodyshops IS 'trigger to set value of column "updated_at" to current timestamp on row update';
|
|
CREATE TRIGGER set_public_cccontract_updated_at BEFORE UPDATE ON public.cccontracts FOR EACH ROW EXECUTE FUNCTION public.set_current_timestamp_updated_at();
|
|
COMMENT ON TRIGGER set_public_cccontract_updated_at ON public.cccontracts IS 'trigger to set value of column "updated_at" to current timestamp on row update';
|
|
CREATE TRIGGER set_public_conversations_updated_at BEFORE UPDATE ON public.conversations FOR EACH ROW EXECUTE FUNCTION public.set_current_timestamp_updated_at();
|
|
COMMENT ON TRIGGER set_public_conversations_updated_at ON public.conversations IS 'trigger to set value of column "updated_at" to current timestamp on row update';
|
|
CREATE TRIGGER set_public_counters_updated_at BEFORE UPDATE ON public.counters FOR EACH ROW EXECUTE FUNCTION public.set_current_timestamp_updated_at();
|
|
COMMENT ON TRIGGER set_public_counters_updated_at ON public.counters IS 'trigger to set value of column "updated_at" to current timestamp on row update';
|
|
CREATE TRIGGER set_public_courtesycars_updated_at BEFORE UPDATE ON public.courtesycars FOR EACH ROW EXECUTE FUNCTION public.set_current_timestamp_updated_at();
|
|
COMMENT ON TRIGGER set_public_courtesycars_updated_at ON public.courtesycars IS 'trigger to set value of column "updated_at" to current timestamp on row update';
|
|
CREATE TRIGGER set_public_csiinvites_updated_at BEFORE UPDATE ON public.csi FOR EACH ROW EXECUTE FUNCTION public.set_current_timestamp_updated_at();
|
|
COMMENT ON TRIGGER set_public_csiinvites_updated_at ON public.csi IS 'trigger to set value of column "updated_at" to current timestamp on row update';
|
|
CREATE TRIGGER set_public_csiquestion_updated_at BEFORE UPDATE ON public.csiquestions FOR EACH ROW EXECUTE FUNCTION public.set_current_timestamp_updated_at();
|
|
COMMENT ON TRIGGER set_public_csiquestion_updated_at ON public.csiquestions IS 'trigger to set value of column "updated_at" to current timestamp on row update';
|
|
CREATE TRIGGER set_public_documents_updated_at BEFORE UPDATE ON public.documents FOR EACH ROW EXECUTE FUNCTION public.set_current_timestamp_updated_at();
|
|
COMMENT ON TRIGGER set_public_documents_updated_at ON public.documents IS 'trigger to set value of column "updated_at" to current timestamp on row update';
|
|
CREATE TRIGGER set_public_employees_updated_at BEFORE UPDATE ON public.employees FOR EACH ROW EXECUTE FUNCTION public.set_current_timestamp_updated_at();
|
|
COMMENT ON TRIGGER set_public_employees_updated_at ON public.employees IS 'trigger to set value of column "updated_at" to current timestamp on row update';
|
|
CREATE TRIGGER set_public_exportlog_updated_at BEFORE UPDATE ON public.exportlog FOR EACH ROW EXECUTE FUNCTION public.set_current_timestamp_updated_at();
|
|
COMMENT ON TRIGGER set_public_exportlog_updated_at ON public.exportlog IS 'trigger to set value of column "updated_at" to current timestamp on row update';
|
|
CREATE TRIGGER set_public_invoicelines_updated_at BEFORE UPDATE ON public.billlines FOR EACH ROW EXECUTE FUNCTION public.set_current_timestamp_updated_at();
|
|
COMMENT ON TRIGGER set_public_invoicelines_updated_at ON public.billlines IS 'trigger to set value of column "updated_at" to current timestamp on row update';
|
|
CREATE TRIGGER set_public_invoices_updated_at BEFORE UPDATE ON public.bills FOR EACH ROW EXECUTE FUNCTION public.set_current_timestamp_updated_at();
|
|
COMMENT ON TRIGGER set_public_invoices_updated_at ON public.bills IS 'trigger to set value of column "updated_at" to current timestamp on row update';
|
|
CREATE TRIGGER set_public_joblines_updated_at BEFORE UPDATE ON public.joblines FOR EACH ROW EXECUTE FUNCTION public.set_current_timestamp_updated_at();
|
|
COMMENT ON TRIGGER set_public_joblines_updated_at ON public.joblines IS 'trigger to set value of column "updated_at" to current timestamp on row update';
|
|
CREATE TRIGGER set_public_jobs_updated_at BEFORE UPDATE ON public.jobs FOR EACH ROW EXECUTE FUNCTION public.set_current_timestamp_updated_at();
|
|
COMMENT ON TRIGGER set_public_jobs_updated_at ON public.jobs IS 'trigger to set value of column "updated_at" to current timestamp on row update';
|
|
CREATE TRIGGER set_public_messages_updated_at BEFORE UPDATE ON public.messages FOR EACH ROW EXECUTE FUNCTION public.set_current_timestamp_updated_at();
|
|
COMMENT ON TRIGGER set_public_messages_updated_at ON public.messages IS 'trigger to set value of column "updated_at" to current timestamp on row update';
|
|
CREATE TRIGGER set_public_notes_updated_at BEFORE UPDATE ON public.notes FOR EACH ROW EXECUTE FUNCTION public.set_current_timestamp_updated_at();
|
|
COMMENT ON TRIGGER set_public_notes_updated_at ON public.notes IS 'trigger to set value of column "updated_at" to current timestamp on row update';
|
|
CREATE TRIGGER set_public_owners_updated_at BEFORE UPDATE ON public.owners FOR EACH ROW EXECUTE FUNCTION public.set_current_timestamp_updated_at();
|
|
COMMENT ON TRIGGER set_public_owners_updated_at ON public.owners IS 'trigger to set value of column "updated_at" to current timestamp on row update';
|
|
CREATE TRIGGER set_public_parts_order_lines_updated_at BEFORE UPDATE ON public.parts_order_lines FOR EACH ROW EXECUTE FUNCTION public.set_current_timestamp_updated_at();
|
|
COMMENT ON TRIGGER set_public_parts_order_lines_updated_at ON public.parts_order_lines IS 'trigger to set value of column "updated_at" to current timestamp on row update';
|
|
CREATE TRIGGER set_public_parts_order_updated_at BEFORE UPDATE ON public.parts_orders FOR EACH ROW EXECUTE FUNCTION public.set_current_timestamp_updated_at();
|
|
COMMENT ON TRIGGER set_public_parts_order_updated_at ON public.parts_orders IS 'trigger to set value of column "updated_at" to current timestamp on row update';
|
|
CREATE TRIGGER set_public_payments_updated_at BEFORE UPDATE ON public.payments FOR EACH ROW EXECUTE FUNCTION public.set_current_timestamp_updated_at();
|
|
COMMENT ON TRIGGER set_public_payments_updated_at ON public.payments IS 'trigger to set value of column "updated_at" to current timestamp on row update';
|
|
CREATE TRIGGER set_public_phonebook_updated_at BEFORE UPDATE ON public.phonebook FOR EACH ROW EXECUTE FUNCTION public.set_current_timestamp_updated_at();
|
|
COMMENT ON TRIGGER set_public_phonebook_updated_at ON public.phonebook IS 'trigger to set value of column "updated_at" to current timestamp on row update';
|
|
CREATE TRIGGER set_public_timetickets_updated_at BEFORE UPDATE ON public.timetickets FOR EACH ROW EXECUTE FUNCTION public.set_current_timestamp_updated_at();
|
|
COMMENT ON TRIGGER set_public_timetickets_updated_at ON public.timetickets IS 'trigger to set value of column "updated_at" to current timestamp on row update';
|
|
CREATE TRIGGER set_public_users_updated_at BEFORE UPDATE ON public.users FOR EACH ROW EXECUTE FUNCTION public.set_current_timestamp_updated_at();
|
|
COMMENT ON TRIGGER set_public_users_updated_at ON public.users IS 'trigger to set value of column "updated_at" to current timestamp on row update';
|
|
CREATE TRIGGER set_public_vehicles_updated_at BEFORE UPDATE ON public.vehicles FOR EACH ROW EXECUTE FUNCTION public.set_current_timestamp_updated_at();
|
|
COMMENT ON TRIGGER set_public_vehicles_updated_at ON public.vehicles IS 'trigger to set value of column "updated_at" to current timestamp on row update';
|
|
CREATE TRIGGER set_public_vendors_updated_at BEFORE UPDATE ON public.vendors FOR EACH ROW EXECUTE FUNCTION public.set_current_timestamp_updated_at();
|
|
COMMENT ON TRIGGER set_public_vendors_updated_at ON public.vendors IS 'trigger to set value of column "updated_at" to current timestamp on row update';
|
|
CREATE TRIGGER trigger_update_conversation_on_message AFTER INSERT ON public.messages FOR EACH ROW EXECUTE FUNCTION public.update_conversation_on_message();
|
|
ALTER TABLE ONLY public.allocations
|
|
ADD CONSTRAINT allocations_employeeid_fkey FOREIGN KEY (employeeid) REFERENCES public.employees(id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
ALTER TABLE ONLY public.allocations
|
|
ADD CONSTRAINT allocations_joblineid_fkey FOREIGN KEY (joblineid) REFERENCES public.joblines(id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
ALTER TABLE ONLY public.appointments
|
|
ADD CONSTRAINT appointments_bodyshopid_fkey FOREIGN KEY (bodyshopid) REFERENCES public.bodyshops(id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
ALTER TABLE ONLY public.appointments
|
|
ADD CONSTRAINT appointments_jobid_fkey FOREIGN KEY (jobid) REFERENCES public.jobs(id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
ALTER TABLE ONLY public.associations
|
|
ADD CONSTRAINT associations_shopid_fkey FOREIGN KEY (shopid) REFERENCES public.bodyshops(id) ON UPDATE RESTRICT ON DELETE RESTRICT;
|
|
ALTER TABLE ONLY public.associations
|
|
ADD CONSTRAINT associations_useremail_fkey FOREIGN KEY (useremail) REFERENCES public.users(email) ON UPDATE RESTRICT ON DELETE RESTRICT;
|
|
ALTER TABLE ONLY public.audit_trail
|
|
ADD CONSTRAINT audit_trail_bodyshopid_fkey FOREIGN KEY (bodyshopid) REFERENCES public.bodyshops(id) ON UPDATE RESTRICT ON DELETE RESTRICT;
|
|
ALTER TABLE ONLY public.audit_trail
|
|
ADD CONSTRAINT audit_trail_useremail_fkey FOREIGN KEY (useremail) REFERENCES public.users(email) ON UPDATE RESTRICT ON DELETE RESTRICT;
|
|
ALTER TABLE ONLY public.available_jobs
|
|
ADD CONSTRAINT available_jobs_bodyshopid_fkey FOREIGN KEY (bodyshopid) REFERENCES public.bodyshops(id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
ALTER TABLE ONLY public.available_jobs
|
|
ADD CONSTRAINT available_jobs_jobid_fkey FOREIGN KEY (jobid) REFERENCES public.jobs(id) ON UPDATE RESTRICT ON DELETE CASCADE;
|
|
ALTER TABLE ONLY public.billlines
|
|
ADD CONSTRAINT billlines_joblineid_fkey FOREIGN KEY (joblineid) REFERENCES public.joblines(id) ON UPDATE RESTRICT ON DELETE SET NULL;
|
|
ALTER TABLE ONLY public.cccontracts
|
|
ADD CONSTRAINT cccontract_courtesycarid_fkey FOREIGN KEY (courtesycarid) REFERENCES public.courtesycars(id) ON UPDATE RESTRICT ON DELETE RESTRICT;
|
|
ALTER TABLE ONLY public.cccontracts
|
|
ADD CONSTRAINT cccontract_jobid_fkey FOREIGN KEY (jobid) REFERENCES public.jobs(id) ON UPDATE RESTRICT ON DELETE RESTRICT;
|
|
ALTER TABLE ONLY public.conversations
|
|
ADD CONSTRAINT conversations_bodyshopid_fkey FOREIGN KEY (bodyshopid) REFERENCES public.bodyshops(id) ON UPDATE RESTRICT ON DELETE RESTRICT;
|
|
ALTER TABLE ONLY public.counters
|
|
ADD CONSTRAINT counters_shopid_fkey FOREIGN KEY (shopid) REFERENCES public.bodyshops(id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
ALTER TABLE ONLY public.courtesycars
|
|
ADD CONSTRAINT courtesycars_bodyshopid_fkey FOREIGN KEY (bodyshopid) REFERENCES public.bodyshops(id) ON UPDATE RESTRICT ON DELETE RESTRICT;
|
|
ALTER TABLE ONLY public.csi
|
|
ADD CONSTRAINT csi_questionset_fkey FOREIGN KEY (questionset) REFERENCES public.csiquestions(id) ON UPDATE RESTRICT ON DELETE RESTRICT;
|
|
ALTER TABLE ONLY public.csi
|
|
ADD CONSTRAINT csiinvites_bodyshopid_fkey FOREIGN KEY (bodyshopid) REFERENCES public.bodyshops(id) ON UPDATE RESTRICT ON DELETE RESTRICT;
|
|
ALTER TABLE ONLY public.csi
|
|
ADD CONSTRAINT csiinvites_jobid_fkey FOREIGN KEY (jobid) REFERENCES public.jobs(id) ON UPDATE RESTRICT ON DELETE RESTRICT;
|
|
ALTER TABLE ONLY public.csiquestions
|
|
ADD CONSTRAINT csiquestion_bodyshopid_fkey FOREIGN KEY (bodyshopid) REFERENCES public.bodyshops(id) ON UPDATE RESTRICT ON DELETE RESTRICT;
|
|
ALTER TABLE ONLY public.documents
|
|
ADD CONSTRAINT documents_billid_fkey FOREIGN KEY (billid) REFERENCES public.bills(id) ON UPDATE RESTRICT ON DELETE SET NULL;
|
|
ALTER TABLE ONLY public.documents
|
|
ADD CONSTRAINT documents_bodyshopid_fkey FOREIGN KEY (bodyshopid) REFERENCES public.bodyshops(id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
ALTER TABLE ONLY public.documents
|
|
ADD CONSTRAINT documents_jobid_fkey FOREIGN KEY (jobid) REFERENCES public.jobs(id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
ALTER TABLE ONLY public.employees
|
|
ADD CONSTRAINT employees_shopid_fkey FOREIGN KEY (shopid) REFERENCES public.bodyshops(id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
ALTER TABLE ONLY public.employees
|
|
ADD CONSTRAINT employees_user_email_fkey FOREIGN KEY (user_email) REFERENCES public.users(email) ON UPDATE SET NULL ON DELETE SET NULL;
|
|
ALTER TABLE ONLY public.exportlog
|
|
ADD CONSTRAINT exportlog_billid_fkey FOREIGN KEY (billid) REFERENCES public.bills(id) ON UPDATE RESTRICT ON DELETE RESTRICT;
|
|
ALTER TABLE ONLY public.exportlog
|
|
ADD CONSTRAINT exportlog_bodyshopid_fkey FOREIGN KEY (bodyshopid) REFERENCES public.bodyshops(id) ON UPDATE RESTRICT ON DELETE RESTRICT;
|
|
ALTER TABLE ONLY public.exportlog
|
|
ADD CONSTRAINT exportlog_jobid_fkey FOREIGN KEY (jobid) REFERENCES public.jobs(id) ON UPDATE RESTRICT ON DELETE RESTRICT;
|
|
ALTER TABLE ONLY public.exportlog
|
|
ADD CONSTRAINT exportlog_paymentid_fkey FOREIGN KEY (paymentid) REFERENCES public.payments(id) ON UPDATE RESTRICT ON DELETE RESTRICT;
|
|
ALTER TABLE ONLY public.exportlog
|
|
ADD CONSTRAINT exportlog_useremail_fkey FOREIGN KEY (useremail) REFERENCES public.users(email) ON UPDATE RESTRICT ON DELETE RESTRICT;
|
|
ALTER TABLE ONLY public.billlines
|
|
ADD CONSTRAINT invoicelines_invoiceid_fkey FOREIGN KEY (billid) REFERENCES public.bills(id) ON UPDATE RESTRICT ON DELETE CASCADE;
|
|
ALTER TABLE ONLY public.bills
|
|
ADD CONSTRAINT invoices_jobid_fkey FOREIGN KEY (jobid) REFERENCES public.jobs(id) ON UPDATE RESTRICT ON DELETE CASCADE;
|
|
ALTER TABLE ONLY public.bills
|
|
ADD CONSTRAINT invoices_vendorid_fkey FOREIGN KEY (vendorid) REFERENCES public.vendors(id) ON UPDATE RESTRICT ON DELETE RESTRICT;
|
|
ALTER TABLE ONLY public.job_conversations
|
|
ADD CONSTRAINT job_conversations_conversationid_fkey FOREIGN KEY (conversationid) REFERENCES public.conversations(id) ON UPDATE RESTRICT ON DELETE RESTRICT;
|
|
ALTER TABLE ONLY public.job_conversations
|
|
ADD CONSTRAINT job_conversations_jobid_fkey FOREIGN KEY (jobid) REFERENCES public.jobs(id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
ALTER TABLE ONLY public.joblines
|
|
ADD CONSTRAINT joblines_jobid_fkey FOREIGN KEY (jobid) REFERENCES public.jobs(id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
ALTER TABLE ONLY public.jobs
|
|
ADD CONSTRAINT jobs_employee_body_fkey FOREIGN KEY (employee_body) REFERENCES public.employees(id) ON UPDATE SET NULL ON DELETE SET NULL;
|
|
ALTER TABLE ONLY public.jobs
|
|
ADD CONSTRAINT jobs_employee_csr_fkey FOREIGN KEY (employee_csr) REFERENCES public.employees(id) ON UPDATE RESTRICT ON DELETE RESTRICT;
|
|
ALTER TABLE ONLY public.jobs
|
|
ADD CONSTRAINT jobs_employee_prep_fkey FOREIGN KEY (employee_prep) REFERENCES public.employees(id) ON UPDATE SET NULL ON DELETE SET NULL;
|
|
ALTER TABLE ONLY public.jobs
|
|
ADD CONSTRAINT jobs_employee_refinish_fkey FOREIGN KEY (employee_refinish) REFERENCES public.employees(id) ON UPDATE SET NULL ON DELETE SET NULL;
|
|
ALTER TABLE ONLY public.jobs
|
|
ADD CONSTRAINT jobs_ownerid_fkey FOREIGN KEY (ownerid) REFERENCES public.owners(id) ON UPDATE RESTRICT ON DELETE CASCADE;
|
|
ALTER TABLE ONLY public.jobs
|
|
ADD CONSTRAINT jobs_shopid_fkey FOREIGN KEY (shopid) REFERENCES public.bodyshops(id) ON UPDATE RESTRICT ON DELETE RESTRICT;
|
|
ALTER TABLE ONLY public.jobs
|
|
ADD CONSTRAINT jobs_vehicleid_fkey FOREIGN KEY (vehicleid) REFERENCES public.vehicles(id) ON UPDATE RESTRICT ON DELETE CASCADE;
|
|
ALTER TABLE ONLY public.messages
|
|
ADD CONSTRAINT messages_conversationid_fkey FOREIGN KEY (conversationid) REFERENCES public.conversations(id) ON UPDATE RESTRICT ON DELETE RESTRICT;
|
|
ALTER TABLE ONLY public.messages
|
|
ADD CONSTRAINT messages_userid_fkey FOREIGN KEY (userid) REFERENCES public.users(email) ON UPDATE SET NULL ON DELETE SET NULL;
|
|
ALTER TABLE ONLY public.notes
|
|
ADD CONSTRAINT notes_created_by_fkey FOREIGN KEY (created_by) REFERENCES public.users(email) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
ALTER TABLE ONLY public.notes
|
|
ADD CONSTRAINT notes_jobid_fkey FOREIGN KEY (jobid) REFERENCES public.jobs(id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
ALTER TABLE ONLY public.owners
|
|
ADD CONSTRAINT owners_shopid_fkey FOREIGN KEY (shopid) REFERENCES public.bodyshops(id) ON UPDATE RESTRICT ON DELETE CASCADE;
|
|
ALTER TABLE ONLY public.parts_orders
|
|
ADD CONSTRAINT parts_order_jobid_fkey FOREIGN KEY (jobid) REFERENCES public.jobs(id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
ALTER TABLE ONLY public.parts_order_lines
|
|
ADD CONSTRAINT parts_order_lines_job_line_id_fkey FOREIGN KEY (job_line_id) REFERENCES public.joblines(id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
ALTER TABLE ONLY public.parts_order_lines
|
|
ADD CONSTRAINT parts_order_lines_orderid_fkey FOREIGN KEY (orderid) REFERENCES public.parts_orders(id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
ALTER TABLE ONLY public.parts_orders
|
|
ADD CONSTRAINT parts_order_ordered_by_user_id_fkey FOREIGN KEY (user_email) REFERENCES public.users(email) ON UPDATE RESTRICT ON DELETE RESTRICT;
|
|
ALTER TABLE ONLY public.parts_orders
|
|
ADD CONSTRAINT parts_orders_returnfrombill_fkey FOREIGN KEY (returnfrombill) REFERENCES public.bills(id) ON UPDATE SET NULL ON DELETE SET NULL;
|
|
ALTER TABLE ONLY public.parts_orders
|
|
ADD CONSTRAINT parts_orders_vendorid_fkey FOREIGN KEY (vendorid) REFERENCES public.vendors(id) ON UPDATE SET NULL ON DELETE SET NULL;
|
|
ALTER TABLE ONLY public.payments
|
|
ADD CONSTRAINT payments_jobid_fkey FOREIGN KEY (jobid) REFERENCES public.jobs(id) ON UPDATE RESTRICT ON DELETE RESTRICT;
|
|
ALTER TABLE ONLY public.phonebook
|
|
ADD CONSTRAINT phonebook_bodyshopid_fkey FOREIGN KEY (bodyshopid) REFERENCES public.bodyshops(id) ON UPDATE RESTRICT ON DELETE RESTRICT;
|
|
ALTER TABLE ONLY public.scoreboard
|
|
ADD CONSTRAINT scoreboard_jobid_fkey FOREIGN KEY (jobid) REFERENCES public.jobs(id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
ALTER TABLE ONLY public.timetickets
|
|
ADD CONSTRAINT timetickets_bodyshopid_fkey FOREIGN KEY (bodyshopid) REFERENCES public.bodyshops(id) ON UPDATE RESTRICT ON DELETE RESTRICT;
|
|
ALTER TABLE ONLY public.timetickets
|
|
ADD CONSTRAINT timetickets_employeeid_fkey FOREIGN KEY (employeeid) REFERENCES public.employees(id) ON UPDATE RESTRICT ON DELETE RESTRICT;
|
|
ALTER TABLE ONLY public.timetickets
|
|
ADD CONSTRAINT timetickets_jobid_fkey FOREIGN KEY (jobid) REFERENCES public.jobs(id) ON UPDATE RESTRICT ON DELETE RESTRICT;
|
|
ALTER TABLE ONLY public.vehicles
|
|
ADD CONSTRAINT vehicles_shopid_fkey FOREIGN KEY (shopid) REFERENCES public.bodyshops(id) ON UPDATE RESTRICT ON DELETE CASCADE;
|
|
ALTER TABLE ONLY public.vendors
|
|
ADD CONSTRAINT vendors_bodyshopid_fkey FOREIGN KEY (bodyshopid) REFERENCES public.bodyshops(id) ON UPDATE CASCADE ON DELETE CASCADE;
|