-- View: "v_order_basic_list"

DROP VIEW v_order_basic_list;

CREATE OR REPLACE VIEW v_order_basic_list AS
SELECT DISTINCT on (basic.orderno)
    basic.orderno,
    basic.ordermember,
    basic.orderdate,
    basic.updcnt,
    basic.takkyubin,
    basic.enquiryno,
    basic.sendbuyer,
    basic.sendmailaddr,
    member.lastnm,
    member.firstnm,
    member.lastnmkana,
    member.firstnmkana,
    member.mailaddr,
    forward.forwardstate,
    n.name AS fwstate,
    depo.depositstate,
    nn.name AS depostate,
    depo.depositdiv,
    nnn.name AS depositdivname,
    depo.cardcompany,
    depo.shitennumberkey,
    depo.kouzanumberkey,
    d.stockstate,
    d.sellercd,
    nnnn.name AS syouhinstatename,
    forward.updcnt AS forwardupdcnt,
    member.updcnt AS memberupdcnt,
    depo.updcnt AS depoupdcnt,
    (SELECT COUNT(*)
        FROM t_order_detail
        WHERE orderno = basic.orderno) AS odrowcont,
    COALESCE(fw.fwstate_cnt, 0) as fwstate_cnt

FROM
    t_order_title basic
    LEFT JOIN t_member_basic_attr member
              ON basic.ordermember = member.membercd
--             AND member.delflg = FALSE
    LEFT JOIN t_forward_detail forward
              ON basic.orderno = forward.orderno
             AND forward.delflg = FALSE
             AND forward.orderrowno =
                 (SELECT MIN(forward.orderrowno)
                    FROM t_forward_detail forward
                   WHERE basic.orderno = forward.orderno
                     AND forward.delflg = FALSE)
    LEFT JOIN t_deposit depo
              ON basic.orderno = depo.orderno
              AND depo.delflg = FALSE
    LEFT JOIN t_order_detail o
              ON basic.orderno = o.orderno
              AND o.typediv = 1
              AND o.delflg = FALSE
    LEFT JOIN t_ex_stock_detail d
              ON o.itemcd = d.itemcd
              AND o.structcd = d.structcd
              AND o.stockcd = d.stockcd
              AND d.delflg = FALSE
    LEFT JOIN t_name nnnn
         ON nnnn.namediv = 10 AND nnnn.namecd = d.stockstate
    LEFT JOIN t_name nnn
         ON nnn.namediv = 13 AND nnn.namecd = depo.depositdiv
    LEFT JOIN t_name nn
         ON nn.namediv = 14 AND nn.namecd = depo.depositstate
    LEFT JOIN t_name n
         ON n.namediv = 12 AND n.namecd = forward.forwardstate

    LEFT JOIN (select fw.orderno, count(fw.orderrowno) as fwstate_cnt
                        from t_forward_detail fw
                      where fw.forwardstate = 3
                      and fw.delflg = FALSE
                      group by fw.orderno) fw
              ON basic.orderno = fw.orderno

WHERE
        basic.delflg = FALSE AND basic.preorderflg = FALSE
;
