-- View: "v_order"

DROP VIEW v_order;

CREATE OR REPLACE VIEW v_order AS

select
 o1.orderno,
 o1.orderdate,
 o1.ordermember,
 case
  when o1.ordermember IS NULL then o1.sendbuyer
  when m1.nicknm IS NULL or m1.nicknm='' then m1.lastnm || ' ' || m1.firstnm
  when m1.nicknm IS NOT NULL then m1.nicknm
 end as ordermembernm,
 o1.sendpostalcd,
 o1.sendprefcd,
 o1.sendaddr1,
 o1.sendaddr2,
 o1.sendtelno1,
 o1.addressee,
 o1.takkyubin,
 d.depositdate,
 d.depositdiv,
 n1.name as depositdivnm,
 d.depositstate,
 n2.name as depositstatenm,
 d.cardcompany,
 d.cardnumber,
 d.cardname,
 d.cardexp,
 d.shitennumberkey,
 d.kouzanumberkey,
 d.sellerpaydate,
 o2.orderrowno,
 o2.typediv,
 n3.name as typedivnm,
 o2.itemcd,
 i.itemnm,
 o2.structcd,
 o2.stockcd,
 o2.price,
 o2.count,
 s.sellercd,
 case
  when m2.nicknm IS NULL or m2.nicknm='' then m2.lastnm || ' ' || m2.firstnm
  when m2.nicknm IS NOT NULL then m2.nicknm
 end as sellernm,
 s.stocktitlecd,
 s.exhibitcount,
 s.condition,
 s.sellingprice,
 s.souryouprc,
 s.stockstate,
 s.scdstartdate,
 s.scdcompdate,
 s.itemexp,
 s.rackid,
 s.managecd,
 s.costprice,
 s.ntcostprice,
 s.consignment,
 s.arrivaldate,
 r.refundreqdate,
 r.refunddate,
 r.refundprice,
 r.refundreason,
 COALESCE(r.allrefund, 0) as allrefund


from t_order_title o1
 left join t_deposit d
  on o1.orderno=d.orderno and d.delflg=false
 left join t_order_detail o2
  on o1.orderno=o2.orderno and o2.delflg=false
 left join t_ex_stock_detail s
  on s.itemcd=o2.itemcd and s.structcd=o2.structcd and s.stockcd=o2.stockcd  and s.delflg=false
 left join t_item_basic_attr i
  on s.itemcd=i.itemcd and i.delflg=false
 left join t_member_basic_attr m1
  on o1.ordermember=m1.membercd and m1.delflg=false
 left join t_member_basic_attr m2
  on s.sellercd=m2.membercd and m2.delflg=false
 left join (
                    SELECT
                    orderno,
                    refunddate,
                    refundreqdate,
                    refundprice,
                    refundreason,
                    0 as allrefund,
                    delflg

                    FROM t_refund ref1
                    WHERE refundprice IS NOT NULL

                    UNION

                    SELECT
                    ref2.orderno as orderno,
                    ref2.refunddate,
                    ref2.refundreqdate,
                    case
                    when od.allprice IS NULL then od2.allprice + od3.allprice
                    else od.allprice + od3.allprice
                    END as refundprice,
                    ref2.refundreason,
                    1 as allrefund,
                    ref2.delflg as delflg
                    FROM t_refund ref2
                    LEFT JOIN (
                        SELECT
                        orderno,
                        SUM(price*count) as allprice
                        FROM t_order_detail t_od
                            left join t_ex_stock_detail s
                            on t_od.itemcd=s.itemcd AND t_od.structcd=s.structcd AND t_od.stockcd=s.stockcd
                        WHERE typediv=1 AND s.sellercd IS NULL
                        GROUP BY orderno
                    ) AS od ON ref2.orderno = od.orderno

                    LEFT JOIN (
                        SELECT
                        orderno,
                        SUM(price) as allprice
                        FROM t_order_detail t_od
                            left join t_ex_stock_detail s
                            on t_od.itemcd=s.itemcd AND t_od.structcd=s.structcd AND t_od.stockcd=s.stockcd
                        WHERE typediv=1 AND s.sellercd IS NOT NULL
                        GROUP BY orderno
                    ) AS od2 ON ref2.orderno = od2.orderno

                    LEFT JOIN (
                        SELECT
                        orderno,
                        SUM(price) as allprice
                        FROM t_order_detail t_od
                        WHERE typediv in (2,3,10,11)
                        GROUP BY orderno
                    ) AS od3 ON ref2.orderno = od3.orderno
                    WHERE refundprice IS NULL
                ) r
  on r.orderno=o2.orderno and r.delflg=false
 left join t_name n1
  on n1.namecd=d.depositdiv and n1.namediv=13 and n1.delflg=false
 left join t_name n2
  on n2.namecd=d.depositstate and n2.namediv=14 and n2.delflg=false
 left join t_name n3
  on n3.namecd=o2.typediv and n3.namediv=11 and n3.delflg=false

where o1.delflg=false AND o1.preorderflg = FALSE
;