#!/bin/bash

#先写一个简单的脚本，后续使用脚本调用存储过程

#by：亚信-张颜

export ORACLE_HOME=/opt/oracle/app/oracle_base/product/11.2.0/db_1

export PATH=$ORACLE_HOME/bin:$PATH

#DATE_STR保存了从数据库中返回的三个值：1小时之前的年月，12小时前的时间串，以及当前时间串

DATE_STR=(`sqlplus -s  用户名/密码 <

set heading off

set feedback off

set pagesize 0

set verify off

set echo off

select to_char(TRUNC(SYSDATE - 1 / 24, 'HH'), 'YYYYMM'),TO_CHAR(TRUNC(SYSDATE - 1 / 2, 'HH'), 'YYYYMMDDHH24MISS'),

       TO_CHAR(TRUNC(SYSDATE, 'HH'), 'YYYYMMDDHH24MISS')  from dual;

exit;

eof`)

echo "`date` 开始校验程控退订数据......"

echo "`date` 开始提取程控功能营业送开通表退订数据"

#通过监控营业送开通工单历史表，查询程控功能退订的数据，当前设定为12个小时运行1次

#

for REGION_ID in {891..897}

do

sqlplus -s  用户名/密码 <

set heading off

set feedback off

set pagesize 0

set verify off

set echo off

insert into zyan_jiankongchengkong

select bill_id, 'MBELL', create_date, sysdate

  from so.i_open_provision_h_${REGION_ID}_${DATE_STR[0]}

 where action_id = 5

   and OLD_PS_PARAM like '%MBELL=1%'

   and (PS_PARAM is null or PS_PARAM not like '%MBELL=1%')

   and create_date >= TO_DATE('${DATE_STR[1]}', 'YYYYMMDDHH24MISS')

   and create_date < TO_DATE('${DATE_STR[2]}', 'YYYYMMDDHH24MISS');

commit;

insert into zyan_jiankongchengkong

  select bill_id, 'CFNRCF', create_date, sysdate

    from so.i_open_provision_h_${REGION_ID}_${DATE_STR[0]}

   where action_id = 5

     and OLD_PS_PARAM like '%CFNRCF=1%'

     and (PS_PARAM is null or PS_PARAM not like '%CFNRCF=1%')

     and create_date >= TO_DATE('${DATE_STR[1]}', 'YYYYMMDDHH24MISS')

     and create_date < TO_DATE('${DATE_STR[2]}', 'YYYYMMDDHH24MISS');

commit;

exit;

eof

done

#校验程控功能订购在退订功能时是否存在订购关系，如果存在则表示是异常，需要进一步核查:

echo "`date` 开始校验程控退订记录营业侧订购关系:"

for REGION_ID in {891..897}

do

sqlplus -s  用户名/密码 <

set heading off

set feedback off

set pagesize 0

set verify off

set echo off

 delete from zyan_jiankongchengkong a

   where create_date <> (select max(create_date)

                           from zyan_jiankongchengkong b

                          where a.bill_id = b.bill_id

                            and a.func_name = b.func_name)

                            and a.done_date>TO_DATE('${DATE_STR[2]}', 'YYYYMMDDHH24MISS');

                            commit;

delete from zyan_jiankongchengkong a

 where not exists (select 1

          from so.ins_user_${REGION_ID}         b,

               so.ins_prod_${REGION_ID}         c,

               so.ins_off_ins_user_${REGION_ID} f

         where a.bill_id = b.bill_id

           and c.prod_id in (121030512001, 121030512002)

           and b.user_id = c.user_id

           and c.user_id = f.user_id

           and c.offer_inst_id = f.offer_inst_id

           and b.region_id=f.region_id

           and f.expire_date > sysdate

           and c.expire_date > sysdate

           and c.effective_date < a.create_date

           and c.create_date

           and exists (select 1 from so. ins_user_${REGION_ID} g where a.bill_id = g.bill_id)

   and a.func_name = 'MBELL'

   and a.done_date>TO_DATE('${DATE_STR[2]}', 'YYYYMMDDHH24MISS');

   commit;

 delete from zyan_jiankongchengkong a

 where not exists (select 1

          from so.ins_user_${REGION_ID}         b,

               so.ins_prod_${REGION_ID}         c,

               so.ins_off_ins_user_${REGION_ID} f

         where a.bill_id = b.bill_id

           and c.prod_id in (121030505101)

           and b.user_id = c.user_id

           and c.user_id = f.user_id

           and c.offer_inst_id = f.offer_inst_id

           and b.region_id=f.region_id

           and f.expire_date > sysdate

           and c.expire_date > sysdate

           and c.effective_date < a.create_date

           and c.create_date

           and exists (select 1 from so. ins_user_${REGION_ID} g where a.bill_id = g.bill_id)

   and a.func_name = 'CFNRCF'

   and a.done_date>TO_DATE('${DATE_STR[2]}', 'YYYYMMDDHH24MISS');

commit;

exit;

eof

done

echo "`date` 本次校验完成！"

echo ""