//ABCNe[u
create table tbm_bi_abc_rank
(
    abc   char(2),
    value numeric
);
insert into tbm_bi_abc_rank (abc,value) values('A1',20.0);
insert into tbm_bi_abc_rank (abc,value) values('A2',35.0);
insert into tbm_bi_abc_rank (abc,value) values('A3',50.0);
insert into tbm_bi_abc_rank (abc,value) values('A4',65.0);
insert into tbm_bi_abc_rank (abc,value) values('A5',80.0);
insert into tbm_bi_abc_rank (abc,value) values('B1',85.0);
insert into tbm_bi_abc_rank (abc,value) values('B2',90.0);
insert into tbm_bi_abc_rank (abc,value) values('B3',95.0);
insert into tbm_bi_abc_rank (abc,value) values('C1',97.0);
insert into tbm_bi_abc_rank (abc,value) values('C2',99.0);
insert into tbm_bi_abc_rank (abc,value) values('C3',100.0);

//GAʔ̔с
create view tbv_bi_area_yymm as
select
    b.yymm,
    c.company_address1 || c.company_address2 as area,
    sum(a.kin) as kin
from tbv_han_syuka_detail a,
    tbm_date b,
    tbv_company_info c
where a.user_company_id = c.user_company_id
and a.syuka_dt = b.ymd
and a.tokui_cd = c.company_id
group by b.yymm,c.company_address1 || c.company_address2;

//Sҕʕޕʁ
create view tbv_bi_tanto_bunrui_yymm as
select
 a.tanto_staff_cd,
 a.tanto_staff_nm,
 d.bunrui1_id,
 d.bunrui1_nm,
 b.yymm,
 sum(a.kin) as kin
from tbv_han_syuka_detail a,
    tbm_date b,
    tbm_item c,
    tbv_bunrui d
where a.user_company_id = c.user_company_id
and c.user_company_id = d.user_company_id
and a.syuka_dt = b.ymd
and a.item_id = c.item_id
and c.bunrui_id = d.bunrui_id
group by a.tanto_staff_cd,a.tanto_staff_nm,d.bunrui1_id,d.bunrui1_nm,b.yymm;

//Ӑʕޕʁ
create view tbv_bi_tokui_bunrui_yymm as
select
 a.tokui_cd,
 a.tokui_nm,
 d.bunrui1_id,
 d.bunrui1_nm,
 b.yymm,
 sum(a.kin) as kin
from tbv_han_syuka_detail a,
    tbm_date b,
    tbm_item c,
    tbv_bunrui d
where a.user_company_id = c.user_company_id
and c.user_company_id = d.user_company_id
and a.syuka_dt = b.ymd
and a.item_id = c.item_id
and c.bunrui_id = d.bunrui_id
group by a.tokui_cd,a.tokui_nm,d.bunrui1_id,d.bunrui1_nm,b.yymm;

//ޕʁ
create view tbv_bi_bunrui_yymm as
select
 d.bunrui1_id,
 d.bunrui1_nm,
 b.yymm,
 sum(a.kin) as kin
from tbv_han_syuka_detail a,
    tbm_date b,
    tbm_item c,
    tbv_bunrui d
where a.user_company_id = c.user_company_id
and c.user_company_id = d.user_company_id
and a.syuka_dt = b.ymd
and a.item_id = c.item_id
and c.bunrui_id = d.bunrui_id
group by d.bunrui1_id,d.bunrui1_nm,b.yymm;

//
create view tbv_bi_kaisyu_yymm as
select
    a.yymm,
    sum(a.kin) as kin,
    sum(b.nyukin_kin) as nyukin_kin,
    round(sum(b.nyukin_kin) / sum(a.kin) * 100,3) as kaisyu_ritu
from (
    select
        b.yymm,
        sum(a.kin) as kin,
        a.user_company_id
    from tbv_han_syuka_detail a,
        tbm_date b
    where a.syuka_dt = b.ymd
    group by b.yymm,a.user_company_id
) a left join
(
    select
        b.yymm,
        sum(a.nyukin_kin) as nyukin_kin,
        a.user_company_id
    from tbv_han_nyukin a,
        tbm_date b
    where a.nyukin_dt = b.ymd
    group by b.yymm,a.user_company_id
) b on a.yymm = b.yymm
where a.user_company_id = b.user_company_id
group by a.yymm;

//Sҕʑ
create view tbv_bi_tanto_kaisyu as
select
    a.tanto_staff_cd,
    a.tanto_staff_nm,
    a.yymm,
    case when sum(a.kin) is null then 0 else sum(a.kin) end as kin,
    case when sum(b.nyukin_kin) is null then 0 else sum(b.nyukin_kin) end as nyukin_kin,
    case when (sum(b.nyukin_kin) / sum(a.kin)) is null then 0 else round(sum(b.nyukin_kin) / sum(a.kin)* 100,3) end as kaisyu_ritu
