Untitled

CREATE OR REPLACE FUNCTION public.form_sa_a5_v1(nomorwo character varying)
 RETURNS TABLE(queuenumber integer, wodate date, ahassid character varying, wonomesin character varying, wonorangka character varying, nopolisi character varying, unittypename character varying, tahunperakitan integer, kmmotor integer, fuelmeter double precision, nama_pembawa text, alamat_pembawa text, kec_pembawa text, telp_pembawa text, email text, sosmed text, nama_pemilik text, alamat_pemilik text, kec_pemilik text, serial_number text, soc integer, telp_pemilik text, alasandatang character varying, relationship character varying, analisa character varying, saranmekanik character varying, sparepartdibawacustomer boolean, estimasimulai time without time zone, estimasiselesai time without time zone, totalhargajasa double precision, totalhargasparepart double precision, totaldiscountjasa double precision, totaldiscountsparepart double precision, confirmnotelp character varying, signphotosa character varying, signphotocustomer character varying, wodisclaimertype character varying, createdat timestamp without time zone, mechanicname character varying, bookingfee double precision)
 LANGUAGE plpgsql
AS $function$
begin
	RETURN QUERY
	WITH inv_part AS (
	  SELECT 
	    SUM(b.total) AS totalhargasparepart,
	    SUM(b.total_discount) AS totaldiscountsparepart,
	    b.woid
	  FROM get_part_wo_exclude_bo_hotline(nomorwo) b
	  GROUP BY b.woid
	),
        cs_totalhargajasa AS (
            SELECT 
                SUM(
                    CASE 
                        WHEN (wos.iscontractservice IS TRUE AND wos.cspackagecode IS NOT NULL) THEN 
                            (SELECT mcs.packagegroupprice / 
                                (
                                    SELECT 
                                        COUNT(DISTINCT mc2.packageid) AS package_total 
                                    FROM mst_configpackageservicepartpromo mc2
                                    INNER JOIN mst_contract_service mcs ON mc2.promoid = mcs.csid 
                                    AND mc2.source = 'CONTRACT_SERVICE'
                                    WHERE mcs.csid = tw.csid 
                                    GROUP BY mcs.csid, mcs.packagegroupprice, mcs.packagegroupfakeprice
                                )
                            FROM mst_contract_service mcs 
                            JOIN trx_wo wo ON mcs.csid = wo.csid 
                            WHERE wo.woid = nomorwo
                        )
                        ELSE wos.total 
                    END / (
				        SELECT COUNT(*) 
				        FROM mst_configpackageservicepartpromo mc 
				        WHERE packageid = wos.cspackagecode 
				        GROUP BY packageid, wos.cspackagecode
				      )
                ) AS totalhargajasa,
                wos.woid
            FROM trx_woservice wos
            INNER JOIN trx_wo tw ON wos.woid = tw.woid 
            WHERE wos.woid = nomorwo
            GROUP BY wos.woid
        )
		SELECT 
			wo.queuenumber as queuenumber,
			wo.wodate as wodate,
			wo.ahassid as ahassid,
			wo.wonomesin as wonomesin,
			wo.wonorangka as wonorangka,
			wo.nopolisi as nopolisi,
			wo.unittypename as unittypename,
			wo.tahunperakitan as tahunperakitan,
			wo.kmmotor as kmmotor,
			wo.fuelmeter as fuelmeter,
			UPPER(custattend.customername) as nama_pembawa,
			UPPER(custattend.address) as alamat_pembawa,
			UPPER(custattend.kecamatan) as kec_pembawa,
			UPPER(custattend.phone) as telp_pembawa,
			UPPER(custattend.email) as email,
			UPPER(custattend.sosmed) as sosmed,
			UPPER(wo.unitcustomername) as nama_pemilik,
			UPPER(ucust.lastaddress) as alamat_pemilik,
			UPPER(ucust.kecamatan) as kec_pemilik,
			UPPER(ucust.serialnumberev) AS serial_number,
        	ucust.socbattery AS soc,
			UPPER(wo.confirmnotelp) as telp_pemilik,
			wo.alasandatang as alasandatang,
			custattend.relationship as relationship,
			wo.analisa as analisa,
			wo.saranmekanik as saranmekanik,
			wo.sparepartdibawacustomer as sparepartdibawacustomer,
			wo.estimasimulai as estimasimulai,
			wo.estimasiselesai as estimasiselesai,
			CASE 
			    WHEN wo.csid IS NOT NULL THEN COALESCE(wo.totalhargajasa, 0)
			    ELSE (
			    	select SUM(two.total)
			    	from trx_woservice two
			    	where two.woid = wo.woid
			 	)
			END AS totalhargajasa,
			case when wo.wolastknownstate in ('PAYMENT', 'COMPLETE') then coalesce(p.totalhargasparepart, 0)
				else wo.totalhargasparepart end as totalhargasparepart,
			wo.totaldiscountjasa as totaldiscountjasa,
			case when wo.wolastknownstate in ('PAYMENT', 'COMPLETE') then coalesce(p.totaldiscountsparepart, 0)
				else wo.totaldiscountsparepart end as totaldiscountsparepart,
			wo.confirmnotelp as confirmnotelp,
			wo.signphotosa as signphotosa,
			wo.signphotocustomer as signphotocustomer,
			wo.wodisclaimertype as wodisclaimertype,
			wo.createdat as createdat,
			wo.mechanicname as mechanicname,
			CASE 
	            WHEN wo.csid is not null or wo.csid not in ('') THEN 0
	            ELSE wo.totalbookingfee
	        END AS bookingfee
			FROM trx_wo wo
			LEFT JOIN mst_customerattendee custattend ON wo.customerattendeeid = custattend.customerattendeeid
			LEFT JOIN mst_unitcustomer ucust ON wo.unitcustomerid = ucust.unitcustomerid
			LEFT JOIN inv_part p on p.woid = wo.woid
			LEFT JOIN cs_totalhargajasa ct ON wo.woid = ct.woid
			where wo.woid = nomorwo;
	end;
$function$
;
;