CREATE OR REPLACE FUNCTION fd_bunsho_file.get_kanri_joukyou_count()
 RETURNS TABLE(kanri_no numeric, group_by character varying[], cell_no character varying, cnt integer)
 LANGUAGE plpgsql
AS $function$
DECLARE
    count_rec record;
    column_rec record;
    column_map hstore;
    column_name varchar;
begin
    CREATE TEMP TABLE result_table (
        kanri_no numeric,
        group_by varchar[],
        cell_no VARCHAR,
        cnt INT
    ) on commit drop;

    CREATE TEMP TABLE IF NOT EXISTS temp_table_kanri_joukyou on commit drop as
        select
            WM91.kanri_jo_no, WM91.taisyou_nendo, WM91.file_name,
            string_to_array(coalesce(WM91.report_select, ''), ',')::integer[] report_part_slected,
            string_to_array(coalesce(WM91.bos_jo_cd, ''), ',')::integer[] bos_jo_cd,
            string_to_array(coalesce(WM91.bos_jo_cd_2, ''), ',')::integer[] bos_jo_cd_2,
            coalesce(M82.KA_FULL_NM, '') KA_FULL_NM, -- M82.BUNRI_GRP_CD,
            T42.bos_nm_no,
--            T42.kan_ka_no, T42.kan_ka_nm,
            EXTRACT(YEAR FROM T42.MOTO_SAKU_YMD) - (case when EXTRACT(MONTH from T42.MOTO_SAKU_YMD) < 4 then 1 else 0 end) MOTO_SAKU_NENDO,
            EXTRACT(YEAR FROM T42.HOZON_MAN_YMD) - (case when EXTRACT(MONTH from T42.HOZON_MAN_YMD) < 4 then 1 else 0 end) HOZON_MAN_NENDO,
            EXTRACT(YEAR FROM T476.HOZON_MAN_YMD) - (case when EXTRACT(MONTH from T476.HOZON_MAN_YMD) < 4 then 1 else 0 end) OLD_HOZON_MAN_NENDO,
            T42.HOZON_MAN_YMD,
            T476.HOZON_MAN_YMD OLD_HOZON_MAN_YMD,
            T42.BOS_HZN_KIKAN_CD in (1001, 999, 1000) as SPECIAL_HZN_KIKAN,
            (T42.BOS_HZN_KIKAN_CD is null or T42.BOS_HZN_KIKAN_CD not in (0, 1002) or coalesce(EXTRACT(YEAR FROM AGE(T42.HOZON_MAN_YMD + INTERVAL '1 DAY' , T42.KISANBI_YMD)), 1) > 0) as ONE_YEAR_ABOVE,
            string_to_array(T42.bos_bai_shub_txt, '¥') bos_bai_shub_txt,
            string_to_array(T42.bos_bai_shub_cd, '/') bos_bai_shub_cd,
            string_to_array(t42.bos_hzn_basho_cd, '/') bos_hzn_basho_cd,
            coalesce(M13.hzn_ki_man_unm, '') BOS_HZN_KI_MAN_TXT,
            T42.administration_type,
            T42.rs_val_20,T42.rs_val_21,
            substring(T42.HZN_KIKAN_TOTAL, 1, 4)::int HZN_KIKAN_TOTAL_YEAR,
            T47.ENCHO_CD,
            coalesce(T47.haien_riyu, '') haien_riyu,
            string_to_array(T47.HAIEN_RIYU_GAITO_RIYU, ',', '"') HAIEN_RIYU_GAITO_RIYU,
            string_to_array(T47.HAIEN_RIYU_GAITO_RIYU, ',', '"') && ARRAY['"5"','"6"','"7"','"8"','"9"'] HAIEN_RIYU_5_TO_9,
            (case
                when T43.SHINSA_STAT = 0 then 1
                when T43.SHINSA_STAT = 3 then 2
                when T47.HAIEN_IRAI_STS = 6 and T47.HAIEN_IRAI_CD = 3 and T47.NAIKAKU_IRAI_STS = 0 then 3
                when T47.HAIEN_IRAI_STS = 1 and T47.HAIEN_IRAI_CD = 3 and T47.NAIKAKU_IRAI_STS = 0 then 4
                when T47.HAIEN_IRAI_STS = 2 and T47.HAIEN_IRAI_CD = 3 and T47.NAIKAKU_IRAI_STS = 0 then 5
                when T47.HAIEN_IRAI_STS = 2 and T47.HAIEN_IRAI_CD = 3 and T47.NAIKAKU_IRAI_STS = 1 and T471.HAIKI_STS = 0 then 6
                when T47.HAIEN_IRAI_STS = 2 and T47.HAIEN_IRAI_CD = 3 and T47.NAIKAKU_IRAI_STS = 2 and T471.HAIKI_STS = 0 then 7
                when T47.HAIEN_IRAI_STS = 2 and T47.HAIEN_IRAI_CD = 3 and T47.NAIKAKU_IRAI_STS = 3 and T471.HAIKI_STS = 0 then 8
                when T47.HAIEN_IRAI_STS = 2 and T47.HAIEN_IRAI_CD = 3 and T47.NAIKAKU_IRAI_STS = 4 and T471.HAIKI_STS = 0 then 9
                when T47.HAIEN_IRAI_STS = 4 and T47.HAIEN_IRAI_CD = 3 and T47.NAIKAKU_IRAI_STS = 0 then 11
                when T47.HAIEN_IRAI_STS = 6 and T47.HAIEN_IRAI_CD = 4 and T47.KOUBUNSYOKAN_IRAI_STS = 0 then 12
                when T47.HAIEN_IRAI_STS = 1 and T47.HAIEN_IRAI_CD = 4 and T47.KOUBUNSYOKAN_IRAI_STS = 0 then 13
                when T47.HAIEN_IRAI_STS = 2 and T47.HAIEN_IRAI_CD = 4 and T47.KOUBUNSYOKAN_IRAI_STS = 0 then 14
                when T47.HAIEN_IRAI_STS = 2 and T47.HAIEN_IRAI_CD = 4 and T47.KOUBUNSYOKAN_IRAI_STS = 1 and T472.IKAN_STS = 0 then 15
                when T47.HAIEN_IRAI_STS = 2 and T47.HAIEN_IRAI_CD = 4 and T47.KOUBUNSYOKAN_IRAI_STS = 2 and T472.IKAN_STS = 0 then 16
                when T47.HAIEN_IRAI_STS = 2 and T47.HAIEN_IRAI_CD = 4 and T47.KOUBUNSYOKAN_IRAI_STS = 3 and T472.IKAN_STS = 0 then 17
                when T47.HAIEN_IRAI_STS = 2 and T47.HAIEN_IRAI_CD = 4 and T47.KOUBUNSYOKAN_IRAI_STS = 4 then 18
                when T47.HAIEN_IRAI_STS = 4 and T47.HAIEN_IRAI_CD = 4 and T47.KOUBUNSYOKAN_IRAI_STS = 0 then 19
                when T47.HAIEN_IRAI_STS = 6 and T47.HAIEN_IRAI_CD = 2 then 20
                when T47.HAIEN_IRAI_STS = 1 and T47.HAIEN_IRAI_CD = 2 then 21
                when T47.HAIEN_IRAI_STS = 2 and T47.HAIEN_IRAI_CD = 2 then 22
                when T47.HAIEN_IRAI_STS = 4 and T47.HAIEN_IRAI_CD = 2 then 23
                when T46.HIKI_IRAI_STS = 6 then 24
                when T46.HIKI_IRAI_STS = 1 then 25
                when T46.HIKI_IRAI_STS = 2 then 26
                when T46.HIKI_IRAI_STS = 4 then 27
                when T46.HIKI_IRAI_STS = 3 then 44
                when T45.KASHI_STATUS = 1 then 28
                when T45.KASHI_STATUS = 2 then 29
                when T45.KASHI_STATUS = 4 then 30
                when T45.KASHI_STATUS = 3 then 31
                when T42.NAIKAKU_STATUS = 0 and T48.RS_STATUS = 1 and T48.SAISIN_FLG = 1 then 32
                when T42.NAIKAKU_STATUS = 0 and T48.RS_STATUS = 2 and T48.SAISIN_FLG = 1 then 33
                when T42.NAIKAKU_STATUS = 0 and T42.NAIKAKU_KAKUNIN_FLG = 1 and T48.RS_STATUS = 3 and T48.SAISIN_FLG = 1 then 34
                when T42.NAIKAKU_STATUS = 1 and T48.RS_STATUS = 3 and T48.NAIKAKU_STATUS = 1 and T48.SAISIN_FLG = 1 then 35
                when T42.NAIKAKU_STATUS = 1 and T48.RS_STATUS = 3 and T48.NAIKAKU_STATUS = 2 and T48.SAISIN_FLG = 1 then 36
                when T42.NAIKAKU_STATUS = 1 and T48.RS_STATUS = 3 and T48.NAIKAKU_STATUS = 3 and T48.SAISIN_FLG = 1 then 37
                when T42.NAIKAKU_STATUS = 1 and T48.RS_STATUS = 3 and T48.NAIKAKU_STATUS = 4 and T48.SAISIN_FLG = 1 then 38
                when T42.NAIKAKU_STATUS = 0 and T42.NAIKAKU_KAKUNIN_FLG != 2 and T48.RS_STATUS = -1 and T48.SAISIN_FLG = 1 then 39
                when T42.HIKI_HAI_STS = 2 and T42.MUKOU_BOS_FLG = '1' then 40
                when T42.HIKI_HAI_STS = 3 and T42.MUKOU_BOS_FLG = '1' then 41
                when T47.HAIEN_IRAI_STS = 2 and T47.HAIEN_IRAI_CD = 3 and T47.NAIKAKU_IRAI_STS = 5 then 10
                when T422.BUN_TOU_FLG = 1 and T42.MUKOU_BOS_FLG = '1' then 42
                when T422.BUN_TOU_FLG = 2 and T42.MUKOU_BOS_FLG = '1' then 43
                when T42.NAIKAKU_STATUS = 2 and T48.RS_STATUS = 1 and T48.SAISIN_FLG = 1 then 32
                when T42.NAIKAKU_STATUS = 2 and T48.RS_STATUS = 2 and T48.SAISIN_FLG = 1 then 33
                when T42.NAIKAKU_STATUS = 2 and T42.NAIKAKU_KAKUNIN_FLG = 3 and T48.RS_STATUS = 3 and T48.SAISIN_FLG = 1 then 34
                when T43.SHINSA_STAT = 10 then 0
                when T42.NAIKAKU_STATUS = 1 and T42.NAIKAKU_KAKUNIN_FLG != 2 and T48.RS_STATUS = 3 and T48.NAIKAKU_STATUS = 5 and T48.SAISIN_FLG = 1 then -1
                when T42.MUKOU_BOS_FLG = '1' then -1
                when T43.SHINSA_STAT = 10 and T42.NAIKAKU_STATUS = 2 and T42.NAIKAKU_KAKUNIN_FLG = 2 and T48.RS_STATUS = 0 then 0
                when T43.SHINSA_STAT = 10 and T42.NAIKAKU_STATUS = 2 and T42.NAIKAKU_KAKUNIN_FLG = 3 and T48.RS_STATUS = 0 then 0
                when T43.SHINSA_STAT = 10 and T42.NAIKAKU_STATUS = 2 and T42.NAIKAKU_KAKUNIN_FLG = 0 and T48.RS_STATUS = 0 then 0
                else 0
            end) as STATUS
        FROM fd_bunsho_file.wm91_kanri_dl_setting WM91
        LEFT JOIN wm911_kanri_dl_setting_busho WM911 ON
            WM91.kanri_jo_no = WM911.kanri_jo_no
        left join M82_KA M82 on
            M82.KA_NO = WM911.busho_code
        LEFT JOIN fd_bunsho_file.T42_BOS_NM T42 ON
            T42.KAN_KA_NO = WM911.busho_code
            and (T42.MUKOU_BOS_FLG = '0' or (T42.MUKOU_BOS_FLG = '1' and HIKI_HAI_STS in (2, 3)))
        LEFT JOIN M13_HZN_KIKAN_MAN M13 ON
            T42.BOS_HZN_KI_MAN_CD = M13.HZN_KI_MAN_CD
		LEFT JOIN (
			select distinct on (bosb_no) * 
			from t47_haien_shinsa
			order by bosb_no, rireki_no desc
		) t47 on T42.bos_nm_no = t47.bosb_no
        LEFT JOIN T471_HAIKI_SHINSA_NAIKAKU T471 ON
            T471.SHIKIBETSU_NO = T47.SHINSA_SHIKIBETSU_NO
        LEFT JOIN T472_KOUBUNSYOKAN T472 ON
            T47.SHINSA_SHIKIBETSU_NO = T472.SHIKIBETSU_NO
        LEFT JOIN T43_BOS_SHIN T43 on
            T43.BOS_NM_NO = T42.BOS_NM_NO and T43.SAISIN_FLG = 1
        LEFT JOIN T46_HIKI_SHINSA T46 on
            T46.BOSB_NO = T42.BOS_NM_NO
        left join (
            select
                distinct on (BOS_NM_NO)
                BOS_NM_NO,
                KASHI_STATUS,
                BOS_KASHI_NO
            from
                T45_BOS_KASHI_KANRI
            order by
                BOS_NM_NO,
                BOS_KASHI_NO desc) T45 on
            T42.BOS_NM_NO = T45.BOS_NM_NO
        LEFT JOIN T48_RS_SHINSA T48 on
            T42.BOS_NM_NO = T48.BOS_NM_NO
            and T48.SAISIN_FLG = 1
        left join (
            select
                distinct on (MOTO_BOS_NM_NO)
                MOTO_BOS_NM_NO,
                BUN_TOU_FLG,
                BUN_TOU_YMDHMS
            from
                T422_BUN_TOU_RIREKI
            order by
                MOTO_BOS_NM_NO,
                BUN_TOU_YMDHMS desc) T422 on
            T422.MOTO_BOS_NM_NO = t42.BOS_NM_NO
        left join T476_ENCHOU_RIREKI T476 on
            T476.BOS_NM_NO = T42.BOS_NM_NO
            and T476.RIREKI_NO = 0
        where wm91.status = 0
        ;

    for count_rec in
        select
            kanri_jo_no,
            count(distinct bos_nm_no) filter (where MOTO_SAKU_NENDO <= 1952 and ONE_YEAR_ABOVE) as C04,
            count(distinct bos_nm_no) filter (where MOTO_SAKU_NENDO <= TAISYOU_NENDO and ONE_YEAR_ABOVE and BOS_HZN_KI_MAN_TXT = '移管') as C05,
            count(distinct bos_nm_no) filter (where MOTO_SAKU_NENDO <= TAISYOU_NENDO and ONE_YEAR_ABOVE and BOS_HZN_KI_MAN_TXT = '廃棄') as C06,
            count(distinct bos_nm_no) filter (where MOTO_SAKU_NENDO <= TAISYOU_NENDO and ONE_YEAR_ABOVE and BOS_HZN_KI_MAN_TXT in ('移管', '廃棄') and administration_type = 1) as C07,
            count(distinct bos_nm_no) filter (where MOTO_SAKU_NENDO <= TAISYOU_NENDO and ONE_YEAR_ABOVE and BOS_HZN_KI_MAN_TXT not in ('移管', '廃棄') and administration_type = 1) as C08,
            count(distinct bos_nm_no) filter (where MOTO_SAKU_NENDO <= TAISYOU_NENDO and ONE_YEAR_ABOVE and BOS_HZN_KI_MAN_TXT not in ('移管', '廃棄') and administration_type = 1 and HOZON_MAN_YMD is null) as C09,
            count(distinct bos_nm_no) filter (where MOTO_SAKU_NENDO <= TAISYOU_NENDO and SPECIAL_HZN_KIKAN) as C10,
            count(distinct bos_nm_no) filter (where OLD_HOZON_MAN_NENDO = TAISYOU_NENDO and ENCHO_CD is not null and '"1"' = any(HAIEN_RIYU_GAITO_RIYU)) as C12,
            count(distinct bos_nm_no) filter (where OLD_HOZON_MAN_NENDO = TAISYOU_NENDO and ENCHO_CD is not null and '"2"' = any(HAIEN_RIYU_GAITO_RIYU)) as C13,
            count(distinct bos_nm_no) filter (where OLD_HOZON_MAN_NENDO = TAISYOU_NENDO and ENCHO_CD is not null and '"3"' = any(HAIEN_RIYU_GAITO_RIYU)) as C14,
            count(distinct bos_nm_no) filter (where OLD_HOZON_MAN_NENDO = TAISYOU_NENDO and ENCHO_CD is not null and '"4"' = any(HAIEN_RIYU_GAITO_RIYU)) as C15,
            count(distinct bos_nm_no) filter (where OLD_HOZON_MAN_NENDO = TAISYOU_NENDO and ENCHO_CD is not null and HAIEN_RIYU_5_TO_9) as C16,
            count(distinct bos_nm_no) filter (where OLD_HOZON_MAN_NENDO = TAISYOU_NENDO and ENCHO_CD is not null and '"7"' = any(HAIEN_RIYU_GAITO_RIYU)) as C17,
            count(distinct bos_nm_no) filter (where OLD_HOZON_MAN_NENDO = TAISYOU_NENDO and ENCHO_CD is not null and '"5"' = any(HAIEN_RIYU_GAITO_RIYU)) as C18,
            count(distinct bos_nm_no) filter (where OLD_HOZON_MAN_NENDO = TAISYOU_NENDO and ENCHO_CD is not null and '"6"' = any(HAIEN_RIYU_GAITO_RIYU)) as C19,
            count(distinct bos_nm_no) filter (where OLD_HOZON_MAN_NENDO = TAISYOU_NENDO and ENCHO_CD is not null and '"9"' = any(HAIEN_RIYU_GAITO_RIYU)) as C20,
            count(distinct bos_nm_no) filter (where OLD_HOZON_MAN_NENDO = TAISYOU_NENDO and ENCHO_CD is not null and HAIEN_RIYU_5_TO_9 and HOZON_MAN_YMD < OLD_HOZON_MAN_YMD + '1 year'::interval) as C21,
            count(distinct bos_nm_no) filter (where OLD_HOZON_MAN_NENDO = TAISYOU_NENDO and ENCHO_CD is not null and HAIEN_RIYU_5_TO_9 and HOZON_MAN_YMD = OLD_HOZON_MAN_YMD + '1 year'::interval) as C22,
            count(distinct bos_nm_no) filter (where OLD_HOZON_MAN_NENDO = TAISYOU_NENDO and ENCHO_CD is not null and HAIEN_RIYU_5_TO_9 and HOZON_MAN_YMD > OLD_HOZON_MAN_YMD + '1 year'::INTERVAL AND HOZON_MAN_YMD < OLD_HOZON_MAN_YMD + '3 year'::interval) as C23,
            count(distinct bos_nm_no) filter (where OLD_HOZON_MAN_NENDO = TAISYOU_NENDO and ENCHO_CD is not null and HAIEN_RIYU_5_TO_9 and HOZON_MAN_YMD >= OLD_HOZON_MAN_YMD + '3 year'::INTERVAL AND HOZON_MAN_YMD < OLD_HOZON_MAN_YMD + '5 year'::interval) as C24,
            count(distinct bos_nm_no) filter (where OLD_HOZON_MAN_NENDO = TAISYOU_NENDO and ENCHO_CD is not null and HAIEN_RIYU_5_TO_9 and HOZON_MAN_YMD >= OLD_HOZON_MAN_YMD + '5 year'::INTERVAL AND HOZON_MAN_YMD < OLD_HOZON_MAN_YMD + '10 year'::interval) as C25,
            count(distinct bos_nm_no) filter (where OLD_HOZON_MAN_NENDO = TAISYOU_NENDO and ENCHO_CD is not null and HAIEN_RIYU_5_TO_9 and HOZON_MAN_YMD >= OLD_HOZON_MAN_YMD + '10 year'::INTERVAL AND HOZON_MAN_YMD < OLD_HOZON_MAN_YMD + '30 year'::interval) as C26,
            count(distinct bos_nm_no) filter (where OLD_HOZON_MAN_NENDO = TAISYOU_NENDO and ENCHO_CD is not null and HAIEN_RIYU_5_TO_9 and HOZON_MAN_YMD >= OLD_HOZON_MAN_YMD + '30 year'::interval) as C27,
            count(distinct bos_nm_no) filter (where OLD_HOZON_MAN_NENDO = TAISYOU_NENDO and ENCHO_CD is not null and HAIEN_RIYU_5_TO_9 and HZN_KIKAN_TOTAL_YEAR < 30) as C28,
            count(distinct bos_nm_no) filter (where OLD_HOZON_MAN_NENDO = TAISYOU_NENDO and ENCHO_CD is not null and HAIEN_RIYU_5_TO_9 and HZN_KIKAN_TOTAL_YEAR >= 30 AND HZN_KIKAN_TOTAL_YEAR < 60 ) as C29,
            count(distinct bos_nm_no) filter (where OLD_HOZON_MAN_NENDO = TAISYOU_NENDO and ENCHO_CD is not null and HAIEN_RIYU_5_TO_9 and HZN_KIKAN_TOTAL_YEAR >= 60) as C30,
            count(distinct bos_nm_no) filter (where MOTO_SAKU_NENDO = TAISYOU_NENDO and ONE_YEAR_ABOVE and BOS_HZN_KI_MAN_TXT = '移管' and rs_val_20 = 1 and rs_val_21 = 1) as C31,
            count(distinct bos_nm_no) filter (where MOTO_SAKU_NENDO = TAISYOU_NENDO and ONE_YEAR_ABOVE and BOS_HZN_KI_MAN_TXT = '廃棄' and rs_val_20 = 1 and rs_val_21 = 1) as C32,
            count(distinct bos_nm_no) filter (where MOTO_SAKU_NENDO = TAISYOU_NENDO and ONE_YEAR_ABOVE and BOS_HZN_KI_MAN_TXT not in ('移管', '廃棄') and rs_val_20 = 1 and rs_val_21 = 1) as C33,
            count(distinct bos_nm_no) filter (where MOTO_SAKU_NENDO = TAISYOU_NENDO and ONE_YEAR_ABOVE and BOS_HZN_KI_MAN_TXT = '移管' and rs_val_20 = 1 and rs_val_21 <> 1) as C34,
            count(distinct bos_nm_no) filter (where MOTO_SAKU_NENDO = TAISYOU_NENDO and ONE_YEAR_ABOVE and BOS_HZN_KI_MAN_TXT = '廃棄' and rs_val_20 = 1 and rs_val_21 <> 1) as C35,
            count(distinct bos_nm_no) filter (where MOTO_SAKU_NENDO = TAISYOU_NENDO and ONE_YEAR_ABOVE and BOS_HZN_KI_MAN_TXT not in ('移管', '廃棄') and rs_val_20 = 1 and rs_val_21 <> 1) as C36,
            count(distinct bos_nm_no) filter (where MOTO_SAKU_NENDO = TAISYOU_NENDO and ONE_YEAR_ABOVE and BOS_HZN_KI_MAN_TXT = '移管' and rs_val_20 <> 1 and rs_val_21 = 1) as C37,
            count(distinct bos_nm_no) filter (where MOTO_SAKU_NENDO = TAISYOU_NENDO and ONE_YEAR_ABOVE and BOS_HZN_KI_MAN_TXT = '廃棄' and rs_val_20 <> 1 and rs_val_21 = 1) as C38,
            count(distinct bos_nm_no) filter (where MOTO_SAKU_NENDO = TAISYOU_NENDO and ONE_YEAR_ABOVE and BOS_HZN_KI_MAN_TXT not in ('移管', '廃棄') and rs_val_20 <> 1 and rs_val_21 = 1) as C39
        from temp_table_kanri_joukyou
        where NOT (STATUS = ANY(bos_jo_cd))
        group by kanri_jo_no
    loop
        column_map := hstore(count_rec);
        select akeys(column_map) into column_rec;
        FOREACH column_name in array column_rec.akeys loop
            if column_name <> 'kanri_jo_no' then
                insert into result_table values (count_rec.kanri_jo_no, array[]::varchar[], column_name, (column_map->column_name)::int);
            end if;
        END LOOP;
    END LOOP;

    for count_rec in
        select
            kanri_jo_no,
            count(distinct bos_nm_no) as C11
        from temp_table_kanri_joukyou
        where HOZON_MAN_NENDO = TAISYOU_NENDO
            and BOS_HZN_KI_MAN_TXT = '廃棄'
            and STATUS in (6,7,8,9)
            and 2 = any(report_part_slected)
        group by kanri_jo_no
    loop
        insert into result_table values (count_rec.kanri_jo_no, array[]::varchar[], 'c11', count_rec.c11);
    END LOOP;

    for count_rec in
        select
            kanri_jo_no,
            array[KA_FULL_NM] as group_by,
            count(distinct bos_nm_no) filter (where MOTO_SAKU_NENDO <= TAISYOU_NENDO and ONE_YEAR_ABOVE) as C40,
            count(distinct bos_nm_no) filter (where MOTO_SAKU_NENDO = TAISYOU_NENDO and ONE_YEAR_ABOVE) as C43,
            count(distinct bos_nm_no) filter (where OLD_HOZON_MAN_NENDO = TAISYOU_NENDO and ENCHO_CD is not null) as C50
        from temp_table_kanri_joukyou
        where NOT (STATUS = ANY(bos_jo_cd))
        group by kanri_jo_no, group_by
    loop
        column_map := hstore(count_rec);
        select akeys(column_map) into column_rec;
        FOREACH column_name in array column_rec.akeys loop
            if column_name <> 'kanri_jo_no' and column_name <> 'group_by' and column_map->column_name <> '0' then
                insert into result_table values (count_rec.kanri_jo_no, count_rec.group_by, column_name, (column_map->column_name)::int);
            end if;
        END LOOP;
    END LOOP;

    for count_rec in
        select
            kanri_jo_no,
            array[KA_FULL_NM] as group_by,
            count(distinct bos_nm_no) filter (where BOS_HZN_KI_MAN_TXT = '移管') as C48,
            count(distinct bos_nm_no) filter (where BOS_HZN_KI_MAN_TXT = '廃棄') as C49
        from temp_table_kanri_joukyou
        where HOZON_MAN_NENDO = TAISYOU_NENDO and ONE_YEAR_ABOVE and STATUS = ANY(bos_jo_cd_2) and 2 = any(report_part_slected)
        group by kanri_jo_no, group_by
    loop
        column_map := hstore(count_rec);
        select akeys(column_map) into column_rec;
        FOREACH column_name in array column_rec.akeys loop
            if column_name <> 'kanri_jo_no' and column_name <> 'group_by' and column_map->column_name <> '0' then
                insert into result_table values (count_rec.kanri_jo_no, count_rec.group_by, column_name, (column_map->column_name)::int);
            end if;
        END LOOP;
    END LOOP;

    for count_rec in
        select
            kanri_jo_no,
            array[MOTO_SAKU_NENDO] as group_by,
            count(distinct bos_nm_no) filter (where BOS_HZN_KI_MAN_TXT in ('移管', '廃棄')) as C46,
            count(distinct bos_nm_no) filter (where BOS_HZN_KI_MAN_TXT not in ('移管', '廃棄')) as C47
        from temp_table_kanri_joukyou
        where MOTO_SAKU_NENDO <= TAISYOU_NENDO
            and ONE_YEAR_ABOVE
            and administration_type = 0
            and NOT (STATUS = ANY(bos_jo_cd))
            and 1 = any(report_part_slected)
        group by kanri_jo_no, MOTO_SAKU_NENDO
    loop
        column_map := hstore(count_rec);
        select akeys(column_map) into column_rec;
        FOREACH column_name in array column_rec.akeys loop
            if column_name <> 'kanri_jo_no' and column_name <> 'group_by' and column_map->column_name <> '0' then
                insert into result_table values (count_rec.kanri_jo_no, count_rec.group_by, column_name, (column_map->column_name)::int);
            end if;
        END LOOP;
    END LOOP;

    for count_rec in
        select
            kanri_jo_no,
            case when bai_unm = 'その他' then array[bai_unm, bai_txt] else array[bai_unm] end as group_by,
            count(distinct bos_nm_no) filter (where MOTO_SAKU_NENDO <= taisyou_nendo) as C41,
            count(distinct bos_nm_no) filter (where MOTO_SAKU_NENDO = taisyou_nendo) as C44
        from (
            select
                kanri_jo_no,
                taisyou_nendo,
                bos_nm_no, MOTO_SAKU_NENDO,
                coalesce(m11.bai_unm, '') bai_unm,
                case when m11.bai_unm = 'その他' and array_position(bos_bai_shub_cd, m11.bai_cd::text) <= array_length(bos_bai_shub_txt, 1) then bos_bai_shub_txt[array_position(bos_bai_shub_cd, m11.bai_cd::text)] else null end as bai_txt
            from temp_table_kanri_joukyou
            join fd_bunsho_file.m11_bai_shub m11
                on m11.bai_cd::text = any(bos_bai_shub_cd)
            where ONE_YEAR_ABOVE
                and NOT (STATUS = ANY(bos_jo_cd))
                and 1 = any(report_part_slected)
        ) t
        group by kanri_jo_no, group_by
    loop
        if count_rec.C41 > 0 then
            insert into result_table values (count_rec.kanri_jo_no, count_rec.group_by, case when array_length(count_rec.group_by, 1) = 1 then 'c41' else 'c53' end, count_rec.C41);
        end if;
        if count_rec.C44 > 0 then
            insert into result_table values (count_rec.kanri_jo_no, count_rec.group_by, case when array_length(count_rec.group_by, 1) = 1 then 'c44' else 'c54' end, count_rec.C44);
        end if;
    END LOOP;

    for count_rec in
        select
            kanri_jo_no,
            array[coalesce(m11.bai_unm, ''), coalesce(M12.HZN_BASHO_UNM, '')] as group_by,
            count(distinct bos_nm_no) filter (where MOTO_SAKU_NENDO <= TAISYOU_NENDO) as C42,
            count(distinct bos_nm_no) filter (where MOTO_SAKU_NENDO = TAISYOU_NENDO) as C45
        from temp_table_kanri_joukyou
        join fd_bunsho_file.m11_bai_shub m11
            on m11.bai_cd::text = any(bos_bai_shub_cd)
        join fd_bunsho_file.m12_hzn_basho m12
            on m12.hzn_basho_cd::text = any(bos_hzn_basho_cd)
        where ONE_YEAR_ABOVE
            and NOT (STATUS = ANY(bos_jo_cd))
            and 1 = any(report_part_slected)
        group by kanri_jo_no, group_by
    loop
        column_map := hstore(count_rec);
        select akeys(column_map) into column_rec;
        FOREACH column_name in array column_rec.akeys loop
            if column_name <> 'kanri_jo_no' and column_name <> 'group_by' and column_map->column_name <> '0' then
                insert into result_table values (count_rec.kanri_jo_no, count_rec.group_by, column_name, (column_map->column_name)::int);
            end if;
        END LOOP;
    END LOOP;

    for count_rec in
        select
            kanri_jo_no,
            array[haien_riyu] as group_by,
            count(distinct bos_nm_no) filter (where '"8"' = any(HAIEN_RIYU_GAITO_RIYU)) as c51,
            count(distinct bos_nm_no) filter (where HAIEN_RIYU_GAITO_RIYU is null) as c52
        from temp_table_kanri_joukyou
        where
            OLD_HOZON_MAN_NENDO = TAISYOU_NENDO and ENCHO_CD is not null
            and NOT (STATUS = ANY(bos_jo_cd))
            and 2 = any(report_part_slected)
        group by kanri_jo_no, group_by
    loop
        column_map := hstore(count_rec);
        select akeys(column_map) into column_rec;
        FOREACH column_name in array column_rec.akeys loop
            if column_name <> 'kanri_jo_no' and column_name <> 'group_by' and column_map->column_name <> '0' then
                insert into result_table values (count_rec.kanri_jo_no, count_rec.group_by, column_name, (column_map->column_name)::int);
            end if;
        END LOOP;
    END LOOP;

    DROP TABLE temp_table_kanri_joukyou;
    RETURN QUERY
        select * from result_table
        order by kanri_no, cell_no, group_by
    ;
    DROP TABLE result_table;
END; $function$
;