from (
    select
        a.tanto_staff_cd,
        a.tanto_staff_nm,
        b.yymm,
        sum(a.kin) as kin,
        a.user_company_id
    from tbv_han_syuka_detail a,
        tbm_date b
    where a.syuka_dt = b.ymd
    group by a.tanto_staff_cd,a.tanto_staff_nm,b.yymm,a.user_company_id
) a left join
(
    select
        a.tanto_staff_cd,
        b.yymm,
        sum(a.nyukin_kin) as nyukin_kin,
        a.user_company_id
    from tbv_han_nyukin a,
        tbm_date b
    where a.nyukin_dt = b.ymd
    group by a.tanto_staff_cd,b.yymm,a.user_company_id
) b on a.tanto_staff_cd = b.tanto_staff_cd and a.yymm = b.yymm
where a.user_company_id = b.user_company_id
group by a.tanto_staff_cd,a.tanto_staff_nm,a.yymm;

//Ӑʑ
create view tbv_bi_tokui_kaisyu as
select
    a.tokui_id,
    a.tokui_nm,
    a.yymm,
    case when sum(a.kin) is null then 0 else sum(a.kin) end as kin,
    case when sum(b.nyukin_kin) is null then 0 else sum(b.nyukin_kin) end as nyukin_kin,
    case when sum(b.nyukin_kin) / sum(a.kin) is null then 0 else round(sum(b.nyukin_kin) / sum(a.kin) * 100,3) end as kaisyu_ritu
from (
    select
        a.tokui_id,
        a.tokui_nm,
        b.yymm,
        sum(a.kin) as kin,
        a.user_company_id
    from tbv_han_syuka_detail a,
        tbm_date b
    where a.bill_dt = b.ymd
    group by a.tokui_id,a.tokui_nm,b.yymm,a.user_company_id
) a left join
(
    select
        a.tokui_id,
        b.yymm,
        sum(a.nyukin_kin) as nyukin_kin,
        a.user_company_id
    from tbv_han_nyukin a,
        tbm_date b
    where a.nyukin_dt = b.ymd
    group by a.tokui_id,b.yymm,a.user_company_id
) b on a.tokui_id = b.tokui_id and a.yymm = b.yymm and a.user_company_id = b.user_company_id
group by a.tokui_id,a.tokui_nm,a.yymm;

//ʃr[
//o׋zv
create view tmp_bi_syuka_sum_kin_yymm as
select
     b.yymm,
     sum(kin) as total_kin
from tbv_han_syuka_detail a,tbm_date b
where a.syuka_dt = b.ymd
group by b.yymm;

//iABĆ
//ʃACeʋzT}
create view tmp_bi_item_abc_1 as
select
     a.item_id,
     a.product_nm,
     c.yymm,
     sum(a.kin) as sum_kin,
     b.total_kin as total_kin
from tbv_han_syuka_detail a,
    tmp_bi_syuka_sum_kin_yymm b,
    tbm_date c
where a.syuka_dt = c.ymd
and b.yymm = c.yymm
group by a.item_id,a.product_nm,b.total_kin,c.yymm
order by c.yymm,a.item_id;

//ʃACeʋz\AN
create view tmp_bi_item_abc_2 as
select
    a.item_id,
    a.product_nm,
    a.yymm,
    a.sum_kin,
    a.total_kin,
    (a.sum_kin/a.total_kin) * 100 as ritu,
    count(b.*)+1 as rank
from tmp_bi_item_abc_1 a
    left join tmp_bi_item_abc_1 b on a.sum_kin < b.sum_kin and a.yymm=b.yymm
group by a.item_id,a.product_nm,a.yymm,a.sum_kin,a.total_kin
order by yymm,rank;

//ʃACeʋz\A݌v\AN
create view tmp_bi_item_abc_3 as
select
    a.item_id,
    a.product_nm,
    a.yymm,
    a.sum_kin,
    a.total_kin,
    a.ritu,
    a.rank,
    case when sum(b.ritu)>100 then 100 else sum(b.ritu) end as sum_ritu
from
    tmp_bi_item_abc_2 a
    left join tmp_bi_item_abc_2 b on a.rank >= b.rank and a.yymm = b.yymm
group by a.item_id,a.product_nm,a.yymm,a.sum_kin,a.total_kin,a.ritu,a.rank
order by a.yymm,a.rank;

