upsert into anli_warehouse_store111 
    select 
      s1.from_wh_id, 
      s2.ts, 
      s1.biz_no, 
      s1.from_wh_no, 
      s1.from_wh_name, 
      s1.to_store_id, 
      s1.to_store_name, 
      s1.goods_num, 
      s1.sku, 
      s1.out_wh_time, 
      case when s2.biz_no is null then -1 else s1.status end as status, 
      s1.province_id, 
      s1.province_name, 
      s1.city_id, 
      s1.city_name, 
      s1.so_type, 
      s1.create_time, 
      s1.out_time, 
      case when s2.reason is null then '' else s2.reason end as reason from 
      (select * from anli_warehouse_store where ts = '2017-11-30 16:00:00' and so_type = 1) s1 
      left join 
    (select 
            straight_join  
            mm.warehouse_id as from_wh_id            , 
            '2017-11-30 19:00:00' as ts, 
            mm.so_no as biz_no                         , 
            mm.warehouse_no   as from_wh_no            , 
            mm.warehouse_name as from_wh_name          , 
            null              as to_store_id           , 
            mm.consignee_addr as to_store_name         , 
            t.goods_num                                , 
            t.sku                                      , 
            case when mm.create_time is null then 0 else cast((unix_timestamp(now() 
                            ) - unix_timestamp(mm.create_time)) / 3600 as int) end 
            as out_wh_time, 
            ( 
                    case when cast(mm.create_time as string) <= concat(FROM_UNIXTIME(UNIX_TIMESTAMP( 
                                    adddate(now(), - 1)), 'yyyy-MM-dd'), ' 17:00:00') and st.so_id is 
                                    null then 1 
                         when mm.create_time >= concat(FROM_UNIXTIME(UNIX_TIMESTAMP(now()), 'yyyy-MM-dd'), ' 00:00:00') then 0 else 
                                    0 end) as status       , 
            aa.province_id                 as province_id  , 
            aa.province_name               as province_name, 
            aa.city_id                     as city_id      , 
            aa.city_name                   as city_name    , 
            1                              as so_type, 
            mm.create_time                 as create_time, 
            st.create_time                 as out_time, 
            ifnull(case when substring(mm.so_err_status, 2, 1) = '1' and mm.so_status not in (10019, 10020, 10054, 10033, 10034, 10028) then 'Òì³£ÔÝÍ£' 
            when substring(mm.so_err_status, 4, 1) = '1' and mm.so_status not in (10019, 10020, 10054, 10033, 10034, 10028) then '²¡µ¥' else '' end, '') as reason 
    from 
            ( 
                    select 
                            case when m.parent_id is null then m.so_no else concat('ESL',cast(m.parent_id as string)) end as parent_no, 
                            it.so_id                                   , 
                            m.so_no                                   , 
                            sum(it.apply_outstore_qty) as goods_num, 
                            group_concat(concat(it.goods_name, ':', cast( 
                            it.apply_outstore_qty as string)), ';') as sku 
                    from 
                            ( 
                                    select 
                                            so_id    , 
                                            goods_name, 
                                            apply_outstore_qty 
                                    from 
                                            eclp_so1_so_item 
                            ) 
                            it 
                    inner join [shuffle] 
                            ( 
                                    select 
                                            * 
                                    from 
                                            eclp_so1_so_main 
                                    where 
                                            so_type         = 1 
                                            and create_time < '2017-11-30 16:00:00' 
                                            and so_status  != 10034 
                            ) 
                            m 
                    on 
                            cast(substring(case when m.parent_id is null then m.so_no else concat('ESL', cast(m.parent_id as string)) end, 4) as string) = cast(it.so_id as string) 
                    group by 
                            it.so_id, case when m.parent_id is null then m.so_no else concat('ESL', cast(m.parent_id as string)) end, m.so_no 
            ) 
            t 
    inner join [shuffle] (select * from eclp_so1_so_main where seller_no = 'ECP0020000003619' and so_type = 1 and create_time < '2017-11-30 16:00:00') mm 
    on 
            t.parent_no = mm.so_no 
    left join [shuffle] 
            ( 
                    select 
                            so_id, 
                            min(create_time) as create_time 
                    from 
                            eclp_so1_so_status 
                    where 
                            so_status in (10019, 10020, 10054, 10033, 10034, 10028) 
                    group by 
                            so_id 
            ) 
            st 
    on 
            substring(t.parent_no, 4) = cast(st.so_id as string) 
    inner join [broadcast] 
            ( 
                    select DISTINCT 
                            province_id  , 
                            province_name, 
                            city_id      , 
                            city_name 
                    from 
                            city_all_around 
            ) 
            aa 
    on 
            case when mm.addr_province in('±±¾©ÊÐ', 'ÉϺ£ÊÐ', 'Ìì½òÊÐ', 'ÖØÇìÊÐ') then 
                            mm.addr_county else mm.addr_city end = aa.city_name) s2 
    on s1.biz_no = s2.biz_no