# T_REPORT

name: pay 账户
dbtype: JnetPay
report_sql:
select FNumber NO,FName 名称 from t_account   where (@begin_date.length = 0 or
         to_char(o.create_date, 'yyyy-mm-dd') >= @begin_date)
     and (@end_date.length = 0 or
         to_char(o.create_date, 'yyyy-mm-dd') <= @end_date)


name: 快递类别业务排名报表（old）
dbtype: JnetRpt
report_sql:
select st.*, rank() over(order by st.order_amount desc) Rank
  from (select '快递系统' T, e.name, ot.*
          from jnettms.t_express_model e,
               (select o.express_model_code,
                       nvl(sum(cbi.amount), 0) + nvl(sum(c.amount), 0) order_amount,
                       nvl(sum(cbi.amount), 0) + nvl(sum(c.amount), 0) -
                       nvl(sum(spbi.amount), 0) - nvl(sum(st.amount), 0) +
                       nvl(sum(spri.REFUND_AMOUNT), 0) -
                       nvl(sum(co.amount), 0) profit_amount,
                       sum(o.weight) weight,
                       sum(o.scale_weight) scale_weight,
                       sum(o.volumetric_weight) volumetric_weight,
                       sum(o.pieces) order_pieces,
                       count(o.id) order_count,
                       count(op.waybill_num) delivery_number,
                       (case
                         when (nvl(count(op.waybill_num), 0) /
                              nvl(count(o.id), 0)) = 0 then
                          '0'
                         else
                          (round(((nvl(count(op.waybill_num), 0) /
                                 nvl(count(o.id), 0))) * 100,
                                 0)) || '%'
                       end) delivery_rate,
                       round(nvl(sum(ceil(ite.time - o.create_date)), 0) /
                             nvl(count(o.id), 0),
                             0) average_days
                  from jnettms.T_ORDER o,
                       jnettms.t_client cli,
                       (select *
                          from (select ot.order_id,
                                       ot.time,
                                       row_number() over(partition by ot.order_id order by ot.time desc) rn
                                  from jnettms.t_Tracking_Item ot)
                         where rn = 1) ite,
                       (SELECT waybill_num, nvl(sum(amount), 0) amount
                          FROM jnettms.t_charge
                         group by waybill_num) c,
                       (SELECT order_id, nvl(sum(amount), 0) amount
                          FROM jnettms.t_cost
                         group by order_id) st,
                       (SELECT waybill_num, nvl(sum(amount), 0) amount
                          FROM jnettms.t_service_provider_bill_item
                         group by waybill_num) spbi,
                       (SELECT waybill_num,
                               nvl(sum(REFUND_AMOUNT), 0) REFUND_AMOUNT
                          FROM jnettms.t_service_provider_refund_item
                         group by waybill_num) spri,
                       (SELECT waybill_num, nvl(sum(amount), 0) amount
                          FROM jnettms.t_compensation
                         group by waybill_num) co,
                       (SELECT waybill_num, nvl(sum(amount), 0) amount
                          FROM jnettms.t_client_bill_item
                         where TYPE = 1
                         group by waybill_num) cbi,
                       (select od.waybill_num, od.create_date, od.modify_date
                          from jnettms.t_order od
                         where od.status = 30) op
                 where 1 = 1
                   AND o.id = ite.order_id(+)
                   and o.id = st.order_id(+)
                   and o.client_id = cli.id
                   and nvl(cli.feature, 0) <> 'TEST'
                   and o.waybill_num = spbi.waybill_num(+)
                   and o.waybill_num = spri.waybill_num(+)
                   and o.waybill_num = op.waybill_num(+)
                   and o.waybill_num = c.waybill_num(+)
                   and o.waybill_num = co.waybill_num(+)
                   and o.waybill_num = cbi.waybill_num
                 group by o.express_model_code) ot
         where e.code = ot. express_model_code
        union all
        select '海外仓' T, e.name, ot.*
          from jnetwlms.t_express_model e,
               (select o.MODEL_CODE,
                       nvl(sum(cbi.amount), 0) + nvl(sum(c.amount), 0) order_amount,
                       nvl(sum(cbi.amount), 0) + nvl(sum(c.amount), 0) -
                       nvl(sum(spbi.amount), 0) +
                       nvl(sum(spri.REFUND_AMOUNT), 0) -
                       nvl(sum(co.amount), 0) profit_amount,
                       sum(nvl(sit.quantity, 0) * nvl(sit.weight, 0)) weight,
                       sum(nvl(sit.quantity, 0) * nvl(sit.weight, 0)) scale_weight,
                       0 volumetric_weight,
                       count(o.id) order_pieces,
                       count(o.id) order_count,
                       count(op.waybill_num) delivery_number,
                       (case
                         when (nvl(count(op.waybill_num), 0) /
                              nvl(count(o.id), 0)) = 0 then
                          '0'
                         else
                          (round(((nvl(count(op.waybill_num), 0) /
                                 nvl(count(o.id), 0))) * 100,
                                 0)) || '%'
                       end) delivery_rate,
                       round(nvl(sum(ceil(ite.time - o.create_date)), 0) /
                             nvl(count(o.id), 0),
                             0) average_days
                  from jnetwlms.t_sendout_order o,
                       jnetwlms.t_client cli,
                       jnetwlms.t_sendout_item sit,
                       (select *
                          from (select ot.order_id,
                                       ot.time,
                                       row_number() over(partition by ot.order_id order by ot.time desc) rn
                                  from jnetwlms.t_tracking_item ot)
                         where rn = 1) ite,
                       (SELECT waybill_num, nvl(sum(amount), 0) amount
                          FROM jnetwlms.t_charge
                         group by waybill_num) c,
                       (SELECT waybill_num, nvl(sum(amount), 0) amount
                          FROM jnetwlms.t_service_provider_bill_item
                         group by waybill_num) spbi,
                       (SELECT waybill_num,
                               nvl(sum(REFUND_AMOUNT), 0) REFUND_AMOUNT
                          FROM jnetwlms.t_service_provider_refund_item
                         group by waybill_num) spri,
                       (SELECT waybill_num, nvl(sum(amount), 0) amount
                          FROM jnetwlms.t_compensation
                         group by waybill_num) co,
                       (SELECT waybill_num, nvl(sum(amount), 0) amount
                          FROM jnetwlms.t_client_bill_item
                         where TYPE = 1
                         group by waybill_num) cbi,
                       (select od.waybill_num, od.create_date, od.modify_date
                          from jnetwlms.t_sendout_order od
                         where od.status = 30) op
                 where 2 = 2
                   AND o.id = ite.order_id(+)
                   and o.id = sit.sendout_order_id(+)
                   and o.client_id = cli.id
                   and nvl(cli.feature, 0) <> 'TEST'
                   and o.waybill_num = spbi.waybill_num(+)
                   and o.waybill_num = spri.waybill_num(+)
                   and o.waybill_num = op.waybill_num(+)
                   and o.waybill_num = c.waybill_num(+)
                   and o.waybill_num = co.waybill_num(+)
                   and o.waybill_num = cbi.waybill_num(+)
                 group by o.MODEL_CODE) ot
         where e.code = ot. MODEL_CODE
        union all
        select '头程FBA' T, e.name, ot.*
          from jnetfba.T_PRODUCT_CHANNEL e,
               (select o.express_model_code,
                       nvl(sum(cbi.amount), 0) + nvl(sum(c.amount), 0) order_amount,
                       nvl(sum(cbi.amount), 0) + nvl(sum(c.amount), 0) -
                       nvl(sum(spbi.amount), 0) - nvl(sum(st.amount), 0) +
                       nvl(sum(spri.REFUND_AMOUNT), 0) -
                       nvl(sum(co.amount), 0) profit_amount,
                       sum(o.ACTUAL_WEIGHT) weight,
                       sum(o.scale_weight) scale_weight,
                       sum(o.volumetric_weight) volumetric_weight,
                       count(o.id) order_pieces,
                       count(o.id) order_count,
                       count(op.waybill_num) delivery_number,
                       (case
                         when (nvl(count(op.waybill_num), 0) /
                              nvl(count(o.id), 0)) = 0 then
                          '0'
                         else
                          (round(((nvl(count(op.waybill_num), 0) /
                                 nvl(count(o.id), 0))) * 100,
                                 0)) || '%'
                       end) delivery_rate,
                       round(nvl(sum(ceil(ite.time - o.create_date)), 0) /
                             nvl(count(o.id), 0),
                             0) average_days
                  from jnetfba.t_order o,
                       jnetfba.t_client cli,
                       (select *
                          from (select ot.order_id,
                                       ot.time,
                                       row_number() over(partition by ot.order_id order by ot.time desc) rn
                                  from jnetfba.t_tracking_item ot)
                         where rn = 1) ite,
                       (SELECT waybill_num, nvl(sum(amount), 0) amount
                          FROM jnetfba.t_charge
                         group by waybill_num) c,
                       (SELECT order_id, nvl(sum(amount), 0) amount
                          FROM jnetfba.t_cost
                         group by order_id) st,
                       (SELECT waybill_num, nvl(sum(amount), 0) amount
                          FROM jnetfba.t_service_provider_bill_item
                         group by waybill_num) spbi,
                       (SELECT waybill_num,
                               nvl(sum(REFUND_AMOUNT), 0) REFUND_AMOUNT
                          FROM jnetfba.t_service_provider_refund_item
                         group by waybill_num) spri,
                       (SELECT waybill_num, nvl(sum(amount), 0) amount
                          FROM jnetfba.t_compensation
                         group by waybill_num) co,
                       (SELECT order_id, nvl(sum(amount), 0) amount
                          FROM jnetfba.t_order_fee
                         group by order_id) cbi,
                       (select od.waybill_num, od.STORAGE_DATE, od.OUT_DATE
                          from jnetfba.t_order od
                         where od.status = 30) op
                 where 3 = 3
                   AND o.id = ite.order_id(+)
                   and o.id = st.order_id(+)
                   and o.client_id = cli.id
                   and nvl(cli.feature, 0) <> 'TEST'
                   and o.waybill_num = spbi.waybill_num(+)
                   and o.waybill_num = spri.waybill_num(+)
                   and o.waybill_num = op.waybill_num(+)
                   and o.waybill_num = c.waybill_num(+)
                   and o.waybill_num = co.waybill_num(+)
                   and o.id = cbi.order_id(+)
                 group by o.express_model_code) ot
         where e.code = ot. express_model_code) st
 where 1 = 1


