Notes

-- Function: public.f_copy_standard(integer, integer, character varying)

-- DROP FUNCTION public.f_copy_standard(integer, integer, character varying);

CREATE OR REPLACE FUNCTION public.f_copy_standard(
std_drawing integer,
dest_job integer,
user_id character varying)
RETURNS void AS
$BODY$
DECLARE
job_std_drawing int;
copied_std_drawings int[];
std_drawings_to_copied int[];
job_drawings int[];
all_job_drawings int[];
job_parts int[];
job_specs int[];
r record;
recursive_copied boolean := false;
sync_time timestamp without time zone;
BEGIN

-- Find for existed job-std drawing in destination job
-- This is also a stopping condition for the recursive copy progress here bellow
SELECT id INTO job_std_drawing
FROM drawings
WHERE job_id=dest_job and deleted=false
and drawing_id=(select drawing_id from drawings where id=std_drawing);

IF NOT FOUND THEN
FOR r IN
-- Find for all std drawings, which referenced by the current selected drawing, to be copied
-- Note: We must dig into the whole components of the referenced drawing not only the referenced parts
WITH RECURSIVE drawings_tree(id, drawing_id, reference_part, path, cycle) AS (
select d.id, d.drawing_id, p.reference_to, array[d.id], false
from drawings d
left join parts p on p.drawing_id=d.id and not p.deleted -- the std drawing might not contain any part
where d.id = std_drawing and not d.deleted
UNION ALL
select ach.drawing_id, left(ach.dpn,11)::varchar(255), ach.reference_to,
dt.path||ach.drawing_id, ach.drawing_id=ANY(dt.path)
from drawings_tree dt
join parts ch on ch.id=dt.reference_part -- direct referenced parts
join parts ach on ach.drawing_id=ch.drawing_id -- all components of the concerning drawings
where not ch.deleted and not ach.deleted and not cycle
)
SELECT distinct dt.id, (jd.id notnull) as copied
FROM drawings_tree dt
LEFT JOIN drawings jd on jd.drawing_id=dt.drawing_id and jd.job_id=dest_job and not jd.deleted
WHERE not cycle
LOOP
-- Loop though the result and split into 2 arrays...
IF r.copied THEN
-- one for storing the std drawings which were copied before...
-- This is used to find the existed job-std draiwings here bellow
copied_std_drawings := copied_std_drawings||r.id;
ELSE
-- another one for storing the std drawings to be copied now
std_drawings_to_copied := std_drawings_to_copied||r.id;
END IF;
END LOOP;

-- COPY DRAWINGS --
WITH inserted_rows AS (
INSERT INTO drawings("drawing_id", "job_id", "revision", "customer_id", "drawing_format", "file_prefix",
"file_type", "is_schematic", "status", "created_by", "updated_by", "drawing_group_id", "drawing_purpose",
"original_drawing_id", "created_at", "updated_at", "exclude_from_customer", "exclude_from_supplier", "is_copied")
SELECT sd.drawing_id, dest_job, sd.revision, sd.customer_id, sd.drawing_format, sd.file_prefix,
sd.file_type, sd.is_schematic, sd.status, sd.created_by, sd.updated_by, sd.drawing_group_id, sd.drawing_purpose,
sd.original_drawing_id, sd.created_at, sd.updated_at, sd.exclude_from_customer, sd.exclude_from_supplier, true
FROM drawings sd
WHERE sd.id = any(std_drawings_to_copied)
RETURNING id
)
SELECT array_agg(id) INTO job_drawings FROM inserted_rows;

-- Collect all job drawings which have just copied, and already copied before as well
-- Note: This is only used to update/insert reference data.
-- Other update/insert must be selected from the job_drawings
all_job_drawings := job_drawings; -- just copied
FOR r in
select jd.id
from drawings sd
join drawings jd on jd.drawing_id=sd.drawing_id
where sd.id = any(copied_std_drawings) and jd.job_id = dest_job and not jd.deleted
LOOP
all_job_drawings := all_job_drawings||r.id; -- copied before
END LOOP;

-- COPY DRAWING DESCRIPTIONS --
INSERT INTO drawing_descriptions("language_id", "drawing_id", "description", "is_copied")
SELECT ddesc.language_id, jd.id, ddesc.description, true
FROM drawing_descriptions ddesc
JOIN drawings sd on sd.id=ddesc.drawing_id
JOIN drawings jd on jd.drawing_id=sd.drawing_id
WHERE sd.id = any(std_drawings_to_copied)
and jd.id = any(job_drawings);

-- COPY PARTS --
WITH inserted_rows AS (
INSERT INTO parts("dpn", "mass", "unit", "mfr_std", "material_std", "job_id", "drawing_id",
"reference_to", "referenced_count", "reference_dpn", "is_assembly","status", "stage", "stage_history",
"modified", "original_part_id", "created_at", "updated_at", "created_by", "updated_by", "is_copied")
SELECT sp.dpn, sp.mass, sp.unit, sp.mfr_std, sp.material_std, dest_job, jd.id,
sp.reference_to, sp.referenced_count, sp.reference_dpn, sp.is_assembly, sp.status, sp.stage, sp.stage_history,
sp.modified, sp.original_part_id, sp.created_at, sp.updated_at, sp.created_by, sp.updated_by, true
FROM parts sp
JOIN drawings jd on jd.drawing_id=left(sp.dpn,11)
WHERE sp.drawing_id = any(std_drawings_to_copied)
and not sp.deleted
and jd.id = any(job_drawings)
RETURNING id
)
SELECT array_agg(id) INTO job_parts FROM inserted_rows;

