get_report_lcr (db ddmsh2_cbr)
unknown
mysql
10 months ago
8.5 kB
1
Indexable
CREATE OR REPLACE FUNCTION public.get_report_lcr(input_dealerid character varying)
RETURNS TABLE(unit_name character varying, tahun_motor integer, nomor_mesin character varying, nomor_rangka character varying, dealerid character varying, last_update character varying, not_checked_yet boolean, not_willing_check boolean, reason character varying, lcr_check boolean, lcr_treatment_1 boolean, lcr_treatment_2 boolean, lcr_ganti boolean, done boolean)
LANGUAGE plpgsql
AS $function$
begin
begin
CREATE TEMP table tmp_history_lcr (
unit_name varchar,
nomor_mesin varchar,
nomor_rangka varchar,
dealerid varchar,
last_update varchar,
not_checked_yet boolean,
not_willing_check boolean,
alasan varchar,
lcr_check boolean,
lcr_treatment_1 boolean,
lcr_treatment_2 boolean,
lcr_ganti boolean,
isdone boolean
) ON COMMIT DROP;
end;
raise notice 'create temp tmp_history_lcr %',now();
-- NOTES : DIKARENAKAN QUERYNYA AGAK BERAT, SAAT INI NOSIN YANG BELUM PERNAH LCR INSERT KE TABLE tmp_history_lcr JUGA.
-- TUJUANNYA UNTUK MENHINDARI SUB-QUERY UNION ALL.
-- begin
-- CREATE TEMP table lcr_not_in_history (
-- unit_name varchar,
-- nomor_mesin varchar,
-- nomor_rangka varchar,
-- dealerid varchar,
-- last_update varchar,
-- not_checked_yet boolean,
-- not_willing_check boolean,
-- alasan varchar,
-- lcr_check boolean,
-- lcr_treatment_1 boolean,
-- lcr_treatment_2 boolean,
-- lcr_ganti boolean,
-- isdone boolean
-- ) ON COMMIT DROP;
-- end;
-- raise notice 'create temp lcr_not_in_history %',now();
begin
INSERT into tmp_history_lcr (
unit_name,
nomor_mesin,
nomor_rangka,
dealerid,
last_update,
not_checked_yet,
not_willing_check,
alasan,
lcr_check,
lcr_treatment_1,
lcr_treatment_2,
lcr_ganti,
isdone
)
SELECT *
from (
WITH monitoring_unit_lcr AS (
SELECT
null as unit_name,
replace(hl.nomesin, ' ', '') AS nomor_mesin,
hl.norangka AS nomor_rangka,
hl.dealerid AS dealerid,
TO_CHAR(DATE_TRUNC('day', MAX(hl.tglvisit)), 'YYYY-MM-DD')::varchar AS last_update,
false as not_checked_yet,
false as not_willing_check,
'' AS alasan,
EXISTS (
SELECT 1
FROM trx_history_lcr thl
WHERE thl.nomesin = hl.nomesin
AND thl.kesediaan LIKE '%01%'
AND thl.dealerid = hl.dealerid
AND thl.woid != 'CANCEL'
) AS lcr_check,
EXISTS (
SELECT 1
FROM trx_history_lcr thl
WHERE thl.nomesin = hl.nomesin
AND thl.treatment = 1
AND thl.isdone
AND thl.dealerid = hl.dealerid
) AS lcr_treatment_1,
EXISTS (
SELECT 1
FROM trx_history_lcr thl
WHERE thl.nomesin = hl.nomesin
AND thl.treatment = 2
AND thl.isdone
AND thl.dealerid = hl.dealerid
) AS lcr_treatment_2,
EXISTS (
SELECT 1
FROM trx_history_lcr thl
WHERE thl.nomesin = hl.nomesin
AND thl.hasilpengecekan LIKE '%02%'
AND thl.isdone = true
AND thl.dealerid = hl.dealerid
) AS lcr_ganti,
(
EXISTS (
SELECT 1
FROM trx_history_lcr thl
WHERE thl.nomesin = hl.nomesin
AND thl.treatment = 1
AND thl.isdone
AND thl.dealerid = hl.dealerid
)
OR EXISTS (
SELECT 1
FROM trx_history_lcr thl
WHERE thl.nomesin = hl.nomesin
AND thl.treatment = 2
AND thl.isdone
AND thl.dealerid = hl.dealerid
)
OR EXISTS (
SELECT 1
FROM trx_history_lcr thl
WHERE thl.nomesin = hl.nomesin
AND thl.hasilpengecekan LIKE '%02%'
AND thl.isdone = true
AND thl.dealerid = hl.dealerid
)
) AS isdone
FROM
trx_history_lcr hl
WHERE
alasan IS NULL
AND
case when input_dealerid = 'ALL' then 1 = 1
else hl.dealerid = input_dealerid end
GROUP BY
nomesin, norangka, hl.dealerid
),
tmp_tdk_bersedia AS (
SELECT
null as unit_name,
replace(thl2.nomesin, ' ', '') AS nomor_mesin,
thl2.norangka AS nomor_rangka,
thl2.dealerid AS dealerid,
TO_CHAR(DATE_TRUNC('day', thl2.tglvisit), 'YYYY-MM-DD')::varchar AS last_update,
false as not_checked_yet,
true as not_willing_check,
thl2.alasan AS alasan,
false AS lcr_check,
false AS lcr_treatment_1,
false AS lcr_treatment_2,
false AS lcr_ganti,
false AS isdone
FROM
trx_history_lcr thl2
WHERE
thl2.alasan IS NOT null
AND
case when input_dealerid = 'ALL' then 1 = 1
else thl2.dealerid = input_dealerid end
GROUP BY
thl2.nomesin, thl2.norangka, thl2.dealerid, thl2.alasan, thl2.tglvisit
)
SELECT *
FROM monitoring_unit_lcr
UNION ALL
SELECT *
FROM tmp_tdk_bersedia
order by nomor_rangka
) AS merged_data;
end;
-- raise notice 'insert into tmp_history_lcr %',now();
-- TETAP INSERT KE tmp_history_lcr, UNTUK MENGHINDARI UNION ALL DALAM SUB-QUERY
begin
if input_dealerid = 'ALL' then
INSERT into tmp_history_lcr (
unit_name,
nomor_mesin,
nomor_rangka,
dealerid,
last_update,
not_checked_yet,
not_willing_check,
alasan,
lcr_check,
lcr_treatment_1,
lcr_treatment_2,
lcr_ganti,
isdone
)
select
null as unit_name,
enginenumber as nomor_mesin,
framenumber,
null,
'',
true,
false,
null,
false,
false,
false,
false,
false
from mst_lcr ml
WHERE (ml.framenumber) NOT IN (SELECT t1.nomor_rangka FROM tmp_history_lcr t1);
raise notice 'insert into lcr_not_in_history %',now();
end if;
end;
RETURN QUERY
select
-- (
-- select mc.unittypename
-- from
-- (
-- select * from mst_unitcustomer mu where replace(mu.machinenumber, ' ', '') = replace(th.nomor_mesin, ' ', '')
-- ) as unit
-- join mst_unittypemarketingcode mc on mc.unittypeid = unit.unittypeid limit 1
-- ) as unit_name,
mc.unittypename as unit_name,
mu.tahunperakitan as tahun_motor,
th.nomor_mesin,
th.nomor_rangka,
th.dealerid,
th.last_update,
th.not_checked_yet as not_checked_yet,
th.not_willing_check as not_willing_check,
th.alasan as reason,
th.lcr_check,
th.lcr_treatment_1,
th.lcr_treatment_2,
th.lcr_ganti,
th.isdone as done
from
tmp_history_lcr th
left join mst_unitcustomer mu on replace(mu.machinenumber, ' ', '') = replace(th.nomor_mesin, ' ', '')
left join mst_unittypemarketingcode mc on mc.unittypeid = mu.unittypeid
order by
th.dealerid,
th.nomor_mesin,
th.last_update;
-- raise notice 'select all after insert %',now();
END;
$function$
;
Editor is loading...
Leave a Comment