//ʃACeʋz\A݌v\ANAABC
create view tbv_bi_item_abc_yymm as
select
    a.item_id,
    a.product_nm,
    a.yymm,
    a.sum_kin,
    a.total_kin,
    round(a.ritu,3) as ritu,
    a.rank,
    round(a.sum_ritu,3) as sum_ritu,
    case when a.rank='1' then c.abc else min(b.abc) end as abc
from
    tmp_bi_item_abc_3 a
    left join tbm_bi_abc_rank b on a.sum_ritu <= b.value,
    (select min(abc) as abc from tbm_bi_abc_rank) c
group by a.item_id,a.product_nm,a.yymm,a.sum_kin,a.total_kin,a.ritu,a.rank,a.sum_ritu,c.abc
order by a.yymm,a.rank;

//SҕABĆ
//ʒSʋzT}
create view tmp_bi_tanto_abc_1 as
select
     d.tanto_staff_cd,
     e.personal_nm,
     c.yymm,
     sum(a.kin) as sum_kin,
     b.total_kin as total_kin
from tbv_han_syuka_detail a,
    tmp_bi_syuka_sum_kin_yymm b,
    tbm_date c,
    tbt_han_syuka d,
    tbv_syain e
where a.syuka_dt = c.ymd
and b.yymm = c.yymm
and a.delivery_no = d.delivery_no
and d.tanto_staff_cd = e.staff_cd
and d.del_flg = '0'
group by d.tanto_staff_cd,e.personal_nm,c.yymm,b.total_kin;

//ʒSҕʋz\AN
create view tmp_bi_tanto_abc_2 as
select
    a.tanto_staff_cd,
    a.personal_nm,
    a.yymm,
    a.sum_kin,
    a.total_kin,
    (a.sum_kin/a.total_kin) * 100 as ritu,
    count(b.*)+1 as rank
from tmp_bi_tanto_abc_1 a
    left join tmp_bi_tanto_abc_1 b on a.sum_kin < b.sum_kin and a.yymm=b.yymm
group by a.tanto_staff_cd,a.personal_nm,a.yymm,a.sum_kin,a.total_kin
order by yymm,rank;

//ʒSҕʋz\A݌v\AN
create view tmp_bi_tanto_abc_3 as
select
    a.tanto_staff_cd,
    a.personal_nm,
    a.yymm,
    a.sum_kin,
    a.total_kin,
    a.ritu,
    a.rank,
    case when sum(b.ritu)>100 then 100 else sum(b.ritu) end as sum_ritu
from
    tmp_bi_tanto_abc_2 a
    left join tmp_bi_tanto_abc_2 b on a.rank >= b.rank and a.yymm = b.yymm
group by a.tanto_staff_cd,a.personal_nm,a.yymm,a.sum_kin,a.total_kin,a.ritu,a.rank
order by a.yymm,a.rank;

//ʒSҕʋz\A݌v\ANAABC
create view tbv_bi_tanto_abc_yymm as
select
    a.tanto_staff_cd,
    a.personal_nm,
    a.yymm,
    a.sum_kin,
    a.total_kin,
    round(a.ritu,3) as ritu,
    a.rank,
    round(a.sum_ritu,3) as sum_ritu,
    case when a.rank='1' then c.abc else min(b.abc) end as abc
from
    tmp_bi_tanto_abc_3 a
    left join tbm_bi_abc_rank b on a.sum_ritu <= b.value,
    (select min(abc) as abc from tbm_bi_abc_rank) c
group by a.tanto_staff_cd,a.personal_nm,a.yymm,a.sum_kin,a.total_kin,a.ritu,a.rank,a.sum_ritu,c.abc
order by a.yymm,a.rank;

//ӐABĆ
//ʃACeʋzT}
create view tmp_bi_tokui_abc_1 as
select
     d.tokui_id,
     e.tokui_nm,
     c.yymm,
     sum(a.kin) as sum_kin,
     b.total_kin as total_kin
from tbv_han_syuka_detail a,
    tmp_bi_syuka_sum_kin_yymm b,
    tbm_date c,
    tbt_han_syuka d,
    tbv_tokui e
where a.syuka_dt = c.ymd
and b.yymm = c.yymm
and a.delivery_no = d.delivery_no
and d.tokui_id = e.tokui_id
and d.del_flg = '0'
group by d.tokui_id,e.tokui_nm,c.yymm,b.total_kin
order by c.yymm,d.tokui_id;

//ʓӐʋz\AN
create view tmp_bi_tokui_abc_2 as
select
    a.tokui_id,
    a.tokui_nm,
    a.yymm,
    a.sum_kin,
    a.total_kin,
    (a.sum_kin/a.total_kin) * 100 as ritu,
    count(b.*)+1 as rank