IF job_parts is not null THEN -- For performance reason
-- COPY PART DESCRIPTIONS --
INSERT INTO part_descriptions("part_id", "job_id", "language_id", "description", "is_copied")
SELECT jp.id, dest_job, pdesc.language_id, pdesc.description, true
FROM part_descriptions pdesc
JOIN parts sp on sp.id=pdesc.part_id
JOIN parts jp on jp.dpn=sp.dpn
WHERE sp.drawing_id = any(std_drawings_to_copied)
and not sp.deleted
and jp.id = any(job_parts);

-- COPY PART MANUFACTURERS
INSERT INTO part_manufacturers(part_id, manufacturer_id, reference)
SELECT jp.id, pm.manufacturer_id, pm.reference
FROM parts sp
JOIN part_manufacturers pm on pm.part_id=sp.id
JOIN parts jp on jp.dpn=sp.dpn
WHERE sp.drawing_id = any(std_drawings_to_copied)
and not sp.deleted
and jp.id = any(job_parts);

INSERT INTO part_manufacturer_attributes(part_manufacturer_id, type_id, value)
SELECT pmjp.id, pma.type_id, pma.value
FROM parts sp
JOIN part_manufacturers pm on pm.part_id=sp.id
JOIN part_manufacturer_attributes pma on pma.part_manufacturer_id=pm.id
JOIN parts jp on jp.dpn=sp.dpn
JOIN part_manufacturers pmjp on pmjp.part_id=jp.id and pmjp.manufacturer_id=pm.manufacturer_id and pmjp.reference=pm.reference
WHERE sp.drawing_id = any(std_drawings_to_copied)
and not sp.deleted
and jp.id = any(job_parts);

-- Switch parts.reference_to to job parts
-- Note: child part could be a component of a copied std drawing (see copied_std_drawings) so that we cannot just
-- find it from job_drawings which have just copied, but from all_job_drawings which included existed job-std
-- drawings as well
UPDATE parts SET reference_to=ch.id
FROM parts as ch
WHERE parts.id = any(job_parts)
and ch.drawing_id = any(all_job_drawings)
and not ch.deleted
and parts.reference_dpn is not null
and parts.reference_dpn = ch.dpn;

-- COPY DPNS --
-- This is included CONTAINED_IN and REFERENCED_BY relationship also
-- Note: See above note for 'jp.drawing_id = any(all_job_drawings)'. About 'jpp.drawing_id = any(job_drawings)',
-- we must limit them in the job-std drawings which have just copied, or duplicated dpns records would be inserted
-- for existed job-std parts
INSERT INTO dpns("job_id", "part_id", "dpn", "is_assembly", "parent_id", "parent_dpn", "relationship", "quantity")
SELECT dest_job, jp.id, jp.dpn, jp.is_assembly, jpp.id, jpp.dpn, dpns.relationship, dpns.quantity
FROM dpns
JOIN parts spp on spp.id=dpns.parent_id
JOIN parts jpp on jpp.dpn=spp.dpn
JOIN parts jp on jp.dpn=dpns.dpn
WHERE spp.drawing_id = any(std_drawings_to_copied)
and not spp.deleted
and jp.drawing_id = any(all_job_drawings)
and jpp.drawing_id = any(job_drawings);
END IF;

-- COPY ASSOCIATED DOCUMENTS --
-- Note: the specification here is only used for refer in the job-std drawings. It does not need to copied into job
WITH inserted_rows AS (
INSERT INTO specifications("spec_id", "job_id", "drawing_id", "status", "created_by", "updated_by",
"reference_count", "original_spec_id", "created_at", "updated_at", "is_copied")
SELECT distinct ss.spec_id, dest_job, ss.drawing_id, ss.status, ss.created_by, ss.updated_by,
ss.reference_count, ss.original_spec_id, ss.created_at, ss.updated_at, true
FROM drawing_specifications ds
JOIN specifications ss on ss.id=ds.specification_id
LEFT JOIN specifications dup on dup.spec_id=ss.spec_id and dup.job_id=dest_job
WHERE not ds.from_drawing -- it's a spec, not a drawing title
AND ds.drawing_id = any(std_drawings_to_copied)
AND dup.id is null -- prevent the duplicated of spec in the destination job

UNION

SELECT distinct ss.spec_id, dest_job, jd.id, ss.status, ss.created_by, ss.updated_by,
ss.reference_count, ss.original_spec_id, ss.created_at, ss.updated_at, true
FROM specifications ss
LEFT JOIN specifications dup on dup.spec_id=ss.spec_id and dup.job_id=dest_job
LEFT JOIN drawings jd on left(ss.spec_id, 11) = jd.drawing_id and jd.job_id = dest_job
WHERE ss.drawing_id = any(std_drawings_to_copied)
AND dup.id is null
RETURNING id
)
SELECT array_agg(id) INTO job_specs FROM inserted_rows;