name: 快递类别业务排名报表
dbtype: JnetRpt
report_sql:
SELECT T                 业务类型,
       NAME              渠道,
       ORDER_AMOUNT      订单金额,
       PROFIT_AMOUNT     毛利,
       WEIGHT            重量,
       SCALE_WEIGHT      称重,
       VOLUMETRIC_WEIGHT 泡重,
       ORDER_PIECES      件数,
       ORDER_COUNT       票数,
       DELIVERY_NUMBER   送达数,
       DELIVERY_RATE     比例,
       AVERAGE_DAYS      平均天数,
       sp_amount         下家重量,
       profit_amount     下家金额
  FROM (select '快递系统' T,
               em.name,
               nvl(sum(s1.amount), 0) + nvl(sum(s2.amount), 0) order_amount,
               nvl(sum(s1.amount), 0) + nvl(sum(s2.amount), 0) -
               nvl(sum(c1.amount), 0) - nvl(sum(c2.amount), 0) -
               nvl(sum(c3.amount), 0) + nvl(sum(c4.REFUND_AMOUNT), 0) profit_amount,
               nvl(sum(c3.amount), 0) - nvl(sum(c4.REFUND_AMOUNT), 0) sp_amount,
               nvl(sum(c3.weight), 0) sp_weight,
               sum(o.weight) weight,
               sum(o.scale_weight) scale_weight,
               sum(o.volumetric_weight) volumetric_weight,
               sum(o.pieces) order_pieces,
               count(o.id) order_count,
               count(op.waybill_num) delivery_number,
               (case
                 when (nvl(count(op.waybill_num), 0) / nvl(count(o.id), 0)) = 0 then
                  '0'
                 else
                  (round(((nvl(count(op.waybill_num), 0) /
                         nvl(count(o.id), 0))) * 100,
                         0)) || '%'
               end) delivery_rate,
               round(nvl(sum(ceil(track.time - o.create_date)), 0) /
                     nvl(count(o.id), 0),
                     0) average_days
          FROM jnettms.T_ORDER o,
               jnettms.t_client cli,
               jnettms.t_express_model em,
               (SELECT waybill_num, nvl(sum(amount), 0) amount
                  FROM jnettms.t_client_bill_item
                 where TYPE = 1
                 group by waybill_num) s1,
               (SELECT waybill_num, nvl(sum(amount), 0) amount
                  FROM jnettms.t_charge
                 group by waybill_num) s2,
               (SELECT order_id, nvl(sum(amount), 0) amount
                  FROM jnettms.t_cost
                 group by order_id) c1,
               (SELECT waybill_num, nvl(sum(amount), 0) amount
                  FROM jnettms.t_compensation
                 group by waybill_num) c2,
               (SELECT waybill_num,
                       nvl(sum(amount), 0) amount,
                       nvl(sum(weight), 0) weight
                  FROM jnettms.t_service_provider_bill_item
                 group by waybill_num) c3,
               (SELECT waybill_num, nvl(sum(REFUND_AMOUNT), 0) REFUND_AMOUNT
                  FROM jnettms.t_service_provider_refund_item
                 group by waybill_num) c4,
               
               (select *
                  from (select ot.order_id,
                               ot.time,
                               row_number() over(partition by ot.order_id order by ot.time desc) rn
                          from jnettms.t_Tracking_Item ot)
                 where rn = 1) track,
               (select od.waybill_num, od.create_date, od.modify_date
                  from jnettms.t_order od
                 where od.status = 30) op
         where 1 = 1
           and to_char(o.create_date, 'yyyymmdd') >= '20190801'
           and to_char(o.create_date, 'yyyymmdd') < '20190901'
           and nvl(cli.feature, 0) <> 'TEST'
           and o.client_id = cli.id
           and o.express_model_code = em.code(+)
           and o.waybill_num = s1.waybill_num(+)
           and o.waybill_num = s2.waybill_num(+)
           and o.id = c1.order_id(+)
           and o.waybill_num = c2.waybill_num(+)
           and o.waybill_num = c3.waybill_num(+)
           and o.waybill_num = c4.waybill_num(+)
           and o.id = track.order_id(+)
           and o.waybill_num = op.waybill_num(+)
         group by em.name
        
        union all
        
        select '海外仓尾程' T,
               em.name,
               nvl(sum(s1.amount), 0) + nvl(sum(s2.amount), 0) order_amount,
               nvl(sum(s1.amount), 0) + nvl(sum(s2.amount), 0) -
               nvl(sum(c3.amount), 0) + nvl(sum(c4.REFUND_AMOUNT), 0) -
               nvl(sum(c2.amount), 0) profit_amount,
               nvl(sum(c3.amount), 0) - nvl(sum(c4.REFUND_AMOUNT), 0) sp_amount,
               nvl(sum(c3.weight), 0) sp_weight,
               sum(sit.weight) weight,
               sum(sit.weight) scale_weight,
               0 volumetric_weight,
               sum(skunum) order_pieces,
               count(o.id) order_count,
               count(op.waybill_num) delivery_number,
               (case
                 when (nvl(count(op.waybill_num), 0) / nvl(count(o.id), 0)) = 0 then
                  '0'
                 else
                  (round(((nvl(count(op.waybill_num), 0) /
                         nvl(count(o.id), 0))) * 100,
                         0)) || '%'
               end) delivery_rate,
               round(nvl(sum(ceil(track.time - o.create_date)), 0) /
                     nvl(count(o.id), 0),
                     0) average_days
          from jnetwlms.t_sendout_order o,
               jnetwlms.t_client cli,
               jnetwlms.t_express_model em,
               (SELECT waybill_num, nvl(sum(amount), 0) amount
                  FROM jnetwlms.t_client_bill_item
                 where TYPE = 1
                 group by waybill_num) s1,
               (SELECT waybill_num, nvl(sum(amount), 0) amount
                  FROM jnetwlms.t_charge
                 group by waybill_num) s2,
               (SELECT waybill_num, nvl(sum(amount), 0) amount
                  FROM jnetwlms.t_compensation
                 group by waybill_num) c2,
               (SELECT waybill_num,
                       nvl(sum(amount), 0) amount,
                       nvl(sum(weight), 0) weight
                  FROM jnetwlms.t_service_provider_bill_item
                 group by waybill_num) c3,
               (SELECT waybill_num, nvl(sum(REFUND_AMOUNT), 0) REFUND_AMOUNT
                  FROM jnetwlms.t_service_provider_refund_item
                 group by waybill_num) c4,
               (select *
                  from (select ot.order_id,
                               ot.time,
                               row_number() over(partition by ot.order_id order by ot.time desc) rn
                          from jnetwlms.t_tracking_item ot)
                 where rn = 1) track,
               (select od.waybill_num, od.create_date, od.modify_date
                  from jnetwlms.t_sendout_order od
                 where od.status = 30) op,
               (SELECT si.sendout_order_id,
                       sum(nvl(si.quantity, 0) * nvl(si.weight, 0)) weight,
                       sum(nvl(si.quantity, 0)) skunum
                  FROM jnetwlms.t_sendout_item si
                 group by si.sendout_order_id) sit
         where 2 = 2
           and to_char(o.create_date, 'yyyymmdd') >= '20190801'
           and to_char(o.create_date, 'yyyymmdd') < '20190901'
           and nvl(cli.feature, 0) <> 'TEST'
           and o.id = sit.sendout_order_id
           and o.client_id = cli.id
           and o.model_code = em.code(+)
           and o.waybill_num = s1.waybill_num(+)
           and o.waybill_num = s2.waybill_num(+)
           and o.waybill_num = c2.waybill_num(+)
           and o.waybill_num = c3.waybill_num(+)
           and o.waybill_num = c4.waybill_num(+)
           and o.id = track.order_id(+)
           and o.waybill_num = op.waybill_num(+)
         group by em.name
        
        union all
        select '海外仓头程' T,
               em.name,
               nvl(sum(s1.amount), 0) + nvl(sum(s2.amount), 0) order_amount,
               nvl(sum(s1.amount), 0) + nvl(sum(s2.amount), 0) -
               nvl(sum(c3.amount), 0) - nvl(sum(c1.amount), 0) +
               nvl(sum(c4.REFUND_AMOUNT), 0) - nvl(sum(c2.amount), 0) profit_amount,
               nvl(sum(c3.amount), 0) - nvl(sum(c4.REFUND_AMOUNT), 0) sp_amount,
               nvl(sum(c3.weight), 0) sp_weight,
               sum(o.ACTUAL_WEIGHT) weight,
               sum(o.scale_weight) scale_weight,
               sum(o.volumetric_weight) volumetric_weight,
               count(o.id) order_pieces,
               count(o.id) order_count,
               count(op.waybill_num) delivery_number,
               (case
                 when (nvl(count(op.waybill_num), 0) / nvl(count(o.id), 0)) = 0 then
                  '0'
                 else
                  (round(((nvl(count(op.waybill_num), 0) /
                         nvl(count(o.id), 0))) * 100,
                         0)) || '%'
               end) delivery_rate,
               round(nvl(sum(ceil(track.time - o.create_date)), 0) /
                     nvl(count(o.id), 0),
                     0) average_days
          from jnetfba.t_order           o,
               jnetfba.t_client          cli,
               jnetfba.t_product_channel em,
               
               (SELECT order_id, nvl(sum(amount), 0) amount
                  FROM jnetfba.t_order_fee
                 group by order_id) s1,
               
               (SELECT waybill_num, nvl(sum(amount), 0) amount
                  FROM jnetfba.t_charge
                 group by waybill_num) s2,
               
               (SELECT order_id, nvl(sum(amount), 0) amount
                  FROM jnetfba.t_cost
                 group by order_id) c1,
               (SELECT waybill_num, nvl(sum(amount), 0) amount
                  FROM jnetfba.t_compensation
                 group by waybill_num) c2,
               
               (SELECT waybill_num,
                       nvl(sum(amount), 0) amount,
                       nvl(sum(weight), 0) weight
                  FROM jnetfba.t_service_provider_bill_item
                 group by waybill_num) c3,
               (SELECT waybill_num, nvl(sum(REFUND_AMOUNT), 0) REFUND_AMOUNT
                  FROM jnetfba.t_service_provider_refund_item
                 group by waybill_num) c4,
               
               (select *
                  from (select ot.order_id,
                               ot.time,
                               row_number() over(partition by ot.order_id order by ot.time desc) rn
                          from jnetfba.t_tracking_item ot)
                 where rn = 1) track,
               (select od.waybill_num, od.STORAGE_DATE, od.OUT_DATE
                  from jnetfba.t_order od
                 where od.status = 30) op
         where 3 = 3
           and to_char(o.create_date, 'yyyymmdd') >= '20190801'
           and to_char(o.create_date, 'yyyymmdd') < '20190901'
           and nvl(cli.feature, 0) <> 'TEST'
           and o.client_id = cli.id
           and o.express_model_code = em.code(+)
           and o.id = s1.order_id(+)
           and o.waybill_num = s2.waybill_num(+)
           and o.id = c1.order_id(+)
           and o.waybill_num = c2.waybill_num(+)
           and o.waybill_num = c3.waybill_num(+)
           and o.waybill_num = c4.waybill_num(+)
           and o.id = track.order_id(+)
           and o.waybill_num = op.waybill_num(+)
         group by em.name)


