Code:
#*****************************************************************************************************
# BUNDLE TOOL
#*****************************************************************************************************
# Author : NAGA
#*****************************************************************************************************
# Date : 30-JAN-2013
#*****************************************************************************************************
#*****************************************************************************************************
# Update 1 Date : 15-JUL-2014
# Update 2 Date : 18-DEC-2014
#*****************************************************************************************************
#!/bin/bash
sudo chmod 777 *
strt=`ls -lrt |grep -c ctrlstart`
if [ $strt == 0 ]
then
echo "1 " > ctrlstart
echo "" > free_min.sql
echo "" > free_minexp.sql
echo "" > bcard.txt
echo "" > bundleinfo.txt
echo "" > subsbundleinfo.txt
echo "" > benefitscomparison.txt
echo "" > avblbundl.txt
date=`date +%Y%m%d%H%M%S`
dat=`date +%Y%m%d`
. /export/home/oracle/env
user=`who am i | awk '{print $1}' | tr -d '\r'`
echo -e "********************************************************************************************" >>/tmp/Naga/FREEMINTOOL/freemintool$dat.log
echo -e "User : $user Logged in @ $date" >>/tmp/Naga/FREEMINTOOL/freemintool$dat.log
clear
echo -e "********************************************************************************************"
echo -e "\t WELCOME TO BUNDLE TOOL\t"
echo -e "********************************************************************************************"
#-------------------------------GETTING INPUT--------------------------------------------------------
echo -e "Pls select the country......."
echo -e "1.Netherlands\n2.UK\n3.SWEDEN\n4.NORWAY\n5.Denmark\n6.Belgium INMVNO\n7.Italy INMVNO\n8.MVNA\n9.Spain\n10.Australia\n11.France\n12.Germany\n13.Swiss\n14.Poland\n15.Ireland\n16.Portugal\n17.Belgium\n18.Austria\n19.ITALY FMVNO"
read country
echo -e "Country selected by $user is $country" >>/tmp/Naga/FREEMINTOOL/freemintool$dat.log
#-------------------------------DB DECLARATION--------------------------------------------------------
if [ $country == 1 ]; then dbconn="xxxx"; dmconn="yyyy"; echo -e "DB CONNECTION STRING IS $dbconn $dmconn">>/tmp/Naga/FREEMINTOOL/freemintool$dat.log; fi
if [ $country == 2 ]; then dbconn="xxxx"; dmconn="yyyy"; echo -e "DB CONNECTION STRING IS $dbconn $dmconn">>/tmp/Naga/FREEMINTOOL/freemintool$dat.log; fi
if [ $country == 3 ]; then dbconn="xxxx"; dmconn="yyyy"; echo -e "DB CONNECTION STRING IS $dbconn $dmconn">>/tmp/Naga/FREEMINTOOL/freemintool$dat.log; fi
if [ $country == 4 ]; then dbconn="xxxx"; dmconn="yyyy"; echo -e "DB CONNECTION STRING IS $dbconn $dmconn">>/tmp/Naga/FREEMINTOOL/freemintool$dat.log; fi
if [ $country == 5 ]; then dbconn="xxxx"; dmconn="yyyy"; echo -e "DB CONNECTION STRING IS $dbconn $dmconn">>/tmp/Naga/FREEMINTOOL/freemintool$dat.log; fi
if [ $country == 6 ]; then dbconn="xxxx"; echo -e "DB CONNECTION STRING IS $dbconn">>/tmp/Naga/FREEMINTOOL/freemintool$dat.log; fi
if [ $country == 7 ]; then dbconn="xxxx"; echo -e "DB CONNECTION STRING IS $dbconn">>/tmp/Naga/FREEMINTOOL/freemintool$dat.log; fi
if [ $country == 8 ]; then dbconn="xxxx"; echo -e "DB CONNECTION STRING IS $dbconn">>/tmp/Naga/FREEMINTOOL/freemintool$dat.log; fi
if [ $country == 9 ]; then dbconn="xxxx"; dmconn="yyyy"; echo -e "DB CONNECTION STRING IS $dbconn $dmconn">>/tmp/Naga/FREEMINTOOL/freemintool$dat.log; fi
if [ $country == 10 ]; then dbconn="xxxx"; dmconn="yyyy"; echo -e "DB CONNECTION STRING IS $dbconn $dmconn">>/tmp/Naga/FREEMINTOOL/freemintool$dat.log; fi
if [ $country == 11 ]; then dbconn="xxxx"; dmconn="yyyy"; echo -e "DB CONNECTION STRING IS $dbconn $dmconn">>/tmp/Naga/FREEMINTOOL/freemintool$dat.log; fi
if [ $country == 12 ]; then dbconn="xxxx"; dmconn="yyyy"; echo -e "DB CONNECTION STRING IS $dbconn $dmconn">>/tmp/Naga/FREEMINTOOL/freemintool$dat.log; fi
if [ $country == 13 ]; then dbconn="xxxx"; dmconn="yyyy"; echo -e "DB CONNECTION STRING IS $dbconn $dmconn">>/tmp/Naga/FREEMINTOOL/freemintool$dat.log; fi
if [ $country == 14 ]; then dbconn="xxxx"; dmconn="yyyy"; echo -e "DB CONNECTION STRING IS $dbconn $dmconn">>/tmp/Naga/FREEMINTOOL/freemintool$dat.log; fi
if [ $country == 15 ]; then dbconn="xxxx"; dmconn="yyyy"; echo -e "DB CONNECTION STRING IS $dbconn $dmconn">>/tmp/Naga/FREEMINTOOL/freemintool$dat.log; fi
if [ $country == 16 ]; then dbconn="xxxx"; dmconn="yyyy"; echo -e "DB CONNECTION STRING IS $dbconn $dmconn">>/tmp/Naga/FREEMINTOOL/freemintool$dat.log; fi
if [ $country == 17 ]; then dbconn="xxxx"; dmconn="yyyy"; echo -e "DB CONNECTION STRING IS $dbconn $dmconn">>/tmp/Naga/FREEMINTOOL/freemintool$dat.log; fi
if [ $country == 18 ]; then dbconn="xxxx"; dmconn="yyyy"; echo -e "DB CONNECTION STRING IS $dbconn $dmconn">>/tmp/Naga/FREEMINTOOL/freemintool$dat.log; fi
if [ $country == 19 ]; then dbconn="xxxx"; dmconn="yyyy"; echo -e "DB CONNECTION STRING IS $dbconn $dmconn">>/tmp/Naga/FREEMINTOOL/freemintool$dat.log; fi
echo -e "PLS CHOOSE APPROPRIATE OPTION FROM BELOW....."
echo -e "a. BUNDLE CANCELLATION/BUNDLE EXTENSION"
echo -e "b. DM BUNDLE EXTENSION"
echo -e "c. MISTOPUP"
read entry
echo -e "$user Selected $entry " >>/tmp/Naga/FREEMINTOOL/freemintool$dat.log
#if2start
if [ $entry == a ]
then
#-------------------------------MAIN--------------------------------------------------------
echo -e "---------------------------------------------------------------" | tee -a /tmp/Naga/FREEMINTOOL/freemintool$dat.log
echo -e "---------------------------------------------------------------" | tee -a /tmp/Naga/FREEMINTOOL/freemintool$dat.log
echo -e "Enter the MSISDN without CC...."
read mdn
echo -e "MSISDN selected by $user is $mdn" >>/tmp/Naga/FREEMINTOOL/freemintool$dat.log
if [ -z $mdn ]
then
echo "ERROR: MDN cannot be Null.....";rm ctrlstart;exit 0
fi
#-------------------------------To fetch available bundle
sqlplus $dbconn <<EOF
set head off line 900 colsep ,;
spool /tmp/Naga/FREEMINTOOL/freemin_add.log;
select BUNDLE_CODE,BUNDLE_EXP from RRBS_SUBS_BUNDLE_BUCKETS where MSISDN='$mdn';
exit
EOF
cat /tmp/Naga/FREEMINTOOL/freemin_add.log >>/tmp/Naga/FREEMINTOOL/freemintool$dat.log
echo -e "---------------------------------------------------------------" | tee -a /tmp/Naga/FREEMINTOOL/freemintool$dat.log
echo -e "---------------------------------------------------------------" | tee -a /tmp/Naga/FREEMINTOOL/freemintool$dat.log
echo -e "Mentioned subscriber has below mentioned bundles...Pls choose the appropriate bundle from below..."
cat freemin_add.log | grep -v SQL | awk '/./' | tr -s '\n' | tr -d ' ' | tr ',' '\n' >/tmp/Naga/FREEMINTOOL/avblbundl.txt
echo -e "---------------------------------------------------------------" | tee -a /tmp/Naga/FREEMINTOOL/freemintool$dat.log
cat /tmp/Naga/FREEMINTOOL/avblbundl.txt
echo -e "---------------------------------------------------------------" | tee -a /tmp/Naga/FREEMINTOOL/freemintool$dat.log
echo -e "---------------------------------------------------------------" | tee -a /tmp/Naga/FREEMINTOOL/freemintool$dat.log
echo -e "Enter the Bundle Code...."
read bcode
echo -e "BCODE selected by $user is $bcode" >>/tmp/Naga/FREEMINTOOL/freemintool$dat.log
if [ -z $bcode ]
then
echo "ERROR: BUNDLE CODE cannot be Null.....";rm ctrlstart;exit 0
fi
echo -e "---------------------------------------------------------------" | tee -a /tmp/Naga/FREEMINTOOL/freemintool$dat.log
echo -e "---------------------------------------------------------------" | tee -a /tmp/Naga/FREEMINTOOL/freemintool$dat.log
#-------------------------------To fetch bundle buckets
sqlplus $dbconn <<EOF
set head off line 900 colsep ,;
spool /tmp/Naga/FREEMINTOOL/freemin_add.log;
select FREE_MINS,FREE_SMS,FREE_DATA,ONNET_MT_MINUTES,ONNET_MT_SMS,OFFNET_MT_MINUTES,OFFNET_MT_SMS,FREE_OFNET_SMS,FREE_OFNET_SMS2,FREE_OFNET_SMS3,FREE_OFNET_MINS,FREE_OFNET_MINS2,FREE_OFNET_MINS3,MINS_EXP,SMS_EXP,DATA_EXP,BUNDLE_EXP,ONNET_MT_EXPIRY,OFFNET_MT_EXPIRY,FREE_OFNET_SMS_EXP,FREE_OFNET_SMS_EXP2,FREE_OFNET_SMS_EXP3,FREE_OFNET_MINS_EXP,FREE_OFNET_MINS_EXP2,FREE_OFNET_MINS_EXP3 from RRBS_SUBS_BUNDLE_BUCKETS where MSISDN='$mdn' and bundle_code='$bcode';
exit
EOF
cat /tmp/Naga/FREEMINTOOL/freemin_add.log >>/tmp/Naga/FREEMINTOOL/freemintool$dat.log
cat freemin_add.log | grep -v SQL | awk '/./' | tr -s '\n' | tr -d ' ' | tr ',' '\n' >/tmp/Naga/FREEMINTOOL/subsbundleinfo.txt
#-------------------------------To fetch card id
sqlplus $dbconn <<EOF
set head off line 900 colsep ,;
spool /tmp/Naga/FREEMINTOOL/freemin_add.log;
select card_id from rrbs_bundle_config where service_code='$bcode';
exit
EOF
cat /tmp/Naga/FREEMINTOOL/freemin_add.log >>/tmp/Naga/FREEMINTOOL/freemintool$dat.log
cardid=`cat freemin_add.log | grep -v SQL | awk '/./' | tr -s '\n' | tr -d ' ' | tr ',' '\n' | tr -d '\r'`
echo $cardid >/tmp/Naga/FREEMINTOOL/bcard.txt
#-------------------------------To get card benefits
sqlplus $dbconn <<EOF
set head off line 900 colsep ,;
spool /tmp/Naga/FREEMINTOOL/freemin_add.log;
select FREE_MINUTES,FREE_SMS,FREE_DATA,ONNET_MT_MINS,ONNET_MT_SMS,OFFNET_MT_MINS,OFFNET_MT_SMS,FREE_OFNET_SMS,FREE_OFNET_SMS2,FREE_OFNET_SMS3,FREE_OFNET_MINS,FREE_OFNET_MINS2,FREE_OFNET_MINS3 from RRBS_SPECIAL_PROMO where card_id='$cardid';
exit
EOF
cat /tmp/Naga/FREEMINTOOL/freemin_add.log >>/tmp/Naga/FREEMINTOOL/freemintool$dat.log
cat freemin_add.log | grep -v SQL | awk '/./' | tr -s '\n' | tr -d ' ' | tr ',' '\n' >/tmp/Naga/FREEMINTOOL/bundleinfo.txt
echo -e "---------------------------------------------------------------" | tee -a /tmp/Naga/FREEMINTOOL/freemintool$dat.log
echo -e "------BENEFITS|SUBSCRIBER|BUNDLE|USED------------" | tee -a /tmp/Naga/FREEMINTOOL/freemintool$dat.log
echo -e "---------------------------------------------------------------" | tee -a /tmp/Naga/FREEMINTOOL/freemintool$dat.log
paste -d "|" /tmp/Naga/FREEMINTOOL/bun_info.txt /tmp/Naga/FREEMINTOOL/subsbundleinfo.txt /tmp/Naga/FREEMINTOOL/bundleinfo.txt >/tmp/Naga/FREEMINTOOL/benefitscomparison.txt
cat /tmp/Naga/FREEMINTOOL/benefitscomparison.txt | awk -F'|' '{print $1"|"$2"|"$3"|"$3 - $2}' | tee -a /tmp/Naga/FREEMINTOOL/freemintool$dat.log
echo -e "---------------------------------------------------------------" | tee -a /tmp/Naga/FREEMINTOOL/freemintool$dat.log
echo -e "---------------------------------------------------------------" | tee -a /tmp/Naga/FREEMINTOOL/freemintool$dat.log
echo -e "Pls select the Appropriate column to add/remove mins/sms/databytes(Select multiple columns separated by '|' symbol)......."
echo -e "For Ex: FREE_MINS|FREE_SMS|FREE_DATA|ONNET_MT_MINUTES|ONNET_MT_SMS|OFFNET_MT_MINUTES|OFFNET_MT_SMS|FREE_OFNET_SMS|FREE_OFNET_SMS2|FREE_OFNET_SMS3|FREE_OFNET_MINS|FREE_OFNET_MINS2|FREE_OFNET_MINS3"
read req
#if3
if [ -z $req ]
then
echo "ERROR: No columns were selected to add mins/sms/bytes.....";rm ctrlstart;exit 0
fi
echo -e "Add Request recieved by $user for $req" >>/tmp/Naga/FREEMINTOOL/freemintool$dat.log
echo "$req" | tr '|' '\n' | tr -d '\r' >columnnames.txt
for col in `cat columnnames.txt | tr -d '\r'`
do
echo "Enter value for $col:"
read colvalue
echo "Value recieved for $col is $colvalue..." >>/tmp/Naga/FREEMINTOOL/freemintool$dat.log
echo "update rrbs_subs_bundle_buckets set $col=nvl($col,0)+$colvalue where MSISDN='$mdn' and bundle_code='$bcode';" >>free_min.sql
echo "commit;" >>free_min.sql
done
echo -e "---------------------------------------------------------------" | tee -a /tmp/Naga/FREEMINTOOL/freemintool$dat.log
echo -e "--------UPDATE Queries as below------------------------------" | tee -a /tmp/Naga/FREEMINTOOL/freemintool$dat.log
cat free_min.sql | tee -a /tmp/Naga/FREEMINTOOL/freemintool$dat.log
echo -e "---------------------------------------------------------------" | tee -a /tmp/Naga/FREEMINTOOL/freemintool$dat.log
echo -e "Press 1 to update or any key to exit......"
read updateflag
#if4
if [ $updateflag == 1 ]
then
sqlplus $dbconn << EOF
spool /tmp/Naga/FREEMINTOOL/freemin_add.log
@free_min.sql
spool off;
exit;
EOF
fi
cat /tmp/Naga/FREEMINTOOL/freemin_add.log >>/tmp/Naga/FREEMINTOOL/freemintool$dat.log
echo "Update Completed Successfully..." | tee -a /tmp/Naga/FREEMINTOOL/freemintool$dat.log
#-------------------------------To fetch bundle buckets
sqlplus $dbconn <<EOF
set head off line 900 colsep ,;
spool /tmp/Naga/FREEMINTOOL/freemin_add.log;
select FREE_MINS,FREE_SMS,FREE_DATA,ONNET_MT_MINUTES,ONNET_MT_SMS,OFFNET_MT_MINUTES,OFFNET_MT_SMS,FREE_OFNET_SMS,FREE_OFNET_SMS2,FREE_OFNET_SMS3,FREE_OFNET_MINS,FREE_OFNET_MINS2,FREE_OFNET_MINS3,MINS_EXP,SMS_EXP,DATA_EXP,BUNDLE_EXP,ONNET_MT_EXPIRY,OFFNET_MT_EXPIRY,FREE_OFNET_SMS_EXP,FREE_OFNET_SMS_EXP2,FREE_OFNET_SMS_EXP3,FREE_OFNET_MINS_EXP,FREE_OFNET_MINS_EXP2,FREE_OFNET_MINS_EXP3 from RRBS_SUBS_BUNDLE_BUCKETS where MSISDN='$mdn' and bundle_code='$bcode';
exit
EOF
cat /tmp/Naga/FREEMINTOOL/freemin_add.log >>/tmp/Naga/FREEMINTOOL/freemintool$dat.log
cat freemin_add.log | grep -v SQL | awk '/./' | tr -s '\n' | tr -d ' ' | tr ',' '\n' >/tmp/Naga/FREEMINTOOL/subsbundleinfo.txt
echo -e "---------------------------------------------------------------" | tee -a /tmp/Naga/FREEMINTOOL/freemintool$dat.log
echo -e "------BENEFITS|SUBSCRIBER|BUNDLE|USED------------" | tee -a /tmp/Naga/FREEMINTOOL/freemintool$dat.log
echo -e "---------------------------------------------------------------" | tee -a /tmp/Naga/FREEMINTOOL/freemintool$dat.log
paste -d "|" /tmp/Naga/FREEMINTOOL/bun_info.txt /tmp/Naga/FREEMINTOOL/subsbundleinfo.txt /tmp/Naga/FREEMINTOOL/bundleinfo.txt >/tmp/Naga/FREEMINTOOL/benefitscomparison.txt
cat /tmp/Naga/FREEMINTOOL/benefitscomparison.txt | awk -F'|' '{print $1"|"$2"|"$3"|"$3 - $2}' | tee -a /tmp/Naga/FREEMINTOOL/freemintool$dat.log
echo -e "---------------------------------------------------------------" | tee -a /tmp/Naga/FREEMINTOOL/freemintool$dat.log
echo -e "---------------------------------------------------------------" | tee -a /tmp/Naga/FREEMINTOOL/freemintool$dat.log
echo -e "Pls select the Appropriate column to change expiry date(Select multiple columns separated by '|' symbol) or Press ENTER to EXIT......."
echo -e "For Ex: MINS_EXP|SMS_EXP|DATA_EXP|BUNDLE_EXP|ONNET_MT_EXPIRY|OFFNET_MT_EXPIRY|FREE_OFNET_SMS_EXP|FREE_OFNET_SMS_EXP2|FREE_OFNET_SMS_EXP3|FREE_OFNET_MINS_EXP|FREE_OFNET_MINS_EXP2|FREE_OFNET_MINS_EXP3"
read reqexp
#if5
if [ -z $reqexp ]
then
echo "ERROR: No columns were selected to change expiry date.....";rm ctrlstart;exit 0
fi
echo -e "Add Request recieved by $user for $reqexp" >>/tmp/Naga/FREEMINTOOL/freemintool$dat.log
echo "$reqexp" | tr '|' '\n' | tr -d '\r' >columnnamesexp.txt
echo "Enter the EXPIRY DATE to be set for the above selected columns:"
read collvalue
echo "Value recieved for $coll is $collvalue..." >>/tmp/Naga/FREEMINTOOL/freemintool$dat.log
for coll in `cat columnnamesexp.txt | tr -d '\r'`
do
echo "update rrbs_subs_bundle_buckets set $coll='$collvalue' where MSISDN='$mdn' and bundle_code='$bcode';" >>free_minexp.sql
echo "commit;" >>free_minexp.sql
done
echo -e "---------------------------------------------------------------" | tee -a /tmp/Naga/FREEMINTOOL/freemintool$dat.log
echo -e "--------UPDATE Queries as below------------------------------" | tee -a /tmp/Naga/FREEMINTOOL/freemintool$dat.log
cat free_minexp.sql | tee -a /tmp/Naga/FREEMINTOOL/freemintool$dat.log
echo -e "---------------------------------------------------------------" | tee -a /tmp/Naga/FREEMINTOOL/freemintool$dat.log
echo -e "Press 1 to proceed or any key to exit......"
read updateflag1
#if6
if [ $updateflag1 == 1 ]
then
sqlplus $dbconn << EOF
spool /tmp/Naga/FREEMINTOOL/freemin_add.log
@free_minexp.sql
spool off;
exit;
EOF
fi
cat /tmp/Naga/FREEMINTOOL/freemin_add.log >>/tmp/Naga/FREEMINTOOL/freemintool$dat.log
echo "Update Completed Successfully..." | tee -a /tmp/Naga/FREEMINTOOL/freemintool$dat.log
#-------------------------------To fetch bundle buckets
sqlplus $dbconn <<EOF
set head off line 900 colsep ,;
spool /tmp/Naga/FREEMINTOOL/freemin_add.log;
select FREE_MINS,FREE_SMS,FREE_DATA,ONNET_MT_MINUTES,ONNET_MT_SMS,OFFNET_MT_MINUTES,OFFNET_MT_SMS,FREE_OFNET_SMS,FREE_OFNET_SMS2,FREE_OFNET_SMS3,FREE_OFNET_MINS,FREE_OFNET_MINS2,FREE_OFNET_MINS3,MINS_EXP,SMS_EXP,DATA_EXP,BUNDLE_EXP,ONNET_MT_EXPIRY,OFFNET_MT_EXPIRY,FREE_OFNET_SMS_EXP,FREE_OFNET_SMS_EXP2,FREE_OFNET_SMS_EXP3,FREE_OFNET_MINS_EXP,FREE_OFNET_MINS_EXP2,FREE_OFNET_MINS_EXP3 from RRBS_SUBS_BUNDLE_BUCKETS where MSISDN='$mdn' and bundle_code='$bcode';
exit
EOF
cat /tmp/Naga/FREEMINTOOL/freemin_add.log >>/tmp/Naga/FREEMINTOOL/freemintool$dat.log
cat freemin_add.log | grep -v SQL | awk '/./' | tr -s '\n' | tr -d ' ' | tr ',' '\n' >/tmp/Naga/FREEMINTOOL/subsbundleinfo.txt
echo -e "---------------------------------------------------------------" | tee -a /tmp/Naga/FREEMINTOOL/freemintool$dat.log
echo -e "------BENEFITS|SUBSCRIBER|BUNDLE|USED------------" | tee -a /tmp/Naga/FREEMINTOOL/freemintool$dat.log
echo -e "---------------------------------------------------------------" | tee -a /tmp/Naga/FREEMINTOOL/freemintool$dat.log
paste -d "|" /tmp/Naga/FREEMINTOOL/bun_info.txt /tmp/Naga/FREEMINTOOL/subsbundleinfo.txt /tmp/Naga/FREEMINTOOL/bundleinfo.txt >/tmp/Naga/FREEMINTOOL/benefitscomparison.txt
cat /tmp/Naga/FREEMINTOOL/benefitscomparison.txt | awk -F'|' '{print $1"|"$2"|"$3"|"$3 - $2}' | tee -a /tmp/Naga/FREEMINTOOL/freemintool$dat.log
echo -e "---------------------------------------------------------------" | tee -a /tmp/Naga/FREEMINTOOL/freemintool$dat.log
echo -e "---------------------------------------------------------------" | tee -a /tmp/Naga/FREEMINTOOL/freemintool$dat.log
fi
if [ $entry == b ]
then
#------------------------------- DM MAIN--------------------------------------------------------
echo -e "---------------------------------------------------------------" | tee -a /tmp/Naga/FREEMINTOOL/freemintool$dat.log
echo -e "---------------------------------------------------------------" | tee -a /tmp/Naga/FREEMINTOOL/freemintool$dat.log
echo -e "Enter the MSISDN without CC...."
read mdn
echo -e "MSISDN selected by $user is $mdn" >>/tmp/Naga/FREEMINTOOL/freemintool$dat.log
#if1
if [ -z $mdn ]
then
echo "ERROR: MDN cannot be Null.....";rm ctrlstart;exit 0
fi
#-------------------------------To fetch available bundle in DM
sqlplus $dmconn <<EOF
set head off line 900 colsep ,;
spool /tmp/Naga/FREEMINTOOL/freemin_add.log;
select TRAN_ID,BUNDLE_CODE,BUNDLE_EXP from RRBS_DM_BUNDLE_TOPUP_HIST where MSISDN='$mdn';
exit
EOF
cat /tmp/Naga/FREEMINTOOL/freemin_add.log >>/tmp/Naga/FREEMINTOOL/freemintool$dat.log
echo -e "---------------------------------------------------------------" | tee -a /tmp/Naga/FREEMINTOOL/freemintool$dat.log
echo -e "---------------------------------------------------------------" | tee -a /tmp/Naga/FREEMINTOOL/freemintool$dat.log
echo -e "Mentioned subscriber has below mentioned bundles...Pls choose the appropriate bundle from below..."
cat freemin_add.log | grep -v SQL | awk '/./' | tr -s '\n' | tr -d ' ' | tr ',' '\n' >/tmp/Naga/FREEMINTOOL/avblbundl.txt
echo -e "---------------------------------------------------------------" | tee -a /tmp/Naga/FREEMINTOOL/freemintool$dat.log
cat /tmp/Naga/FREEMINTOOL/avblbundl.txt
echo -e "---------------------------------------------------------------" | tee -a /tmp/Naga/FREEMINTOOL/freemintool$dat.log
echo -e "---------------------------------------------------------------" | tee -a /tmp/Naga/FREEMINTOOL/freemintool$dat.log
echo -e "Enter the Transaction ID...."
read transid
if [ -z $transid ]
then
echo "ERROR: TRANSACTION ID cannot be Null.....";rm ctrlstart;exit 0
fi
echo -e "Enter the Bundle Code...."
read bcode
echo -e "BCODE selected by $user is $bcode" >>/tmp/Naga/FREEMINTOOL/freemintool$dat.log
if [ -z $bcode ]
then
echo "ERROR: BUNDLE CODE cannot be Null.....";rm ctrlstart;exit 0
fi
echo -e "---------------------------------------------------------------" | tee -a /tmp/Naga/FREEMINTOOL/freemintool$dat.log
echo -e "---------------------------------------------------------------" | tee -a /tmp/Naga/FREEMINTOOL/freemintool$dat.log
#-------------------------------To fetch dm bundle topup hist
sqlplus $dmconn <<EOF
set head off line 900 colsep ,;
spool /tmp/Naga/FREEMINTOOL/freemin_add.log;
select BUNDLE_EXP,ONNET_MINS_EXP,ONNET_SMS_EXP,FREE_BYTES_EXP,OFFNET_MINS1_EXP,OFFNET_MINS2_EXP,OFFNET_MINS3_EXP,OFFNET_SMS1_EXP,OFFNET_SMS2_EXP,OFFNET_SMS3_EXP,ONNET_MT_EXPIRY,OFFNET_MT_EXPIRY from RRBS_DM_BUNDLE_TOPUP_HIST where TRAN_ID='$transid' and MSISDN='$mdn' and bundle_code='$bcode';
exit
EOF
cat /tmp/Naga/FREEMINTOOL/freemin_add.log >>/tmp/Naga/FREEMINTOOL/freemintool$dat.log
cat freemin_add.log | grep -v SQL | awk '/./' | tr -s '\n' | tr -d ' ' | tr ',' '\n' >/tmp/Naga/FREEMINTOOL/subsbundleinfo.txt
echo -e "---------------------------------------------------------------" | tee -a /tmp/Naga/FREEMINTOOL/freemintool$dat.log
echo -e "---------------------------------------------------------------" | tee -a /tmp/Naga/FREEMINTOOL/freemintool$dat.log
paste -d "|" /tmp/Naga/FREEMINTOOL/dmbun_info.txt /tmp/Naga/FREEMINTOOL/subsbundleinfo.txt >/tmp/Naga/FREEMINTOOL/benefitscomparison.txt
cat /tmp/Naga/FREEMINTOOL/benefitscomparison.txt | awk -F'|' '{print $1"|"$2}' | tee -a /tmp/Naga/FREEMINTOOL/freemintool$dat.log
echo -e "---------------------------------------------------------------" | tee -a /tmp/Naga/FREEMINTOOL/freemintool$dat.log
echo -e "Pls select the Appropriate column to change expiry date(Select multiple columns separated by '|' symbol) or Press ENTER to EXIT......."
echo -e "For Ex: BUNDLE_EXP|ONNET_MINS_EXP|ONNET_SMS_EXP|FREE_BYTES_EXP|OFFNET_MINS1_EXP|OFFNET_MINS2_EXP|OFFNET_MINS3_EXP|OFFNET_SMS1_EXP|OFFNET_SMS2_EXP|OFFNET_SMS3_EXP|ONNET_MT_EXPIRY|OFFNET_MT_EXPIRY"
read reqexp
#if5
if [ -z $reqexp ]
then
echo "ERROR: No columns were selected to change expiry date.....";rm ctrlstart;exit 0
fi
echo -e "Add Request recieved by $user for $reqexp" >>/tmp/Naga/FREEMINTOOL/freemintool$dat.log
echo "$reqexp" | tr '|' '\n' | tr -d '\r' >columnnamesexp.txt
echo "Enter the EXPIRY DATE to be set for the above selected columns:"
read collvalue
echo "Value recieved for $coll is $collvalue..." >>/tmp/Naga/FREEMINTOOL/freemintool$dat.log
for coll in `cat columnnamesexp.txt | tr -d '\r'`
do
echo "update RRBS_DM_BUNDLE_TOPUP_HIST set $coll='$collvalue' where TRAN_ID='$transid' and MSISDN='$mdn' and bundle_code='$bcode';" >>free_minexp.sql
echo "commit;" >>free_minexp.sql
done
echo -e "---------------------------------------------------------------" | tee -a /tmp/Naga/FREEMINTOOL/freemintool$dat.log
echo -e "--------UPDATE Queries as below------------------------------" | tee -a /tmp/Naga/FREEMINTOOL/freemintool$dat.log
cat free_minexp.sql | tee -a /tmp/Naga/FREEMINTOOL/freemintool$dat.log
echo -e "---------------------------------------------------------------" | tee -a /tmp/Naga/FREEMINTOOL/freemintool$dat.log
echo -e "Press 1 to proceed or any key to exit......"
read updateflag1
#if6
if [ $updateflag1 == 1 ]
then
sqlplus $dmconn << EOF
spool /tmp/Naga/FREEMINTOOL/freemin_add.log
@free_minexp.sql
spool off;
exit;
EOF
fi
cat /tmp/Naga/FREEMINTOOL/freemin_add.log >>/tmp/Naga/FREEMINTOOL/freemintool$dat.log
echo "Update Completed Successfully..." | tee -a /tmp/Naga/FREEMINTOOL/freemintool$dat.log
#-------------------------------To fetch dm bundle topup hist
sqlplus $dmconn <<EOF
set head off line 900 colsep ,;
spool /tmp/Naga/FREEMINTOOL/freemin_add.log;
select BUNDLE_EXP,ONNET_MINS_EXP,ONNET_SMS_EXP,FREE_BYTES_EXP,OFFNET_MINS1_EXP,OFFNET_MINS2_EXP,OFFNET_MINS3_EXP,OFFNET_SMS1_EXP,OFFNET_SMS2_EXP,OFFNET_SMS3_EXP,ONNET_MT_EXPIRY,OFFNET_MT_EXPIRY from RRBS_DM_BUNDLE_TOPUP_HIST where TRAN_ID='$transid' and MSISDN='$mdn' and bundle_code='$bcode';
exit
EOF
cat /tmp/Naga/FREEMINTOOL/freemin_add.log >>/tmp/Naga/FREEMINTOOL/freemintool$dat.log
cat freemin_add.log | grep -v SQL | awk '/./' | tr -s '\n' | tr -d ' ' | tr ',' '\n' >/tmp/Naga/FREEMINTOOL/subsbundleinfo.txt
echo -e "---------------------------------------------------------------" | tee -a /tmp/Naga/FREEMINTOOL/freemintool$dat.log
echo -e "---------------------------------------------------------------" | tee -a /tmp/Naga/FREEMINTOOL/freemintool$dat.log
paste -d "|" /tmp/Naga/FREEMINTOOL/dmbun_info.txt /tmp/Naga/FREEMINTOOL/subsbundleinfo.txt >/tmp/Naga/FREEMINTOOL/benefitscomparison.txt
cat /tmp/Naga/FREEMINTOOL/benefitscomparison.txt | awk -F'|' '{print $1"|"$2}' | tee -a /tmp/Naga/FREEMINTOOL/freemintool$dat.log
fi
echo -e "---------------------------------------------------------------" | tee -a /tmp/Naga/FREEMINTOOL/freemintool$dat.log
if [ $entry == c ]
then
echo -e "Enter the WRONG MSISDN without CC...."
read wrmdn
echo -e "WRONG MSISDN selected by $user is $wrmdn" >>/tmp/Naga/FREEMINTOOL/freemintool$dat.log
echo -e "Enter the CORRECT MSISDN without CC...."
read crmdn
echo -e "CORRECT MSISDN selected by $user is $crmdn" >>/tmp/Naga/FREEMINTOOL/freemintool$dat.log
if [ -z $wrmdn ]
then
echo "ERROR: WRONG MDN cannot be Null.....";rm ctrlstart;exit 0
fi
if [ -z $crmdn ]
then
echo "ERROR: CORRECT MDN cannot be Null.....";rm ctrlstart;exit 0
fi
echo -e "---------------------------------------------------------------" | tee -a /tmp/Naga/FREEMINTOOL/freemintool$dat.log
#-------------------------------MAIN--------------------------------------------------------
echo -e "---------------------------------------------------------------" | tee -a /tmp/Naga/FREEMINTOOL/freemintool$dat.log
echo -e "---------------------------------------------------------------" | tee -a /tmp/Naga/FREEMINTOOL/freemintool$dat.log
#-------------------------------To fetch available balance
sqlplus $dbconn <<EOF
set head off line 900 colsep ,;
spool /tmp/Naga/FREEMINTOOL/freemin_add.log;
select msisdn,acct_balance,topup_face_value,promo_expiry from rrbs_subscriber_profile where msisdn='$wrmdn';
exit
cat freemin_add.log | grep -v SQL | awk '/./' | tr -s '\n' | tr -d ' ' | tr ',' '\n' >/tmp/Naga/FREEMINTOOL/wrongmdn.txt
sqlplus $dbconn <<EOF
set head off line 900 colsep ,;
spool /tmp/Naga/FREEMINTOOL/freemin_add.log;
select msisdn,acct_balance,topup_face_value,promo_expiry from rrbs_subscriber_profile where msisdn='$crmdn';
exit
cat freemin_add.log | grep -v SQL | awk '/./' | tr -s '\n' | tr -d ' ' | tr ',' '\n' >/tmp/Naga/FREEMINTOOL/correctmdn.txt
echo -e "---------------------------------------------------------------" | tee -a /tmp/Naga/FREEMINTOOL/freemintool$dat.log
echo -e "------PROMO BALANCE|WRONG MSISDN|CORRECT MSISDN------------" | tee -a /tmp/Naga/FREEMINTOOL/freemintool$dat.log
echo -e "---------------------------------------------------------------" | tee -a /tmp/Naga/FREEMINTOOL/freemintool$dat.log
paste -d "|" /tmp/Naga/FREEMINTOOL/promo_info.txt /tmp/Naga/FREEMINTOOL/wrongmdn.txt /tmp/Naga/FREEMINTOOL/correctmdn.txt >/tmp/Naga/FREEMINTOOL/promocomparison.txt
cat /tmp/Naga/FREEMINTOOL/promocomparison.txt | awk -F'|' '{print $1"|"$2"|"$3}' | tee -a /tmp/Naga/FREEMINTOOL/freemintool$dat.log
echo -e "---------------------------------------------------------------" | tee -a /tmp/Naga/FREEMINTOOL/freemintool$dat.log
echo -e "!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!"
echo "WARNING: PLS PROCEED ONLY IF ACCT_BALANCE is > TOPUP_FACE_VALUE for CORRECT MSISDN..."
echo -e "!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!"
echo -e "Enter the Amount to be debited and credited..."
read promo_amt
if [ -z $promo_amt ]
then
echo "ERROR: Amount cannot be Null.....";rm ctrlstart;exit 0
fi
echo -e "Enter the PROMO EXPIRY to be set for CORRECT MSISDN..."
read promo_exp
if [ -z $promo_exp ]
then
echo "ERROR: PROMO EXP cannot be Null.....";rm ctrlstart;exit 0
fi
echo "update rrbs_subscriber_profile set ACCT_BALANCE=nvl(ACCT_BALANCE,0) - $promo_amt where MSISDN='$wrmdn';" >free_min.sql
echo "commit;" >>free_min.sql
echo "update rrbs_subscriber_profile set ACCT_BALANCE=nvl(ACCT_BALANCE,0) + $promo_amt, promo_expiry='$promo_exp' where MSISDN='$crmdn' and ACCT_BALANCE>=topup_face_value;" >>free_min.sql
echo "commit;" >>free_min.sql
echo -e "---------------------------------------------------------------" | tee -a /tmp/Naga/FREEMINTOOL/freemintool$dat.log
echo -e "--------UPDATE Queries as below------------------------------" | tee -a /tmp/Naga/FREEMINTOOL/freemintool$dat.log
cat free_min.sql | tee -a /tmp/Naga/FREEMINTOOL/freemintool$dat.log
echo -e "---------------------------------------------------------------" | tee -a /tmp/Naga/FREEMINTOOL/freemintool$dat.log
echo -e "Press 1 to update or any key to exit......"
read updateflag
if [ $updateflag == 1 ]
then
sqlplus $dbconn << EOF
spool /tmp/Naga/FREEMINTOOL/freemin_add.log
@free_min.sql
spool off;
exit;
EOF
fi
cat /tmp/Naga/FREEMINTOOL/freemin_add.log >>/tmp/Naga/FREEMINTOOL/freemintool$dat.log
echo "Update Completed Successfully..." | tee -a /tmp/Naga/FREEMINTOOL/freemintool$dat.log
sqlplus $dbconn <<EOF
set head off line 900 colsep ,;
spool /tmp/Naga/FREEMINTOOL/freemin_add.log;
select msisdn,acct_balance,topup_face_value,promo_expiry from rrbs_subscriber_profile where msisdn='$wrmdn';
exit
EOF
cat freemin_add.log | grep -v SQL | awk '/./' | tr -s '\n' | tr -d ' ' | tr ',' '\n' >/tmp/Naga/FREEMINTOOL/wrongmdn.txt
sqlplus $dbconn <<EOF
set head off line 900 colsep ,;
spool /tmp/Naga/FREEMINTOOL/freemin_add.log;
select msisdn,acct_balance,topup_face_value,promo_expiry from rrbs_subscriber_profile where msisdn='$crmdn';
exit
EOF
cat freemin_add.log | grep -v SQL | awk '/./' | tr -s '\n' | tr -d ' ' | tr ',' '\n' >/tmp/Naga/FREEMINTOOL/correctmdn.txt
echo -e "---------------------------------------------------------------" | tee -a /tmp/Naga/FREEMINTOOL/freemintool$dat.log
echo -e "------PROMO BALANCE|WRONG MSISDN|CORRECT MSISDN------------" | tee -a /tmp/Naga/FREEMINTOOL/freemintool$dat.log
echo -e "---------------------------------------------------------------" | tee -a /tmp/Naga/FREEMINTOOL/freemintool$dat.log
paste -d "|" /tmp/Naga/FREEMINTOOL/promo_info.txt /tmp/Naga/FREEMINTOOL/wrongmdn.txt /tmp/Naga/FREEMINTOOL/correctmdn.txt >/tmp/Naga/FREEMINTOOL/promocomparison.txt
cat /tmp/Naga/FREEMINTOOL/promocomparison.txt | awk -F'|' '{print $1"|"$2"|"$3}' | tee -a /tmp/Naga/FREEMINTOOL/freemintool$dat.log
echo -e "---------------------------------------------------------------" | tee -a /tmp/Naga/FREEMINTOOL/freemintool$dat.log
fi
else
echo "SCRIPT IS ALREADY RUNNING..."
rm ctrlstart
fi
exit 0