IT

##############################################################

###     the longest sql queries I ever made

##############################################################



    select __is_kegiatan.description as nmitem, table_data.*
    from __is_kegiatan 
    join 
    (
        select table_percentage.kdgiat, table_percentage.kdoutput, table_percentage.kdsoutput, 
        ((percentage_kdsoutput_by_kdoutput*kemenkeu_jumlah_perkdoutput)/100) as jumlah_pagu,
        ((percentage_kdsoutput_by_kdoutput*kemenkeu_realisasi_perkdoutput)/100) as jumlah_realisasi
        from (
            select table_detail.kdgiat, table_detail.kdoutput, table_detail.kdsoutput, 
            ((sum_perkdsoutput/sum_perkdoutput)*100) as percentage_kdsoutput_by_kdoutput
            from (
                SELECT items.kdgiat, items.kdoutput, items.kdsoutput, sum(jumlah) as sum_perkdsoutput
                FROM items
                join subkomponens
                    on items.thang = subkomponens.thang
                    and items.kdgiat = subkomponens.kdgiat
                    and items.kdoutput = subkomponens.kdoutput
                    and items.kdsoutput = subkomponens.kdsoutput
                    and items.kdsatker = subkomponens.kdsatker
                    and items.kddekon = subkomponens.kddekon
                    and items.kdlokasi = subkomponens.kdlokasi
                where items.thang='".$thang."' and subkomponens.thang='".$thang."'  
                    and items.kdsatker='".$kdsatker."' and items.kdlokasi='".$user->kdlokasi."' 
                    and subkomponens.kdsatker='".$kdsatker."' and subkomponens.kdlokasi='".$user->kdlokasi."' 
                    ".$q_kab."
                group by items.kdgiat, items.kdoutput, items.kdsoutput
            ) as table_detail
            join 
            (
                SELECT items.kdgiat, items.kdoutput, sum(jumlah) as sum_perkdoutput
                FROM items
                where items.thang='".$thang."' 
                group by items.kdgiat, items.kdoutput
            ) as table_header
            on 
            table_detail.kdgiat=table_header.kdgiat and table_detail.kdoutput=table_header.kdoutput
        ) as table_percentage
        join 
        (
            SELECT keuangan_kemenkeu.kdgiat, keuangan_kemenkeu.kdoutput, sum(jumlah) as kemenkeu_jumlah_perkdoutput, 
            sum(realisasi) as kemenkeu_realisasi_perkdoutput
            FROM keuangan_kemenkeu
            join subkomponens
                on keuangan_kemenkeu.thang = subkomponens.thang
                and keuangan_kemenkeu.kdgiat = subkomponens.kdgiat
                and keuangan_kemenkeu.kdoutput = subkomponens.kdoutput
                and keuangan_kemenkeu.kdsatker = subkomponens.kdsatker
            where kemenkeu_realisasi_date = '".$this->__d."'  
                and keuangan_kemenkeu.thang='".$thang."' and subkomponens.thang='".$thang."'  
                and keuangan_kemenkeu.kdsatker='".$kdsatker."' and subkomponens.kdsatker='".$kdsatker."' 
                and subkomponens.kdlokasi='".$user->kdlokasi."'  
                ".$q_kab."
            group by keuangan_kemenkeu.kdgiat, keuangan_kemenkeu.kdoutput
        ) as table_realisasi
        on 
        table_percentage.kdgiat=table_realisasi.kdgiat
        and table_percentage.kdoutput=table_realisasi.kdoutput
    ) as table_data
    on __is_kegiatan.kdgiat=table_data.kdgiat
    and __is_kegiatan.kdoutput=table_data.kdoutput
    and __is_kegiatan.kdsoutput=table_data.kdsoutput
    where __is_kegiatan.is_kegiatan_utama='yes';