name: 金蝶凭证
parent_menu: 金蝶
dbtype: Kindee
report_sql:
select m.FVoucherID ,m.FDate,m.FYear,m.FPeriod, m.FNumber,d.FExplanation,d.FAmount,d.FAmountFor,d.FQuantity  from t_Voucher m left join  t_VoucherEntry d on m.FVoucherID = d.FVoucherID where 1=1 and (@FID.length = 0 or  m.FNumber =@FID  ) and (@Y.length = 0 or  m.FYear=@Y) and (@M.length = 0 or  m.FPeriod=@M)   and  d.FExplanation like '%'+@description+'%'


name: 三个系统业务量（按天）
create_date: 2019-10-10
parent_menu: Data
dbtype: JnetRpt
report_sql:
with performe as
 (select '快递系统' T,
         --o.waybill_num,
         to_char(o.create_date, 'yyyymmdd') cdate,
         cli.manager_name,
         em.name,
         nvl(sum(o.total_amount), 0) oamount,
         nvl(sum(s1.amount), 0) /*+ nvl(sum(s2.amount), 0)*/ order_amount,
         nvl(sum(s1.amount), 0) + nvl(sum(s2.amount), 0) -
         nvl(sum(c1.amount), 0) - nvl(sum(c2.amount), 0) -
         nvl(sum(c3.amount), 0) + nvl(sum(c4.REFUND_AMOUNT), 0) profit_amount,
         nvl(sum(c3.amount), 0) - nvl(sum(c4.REFUND_AMOUNT), 0) sp_amount,
         nvl(sum(c3.weight), 0) sp_weight,
         sum(o.weight) weight,
         sum(o.scale_weight) scale_weight,                                                                                                                                                                                                                                                                                                                                                                    
         sum(o.volumetric_weight) volumetric_weight,
         sum(o.pieces) order_pieces,
         count(o.id) order_count,
         count(op.waybill_num) delivery_number,
         (case
           when (nvl(count(op.waybill_num), 0) / nvl(count(o.id), 0)) = 0 then
            '0'
           else
            (round(((nvl(count(op.waybill_num), 0) / nvl(count(o.id), 0))) * 100,
                   0)) || '%'
         end) delivery_rate,
         round(nvl(sum(ceil(track.time - o.create_date)), 0) /
               nvl(count(o.id), 0),
               0) average_days
    FROM jnettms.T_ORDER o,
         jnettms.t_client cli,
         jnettms.t_express_model em,
         (SELECT waybill_num, nvl(sum(amount), 0) amount
            FROM jnettms.t_client_bill_item
           where TYPE = 1
           group by waybill_num) s1,
         (SELECT waybill_num, nvl(sum(amount), 0) amount
            FROM jnettms.t_charge
           group by waybill_num) s2,
         (SELECT order_id, nvl(sum(amount), 0) amount
            FROM jnettms.t_cost
           group by order_id) c1,
         (SELECT waybill_num, nvl(sum(amount), 0) amount
            FROM jnettms.t_compensation
           group by waybill_num) c2,
         (SELECT waybill_num,
                 nvl(sum(amount), 0) amount,
                 nvl(sum(weight), 0) weight
            FROM jnettms.t_service_provider_bill_item
           group by waybill_num) c3,
         (SELECT waybill_num, nvl(sum(REFUND_AMOUNT), 0) REFUND_AMOUNT
            FROM jnettms.t_service_provider_refund_item
           group by waybill_num) c4,
         
         (select *
            from (select ot.order_id,
                         ot.time,
                         row_number() over(partition by ot.order_id order by ot.time desc) rn
                    from jnettms.t_Tracking_Item ot)
           where rn = 1) track,
         (select od.waybill_num, od.create_date, od.modify_date
            from jnettms.t_order od
           where od.status = 30) op
   where 1 = 1
        and (@start_date.length = 0 or
         to_char(o.create_date, 'yyyy-mm-dd') >= @start_date)
     and (@end_date.length = 0 or
         to_char(o.create_date, 'yyyy-mm-dd') <= @end_date)
     and nvl(cli.feature, 0) <> 'TEST'
     and o.client_id = cli.id
     and o.express_model_code = em.code(+)
     and o.waybill_num = s1.waybill_num(+)
     and o.waybill_num = s2.waybill_num(+)
     and o.id = c1.order_id(+)
     and o.waybill_num = c2.waybill_num(+)
     and o.waybill_num = c3.waybill_num(+)
     and o.waybill_num = c4.waybill_num(+)
     and o.id = track.order_id(+)
     and o.waybill_num = op.waybill_num(+)
   group by --o.waybill_num, 
            cli.manager_name,
            to_char(o.create_date, 'yyyymmdd'),
            em.name
  
  union all
  
  select '海外仓尾程' T,
         --o.waybill_num,
         to_char(o.create_date, 'yyyymmdd') cdate,
         cli.manager_name,
         em.name,
         nvl(sum(o.amount), 0) oamount,
         nvl(sum(s1.amount), 0) /*+ nvl(sum(s2.amount), 0)*/ order_amount,
         nvl(sum(s1.amount), 0) + nvl(sum(s2.amount), 0) -
         nvl(sum(c3.amount), 0) + nvl(sum(c4.REFUND_AMOUNT), 0) -
         nvl(sum(c2.amount), 0) profit_amount,
         nvl(sum(c3.amount), 0) - nvl(sum(c4.REFUND_AMOUNT), 0) sp_amount,
         nvl(sum(c3.weight), 0) sp_weight,
         sum(sit.weight) weight,
         sum(sit.weight) scale_weight,
         0 volumetric_weight,
         sum(skunum) order_pieces,
         count(o.id) order_count,
         count(op.waybill_num) delivery_number,
         (case
           when (nvl(count(op.waybill_num), 0) / nvl(count(o.id), 0)) = 0 then
            '0'
           else
            (round(((nvl(count(op.waybill_num), 0) / nvl(count(o.id), 0))) * 100,
                   0)) || '%'
         end) delivery_rate,
         round(nvl(sum(ceil(track.time - o.create_date)), 0) /
               nvl(count(o.id), 0),
               0) average_days
    from jnetwlms.t_sendout_order o,
         jnetwlms.t_client cli,
         jnetwlms.t_express_model em,
         (SELECT waybill_num, nvl(sum(amount), 0) amount
            FROM jnetwlms.t_client_bill_item
           where TYPE = 1
           group by waybill_num) s1,
         (SELECT waybill_num, nvl(sum(amount), 0) amount
            FROM jnetwlms.t_charge
           group by waybill_num) s2,
         (SELECT waybill_num, nvl(sum(amount), 0) amount
            FROM jnetwlms.t_compensation
           group by waybill_num) c2,
         (SELECT waybill_num,
                 nvl(sum(amount), 0) amount,
                 nvl(sum(weight), 0) weight
            FROM jnetwlms.t_service_provider_bill_item
           group by waybill_num) c3,
         (SELECT waybill_num, nvl(sum(REFUND_AMOUNT), 0) REFUND_AMOUNT
            FROM jnetwlms.t_service_provider_refund_item
           group by waybill_num) c4,
         (select *
            from (select ot.order_id,
                         ot.time,
                         row_number() over(partition by ot.order_id order by ot.time desc) rn
                    from jnetwlms.t_tracking_item ot)
           where rn = 1) track,
         (select od.waybill_num, od.create_date, od.modify_date
            from jnetwlms.t_sendout_order od
           where od.status = 30) op,
         (SELECT si.sendout_order_id,
                 sum(nvl(si.quantity, 0) * nvl(si.weight, 0)) weight,
                 sum(nvl(si.quantity, 0)) skunum
            FROM jnetwlms.t_sendout_item si
           group by si.sendout_order_id) sit
   where 2 = 2
       and (@start_date.length = 0 or
         to_char(o.create_date, 'yyyy-mm-dd') >= @start_date)
     and (@end_date.length = 0 or
         to_char(o.create_date, 'yyyy-mm-dd') <= @end_date)
     and nvl(cli.feature, 0) <> 'TEST'
     and o.id = sit.sendout_order_id
     and o.client_id = cli.id
     and o.model_code = em.code(+)
     and o.waybill_num = s1.waybill_num(+)
     and o.waybill_num = s2.waybill_num(+)
     and o.waybill_num = c2.waybill_num(+)
     and o.waybill_num = c3.waybill_num(+)
     and o.waybill_num = c4.waybill_num(+)
     and o.id = track.order_id(+)
     and o.waybill_num = op.waybill_num(+)
   group by --o.waybill_num, 
            cli.manager_name,
            to_char(o.create_date, 'yyyymmdd'),
            em.name
  
  union all
  select '海外仓头程' T,
         --o.waybill_num,
         to_char(o.create_date, 'yyyymmdd') cdate,
         cli.manager_name,
         em.name,
         nvl(sum(o.total_amount), 0) oamount,
         nvl(sum(s1.amount), 0) /*+ nvl(sum(s2.amount), 0)*/ order_amount,
         nvl(sum(s1.amount), 0) + nvl(sum(s2.amount), 0) -
         nvl(sum(c3.amount), 0) - nvl(sum(c1.amount), 0) +
         nvl(sum(c4.REFUND_AMOUNT), 0) - nvl(sum(c2.amount), 0) profit_amount,
         nvl(sum(c3.amount), 0) - nvl(sum(c4.REFUND_AMOUNT), 0) sp_amount,
         nvl(sum(c3.weight), 0) sp_weight,
         sum(o.ACTUAL_WEIGHT) weight,
         sum(o.scale_weight) scale_weight,
         sum(o.volumetric_weight) volumetric_weight,
         count(o.id) order_pieces,
         count(o.id) order_count,
         count(op.waybill_num) delivery_number,
         (case
           when (nvl(count(op.waybill_num), 0) / nvl(count(o.id), 0)) = 0 then
            '0'
           else
            (round(((nvl(count(op.waybill_num), 0) / nvl(count(o.id), 0))) * 100,
                   0)) || '%'
         end) delivery_rate,
         round(nvl(sum(ceil(track.time - o.create_date)), 0) /
               nvl(count(o.id), 0),
               0) average_days
    from jnetfba.t_order           o,
         jnetfba.t_client          cli,
         jnetfba.t_product_channel em,
         
         (SELECT order_id, nvl(sum(amount), 0) amount
            FROM jnetfba.t_order_fee
           group by order_id) s1,
         
         (SELECT waybill_num, nvl(sum(amount), 0) amount
            FROM jnetfba.t_charge
           group by waybill_num) s2,
         
         (SELECT order_id, nvl(sum(amount), 0) amount
            FROM jnetfba.t_cost
           group by order_id) c1,
         (SELECT waybill_num, nvl(sum(amount), 0) amount
            FROM jnetfba.t_compensation
           group by waybill_num) c2,
         
         (SELECT waybill_num,
                 nvl(sum(amount), 0) amount,
                 nvl(sum(weight), 0) weight
            FROM jnetfba.t_service_provider_bill_item
           group by waybill_num) c3,
         (SELECT waybill_num, nvl(sum(REFUND_AMOUNT), 0) REFUND_AMOUNT
            FROM jnetfba.t_service_provider_refund_item
           group by waybill_num) c4,
         
         (select *
            from (select ot.order_id,
                         ot.time,
                         row_number() over(partition by ot.order_id order by ot.time desc) rn
                    from jnetfba.t_tracking_item ot)
           where rn = 1) track,
         (select od.waybill_num, od.STORAGE_DATE, od.OUT_DATE
            from jnetfba.t_order od
           where od.status = 30) op
   where 3 = 3
        and (@start_date.length = 0 or
         to_char(o.create_date, 'yyyy-mm-dd') >= @start_date)
        and (@end_date.length = 0 or
         to_char(o.create_date, 'yyyy-mm-dd') <= @end_date)
     and nvl(cli.feature, 0) <> 'TEST'
     and o.client_id = cli.id
     and o.express_model_code = em.code(+)
     and o.id = s1.order_id(+)
     and o.waybill_num = s2.waybill_num(+)
     and o.id = c1.order_id(+)
     and o.waybill_num = c2.waybill_num(+)
     and o.waybill_num = c3.waybill_num(+)
     and o.waybill_num = c4.waybill_num(+)
     and o.id = track.order_id(+)
     and o.waybill_num = op.waybill_num(+)
   group by --o.waybill_num, 
            cli.manager_name,
            to_char(o.create_date, 'yyyymmdd'),
            em.name)

