Untitled

CREATE OR REPLACE FUNCTION public.get_report_lcr(input_dealerid character varying)
 RETURNS TABLE(unit_name character varying, 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;
	
		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;
		
		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%'
				        ) AS lcr_check,
				        EXISTS (
				            SELECT 1 
				            FROM trx_history_lcr thl 
				            WHERE thl.nomesin = hl.nomesin
				            AND thl.treatment = 1
				            AND thl.isdone
				        ) AS lcr_treatment_1,
				        EXISTS (
				            SELECT 1 
				            FROM trx_history_lcr thl 
				            WHERE thl.nomesin = hl.nomesin 
				            AND thl.treatment = 2
				            AND thl.isdone 
				        ) 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
				        ) AS lcr_ganti,
				        true AS isdone
				    FROM 
				        trx_history_lcr hl
				    WHERE 
				        alasan IS NULL 
				    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 
				    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;
	
		begin
			INSERT into lcr_not_in_history (
				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);
		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,
			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 
		(select * from tmp_history_lcr
		union all
		select * from lcr_not_in_history) as 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 
			CASE 
		        WHEN th.last_update IS NOT NULL AND COALESCE(LENGTH(th.last_update), 0) > 0 THEN 0
		        ELSE 1
		    END,
		    th.nomor_mesin,
		    th.last_update;
		
	END;


$function$
;