-- ------------------------------------------------------------------------------------------------------------------------------

    select table_keuangan.*, fisik_target,fisik_realisasi,fisik_percentage,fisik_satkeg
    from 
    (
        select __is_kegiatan.description as nmitem, table_data.*, 
            ((anggaran_realisasi/anggaran_pagu)*100) as anggaran_percentage
        from __is_kegiatan 
        join ( 
            select table_percentage.kdgiat, table_percentage.kdoutput, table_percentage.kdsoutput, 
                ((percentage_kdsoutput_by_kdoutput*kemenkeu_jumlah_perkdoutput)/100) as anggaran_pagu, 
                ((percentage_kdsoutput_by_kdoutput*kemenkeu_realisasi_perkdoutput)/100) as anggaran_realisasi
                from ( 
                    select table_detail.kdgiat, table_detail.kdoutput, table_detail.kdsoutput, 
                    ((sum_perkdsoutput/sum_perkdoutput)*100) as percentage_kdsoutput_by_kdoutput 
                    from ( 
                        SELECT items.kdgiat, items.kdoutput, items.kdsoutput, sum(jumlah) as sum_perkdsoutput 
                        FROM items
                        join subkomponens
                            on items.thang = subkomponens.thang
                            and items.kdgiat = subkomponens.kdgiat
                            and items.kdoutput = subkomponens.kdoutput
                            and items.kdsoutput = subkomponens.kdsoutput
                            and items.kdsatker = subkomponens.kdsatker
                            and items.kddekon = subkomponens.kddekon
                            and items.kdlokasi = subkomponens.kdlokasi
                        where items.thang='".$thang."' and subkomponens.thang='".$thang."'  
                            and items.kdsatker='".$kdsatker."' and items.kdlokasi='".$user->kdlokasi."' 
                            and subkomponens.kdsatker='".$kdsatker."' and subkomponens.kdlokasi='".$user->kdlokasi."'  
                            ".$q_kab_on_subkomponen."
                        group by items.kdgiat, items.kdoutput, items.kdsoutput 
                    ) as table_detail 
                    join ( 
                        SELECT items.kdgiat, items.kdoutput, sum(jumlah) as sum_perkdoutput 
                        FROM 
                        items
                        join subkomponens
                            on items.thang = subkomponens.thang
                            and items.kdgiat = subkomponens.kdgiat
                            and items.kdoutput = subkomponens.kdoutput
                            and items.kdsoutput = subkomponens.kdsoutput
                            and items.kdsatker = subkomponens.kdsatker
                            and items.kddekon = subkomponens.kddekon
                            and items.kdlokasi = subkomponens.kdlokasi
                        where items.thang='".$thang."' and subkomponens.thang='".$thang."'  
                            and items.kdsatker='".$kdsatker."' and items.kdlokasi='".$user->kdlokasi."' 
                            and subkomponens.kdsatker='".$kdsatker."' and subkomponens.kdlokasi='".$user->kdlokasi."'  
                            ".$q_kab_on_subkomponen."
                        group by items.kdgiat, items.kdoutput 
                    ) as table_header 
                    on table_detail.kdgiat=table_header.kdgiat and table_detail.kdoutput=table_header.kdoutput 
                ) as table_percentage 
                join 
                ( 
                    SELECT keuangan_kemenkeu.kdgiat, keuangan_kemenkeu.kdoutput, 
                    sum(jumlah) as kemenkeu_jumlah_perkdoutput, sum(realisasi) as kemenkeu_realisasi_perkdoutput 
                    FROM keuangan_kemenkeu
                    join subkomponens
                        on keuangan_kemenkeu.thang = subkomponens.thang
                        and keuangan_kemenkeu.kdgiat = subkomponens.kdgiat
                        and keuangan_kemenkeu.kdoutput = subkomponens.kdoutput
                        and keuangan_kemenkeu.kdsatker = subkomponens.kdsatker
                    where kemenkeu_realisasi_date = '".$this->__d."'  
                        and keuangan_kemenkeu.thang='".$thang."' and subkomponens.thang='".$thang."'  
                        and keuangan_kemenkeu.kdsatker='".$kdsatker."' and subkomponens.kdsatker='229108' 
                        and subkomponens.kdlokasi='".$user->kdlokasi."'  
                        ".$q_kab_on_subkomponen."
                    group by keuangan_kemenkeu.kdgiat, keuangan_kemenkeu.kdoutput 
                ) as table_realisasi 
                on table_percentage.kdgiat=table_realisasi.kdgiat 
                and table_percentage.kdoutput=table_realisasi.kdoutput 
        ) as table_data 
        on __is_kegiatan.kdgiat=table_data.kdgiat 
        and __is_kegiatan.kdoutput=table_data.kdoutput and __is_kegiatan.kdsoutput=table_data.kdsoutput 
        where __is_kegiatan.is_kegiatan_utama='yes'
    ) as  table_keuangan
    left join 
    (
        select description as nmitem, table_data.kdgiat, table_data.kdoutput, table_data.kdsoutput, 
        table_data.fisik_target, table_data.fisik_realisasi, table_data.fisik_percentage, table_data.fisik_satkeg
        from ( 
            SELECT __is_kegiatan.kdgiat, __is_kegiatan.kdoutput, __is_kegiatan.kdsoutput, 
                description, acronym, satkeg as fisik_satkeg, 
                sum(target) as fisik_target, avg(realisasi) as fisik_realisasi, avg(realisasi) as fisik_percentage
            FROM __is_kegiatan 
                join fisiks on __is_kegiatan.kdgiat=fisiks.kdgiat and __is_kegiatan.kdoutput=fisiks.kdoutput 
                    and __is_kegiatan.kdsoutput=fisiks.kdsoutput 
                join fisik_poktan on fisik_poktan.fisik_id=fisiks.id 
                    WHERE is_kegiatan_utama = 'yes' and fisiks.thang='".$thang."'
                    and fisiks.kdsatker='".$kdsatker."' 
                    and fisiks.kdlokasi='".$user->kdlokasi."'  
                    ".$q_kab_on_fisik."
            group by __is_kegiatan.kdgiat, __is_kegiatan.kdoutput, __is_kegiatan.kdsoutput, description, acronym, satkeg 
        ) as table_data
    ) as table_fisik
    on table_fisik.kdgiat=table_keuangan.kdgiat
    and table_fisik.kdoutput=table_keuangan.kdoutput
    and table_fisik.kdsoutput=table_keuangan.kdsoutput;