SELECT
       p.t 系统, 
       p.name 渠道,
       p.cdate 日期,
       --p.manager_name 经办,
       sum(p.ORDER_COUNT) 票数,
       sum(p.WEIGHT) 重量,
       sum(p.oamount) 应收金额,
       sum(p.ORDER_AMOUNT) 实收金额
--sum(p.SCALE_WEIGHT) 称重, 
--sum(p.PROFIT_AMOUNT) 毛利 , 
--sum(p.ORDER_PIECES) 件数
  FROM performe p where  1=1 -- and substr(p.cdate,0,6) = '201909'
 group by p.cdate, p.name, p.t
union all
SELECT
       '' 系统, 
       '' 渠道,
       '' 日期, 
       sum(p.ORDER_COUNT) 票数,
       sum(p.WEIGHT) 重量,
       sum(p.oamount) 应收金额,
       sum(p.ORDER_AMOUNT) 实收金额 
  FROM performe p 




name: 三个系统业务量（按月）
create_date: 2019-10-10
parent_menu: Data
dbtype: JnetRpt
report_sql:
with performe as
 (select '快递系统' T,
         --o.waybill_num,
         to_char(o.create_date, 'yyyymm') cdate,
         cli.manager_name,
         em.name,
         nvl(sum(o.total_amount), 0) oamount,
         nvl(sum(s1.amount), 0) /*+ nvl(sum(s2.amount), 0)*/ order_amount,
         nvl(sum(s1.amount), 0) + nvl(sum(s2.amount), 0) -
         nvl(sum(c1.amount), 0) - nvl(sum(c2.amount), 0) -
         nvl(sum(c3.amount), 0) + nvl(sum(c4.REFUND_AMOUNT), 0) profit_amount,
         nvl(sum(c3.amount), 0) - nvl(sum(c4.REFUND_AMOUNT), 0) sp_amount,
         nvl(sum(c3.weight), 0) sp_weight,
         sum(o.weight) weight,
         sum(o.scale_weight) scale_weight,                                                                                                                                                                                                                                                                                                                                                                    
         sum(o.volumetric_weight) volumetric_weight,
         sum(o.pieces) order_pieces,
         count(o.id) order_count,
         count(op.waybill_num) delivery_number,
         (case
           when (nvl(count(op.waybill_num), 0) / nvl(count(o.id), 0)) = 0 then
            '0'
           else
            (round(((nvl(count(op.waybill_num), 0) / nvl(count(o.id), 0))) * 100,
                   0)) || '%'
         end) delivery_rate,
         round(nvl(sum(ceil(track.time - o.create_date)), 0) /
               nvl(count(o.id), 0),
               0) average_days
    FROM jnettms.T_ORDER o,
         jnettms.t_client cli,
         jnettms.t_express_model em,
         (SELECT waybill_num, nvl(sum(amount), 0) amount
            FROM jnettms.t_client_bill_item
           where TYPE = 1
           group by waybill_num) s1,
         (SELECT waybill_num, nvl(sum(amount), 0) amount
            FROM jnettms.t_charge
           group by waybill_num) s2,
         (SELECT order_id, nvl(sum(amount), 0) amount
            FROM jnettms.t_cost
           group by order_id) c1,
         (SELECT waybill_num, nvl(sum(amount), 0) amount
            FROM jnettms.t_compensation
           group by waybill_num) c2,
         (SELECT waybill_num,
                 nvl(sum(amount), 0) amount,
                 nvl(sum(weight), 0) weight
            FROM jnettms.t_service_provider_bill_item
           group by waybill_num) c3,
         (SELECT waybill_num, nvl(sum(REFUND_AMOUNT), 0) REFUND_AMOUNT
            FROM jnettms.t_service_provider_refund_item
           group by waybill_num) c4,
         
         (select *
            from (select ot.order_id,
                         ot.time,
                         row_number() over(partition by ot.order_id order by ot.time desc) rn
                    from jnettms.t_Tracking_Item ot)
           where rn = 1) track,
         (select od.waybill_num, od.create_date, od.modify_date
            from jnettms.t_order od
           where od.status = 30) op
   where 1 = 1
      and (@start_date.length = 0 or
         to_char(o.create_date, 'yyyy-mm-dd') >= @start_date)
     and (@end_date.length = 0 or
         to_char(o.create_date, 'yyyy-mm-dd') <= @end_date)
     and nvl(cli.feature, 0) <> 'TEST'
     and o.client_id = cli.id
     and o.express_model_code = em.code(+)
     and o.waybill_num = s1.waybill_num(+)
     and o.waybill_num = s2.waybill_num(+)
     and o.id = c1.order_id(+)
     and o.waybill_num = c2.waybill_num(+)
     and o.waybill_num = c3.waybill_num(+)
     and o.waybill_num = c4.waybill_num(+)
     and o.id = track.order_id(+)
     and o.waybill_num = op.waybill_num(+)
   group by --o.waybill_num, 
            cli.manager_name,
            to_char(o.create_date, 'yyyymm'),
            em.name
  
  union all
  
  select '海外仓尾程' T,
         --o.waybill_num,
         to_char(o.create_date, 'yyyymm') cdate,
         cli.manager_name,
         em.name,
         nvl(sum(o.amount), 0) oamount,
         nvl(sum(s1.amount), 0) /*+ nvl(sum(s2.amount), 0)*/ order_amount,
         nvl(sum(s1.amount), 0) + nvl(sum(s2.amount), 0) -
         nvl(sum(c3.amount), 0) + nvl(sum(c4.REFUND_AMOUNT), 0) -
         nvl(sum(c2.amount), 0) profit_amount,
         nvl(sum(c3.amount), 0) - nvl(sum(c4.REFUND_AMOUNT), 0) sp_amount,
         nvl(sum(c3.weight), 0) sp_weight,
         sum(sit.weight) weight,
         sum(sit.weight) scale_weight,
         0 volumetric_weight,
         sum(skunum) order_pieces,
         count(o.id) order_count,
         count(op.waybill_num) delivery_number,
         (case
           when (nvl(count(op.waybill_num), 0) / nvl(count(o.id), 0)) = 0 then
            '0'
           else
            (round(((nvl(count(op.waybill_num), 0) / nvl(count(o.id), 0))) * 100,
                   0)) || '%'
         end) delivery_rate,
         round(nvl(sum(ceil(track.time - o.create_date)), 0) /
               nvl(count(o.id), 0),
               0) average_days
    from jnetwlms.t_sendout_order o,
         jnetwlms.t_client cli,
         jnetwlms.t_express_model em,
         (SELECT waybill_num, nvl(sum(amount), 0) amount
            FROM jnetwlms.t_client_bill_item
           where TYPE = 1
           group by waybill_num) s1,
         (SELECT waybill_num, nvl(sum(amount), 0) amount
            FROM jnetwlms.t_charge
           group by waybill_num) s2,
         (SELECT waybill_num, nvl(sum(amount), 0) amount
            FROM jnetwlms.t_compensation
           group by waybill_num) c2,
         (SELECT waybill_num,
                 nvl(sum(amount), 0) amount,
                 nvl(sum(weight), 0) weight
            FROM jnetwlms.t_service_provider_bill_item
           group by waybill_num) c3,
         (SELECT waybill_num, nvl(sum(REFUND_AMOUNT), 0) REFUND_AMOUNT
            FROM jnetwlms.t_service_provider_refund_item
           group by waybill_num) c4,
         (select *
            from (select ot.order_id,
                         ot.time,
                         row_number() over(partition by ot.order_id order by ot.time desc) rn
                    from jnetwlms.t_tracking_item ot)
           where rn = 1) track,
         (select od.waybill_num, od.create_date, od.modify_date
            from jnetwlms.t_sendout_order od
           where od.status = 30) op,
         (SELECT si.sendout_order_id,
                 sum(nvl(si.quantity, 0) * nvl(si.weight, 0)) weight,
                 sum(nvl(si.quantity, 0)) skunum
            FROM jnetwlms.t_sendout_item si
           group by si.sendout_order_id) sit
   where 2 = 2
       and (@start_date.length = 0 or
         to_char(o.create_date, 'yyyy-mm-dd') >= @start_date)
     and (@end_date.length = 0 or
         to_char(o.create_date, 'yyyy-mm-dd') <= @end_date)
     and nvl(cli.feature, 0) <> 'TEST'
     and o.id = sit.sendout_order_id
     and o.client_id = cli.id
     and o.model_code = em.code(+)
     and o.waybill_num = s1.waybill_num(+)
     and o.waybill_num = s2.waybill_num(+)
     and o.waybill_num = c2.waybill_num(+)
     and o.waybill_num = c3.waybill_num(+)
     and o.waybill_num = c4.waybill_num(+)
     and o.id = track.order_id(+)
     and o.waybill_num = op.waybill_num(+)
   group by --o.waybill_num, 
            cli.manager_name,
            to_char(o.create_date, 'yyyymm'),
            em.name
  
  union all
  select '海外仓头程' T,
         --o.waybill_num,
         to_char(o.create_date, 'yyyymm') cdate,
         cli.manager_name,
         em.name,
         nvl(sum(o.total_amount), 0) oamount,
         nvl(sum(s1.amount), 0) /*+ nvl(sum(s2.amount), 0)*/ order_amount,
         nvl(sum(s1.amount), 0) + nvl(sum(s2.amount), 0) -
         nvl(sum(c3.amount), 0) - nvl(sum(c1.amount), 0) +
         nvl(sum(c4.REFUND_AMOUNT), 0) - nvl(sum(c2.amount), 0) profit_amount,
         nvl(sum(c3.amount), 0) - nvl(sum(c4.REFUND_AMOUNT), 0) sp_amount,
         nvl(sum(c3.weight), 0) sp_weight,
         sum(o.ACTUAL_WEIGHT) weight,
         sum(o.scale_weight) scale_weight,
         sum(o.volumetric_weight) volumetric_weight,
         count(o.id) order_pieces,
         count(o.id) order_count,
         count(op.waybill_num) delivery_number,
         (case
           when (nvl(count(op.waybill_num), 0) / nvl(count(o.id), 0)) = 0 then
            '0'
           else
            (round(((nvl(count(op.waybill_num), 0) / nvl(count(o.id), 0))) * 100,
                   0)) || '%'
         end) delivery_rate,
         round(nvl(sum(ceil(track.time - o.create_date)), 0) /
               nvl(count(o.id), 0),
               0) average_days
    from jnetfba.t_order           o,
         jnetfba.t_client          cli,
         jnetfba.t_product_channel em,
         
         (SELECT order_id, nvl(sum(amount), 0) amount
            FROM jnetfba.t_order_fee
           group by order_id) s1,
         
         (SELECT waybill_num, nvl(sum(amount), 0) amount
            FROM jnetfba.t_charge
           group by waybill_num) s2,
         
         (SELECT order_id, nvl(sum(amount), 0) amount
            FROM jnetfba.t_cost
           group by order_id) c1,
         (SELECT waybill_num, nvl(sum(amount), 0) amount
            FROM jnetfba.t_compensation
           group by waybill_num) c2,
         
         (SELECT waybill_num,
                 nvl(sum(amount), 0) amount,
                 nvl(sum(weight), 0) weight
            FROM jnetfba.t_service_provider_bill_item
           group by waybill_num) c3,
         (SELECT waybill_num, nvl(sum(REFUND_AMOUNT), 0) REFUND_AMOUNT
            FROM jnetfba.t_service_provider_refund_item
           group by waybill_num) c4,
         
         (select *
            from (select ot.order_id,
                         ot.time,
                         row_number() over(partition by ot.order_id order by ot.time desc) rn
                    from jnetfba.t_tracking_item ot)
           where rn = 1) track,
         (select od.waybill_num, od.STORAGE_DATE, od.OUT_DATE
            from jnetfba.t_order od
           where od.status = 30) op
   where 3 = 3
       and (@start_date.length = 0 or
         to_char(o.create_date, 'yyyy-mm-dd') >= @start_date)
       and (@end_date.length = 0 or
         to_char(o.create_date, 'yyyy-mm-dd') <= @end_date)
     and nvl(cli.feature, 0) <> 'TEST'
     and o.client_id = cli.id
     and o.express_model_code = em.code(+)
     and o.id = s1.order_id(+)
     and o.waybill_num = s2.waybill_num(+)
     and o.id = c1.order_id(+)
     and o.waybill_num = c2.waybill_num(+)
     and o.waybill_num = c3.waybill_num(+)
     and o.waybill_num = c4.waybill_num(+)
     and o.id = track.order_id(+)
     and o.waybill_num = op.waybill_num(+)
   group by --o.waybill_num, 
            cli.manager_name,
            to_char(o.create_date, 'yyyymm'),
            em.name)

