report_penjualanjasa_by_kodejasa_v4

CREATE OR REPLACE FUNCTION public.report_penjualanjasa_by_kodejasa_v4(fromdate character varying, todate character varying, kodeahass character varying)
 RETURNS TABLE(no_kwitansi character varying, tgl_kwitansi date, noidentitascust character varying, custname character varying, alamat character varying, cwsattr text, hargasatuan double precision, qty integer, hargatotal double precision, discount double precision, dpp double precision, ppn double precision)
 LANGUAGE plpgsql
AS $function$
declare
--        startdate Date := fromdate::Date;
--    enddate timestamp := (todate::timestamp + interval '1 day - 1 second');
--        startdate Date := fromdate::Date;
--        enddate Date := todate::Date;
           startdate Date := to_date(fromdate, 'DD/MM/YYYY');--startdate::Date;
        enddate timestamp := (to_date(todate, 'DD/MM/YYYY')::timestamp + interval '1 day - 1 second');
        
        startdate2 character varying := to_char(to_date(fromdate, 'DD/MM/YYYY'), 'YYYY-MM-DD');
        enddate2 character varying := to_char(to_date(todate, 'DD/MM/YYYY'), 'YYYY-MM-DD');
       
               ppn_rate_value double precision;
      
begin
                
                SELECT ppn_rate 
            INTO ppn_rate_value 
            FROM mst_ppn 
            WHERE status = 'Active'
            AND ahm_dealer_code = :kodeahass
            ORDER BY id DESC 
            LIMIT 1;
        
        return QUERY
        
select 
                wo.invoice,
                Date(wo.modifiedat) tgl_kwitansi,
                case when wo.noktp is null then '' else wo.noktp end as noidentitascust,
                wo.customername custname,
                case when wo.lastaddress is null then '' else wo.lastaddress end as alamat,
                (select a.* from view_get_configworkshopservice_attribute_by_woservice(wo.configworkshopserviceid, wo.marketingcode, wo.ahassid) a) cwsattr,
                wo.harga hargasatuan,
                wo.qty,
                wo.total hargatotal,
                wo.discount,
--                (select ppn_result from calculate_ppn_v1(wo.harga, wo.qty::integer, wo.discount, ppn_rate_value)) as ppn,
                ((wo.qty * wo.harga) - (wo.qty * wo.discount) - (select ppn_result from calculate_ppn_v1(wo.harga, wo.qty::integer, wo.discount, ppn_rate_value))) as dpp,
                (select ppn_result from calculate_ppn_v1(wo.harga, wo.qty::integer, wo.discount, ppn_rate_value)) as ppn
                
from rpt_wo_monitoring_wo_v3 wo 
where 
        wo.createdat between startdate and enddate
        and wo.ahassid = kodeahass
        and (select a.* from view_get_configworkshopservice_attribute_by_woservice(wo.configworkshopserviceid, wo.marketingcode, wo.ahassid) a) not like '%ASS%' and (select a.* from view_get_configworkshopservice_attribute_by_woservice(wo.configworkshopserviceid, wo.marketingcode, wo.ahassid) a) not like '%CLAIM C2%'
        --and rwm.wolastknownstate = 'PAYMENT'
group by wo.invoice , wo.modifiedat , wo.noktp , wo.customername , wo.lastaddress, cwsattr, wo.harga, wo.qty, wo.total, wo.discount
order by tgl_kwitansi asc, wo.invoice asc;
-- order by wo.woid asc;
end
$function$
;