【记一次项目上线的经历】（二）

（数据去重）
由于代码重复和数据状态导致的多条的影响导致已有数据重复
经过查看数据，发现很多数据只是多了几条需要将多余的删去

SELECT COUNT(CODE) ,MAX(ID),.... FROM 
HISBASE.xxxxx
GROUP BY CODE...
HAVING COUNT(CODE)>1
--获取重复数据中最大的ID，拿到这个值之后即可更新

（上线出现的问题）
再经历部分数据重导，去重，排查，校验等等操作之后（肝到了凌晨），终于上线了。但是上线之后的问题很多

1操作流程不够简介，用户需要的是最简洁的操作。如开处方界面可以修改患者信息。而不是返回另一个菜单选项去完成。重复的操作用户不希望再点。

2用户的不熟悉系统导致的抵抗情绪很重，十分抱怨，前期培训应该多多加强。

3用户前期反馈的问题不够完善。用户前期使用仅仅是轻度使用，在实际环境重度使用后又提出了各种的需求，导致一边排查问题一边开发新需求

4字体的问题，打印机的问题等繁琐事情未处理好。比如之前测试是正常的，但是上线的时候发票打印调纸，位置不对。最后发现是打印机驱动的问题。字体导致很多报表的内容被吞。因此前期的测试也是需要涉及多台设备。

（代码调整）
有一个需求是发票号码需要求出中断和连续的号码。大佬直接在SQL中搞定了

  for cur_invoice_no in (
    select
      a.invoice_no
    , substr(a.invoice_no,1,2) as no_prefix
    , substr(a.invoice_no,3,255) as no_curr
    , cast( substr(a.invoice_no,3,255) as integer ) + 1 as no_next
    from mzgl.charge_order a
    where ( ( v_mode = 0 and a.tollman_dept_id = v_dept_id and a.tollman_user_id = v_user_id and nvl(a.settl_id,0) = 0 ) or 
            ( v_mode = 1 and a.settl_id = v_settl_id ) )
      and a.status = 1
      and a.invoice_no is not null
      and nvl(a.org_order_id,0) = 0
      --作废发票不能计入
      --and a.id not in ( 
      --      select 
      --        b.org_order_id
      --      from mzgl.charge_order b
      --      where ( ( v_mode = 0 and b.tollman_dept_id = v_dept_id and b.tollman_user_id = v_user_id and nvl(b.settl_id,0) = 0 ) or 
      --              ( v_mode = 1 and b.settl_id = v_settl_id ) )
      --        and b.status = 1
      --        and b.org_order_id > 0
      --    )
    order by a.invoice_no )
  loop

其中由于单价精确到小数4位，而收费只精确到分收，所以需要找一笔费用进行平衡。因为这个原因，导致之前的支付金额校验始终过不去。

   --处理平衡
    select
      sum(amount_ratio)
    into
      v_amount_ratio
    from mzgl.temp_charge_settl_item
    where order_id = cur_charge_order.id
      and payment_code = cur_charge_order.payment_method_code;

    --找一笔来平衡
    if v_amount_ratio != cur_charge_order.payment_amount then

      update mzgl.temp_charge_settl_item set
        amount_ratio = amount_ratio + ( cur_charge_order.payment_amount - v_amount_ratio )
      where order_id = cur_charge_order.id
        and payment_code = cur_charge_order.payment_method_code
        and rownum = 1;

处理动态列，行转列

 --汇合字段
  select
    listagg(', sum( case payment_code when ''' || payment_code ||''' then amount_ratio else 0 end ) as "' || payment_name ||'"')
    within group(order by payment_code )
  into
    v_sum_field
  from (
    select distinct
      payment_code
    , payment_name
    from mzgl.temp_charge_settl_item ) g;

  v_sql :=
    ' select '||
    '   kind_name as "项目" '|| v_sum_field ||
    ' , sum( amount_ratio ) as "合计" '||
    ' from mzgl.temp_charge_settl_item '||
    ' group by kind_name ';

  dbms_output.put_line(v_sql);

  open out_result1_ref for v_sql;

  --计算支付
  open out_result2_ref for
with g as (
    select
      b.payment_method_code as code
    , b.payment_method_name as name
    , sum(b.payment_amount) as amount
    from mzgl.charge_order a
         inner join mzgl.charge_order_payment b
            on b.order_id = a.id and b.status != 0 
    where ( 
            ( v_mode = 0 and a.tollman_dept_id = v_dept_id and a.tollman_user_id = v_user_id and nvl(a.settl_id,0) = 0 ) or 
            ( v_mode = 1 and a.settl_id = v_settl_id ) 
          ) --146716
      and a.status = 1
    group by b.payment_method_code, b.payment_method_name )

  select
    code
  , code as parent_code
  , name
  , amount
  , mzgl.fn_MoneyToChinese(amount) as amount_ch
  from g
  where code != '02'
  
  union all

  select
    '99999' as code
  , '99999' as parent_code
  , '合计'  as name
  , sum(amount) as amount
  , mzgl.fn_MoneyToChinese(sum(amount)) as amount_ch
  from g

  union all

  select
    c.med_type as code
  , '02' as parent_code
  , hisbase.fn_get_keydict_by_value('MED_TYPE',c.med_type) as name
  , sum(b.payment_amount) as amount
  , mzgl.fn_MoneyToChinese(sum(b.payment_amount)) as amount_ch
  from mzgl.charge_order a
       inner join mzgl.charge_order_payment b
          on b.order_id = a.id and b.status != 0 and b.payment_method_code = '02'
       inner join mzgl.charge_mib_setlinfo c
          on ( ( a.org_order_id > 0 and c.order_id = a.org_order_id and c.cancel = 1 ) or 
               ( c.order_id = a.id and c.cancel = 0 ) ) 
         and c.setl_id is not null
  where ( ( v_mode = 0 and a.tollman_dept_id = v_dept_id and a.tollman_user_id = v_user_id and nvl(a.settl_id,0) = 0 ) or
          ( v_mode = 1 and a.settl_id = v_settl_id ) ) --146716
    and a.status = 1
  group by c.med_type
  order by parent_code, code;