SELECT p.t 系统,
       p.cdate 月份,
       p.manager_name 经办,
       p.name 渠道,
       sum(p.ORDER_COUNT) 票数,
       sum(p.WEIGHT) 重量,
       sum(p.oamount) 应收金额,
       sum(p.ORDER_AMOUNT) 实收金额
--sum(p.SCALE_WEIGHT) 称重, 
--sum(p.PROFIT_AMOUNT) 毛利 , 
--sum(p.ORDER_PIECES) 件数
  FROM performe p  
 group by p.cdate, p.name, p.manager_name, p.t 
union all

SELECT '' 系统,
       '' 月份,
       '' 经办,
       '' 渠道,
       sum(p.ORDER_COUNT) 票数,
       sum(p.WEIGHT) 重量,
       sum(p.oamount) 应收金额,
       sum(p.ORDER_AMOUNT) 实收金额 
  FROM performe p  
  


name: 三个系统业务量（线路分类）
create_date: 2019-10-11
parent_menu: Data
dbtype: JnetRpt
report_sql:
with performe as
 (select '快递系统' T,
         --o.waybill_num,
         to_char(o.create_date, 'yyyymmdd') cdate,
         cli.manager_name,
         em.name,
         nvl(sum(o.total_amount), 0) oamount,
         nvl(sum(s1.amount), 0) /*+ nvl(sum(s2.amount), 0)*/ order_amount,
         nvl(sum(s1.amount), 0) + nvl(sum(s2.amount), 0) -
         nvl(sum(c1.amount), 0) - nvl(sum(c2.amount), 0) -
         nvl(sum(c3.amount), 0) + nvl(sum(c4.REFUND_AMOUNT), 0) profit_amount,
         nvl(sum(c3.amount), 0) - nvl(sum(c4.REFUND_AMOUNT), 0) sp_amount,
         nvl(sum(c3.weight), 0) sp_weight,
         sum(o.weight) weight,
         sum(o.scale_weight) scale_weight,                                                                                                                                                                                                                                                                                                                                                                    
         sum(o.volumetric_weight) volumetric_weight,
         sum(o.pieces) order_pieces,
         count(o.id) order_count,
         count(op.waybill_num) delivery_number,
         (case
           when (nvl(count(op.waybill_num), 0) / nvl(count(o.id), 0)) = 0 then
            '0'
           else
            (round(((nvl(count(op.waybill_num), 0) / nvl(count(o.id), 0))) * 100,
                   0)) || '%'
         end) delivery_rate,
         round(nvl(sum(ceil(track.time - o.create_date)), 0) /
               nvl(count(o.id), 0),
               0) average_days
    FROM jnettms.T_ORDER o,
         jnettms.t_client cli,
         jnettms.t_express_model em,
         (SELECT waybill_num, nvl(sum(amount), 0) amount
            FROM jnettms.t_client_bill_item
           where TYPE = 1
           group by waybill_num) s1,
         (SELECT waybill_num, nvl(sum(amount), 0) amount
            FROM jnettms.t_charge
           group by waybill_num) s2,
         (SELECT order_id, nvl(sum(amount), 0) amount
            FROM jnettms.t_cost
           group by order_id) c1,
         (SELECT waybill_num, nvl(sum(amount), 0) amount
            FROM jnettms.t_compensation
           group by waybill_num) c2,
         (SELECT waybill_num,
                 nvl(sum(amount), 0) amount,
                 nvl(sum(weight), 0) weight
            FROM jnettms.t_service_provider_bill_item
           group by waybill_num) c3,
         (SELECT waybill_num, nvl(sum(REFUND_AMOUNT), 0) REFUND_AMOUNT
            FROM jnettms.t_service_provider_refund_item
           group by waybill_num) c4,
         
         (select *
            from (select ot.order_id,
                         ot.time,
                         row_number() over(partition by ot.order_id order by ot.time desc) rn
                    from jnettms.t_Tracking_Item ot)
           where rn = 1) track,
         (select od.waybill_num, od.create_date, od.modify_date
            from jnettms.t_order od
           where od.status = 30) op
   where 1 = 1
        and (@start_date.length = 0 or
         to_char(o.create_date, 'yyyy-mm-dd') >= @start_date)
     and (@end_date.length = 0 or
         to_char(o.create_date, 'yyyy-mm-dd') <= @end_date)
     and nvl(cli.feature, 0) <> 'TEST'
     and o.client_id = cli.id
     and o.express_model_code = em.code(+)
     and o.waybill_num = s1.waybill_num(+)
     and o.waybill_num = s2.waybill_num(+)
     and o.id = c1.order_id(+)
     and o.waybill_num = c2.waybill_num(+)
     and o.waybill_num = c3.waybill_num(+)
     and o.waybill_num = c4.waybill_num(+)
     and o.id = track.order_id(+)
     and o.waybill_num = op.waybill_num(+)
   group by --o.waybill_num, 
            cli.manager_name,
            to_char(o.create_date, 'yyyymmdd'),
            em.name
  
  union all
  
  select '海外仓尾程' T,
         --o.waybill_num,
         to_char(o.create_date, 'yyyymmdd') cdate,
         cli.manager_name,
         em.name,
         nvl(sum(o.amount), 0) oamount,
         nvl(sum(s1.amount), 0) /*+ nvl(sum(s2.amount), 0)*/ order_amount,
         nvl(sum(s1.amount), 0) + nvl(sum(s2.amount), 0) -
         nvl(sum(c3.amount), 0) + nvl(sum(c4.REFUND_AMOUNT), 0) -
         nvl(sum(c2.amount), 0) profit_amount,
         nvl(sum(c3.amount), 0) - nvl(sum(c4.REFUND_AMOUNT), 0) sp_amount,
         nvl(sum(c3.weight), 0) sp_weight,
         sum(sit.weight) weight,
         sum(sit.weight) scale_weight,
         0 volumetric_weight,
         sum(skunum) order_pieces,
         count(o.id) order_count,
         count(op.waybill_num) delivery_number,
         (case
           when (nvl(count(op.waybill_num), 0) / nvl(count(o.id), 0)) = 0 then
            '0'
           else
            (round(((nvl(count(op.waybill_num), 0) / nvl(count(o.id), 0))) * 100,
                   0)) || '%'
         end) delivery_rate,
         round(nvl(sum(ceil(track.time - o.create_date)), 0) /
               nvl(count(o.id), 0),
               0) average_days
    from jnetwlms.t_sendout_order o,
         jnetwlms.t_client cli,
         jnetwlms.t_express_model em,
         (SELECT waybill_num, nvl(sum(amount), 0) amount
            FROM jnetwlms.t_client_bill_item
           where TYPE = 1
           group by waybill_num) s1,
         (SELECT waybill_num, nvl(sum(amount), 0) amount
            FROM jnetwlms.t_charge
           group by waybill_num) s2,
         (SELECT waybill_num, nvl(sum(amount), 0) amount
            FROM jnetwlms.t_compensation
           group by waybill_num) c2,
         (SELECT waybill_num,
                 nvl(sum(amount), 0) amount,
                 nvl(sum(weight), 0) weight
            FROM jnetwlms.t_service_provider_bill_item
           group by waybill_num) c3,
         (SELECT waybill_num, nvl(sum(REFUND_AMOUNT), 0) REFUND_AMOUNT
            FROM jnetwlms.t_service_provider_refund_item
           group by waybill_num) c4,
         (select *
            from (select ot.order_id,
                         ot.time,
                         row_number() over(partition by ot.order_id order by ot.time desc) rn
                    from jnetwlms.t_tracking_item ot)
           where rn = 1) track,
         (select od.waybill_num, od.create_date, od.modify_date
            from jnetwlms.t_sendout_order od
           where od.status = 30) op,
         (SELECT si.sendout_order_id,
                 sum(nvl(si.quantity, 0) * nvl(si.weight, 0)) weight,
                 sum(nvl(si.quantity, 0)) skunum
            FROM jnetwlms.t_sendout_item si
           group by si.sendout_order_id) sit
   where 2 = 2
       and (@start_date.length = 0 or
         to_char(o.create_date, 'yyyy-mm-dd') >= @start_date)
     and (@end_date.length = 0 or
         to_char(o.create_date, 'yyyy-mm-dd') <= @end_date)
     and nvl(cli.feature, 0) <> 'TEST'
     and o.id = sit.sendout_order_id
     and o.client_id = cli.id
     and o.model_code = em.code(+)
     and o.waybill_num = s1.waybill_num(+)
     and o.waybill_num = s2.waybill_num(+)
     and o.waybill_num = c2.waybill_num(+)
     and o.waybill_num = c3.waybill_num(+)
     and o.waybill_num = c4.waybill_num(+)
     and o.id = track.order_id(+)
     and o.waybill_num = op.waybill_num(+)
   group by --o.waybill_num, 
            cli.manager_name,
            to_char(o.create_date, 'yyyymmdd'),
            em.name
  
  union all
  select '海外仓头程' T,
         --o.waybill_num,
         to_char(o.create_date, 'yyyymmdd') cdate,
         cli.manager_name,
         em.name,
         nvl(sum(o.total_amount), 0) oamount,
         nvl(sum(s1.amount), 0) /*+ nvl(sum(s2.amount), 0)*/ order_amount,
         nvl(sum(s1.amount), 0) + nvl(sum(s2.amount), 0) -
         nvl(sum(c3.amount), 0) - nvl(sum(c1.amount), 0) +
         nvl(sum(c4.REFUND_AMOUNT), 0) - nvl(sum(c2.amount), 0) profit_amount,
         nvl(sum(c3.amount), 0) - nvl(sum(c4.REFUND_AMOUNT), 0) sp_amount,
         nvl(sum(c3.weight), 0) sp_weight,
         sum(o.ACTUAL_WEIGHT) weight,
         sum(o.scale_weight) scale_weight,
         sum(o.volumetric_weight) volumetric_weight,
         count(o.id) order_pieces,
         count(o.id) order_count,
         count(op.waybill_num) delivery_number,
         (case
           when (nvl(count(op.waybill_num), 0) / nvl(count(o.id), 0)) = 0 then
            '0'
           else
            (round(((nvl(count(op.waybill_num), 0) / nvl(count(o.id), 0))) * 100,
                   0)) || '%'
         end) delivery_rate,
         round(nvl(sum(ceil(track.time - o.create_date)), 0) /
               nvl(count(o.id), 0),
               0) average_days
    from jnetfba.t_order           o,
         jnetfba.t_client          cli,
         jnetfba.t_product_channel em,
         
         (SELECT order_id, nvl(sum(amount), 0) amount
            FROM jnetfba.t_order_fee
           group by order_id) s1,
         
         (SELECT waybill_num, nvl(sum(amount), 0) amount
            FROM jnetfba.t_charge
           group by waybill_num) s2,
         
         (SELECT order_id, nvl(sum(amount), 0) amount
            FROM jnetfba.t_cost
           group by order_id) c1,
         (SELECT waybill_num, nvl(sum(amount), 0) amount
            FROM jnetfba.t_compensation
           group by waybill_num) c2,
         
         (SELECT waybill_num,
                 nvl(sum(amount), 0) amount,
                 nvl(sum(weight), 0) weight
            FROM jnetfba.t_service_provider_bill_item
           group by waybill_num) c3,
         (SELECT waybill_num, nvl(sum(REFUND_AMOUNT), 0) REFUND_AMOUNT
            FROM jnetfba.t_service_provider_refund_item
           group by waybill_num) c4,
         
         (select *
            from (select ot.order_id,
                         ot.time,
                         row_number() over(partition by ot.order_id order by ot.time desc) rn
                    from jnetfba.t_tracking_item ot)
           where rn = 1) track,
         (select od.waybill_num, od.STORAGE_DATE, od.OUT_DATE
            from jnetfba.t_order od
           where od.status = 30) op
   where 3 = 3
        and (@start_date.length = 0 or
         to_char(o.create_date, 'yyyy-mm-dd') >= @start_date)
        and (@end_date.length = 0 or
         to_char(o.create_date, 'yyyy-mm-dd') <= @end_date)
     and nvl(cli.feature, 0) <> 'TEST'
     and o.client_id = cli.id
     and o.express_model_code = em.code(+)
     and o.id = s1.order_id(+)
     and o.waybill_num = s2.waybill_num(+)
     and o.id = c1.order_id(+)
     and o.waybill_num = c2.waybill_num(+)
     and o.waybill_num = c3.waybill_num(+)
     and o.waybill_num = c4.waybill_num(+)
     and o.id = track.order_id(+)
     and o.waybill_num = op.waybill_num(+)
   group by --o.waybill_num, 
            cli.manager_name,
            to_char(o.create_date, 'yyyymmdd'),
            em.name)