-- ------------------------------------------------------------------------------------------------------------------------------

    select nmlokasi, anggaran_pagu, anggaran_realisasi, 
        ((anggaran_realisasi/anggaran_pagu)*100) as anggaran_percentage,
        table_fisik.fisik_target, table_fisik.fisik_realisasi, table_fisik.fisik_percentage 
    from
    lokasis 
    left join 
    (
        select table_percentage.kdsatker, table_percentage.kdlokasi, table_percentage.kdgiat, 
            table_percentage.kdoutput, table_percentage.kdsoutput,
            ((percentage_kdsoutput_by_kdoutput*kemenkeu_jumlah_perkdoutput)/100) as anggaran_pagu, 
            ((percentage_kdsoutput_by_kdoutput*kemenkeu_realisasi_perkdoutput)/100) as anggaran_realisasi
        from 
        (
            select table_detail.kdsatker, table_detail.kdlokasi, table_detail.kdgiat, table_detail.kdoutput, 
            table_detail.kdsoutput, 
            ((sum_perkdsoutput/sum_perkdoutput)*100) as percentage_kdsoutput_by_kdoutput
            from (
                SELECT items.kdsatker, items.kdlokasi, items.kdgiat, items.kdoutput, items.kdsoutput, 
                    sum(jumlah) as sum_perkdsoutput
                FROM items
                where items.thang='".$thang."' and items.kdgiat='".$kdgiat."' 
                    and items.kdoutput='".$kdoutput."' and items.kdsoutput='".$kdsoutput."'
                group by items.kdsatker, items.kdlokasi, items.kdgiat, items.kdoutput, items.kdsoutput
            ) as table_detail
            join 
            (
                SELECT items.kdsatker, items.kdlokasi, items.kdgiat, items.kdoutput, sum(jumlah) as sum_perkdoutput
                FROM items
                where items.thang='".$thang."' and items.kdgiat='".$kdgiat."' and items.kdoutput='".$kdoutput."' 
                group by items.kdsatker, items.kdlokasi, items.kdgiat, items.kdoutput
            ) as table_header
            on 
            table_detail.kdsatker=table_header.kdsatker and 
            table_detail.kdlokasi=table_header.kdlokasi and 
            table_detail.kdgiat=table_header.kdgiat and 
            table_detail.kdoutput=table_header.kdoutput
        ) as table_percentage
        join
        (
            SELECT keuangan_kemenkeu.kdsatker, keuangan_kemenkeu.kdgiat, keuangan_kemenkeu.kdoutput, 
               sum(jumlah) as kemenkeu_jumlah_perkdoutput, 
               sum(realisasi) as kemenkeu_realisasi_perkdoutput
            FROM keuangan_kemenkeu
            where kemenkeu_realisasi_date = '".$this->__d."' 
                and keuangan_kemenkeu.thang='".$thang."' 
                and keuangan_kemenkeu.kdgiat='".$kdgiat."' and keuangan_kemenkeu.kdoutput='".$kdoutput."' 
            group by keuangan_kemenkeu.kdsatker, keuangan_kemenkeu.kdgiat, keuangan_kemenkeu.kdoutput
        )
        as table_realisasi
        on table_percentage.kdsatker=table_realisasi.kdsatker
        and table_percentage.kdgiat=table_realisasi.kdgiat
        and table_percentage.kdoutput=table_realisasi.kdoutput
    ) 
    as table_keuangan
    on lokasis.kdlokasi=table_keuangan.kdlokasi
    left join 
    (
        select kdsatker, kdlokasi, table_data.kdgiat, table_data.kdoutput, table_data.kdsoutput, 
        table_data.fisik_target, table_data.fisik_realisasi, table_data.fisik_percentage, table_data.fisik_satkeg 
        from ( 
            SELECT fisiks.kdsatker, fisiks.kdlokasi, __is_kegiatan.kdgiat, __is_kegiatan.kdoutput, __is_kegiatan.kdsoutput, 
                description, acronym, satkeg as fisik_satkeg, 
                sum(target) as fisik_target, avg(realisasi) as fisik_realisasi, avg(realisasi) as fisik_percentage
            FROM __is_kegiatan 
                join fisiks on __is_kegiatan.kdgiat=fisiks.kdgiat and __is_kegiatan.kdoutput=fisiks.kdoutput 
                    and __is_kegiatan.kdsoutput=fisiks.kdsoutput 
                join fisik_poktan on fisik_poktan.fisik_id=fisiks.id 
                    WHERE is_kegiatan_utama = 'yes' and fisiks.thang='".$thang."' 
                      and fisiks.kdgiat='".$kdgiat."' and fisiks.kdoutput='".$kdoutput."' 
                      and fisiks.kdsoutput='".$kdsoutput."'
            group by fisiks.kdsatker, fisiks.kdlokasi, 
            __is_kegiatan.kdgiat, __is_kegiatan.kdoutput, __is_kegiatan.kdsoutput, description, acronym, satkeg 
        ) as table_data
    )
    as table_fisik
    on table_keuangan.kdsatker=table_fisik.kdsatker
    and table_keuangan.kdlokasi=table_fisik.kdlokasi
    and table_keuangan.kdgiat=table_fisik.kdgiat
    and table_keuangan.kdoutput=table_fisik.kdoutput
    and table_keuangan.kdsoutput=table_fisik.kdsoutput                
    ".$q_prov."
    and table_keuangan.kdgiat='".$kdgiat."'
    and table_keuangan.kdoutput='".$kdoutput."'
    and table_keuangan.kdsoutput='".$kdsoutput."'
    order by nmlokasi asc;

Advertisements