쿼리연습

-- 본사에 보내야할 청구액을 확인하고 버튼을 통해 입금등록 하는 테이블

-- 지금이 9월이면 8/1 ~ 8/31까지의 본사청구액을 계산해서 FEE테이블에 넣어야함

-- 일정시점에 버튼을 누르면 자동으로 insert

-- 1. 가맹비 청구시 분할결제는 지원하지 않는다.					
-- 2. 본사는 가맹비를 연체한 가맹점주를 대상으로, 밀린 달에 대해서만 쿠폰비를 지원하지 않는다.					
-- 3. 다만 납부 기준일로부터 일주일 내에 가맹비를 납부하면 쿠폰비를 지원한다.

insert into fee(fee_id, FRAN_ID, fee_sdate, fee_fruse, fee_royal, fee_cost, fee_tplus, fee_tminus, fee_overdue, fee_cpn) 
values(
        'CB2022901' ||'-'||TO_CHAR(sysdate,'yyyyMMDD'),
        
        'CB2022901',
        
         TO_CHAR(sysdate,'yyyy/MM/DD'),
         
        500000,
        
        (select total*0.05 as royal
        from(
            select sum(a.pay_cost) as total
            from pay a
            inner join morder b
            on (a.morder_id = b.morder_id)
            where (a.pay_date >= '2022/08/01' and a.pay_date < '2022/09/01')
            and a.morder_id in (
            
            select b.morder_id
            from resv a
            right outer join morder b
            on (a.resv_id = b.resv_id)
            where a.fran_id='CB2022901' 
            and (a.resv_date >= '2022/08/01' and a.resv_date < '2022/09/01')
            or b.resv_id is null
            )
        )),
        
        (select sum(frorder_price) as total_frorder_price
        from frorder
        where fran_id='CB2022901'
        and FRORDER_STATE = 'ST15'
        and (frorder_date >= '2022/08/01' and frorder_date < '2022/09/01')),
        
        (select sum(sum_per) as trade_plus_sum
        from (
        select distinct b.buyerprod_id, b.sum_qty, a.headin_release, sum_qty * headin_release as sum_per
        from headin a
        inner join (
                        select buyerprod_id, sum(resp_qty) as sum_qty
                        from trade
                        where req_fran = 'CB2022901'
                        and (req_date>= '2022/08/01' and req_date < '2022/09/01') 
                        and resp_qty is not null 
                        group by buyerprod_id
                     ) b
            on a.buyerprod_id = b.buyerprod_id ) ),     
            
        (select sum(sum_per) as trade_minus_sum
        from (
        select distinct b.buyerprod_id, b.sum_qty, a.headin_release, sum_qty * headin_release as sum_per
        from headin a
        inner join (
                        select buyerprod_id, sum(resp_qty) as sum_qty
                        from trade
                        where resp_fran = 'CB2022901'
                        and (req_date>= '2022/08/01' and req_date < '2022/09/01') 
                        and resp_qty is not null 
                        group by buyerprod_id
                     ) b
        on a.buyerprod_id = b.buyerprod_id)),
        
        0,
        
        (select sum(lgu_name)
            from (
            select distinct a.mycpn_id, b.lgu_name
            from(
                    select distinct a.MYCPN_ID, b.cpn_lgu
                    from (
                            select distinct a.mycpn_id, b.cpn_id
                            from(
                                select distinct a.mycpn_id
                                from pay a
                                inner join morder b
                                on (a.morder_id = b.morder_id)
                                where (a.pay_date >= '2022/08/01' and a.pay_date < '2022/09/01')
                                and a.morder_id in (
                                                    select b.morder_id
                                                    from resv a
                                                    right outer join morder b
                                                    on (a.resv_id = b.resv_id)
                                                    where a.fran_id='CB2022901' 
                                                    and (a.resv_date >= '2022/08/01' and a.resv_date < '2022/09/01')
                                                    or b.resv_id is null
                                                    ) 
                                ) a
                            inner join mycpn b
                            on(a.mycpn_id = b.mycpn_id)
                    ) a
                    inner join cpn b
                    on (a.cpn_id = b.cpn_id)
            ) a
            inner join lgu b
            on(a.cpn_lgu = b.lgu_id)
        ))

       )

-- 총 수익 계산하는 방법 뭐임
select total*0.05 as royal
from(
    select sum(a.pay_cost) as total
    from pay a
    inner join morder b
    on (a.morder_id = b.morder_id)
    where (a.pay_date >= '2022/08/01' and a.pay_date < '2022/09/01')
    and a.morder_id in (
    
    select b.morder_id
    from resv a
    right outer join morder b
    on (a.resv_id = b.resv_id)
    where a.fran_id='CB2022901' 
    and (a.resv_date >= '2022/08/01' and a.resv_date < '2022/09/01')
    or b.resv_id is null
    )
);

-- 한달간의 총 가불비 계산하는 방법 뭐임
select sum(frorder_price) as total_frorder_price
from frorder
where fran_id='CB2022901'
and FRORDER_STATE = 'ST15'
and (frorder_date >= '2022/08/01' and frorder_date < '2022/09/01')


-- 트레이딩 추가결제비
select sum(sum_per) as trade_plus_sum
from (
    select distinct b.buyerprod_id, b.sum_qty, a.headin_release, sum_qty * headin_release as sum_per
    from headin a
    inner join (
                    select buyerprod_id, sum(resp_qty) as sum_qty
                    from trade
                    where req_fran = 'CB2022901'
                    and (req_date>= '2022/08/01' and req_date < '2022/09/01') 
                    and resp_qty is not null 
                    group by buyerprod_id
                 ) b
    on a.buyerprod_id = b.buyerprod_id
);

-- 트레이딩 삭감비
select sum(sum_per) as trade_minus_sum
from (
    select distinct b.buyerprod_id, b.sum_qty, a.headin_release, sum_qty * headin_release as sum_per
    from headin a
    inner join (
                    select buyerprod_id, sum(resp_qty) as sum_qty
                    from trade
                    where resp_fran = 'CB2022901'
                    and (req_date>= '2022/08/01' and req_date < '2022/09/01') 
                    and resp_qty is not null 
                    group by buyerprod_id
                 ) b
    on a.buyerprod_id = b.buyerprod_id
);

-- 연체이자(납부총금액의5%)


-- 쿠폰 지원금
-- pay에서 사용한 쿠폰번호 끌어와서 쿠폰테이블과 비교하면될듯
select sum(lgu_name)
from (
select distinct a.mycpn_id, b.lgu_name
from(
        select distinct a.MYCPN_ID, b.cpn_lgu
        from (
                select distinct a.mycpn_id, b.cpn_id
                from(
                    select distinct a.mycpn_id
                    from pay a
                    inner join morder b
                    on (a.morder_id = b.morder_id)
                    where (a.pay_date >= '2022/08/01' and a.pay_date < '2022/09/01')
                    and a.morder_id in (
                                        select b.morder_id
                                        from resv a
                                        right outer join morder b
                                        on (a.resv_id = b.resv_id)
                                        where a.fran_id='CB2022901' 
                                        and (a.resv_date >= '2022/08/01' and a.resv_date < '2022/09/01')
                                        or b.resv_id is null
                                        ) 
                    ) a
                inner join mycpn b
                on(a.mycpn_id = b.mycpn_id)
        ) a
        inner join cpn b
        on (a.cpn_id = b.cpn_id)
) a
inner join lgu b
on(a.cpn_lgu = b.lgu_id)
);