SELECT 
       p.cdate 日期, 
       c.class 线路分类, 
       sum(p.ORDER_COUNT) 票数,
       sum(p.WEIGHT) 重量,
       sum(p.oamount) 应收金额,
       sum(p.ORDER_AMOUNT) 实收金额 
  FROM performe p left join jnetwlms.t_em_class c on p.name = c.name 
 group by p.cdate, c.class 
union all
SELECT 
       '' 日期, 
       '' 线路分类, 
       sum(p.ORDER_COUNT) 票数,
       sum(p.WEIGHT) 重量,
       sum(p.oamount) 应收金额,
       sum(p.ORDER_AMOUNT) 实收金额 
  FROM performe p  



name: 三系统渠道
create_date: 2019-10-11
dbtype: JnetRpt
report_sql:
select 'TMS', t.code,
       t.name  
  from jnettms.t_express_model t
union all
select 'W', t.code, t.name  
  from jnetwlms.t_express_model t where  type = 2
union all
select 'T', t.code, t.name  
  from jnetwlms.t_express_model t where  type = 1


name: 渠道分类
create_date: 2019-10-11
dbtype: JnetRpt
report_sql:
SELECT * FROM jnetwlms.t_em_class where 1=1 AND (@name.length = 0 or
          NAME like '%'||@name||'%' or class like '%'||@name||'%')


name: 提成数据汇总
create_date: 2019-10-14
dbtype: JnetTms
report_sql:
with a as
 (select
o.create_date, 
c.name client_name,
       o.waybill_num,
       o.des,
     decode(o.status,10,'未发送',11,'已发送',20,'转运中',21,'投递中',22,'到达待取', 30,'签收',41,'转运异常',42,'清关异常',43,'投递异常',44,'操作异常',45,'包裹异常',50,'退件',60,'销毁',70,'丢件') status,
       m.name          express_name,
       o.weight,
       o.manager_name,
       o.check_in_site,
       o.total_amount,
       o.INSURANCE_FEE,
       b.              amount charge_amount,
       p.amount        compensation_amount,
       --b.              type charge_type,
       --p.type          compensation_type,
       --pay.service_name,
       pay.pay,
       not_pay.notPay,
       decode(cbi.PAYMENT_STATUS,0,'未支付',1,'已支付','未生成账单') PAYMENT_STATUS
  from t_order o,t_client c,
  (select distinct (bi.bill_num) bill_num，bi.PAYMENT_STATUS, cb.waybill_num
                from t_client_bill bi, t_client_bill_item cb
                 where bi.id = cb.bill_id and cb.type=1) cbi,   /* and rownum = 1 订单在多个账单里面取一个*/
       (select waybill_num, sum(amount) amount
          from t_client_bill_item
         where type = 1
         group by waybill_num) s,
       (select waybill_num,  sum(nvl(amount, 0)) amount
          from t_charge
         group by waybill_num) b,
       (select waybill_num, sum(nvl(amount, 0)) amount  from t_compensation   group by waybill_num) p,  
       (select bit.waybill_num,
                 /* s.name service_name,*/
                 sum(service_provider_amount) pay
            from t_service_provider_bill      bi,
                 t_service_provider_bill_item bit,
                 t_service_provider           s
           where bi.id = bit.bill_id(+)
             and bi.service_provider_id = s.id(+)
             and bi.payment_status = 1
           group by bit.waybill_num /*, s.name*/ ) pay,
       (select bit.waybill_num, sum(service_provider_amount) notPay
          from t_service_provider_bill bi, t_service_provider_bill_item bit
         where bi.id = bit.bill_id
           and bi.payment_status = 0
         group by bit.waybill_num) not_pay,
       t_express_model m
 where o.waybill_num = s.waybill_num(+)
   and o.waybill_num = p.waybill_num(+)
   and o.waybill_num = b.waybill_num(+)
   and o.waybill_num = pay.waybill_num(+)
   and o.waybill_num = not_pay.waybill_num(+)
   and o.waybill_num = cbi.waybill_num(+)
   and o.express_model_code = m.code
	 and o.client_id=c.id
and (@model_name.length = 0 or m.name =@model_name)
and (@manager_name.length = 0 or o.manager_name =@manager_name)
     and (@depot.length = 0 or o.check_in_site =@depot)
     and (@start_date.length = 0 or
         to_char(o.create_date, 'yyyy-mm-dd') >= @start_date)
     and (@end_date.length = 0 or
         to_char(o.create_date, 'yyyy-mm-dd') <= @end_date)
order by o.create_date)

select to_char(create_date,'yyyy-mm-dd')         创建日期,
client_name 客户名称,
       waybill_num         运单号,
       des                 目的地,
       status              状态,
       express_name        线路渠道,
       weight              计费重,
       manager_name        经办人,
       check_in_site       录入仓库,
       total_amount        订单金额,
       INSURANCE_FEE       保险费,
       charge_amount       补款费,
       compensation_amount 赔偿费,
       --charge_type         补款类型,
       --compensation_type   赔偿类型,
       --service_name        下家,
       pay                 已支付下家,
       notPay              未支付下家 ,
       PAYMENT_STATUS      支付状态
  from a
union all 
select '总计', '','','','', '',sum(weight),'','',sum(total_amount),0,sum(charge_amount),sum(compensation_amount),
       --'', '', '',
       sum(pay),
       sum(notPay),
       '' 
       from a