from tmp_bi_tokui_abc_1 a
    left join tmp_bi_tokui_abc_1 b on a.sum_kin < b.sum_kin and a.yymm=b.yymm
group by a.tokui_id,a.tokui_nm,a.yymm,a.sum_kin,a.total_kin
order by yymm,rank;

//ʓӐʋz\A݌v\AN
create view tmp_bi_tokui_abc_3 as
select
    a.tokui_id,
    a.tokui_nm,
    a.yymm,
    a.sum_kin,
    a.total_kin,
    a.ritu,
    a.rank,
    case when sum(b.ritu)>100 then 100 else sum(b.ritu) end as sum_ritu
from
    tmp_bi_tokui_abc_2 a
    left join tmp_bi_tokui_abc_2 b on a.rank >= b.rank and a.yymm = b.yymm
group by a.tokui_id,a.tokui_nm,a.yymm,a.sum_kin,a.total_kin,a.ritu,a.rank
order by a.yymm,a.rank;

//ʓӐʋz\A݌v\ANAABC
create view tbv_bi_tokui_abc_yymm as
select
    a.tokui_id,
    a.tokui_nm,
    a.yymm,
    a.sum_kin,
    a.total_kin,
    round(a.ritu,3) as ritu,
    a.rank,
    round(a.sum_ritu,3) as sum_ritu,
    case when a.rank='1' then c.abc else min(b.abc) end as abc
from
    tmp_bi_tokui_abc_3 a
    left join tbm_bi_abc_rank b on a.sum_ritu <= b.value,
    (select min(abc) as abc from tbm_bi_abc_rank) c
group by a.tokui_id,a.tokui_nm,a.yymm,a.sum_kin,a.total_kin,a.ritu,a.rank,a.sum_ritu,c.abc
order by a.yymm,a.rank;

//Sҕ Ԕ㑍v
// MINAMAXAς܂
create view tmp_bi_tanto_syukei_1 as
select
 a.tanto_staff_cd,
 a.tanto_staff_nm,
 b.yymm,
 b.yyyy,
 sum(a.su) as su,
 sum(a.kin) as kin,
 sum(c.standardcost_tnk) as genka,
 sum(a.kin) - sum(c.standardcost_tnk) as arari
from tbv_han_syuka_detail a,
    tbm_date b,
    tbm_item c
where a.user_company_id = c.user_company_id
and a.syuka_dt = b.ymd
and a.item_id = c.item_id
group by a.tanto_staff_cd,a.tanto_staff_nm,b.yymm,b.yyyy;

create view tbv_bi_tanto_syukei_yymm as
select
 tanto_staff_cd,
 tanto_staff_nm,
 yymm,
 su,
 kin,
 genka,
 arari,
 round((arari / kin) * 100,3) as arari_ritu
from tmp_bi_tanto_syukei_1;

create view tbv_bi_tanto_syukei_yyyy as
select
 tanto_staff_cd,
 tanto_staff_nm,
 yyyy,
 sum(su) as su,
 sum(kin) as kin,
 sum(genka) as genka,
 sum(arari) as arari,
 round((sum(arari) / sum(kin)) * 100,3) as arari_ritu
from tmp_bi_tanto_syukei_1
group by tanto_staff_cd,tanto_staff_nm,yyyy;

//ӐʔWv\
// MINAMAXAς܂
create view tmp_bi_tokui_syukei_1 as
select
 a.tokui_cd,
 a.tokui_nm,
 b.yymm,
 b.yyyy,
 sum(a.su) as su,
 sum(a.kin) as kin,
 sum(c.standardcost_tnk) as genka,
 sum(a.kin) - sum(c.standardcost_tnk) as arari
from tbv_han_syuka_detail a,
    tbm_date b,
    tbm_item c
where a.user_company_id = c.user_company_id
and a.bill_dt = b.ymd
and a.item_id = c.item_id
group by a.tokui_cd,a.tokui_nm,b.yymm,b.yyyy;

create view tbv_bi_tokui_syukei_yymm as
select
 tokui_cd,
 tokui_nm,
 yymm,
 su,
 kin,
 genka,
 arari,
 round((arari / kin) * 100,3) as arari_ritu
from tmp_bi_tokui_syukei_1;

create view tbv_bi_tokui_syukei_yyyy as
select
 tokui_cd,
 tokui_nm,
 yyyy,
 sum(su) as su,
 sum(kin) as kin,
 sum(genka) as genka,
 sum(arari) as arari,
 round((sum(arari) / sum(kin)) * 100,3) as arari_ritu
from tmp_bi_tokui_syukei_1
group by tokui_cd,tokui_nm,yyyy;
