SELECT

r.course_id 课程id,

r.user_id 用户ID,

u.user_full_name  姓名,

u.province_name 省名,

u.city_name 城市,

c.card_password 兑换码,

o.order_id  订单,

o.real_fee 实付金额,

o.order_type 订单类型,

o.pay_type 支付方式,

o.device_type 设备类型,

o.order_status 订单状态,

(case when c.user_id is null and o.user_id is null then '有权限无来源'  when o.order_status='已退款' then '有权限无来源'  when c.card_password is not null then '兑换码'  when o.user_id  is not null then '订单' else '' end) 状态检查

from

(

    select distinct a.course_id,a.user_id,b.sku_id goods_id,a.order_id

    from tetralogy.user_rights_course a left join tetralogy.goods_sku_course b on b.course_id=a.course_id

    where a.current_status='1'

) r

  left join

(

    select

    card_unlock_user_id user_id,

    card_password,

    goods_id

    from tetralogy.card_info

    where 1=1

    and card_is_unlock=0

    and card_is_valid=1

)  c on r.user_id=c.user_id and r.goods_id=c.goods_id

  left join

(

    select

    user_id,id order_id,goods_ids goods_id,device_type,real_fee,

    (case order_type when 0 then '课程'  when 1 then '解锁码' else order_type end ) order_type,

    (case pay_type when 0 then '微信'  when 1 then '支付宝' when 2 then '苹果' when 3 then '对公转账' else order_type end ) pay_type,

    (case order_status when 0 then '待支付'  when 1 then '已支付' when 2 then '退款中' when 3 then '已退款'  when 9 then '已关闭' else order_status end ) order_status

    from tetralogy.app_order

    where order_status=1

)   o on r.order_id=o.order_id

  left join tetralogy.user_info  u on u.id=r.user_id

where 1=1