name: 提成数据汇总-2
create_date: 2019-10-15
report_sql:
with a as (select o.create_date, c.name client_name, o.waybill_num, o.des, decode(o.status, 10, '未发送', 11, '已发送', 20, '转运中', 21, '投递中', 22, '到达待取', 30, '签收', 41, '转运异常', 42, '清关异常', 43, '投递异常', 44, '操作异常', 45, '包裹异常', 50, '退件', 60, '销毁', 70, '丢件') status, m.name express_name, o.weight, o.manager_name, o.check_in_site, o.total_amount, o.INSURANCE_FEE, b. amount charge_amount, p.pk, p.tk, p.ck, pay.pay, not_pay.notPay, decode(cbi.PAYMENT_STATUS, 0, '未支付', 1, '已支付', '未生成账单') PAYMENT_STATUS from t_order o, t_client c, (select distinct (bi.bill_num) bill_num，bi.PAYMENT_STATUS, cb.waybill_num from t_client_bill bi, t_client_bill_item cb where bi.id = cb.bill_id and cb.type = 1) cbi, (select waybill_num, sum(amount) amount from t_client_bill_item where type = 1 group by waybill_num) s, (select waybill_num, sum(nvl(amount, 0)) amount from t_charge group by waybill_num) b, (select waybill_num, pk, tk, ck from (SELECT waybill_num, type2 type, sum(nvl(amount, 0)) amount FROM (select waybill_num, decode(type, '赔款-丢件', 'pk', '赔款-其他', 'pk', '赔款-延误', 'pk', '赔款-破损', 'pk', '赔款-超时', 'pk', '赔款-退运', 'pk', '赔款-仓库操作失误', 'ck', '退款', 'tk', '退款-主营', 'tk', '退款-其他', 'tk', '退款-非主营', 'tk') type2, type, amount from t_compensation where 1 = 1) group by waybill_num, type2) pivot(sum(amount) for type in('pk' pk, 'tk' tk, 'ck' ck))) p, (select bit.waybill_num, sum(service_provider_amount) pay from t_service_provider_bill bi, t_service_provider_bill_item bit, t_service_provider s where bi.id = bit.bill_id(+) and bi.service_provider_id = s.id(+) and bi.payment_status = 1 group by bit.waybill_num ) pay, (select bit.waybill_num, sum(service_provider_amount) notPay from t_service_provider_bill bi, t_service_provider_bill_item bit where bi.id = bit.bill_id and bi.payment_status = 0 group by bit.waybill_num) not_pay, t_express_model m where o.waybill_num = s.waybill_num(+) and o.waybill_num = p.waybill_num(+) and o.waybill_num = b.waybill_num(+) and o.waybill_num = pay.waybill_num(+) and o.waybill_num = not_pay.waybill_num(+) and o.waybill_num = cbi.waybill_num(+) and o.express_model_code = m.code and o.client_id = c.id and (@model_name.length = 0 or m.name = @model_name) and (@manager_name.length = 0 or o.manager_name = @manager_name) and (@depot.length = 0 or o.check_in_site = @depot) and (@start_date.length = 0 or to_char(o.create_date, 'yyyy-mm-dd') >= @start_date) and (@end_date.length = 0 or to_char(o.create_date, 'yyyy-mm-dd') <= @end_date) order by o.create_date) select to_char(create_date, 'yyyy-mm-dd') 创建日期, client_name 客户名称, waybill_num 运单号, des 目的地, status 状态, express_name 线路渠道, weight 计费重, manager_name 经办人, check_in_site 录入仓库, total_amount 订单金额, INSURANCE_FEE 保险费, charge_amount 补款费, pk 赔款, tk 退款, ck 仓库赔款, pay 已支付下家, notPay 未支付下家, PAYMENT_STATUS 支付状态 from a union all select '总计', '', '', '', '', '', sum(weight), '', '', sum(total_amount), 0, sum(charge_amount), sum(pk), sum(tk), sum(ck), sum(pay), sum(notPay), '' from a


name: 所有客户
create_date: 2019-10-15
dbtype: JnetRpt
report_sql:
SELECT account      登录账户,
       t.name       名称,
       mobile_phone 电话,
       email        邮件,
       manager_name 经办,
       source       来源,
       to_char(create_date,'yyyy-mm-dd hh24:mi:ss')  注册日期
  FROM jnetauth.t_client t


name: 三个系统业务量（客户）
create_date: 2019-10-29
parent_menu: Data
dbtype: JnetRpt
report_sql:
with performe as
 (select '快递系统' T,
         --o.waybill_num,
        cli.name,
         nvl(sum(o.total_amount), 0) oamount,
         nvl(sum(s1.amount), 0) /*+ nvl(sum(s2.amount), 0)*/ order_amount,
         nvl(sum(s1.amount), 0) + nvl(sum(s2.amount), 0) -
         nvl(sum(c1.amount), 0) - nvl(sum(c2.amount), 0) -
         nvl(sum(c3.amount), 0) + nvl(sum(c4.REFUND_AMOUNT), 0) profit_amount,
         nvl(sum(c3.amount), 0) - nvl(sum(c4.REFUND_AMOUNT), 0) sp_amount,
         nvl(sum(c3.weight), 0) sp_weight,
         sum(o.weight) weight,
         sum(o.scale_weight) scale_weight,
         sum(o.volumetric_weight) volumetric_weight,
         sum(o.pieces) order_pieces,
         count(o.id) order_count,
         count(op.waybill_num) delivery_number,
         (case
           when (nvl(count(op.waybill_num), 0) / nvl(count(o.id), 0)) = 0 then
            '0'
           else
            (round(((nvl(count(op.waybill_num), 0) / nvl(count(o.id), 0))) * 100,
                   0)) || '%'
         end) delivery_rate,
         round(nvl(sum(ceil(track.time - o.create_date)), 0) /
               nvl(count(o.id), 0),
               0) average_days
    FROM jnettms.T_ORDER o,
         jnettms.t_client cli,
         jnettms.t_express_model em,
         (SELECT waybill_num, nvl(sum(amount), 0) amount
            FROM jnettms.t_client_bill_item
           where TYPE = 1
           group by waybill_num) s1,
         (SELECT waybill_num, nvl(sum(amount), 0) amount
            FROM jnettms.t_charge
           group by waybill_num) s2,
         (SELECT order_id, nvl(sum(amount), 0) amount
            FROM jnettms.t_cost
           group by order_id) c1,
         (SELECT waybill_num, nvl(sum(amount), 0) amount
            FROM jnettms.t_compensation
           group by waybill_num) c2,
         (SELECT waybill_num,
                 nvl(sum(amount), 0) amount,
                 nvl(sum(weight), 0) weight
            FROM jnettms.t_service_provider_bill_item
           group by waybill_num) c3,
         (SELECT waybill_num, nvl(sum(REFUND_AMOUNT), 0) REFUND_AMOUNT
            FROM jnettms.t_service_provider_refund_item
           group by waybill_num) c4,
         
         (select *
            from (select ot.order_id,
                         ot.time,
                         row_number() over(partition by ot.order_id order by ot.time desc) rn
                    from jnettms.t_Tracking_Item ot)
           where rn = 1) track,
         (select od.waybill_num, od.create_date, od.modify_date
            from jnettms.t_order od
           where od.status = 30) op
   where 1 = 1
    and (@start_date.length = 0 or
         to_char(o.create_date, 'yyyy-mm-dd') >= @start_date)
     and (@end_date.length = 0 or
         to_char(o.create_date, 'yyyy-mm-dd') <= @end_date)
     and nvl(cli.feature, 0) <> 'TEST'
     and o.client_id = cli.id
     and o.express_model_code = em.code(+)
     and o.waybill_num = s1.waybill_num(+)
     and o.waybill_num = s2.waybill_num(+)
     and o.id = c1.order_id(+)
     and o.waybill_num = c2.waybill_num(+)
     and o.waybill_num = c3.waybill_num(+)
     and o.waybill_num = c4.waybill_num(+)
     and o.id = track.order_id(+)
     and o.waybill_num = op.waybill_num(+)
   group by cli.name
  
  union all
  
  select '海外仓尾程' T,
         --o.waybill_num,
        cli.name,
         nvl(sum(o.amount), 0) oamount,
         nvl(sum(s1.amount), 0) /*+ nvl(sum(s2.amount), 0)*/ order_amount,
         nvl(sum(s1.amount), 0) + nvl(sum(s2.amount), 0) -
         nvl(sum(c3.amount), 0) + nvl(sum(c4.REFUND_AMOUNT), 0) -
         nvl(sum(c2.amount), 0) profit_amount,
         nvl(sum(c3.amount), 0) - nvl(sum(c4.REFUND_AMOUNT), 0) sp_amount,
         nvl(sum(c3.weight), 0) sp_weight,
         sum(sit.weight) weight,
         sum(sit.weight) scale_weight,
         0 volumetric_weight,
         sum(skunum) order_pieces,
         count(o.id) order_count,
         count(op.waybill_num) delivery_number,
         (case
           when (nvl(count(op.waybill_num), 0) / nvl(count(o.id), 0)) = 0 then
            '0'
           else
            (round(((nvl(count(op.waybill_num), 0) / nvl(count(o.id), 0))) * 100,
                   0)) || '%'
         end) delivery_rate,
         round(nvl(sum(ceil(track.time - o.create_date)), 0) /
               nvl(count(o.id), 0),
               0) average_days
    from jnetwlms.t_sendout_order o,
         jnetwlms.t_client cli,
         jnetwlms.t_express_model em,
         (SELECT waybill_num, nvl(sum(amount), 0) amount
            FROM jnetwlms.t_client_bill_item
           where TYPE = 1
           group by waybill_num) s1,
         (SELECT waybill_num, nvl(sum(amount), 0) amount
            FROM jnetwlms.t_charge
           group by waybill_num) s2,
         (SELECT waybill_num, nvl(sum(amount), 0) amount
            FROM jnetwlms.t_compensation
           group by waybill_num) c2,
         (SELECT waybill_num,
                 nvl(sum(amount), 0) amount,
                 nvl(sum(weight), 0) weight
            FROM jnetwlms.t_service_provider_bill_item
           group by waybill_num) c3,
         (SELECT waybill_num, nvl(sum(REFUND_AMOUNT), 0) REFUND_AMOUNT
            FROM jnetwlms.t_service_provider_refund_item
           group by waybill_num) c4,
         (select *
            from (select ot.order_id,
                         ot.time,
                         row_number() over(partition by ot.order_id order by ot.time desc) rn
                    from jnetwlms.t_tracking_item ot)
           where rn = 1) track,
         (select od.waybill_num, od.create_date, od.modify_date
            from jnetwlms.t_sendout_order od
           where od.status = 30) op,
         (SELECT si.sendout_order_id,
                 sum(nvl(si.quantity, 0) * nvl(si.weight, 0)) weight,
                 sum(nvl(si.quantity, 0)) skunum
            FROM jnetwlms.t_sendout_item si
           group by si.sendout_order_id) sit
   where 2 = 2
      and (@start_date.length = 0 or
         to_char(o.create_date, 'yyyy-mm-dd') >= @start_date)
     and (@end_date.length = 0 or
         to_char(o.create_date, 'yyyy-mm-dd') <= @end_date)
     and nvl(cli.feature, 0) <> 'TEST'
     and o.id = sit.sendout_order_id
     and o.client_id = cli.id
     and o.model_code = em.code(+)
     and o.waybill_num = s1.waybill_num(+)
     and o.waybill_num = s2.waybill_num(+)
     and o.waybill_num = c2.waybill_num(+)
     and o.waybill_num = c3.waybill_num(+)
     and o.waybill_num = c4.waybill_num(+)
     and o.id = track.order_id(+)
     and o.waybill_num = op.waybill_num(+)
   group by cli.name
  
  union all
  select '海外仓头程' T,
         --o.waybill_num,
        cli.name,
         nvl(sum(o.total_amount), 0) oamount,
         nvl(sum(s1.amount), 0) /*+ nvl(sum(s2.amount), 0)*/ order_amount,
         nvl(sum(s1.amount), 0) + nvl(sum(s2.amount), 0) -
         nvl(sum(c3.amount), 0) - nvl(sum(c1.amount), 0) +
         nvl(sum(c4.REFUND_AMOUNT), 0) - nvl(sum(c2.amount), 0) profit_amount,
         nvl(sum(c3.amount), 0) - nvl(sum(c4.REFUND_AMOUNT), 0) sp_amount,
         nvl(sum(c3.weight), 0) sp_weight,
         sum(o.ACTUAL_WEIGHT) weight,
         sum(o.scale_weight) scale_weight,
         sum(o.volumetric_weight) volumetric_weight,
         count(o.id) order_pieces,
         count(o.id) order_count,
         count(op.waybill_num) delivery_number,
         (case
           when (nvl(count(op.waybill_num), 0) / nvl(count(o.id), 0)) = 0 then
            '0'
           else
            (round(((nvl(count(op.waybill_num), 0) / nvl(count(o.id), 0))) * 100,
                   0)) || '%'
         end) delivery_rate,
         round(nvl(sum(ceil(track.time - o.create_date)), 0) /
               nvl(count(o.id), 0),
               0) average_days
    from jnetfba.t_order           o,
         jnetfba.t_client          cli,
         jnetfba.t_product_channel em,
         
         (SELECT order_id, nvl(sum(amount), 0) amount
            FROM jnetfba.t_order_fee
           group by order_id) s1,
         
         (SELECT waybill_num, nvl(sum(amount), 0) amount
            FROM jnetfba.t_charge
           group by waybill_num) s2,
         
         (SELECT order_id, nvl(sum(amount), 0) amount
            FROM jnetfba.t_cost
           group by order_id) c1,
         (SELECT waybill_num, nvl(sum(amount), 0) amount
            FROM jnetfba.t_compensation
           group by waybill_num) c2,
         
         (SELECT waybill_num,
                 nvl(sum(amount), 0) amount,
                 nvl(sum(weight), 0) weight
            FROM jnetfba.t_service_provider_bill_item
           group by waybill_num) c3,
         (SELECT waybill_num, nvl(sum(REFUND_AMOUNT), 0) REFUND_AMOUNT
            FROM jnetfba.t_service_provider_refund_item
           group by waybill_num) c4,
         
         (select *
            from (select ot.order_id,
                         ot.time,
                         row_number() over(partition by ot.order_id order by ot.time desc) rn
                    from jnetfba.t_tracking_item ot)
           where rn = 1) track,
         (select od.waybill_num, od.STORAGE_DATE, od.OUT_DATE
            from jnetfba.t_order od
           where od.status = 30) op
   where 3 = 3
     and (@start_date.length = 0 or
         to_char(o.create_date, 'yyyy-mm-dd') >= @start_date)
     and (@end_date.length = 0 or
         to_char(o.create_date, 'yyyy-mm-dd') <= @end_date)
     and nvl(cli.feature, 0) <> 'TEST'
     and o.client_id = cli.id
     and o.express_model_code = em.code(+)
     and o.id = s1.order_id(+)
     and o.waybill_num = s2.waybill_num(+)
     and o.id = c1.order_id(+)
     and o.waybill_num = c2.waybill_num(+)
     and o.waybill_num = c3.waybill_num(+)
     and o.waybill_num = c4.waybill_num(+)
     and o.id = track.order_id(+)
     and o.waybill_num = op.waybill_num(+)
   group by cli.name)

