1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68
| ORACLE_BASE=/SAN2/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1; export ORACLE_HOME
ORACLE_SID=MISDB; export ORACLE_SID
ORACLE_TERM=xterm; export ORACLE_TERM
NLS_LANG=FRENCH_BELGIUM.WE8MSWIN1252; export NLS_LANG
ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data; export ORA_NLS33
# Set shell search paths:
PATH=$PATH:$ORACLE_HOME/bin; export PATH
for ((w=$2; w>=$1 ; w--))
do
p=`date -d"$w day ago" +%Y_%m%d`
part1="VOUCHERCDR_"$p
part2="ADJUSTMENTCDR_"$p
part3="REFILL_"$p
echo
echo "Traitement de la partition du " $p
#### EXTRACTION DES INFORMATIONS AGGREGEES PAR JOUR, HEURE, MSISDN, SERVICECLASS, DESTINATION, TRAFFIC TYPE, TELESERVICECODE
sqlplus -s de/nga@dere << EOF > cpp1.txt
set linesize 8000
set head off
select to_char(startdate,'YYYYMMDDHH24')||';'||msisdn||';'||to_number(replace(cost,'.',','))||';'||count(*)||';'||sum(replace(cost,'.',','))||';'||'CPP;' from sysadm.voucher_cdr partition($part1) group by to_char(startdate,'YYYYMMDDHH24'), msisdn,to_number(replace(cost,'.',','));
set head on
exit
EOF
sqlplus -s de/nga@dere << EOF > evd1.txt
set linesize 8000
set head off
select to_char(startdate,'YYYYMMDDHH24')||';'||msisdn||';'||to_number(replace(nominalamount,'.',','))||';'||count(*)||';'||sum(replace(nominalamount,'.',','))||';'||'EVD'||';'||SERVICECLASSID from sysadm.adjustment_cdr partition($part2) where upper(description) like 'MTOP%' group by to_char(startdate,'YYYYMMDDHH24'), msisdn,to_number(replace(nominalamount,'.',',')),serviceclassid;
set head on
exit
EOF
sed '$d' cpp1.txt > cpp.txt
sed '$d' evd1.txt > evd.txt
cat cpp.txt evd.txt > refill1.txt
sqlplus -s dbe/db2access@serv2<<EOF
ALTER TABLE REFILL_AGG TRUNCATE PARTITION $part3;
TRUNCATE TABLE refilltrans;
COMMIT;
EOF
sed '/./!d' refill1.txt > refill2.txt
sed '$d' refill2.txt>refill.txt
rm -f refill1.txt refill2.txt
sqlldr MISWH/WHMIS@misdb control=/home/niger/scripts/subscribers/refill.ctl
sqlplus -s MISWH/WHMIS@misdb<<EOF
delete from refilltrans where upper(reportdate) like '%SELECTED%';
commit;
CREATE OR REPLACE VIEW REFILL_VIEW(reportdate,heure,msisdn,serviceclass,denomination,recharges,revenue,refilltype) AS
select to_date(substr(reportdate,1,8),'YYYYMMDD'),substr(reportdate,9,2),msisdn,serviceclass,denomination,recharges,revenue,refilltype from refilltrans;
COMMIT;
INSERT INTO REFILL_AGG(reportdate,heure,msisdn,serviceclass,denomination,recharges,revenue,refilltype)
SELECT reportdate,heure,msisdn,serviceclass,denomination,recharges,revenue,refilltype from REFILL_VIEW;
COMMIT;
EOF
done |
Partager