IF job_specs is not null THEN -- For performance reason
-- COPY SPECIFICATION DESCRIPTIONS --
INSERT INTO specification_descriptions("language_id", "specification_id", "content", "text_content", "is_copied")
SELECT distinct sdesc.language_id, js.id, sdesc.content, sdesc.text_content, true
FROM drawing_specifications ds
JOIN specifications ss on ss.id=ds.specification_id
JOIN specification_descriptions sdesc on sdesc.specification_id=ss.id
JOIN specifications js on ss.spec_id=js.spec_id
WHERE not ds.from_drawing -- it's a spec, not a drawing title
and ds.drawing_id = any(std_drawings_to_copied)
and js.id = any(job_specs);

-- Create drawing - specifications relationship
INSERT INTO drawing_specifications("drawing_id", "specification_id", "from_drawing")
SELECT jd.id, js.id, ds.from_drawing
FROM drawing_specifications ds
JOIN drawings sd on sd.id=ds.drawing_id
JOIN drawings jd on jd.drawing_id=sd.drawing_id
JOIN specifications ss on ds.specification_id=ss.id
JOIN specifications js on ss.spec_id=js.spec_id
WHERE not ds.from_drawing
and sd.id = any(std_drawings_to_copied)
and jd.id = any(job_drawings)
and js.id = any(job_specs);
END IF;

-- COPY ASSOCIATED DETAILED DOCUMENTS --
-- Note: Do a recursive copy for documents(drawings) found
FOR r in
select ds.specification_id
from drawing_specifications ds
where ds.from_drawing and ds.drawing_id = any(std_drawings_to_copied)
LOOP
recursive_copied := true;
PERFORM f_copy_standard(r.specification_id, dest_job, user_id);
END LOOP;

IF recursive_copied THEN -- For performance reason
-- Create drawing - documents relationship
INSERT INTO drawing_specifications("drawing_id", "specification_id", "from_drawing")
SELECT jd.id, jdoc.id, true
FROM drawing_specifications ds
JOIN drawings sd on sd.id=ds.drawing_id
JOIN drawings jd on jd.drawing_id=sd.drawing_id
JOIN drawings sdoc on sdoc.id=ds.specification_id
JOIN drawings jdoc on jdoc.drawing_id=sdoc.drawing_id -- documents(drawings) that just recursive copied here above
WHERE ds.from_drawing
and sd.id = any(std_drawings_to_copied)
and jdoc.job_id=dest_job
and jd.id = any(job_drawings);
END IF;

-- COPY OPERATIONS ON DRAWINGS --
-- Note: Only copy drawing related transactions. Then create a synchronize transation as well
sync_time := clock_timestamp() AT TIME ZONE 'UTC';
INSERT INTO operations_on_drawings("job_id", "operation_id", "sequence", "drawing_id", "part_id", "key",
"part_name", "revision", "quantity", "sent_date", "addressee", "medium", "format", "internal_reason",
"raw_reason", "description", "raw_description", "created_by", "updated_by", "created_date", "updated_date",
"element_id", "created_at", "updated_at", "is_copied")
SELECT dest_job, od.operation_id, od.sequence, jd.id, od.part_id, od.key,
od.part_name, od.revision, od.quantity, od.sent_date, od.addressee, od.medium, od.format, od.internal_reason,
od.raw_reason, od.description, od.raw_description, od.created_by, od.updated_by, od.created_date, od.updated_date,
od.element_id, od.created_at, od.updated_at, true
FROM operations_on_drawings od
JOIN parameters pa on od.operation_id = pa.id
JOIN drawings jd on jd.drawing_id=left(od.part_name,11) -- od.part_name contains T001 as suffix
WHERE od.drawing_id = any(std_drawings_to_copied)
and jd.id = any(job_drawings)
and pa.parameter_id in ('CRPL', 'RVPL', 'CHPL', 'DEPL')
-- Also create SYPL transations (1658) based on CRPL
UNION ALL
SELECT dest_job, 1658, od.sequence, jd.id, od.part_id, od.key,
od.part_name, '', od.quantity, sync_time, od.addressee, od.medium, od.format, '(drawing sync)',
'(drawing sync)', 'Copy into Job','Copy into Job', user_id, user_id, sync_time, sync_time,
od.element_id, sync_time, sync_time, false
FROM operations_on_drawings od
JOIN parameters pa on od.operation_id = pa.id
JOIN drawings jd on jd.drawing_id=left(od.part_name,11) -- od.part_name contains T001 as suffix
WHERE od.drawing_id = any(std_drawings_to_copied)
and jd.id = any(job_drawings)
and pa.parameter_id in ('CRPL');
END IF; -- NOT FOUND existed job-std drawing

END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION public.f_copy_standard(integer, integer, character varying)
OWNER TO postgres;