SELECT p.t     系统,
       p.name   客户,
       --p.manager_name 经办,
       sum(p.ORDER_COUNT) 票数,
       sum(p.WEIGHT) 重量,
       sum(p.oamount) 应收金额,
       sum(p.ORDER_AMOUNT) 实收金额
--sum(p.SCALE_WEIGHT) 称重, 
--sum(p.PROFIT_AMOUNT) 毛利 , 
--sum(p.ORDER_PIECES) 件数
  FROM performe p
 where 1 = 1 -- and substr(p.cdate,0,6) = '201909'
 group by  p.name,p.t
union all
SELECT '' 系统,
       '' 客户, 
       sum(p.ORDER_COUNT) 票数,
       sum(p.WEIGHT) 重量,
       sum(p.oamount) 应收金额,
       sum(p.ORDER_AMOUNT) 实收金额
  FROM performe p 


name: 整个系统客户
create_date: 2019-10-29
dbtype: JnetRpt
report_sql:
SELECT t 系统, manager_name 经办,name 客户
  FROM (SELECT 'TMS' t, t.manager_name, t.name
          FROM jnettms.t_client t
        union
        SELECT 'Wlms' t, t.manager_name, t.name
          FROM jnetwlms.t_client t
        union
        SELECT 'fba' t, t.manager_name, t.name
          FROM jnetfba.t_client t
        union
        SELECT 'pay' t, t.manager_name, t.name
          FROM jnetpay.t_account t
        union
        SELECT 'auth' t, t.manager_name, t.name
          FROM jnetauth.t_client t) 
 where 1=1 and  (@name.length = 0 or name like '%'||@name||'%')


name: 整个系统客户(ALL)
create_date: 2019-11-04
dbtype: JnetRpt
report_sql:
SELECT t            系统,
       id           ID,
       manager_name 经办,
       name         客户,
       pay_account  登录账户,
       mobile_phone 电话,
       password     密码,
       key          秘钥,
       auth_id

  FROM (SELECT 'TMS' t,
               t.id,
               t.manager_name,
               t.name,
               t.pay_account1 pay_account,
               t.mobile_phone,
               '-' password,
               '-' key,
               t.auth_id
          FROM jnettms.t_client t
        union
        SELECT 'Wlms' t,
               t.id,
               t.manager_name,
               t.name,
               t.pay_account,
               t.mobile_phone,
               '-' password,
               '-' key,
               t.auth_id
          FROM jnetwlms.t_client t
        union
        SELECT 'fba' t,
               t.id,
               t.manager_name,
               t.name,
               t.pay_account,
               t.mobile_phone,
               '-' password,
               '-' key,
               t.auth_id
          FROM jnetfba.t_client t
        union
        SELECT 'pay' t,
               t.id,
               t.manager_name,
               t.name,
               t.account pay_account,
               t.phone mobile_phone,
               t.password,
               '-' key,
               t.auth_id
          FROM jnetpay.t_account t
        union
        SELECT 'auth' t,
               t.id,
               t.manager_name,
               t.name,
               t.account pay_account,
               t.mobile_phone,
               t.password,
               t.key,
               to_char(t.id) auth_id
          FROM jnetauth.t_client t)
 where 1 = 1
   and (@name.length = 0 or name like '%' || @name || '%' or
       pay_account like '%' || @name || '%' or
       mobile_phone like '%' || @name || '%')


name: 三个系统订单（ALL）
create_date: 2019-11-05
dbtype: JnetRpt
report_sql:
SELECT o.t 类型, o.waybill_num 单号, o.name 客户
  FROM (SELECT 'TMS PreOrder' T, waybill_num, c.name
          FROM jnettms.t_order_pre t
          left join jnettms.t_client c
            on t.client_id = c.id
        union all
        SELECT 'TMS Order' T, waybill_num, c.name
          FROM jnettms.t_order t
          left join jnettms.t_client c
            on t.client_id = c.id
        union all
        SELECT 'WLMS' T, waybill_num, c.name
          FROM jnetwlms.t_sendout_order t
          left join jnetwlms.t_client c
            on t.client_id = c.id
        union all
        SELECT 'FBA' T, waybill_num, c.name
          FROM jnetfba.t_order t
          left join jnetfba.t_client c
            on t.client_id = c.id
        union all
        SELECT 'TMS 单号提取' T, t.waybill_num, c.name
          FROM jnettms.t_waybill_number t
          left join jnettms.t_client c
            on t.client_id = c.id
        union all
        SELECT 'WLMS 单号提取' T, t.waybill_num, c.name
          FROM jnetwlms.t_waybill_number t
          left join jnetwlms.t_client c
            on t.client_id = c.id) o
 where (@way.length = 0 or waybill_num = @way)


name: WLMS/TMS账单明细
create_date: 2019-11-07
dbtype: JnetRpt
report_sql:
SELECT *
  FROM (SELECT 'TMS' T,
               b.bill_num,
               b.payment_status,
               b.status bill_staus,
               i.id,
               i.waybill_num,
               i.transfer_num,
               i.weight,
               i.amount,
               i.des,
               i.payment_method,
               i.manager_name,
               i.bill_id,
               i.create_user,
               i.create_date,
               i.modify_user,
               i.modify_date,
               i.status,
               i.memo,
               i.type,
               i.memo1,
               i.memo2
        
          FROM jnettms.t_client_bill_item i
         right join jnettms.t_client_bill b
            on i.bill_id = b.id
        union all
        SELECT 'WLMS' T,
               b.bill_num,
               b.payment_status bill_staus,
               b.status,
               i.id,
               i.waybill_num,
               i.transfer_num,
               i.weight,
               i.amount,
               i.des,
               i.payment_method,
               i.manager_name,
               i.bill_id,
               i.create_user,
               i.create_date,
               i.modify_user,
               i.modify_date,
               i.status,
               i.memo,
               i.type,
               i.memo1,
               i.memo2
          FROM jnetwlms.t_client_bill_item i
         right join jnetwlms.t_client_bill b
            on i.bill_id = b.id)
 where (@way.length = 0 or waybill_num = @way)


# T_REPORT_DICTIONARY

name: 客户名
dbtype: JnetPay
sql:
select t.id value,t.name from t_client t where exists (
select 1 from t_goods g where g.client_id=t.id
)


name: 出库订单状态
dbtype: JnetPay
sql:
select 1 value,'预录' name from dual union all
select 2 value,'拣货中' name from dual union all
select 3 value,'已出库' name from dual union all
select 4 value,'取消' name from dual union all
select 21 value,'派送中' name from dual union all
select 22 value,'待取' name from dual union all
select 30 value,'已送达' name from dual union all
select 43 value,'转运异常' name from dual union all
select 45 value,'派送异常' name from dual union all
select 50 value,'退件' name from dual union all
select 60 value,'销毁' name from dual union all
select 70 value,'丢件' name from dual


name: 销售员
dbtype: JnetPay
sql:
select u.name as value,
       decode(u.kind, null, '', u.kind || ' - ') || u.name as name
  from t_user u, t_user_role ur
 where u.id = ur.user_id
    and  exists(
     SELECT null FROM t_role where name = '销售部' and  ur.role_id = id
     )
 order by u.kind, u.name asc


name: 月份
dbtype: JnetPay
sql:
select '2019六月' name,'201906' value from dual
union all
select '2019七月' name,'201907' value from dual
union all
select '2019八月' name,'201908' value from dual
union all
select '2019九月' name,'201909' value from dual
union all
select '2019十月' name,'201910' value from dual


