#!/bin/ksh
#
# This module, in any form, is the proprietary property of
# Western Wireless Corp and may not be used or distributed
# without the written permission of Western Wireless Corp.
# Copyright(c) 2002
#
# File:         charges_by_subs
#
# Description:  create a table of charges by subscriber
#               and bill cycle for the current month.
#
# $Header$
#
# $Revision$
#
# Audit Trail Log:
# 04/30/2002 Jeff Martin    Initial creation. 
#
###########################################################################

#### DEBUG ####
DBUSER=wwouapp2b
DBPWD=wwouapp2b
DBNAME=test
#### DEBUG ####

#check to see if the script is already running
#BASENAME=`basename $0`
BASENAME=DEBUG
ISRUNNING=`ps -ef|grep $BASENAME|grep -v vi|grep -v more|grep -v grep|wc -l`
if [ "$ISRUNNING" -ge 2 ]
then
    echo "\n$BASENAME already running.\n"
    exit 0
fi

# source ksh library
#. $HOME/SummitRpts/Env/kshLIB
. $HOME/jmartin/Env/kshLIB

PROCESS_NAME="CHARGES BY SUBSCRIBER"

#MAIL_LIST=""

# Get dates.
START_DATE=`date -u +01-%b-%Y | tr "[:lower:]" "[:upper:]"`
SYSTEMTIME=`date +%H%M%S`
CURRENT_DATE=`date +%Y%m%d`
TODAY=`date "+%m/%d/%Y"`
export SYSTEMTIME CURRENT_DATE START_DATE TODAY
TIMESTAMP=`date "+%m/%d/%Y %H:%M:%S"`

# define directories variables
LOG_FILE=./Log/charge_by_subs_"$CURRENT_DATE".log

################# main ##############################################

#compute the date range to use
if [ "$1" -gt 0 -a "$1" -lt 13 ]
then
        CURRMONTH=$1
else
        CURRMONTH=`date "+%m"`
        echo No month given, using $CURRMONTH
fi
if [ -n "$2" ]
then
        CURRYEAR=$2
else
        CURRYEAR=`date "+%Y"`
        echo No year given, using $CURRYEAR
fi

PREVYEAR=$CURRYEAR  #Only of use when it is January.

# eliminate leading zero from month number
CURRMONTH=$(($CURRMONTH + 0))

if [ $CURRMONTH -eq 1 ]
then
        PREVMONTH=12
        PREVYEAR=$((CURRYEAR - 1))
else
        PREVMONTH=$(($CURRMONTH - 1))
fi

RUN_MONTH_START=$PREVMONTH
RUN_MONTH_END=$CURRMONTH
RUN_YEAR_START=$PREVYEAR
RUN_YEAR_END=$CURRYEAR
TABLE_NAME=CHARGES_BY_SUBS_"$RUN_MONTH_END"_"$RUN_YEAR_END"

echo "+++++++++++++++++++++++++++++++++++"         >  $LOG_FILE
echo "+ CHARGES_BY_SUBS PROCESS STARTED +"         >> $LOG_FILE
echo "+++++++++++++++++++++++++++++++++++"         >> $LOG_FILE
echo "Started: $TIMESTAMP\n"                       >> $LOG_FILE
echo "Using $RUN_MONTH_START/$RUN_YEAR_START - \
$RUN_MONTH_END/$RUN_YEAR_END for $TABLE_NAME\n"    >> $LOG_FILE

#################################################################
#create a driver table
#################################################################
sqlplus -s $DBUSER/$DBPWD@$DBNAME << CREATE_DRIVER >> $LOG_FILE 

set ECHO OFF
set TERM OFF

PROMPT Dropping ALL_BAN_PER_DATE table
DROP TABLE ALL_BAN_PER_DATE;

PROMPT Creating ALL_BAN_PER_DATE table
!date

CREATE TABLE ALL_BAN_PER_DATE AS
SELECT bill.ban,
       bill.cycle_code,
       bill.cycle_run_month,
       bill.cycle_run_year
FROM   bill
WHERE  ((bill.cycle_code BETWEEN 1 AND 19
       AND bill.cycle_run_month = $RUN_MONTH_END
       AND bill.cycle_run_year  = $RUN_YEAR_END)
       OR
       (bill.cycle_code BETWEEN 20 AND 31
       AND bill.cycle_run_month = $RUN_MONTH_START
       AND bill.cycle_run_year  = $RUN_YEAR_START));

COMMIT;

PROMPT Created ALL_BAN_PER_DATE table
!date
SELECT COUNT(*) from ALL_BAN_PER_DATE;

EXIT;
CREATE_DRIVER

################################################################
#create the base table
################################################################
sqlplus -s $DBUSER/$DBPWD@$DBNAME << CREATE_TABLE >> $LOG_FILE 

set ECHO OFF
set TERM OFF

PROMPT Dropping $TABLE_NAME
DROP TABLE $TABLE_NAME;
COMMIT;

CREATE TABLE $TABLE_NAME
(
        ban                     NUMBER(9) NOT NULL,
        cycle_code              NUMBER(2), 
        cycle_run_month         NUMBER(2),
        cycle_run_year          NUMBER(4),
        subscriber_no           VARCHAR2(20),
        feature_code            CHAR(6) NOT NULL, 
        ftr_revenue_code        CHAR(3) NOT NULL,
        sumofactv_amt           NUMBER(12,2) NOT NULL
) 
-- TABLESPACE cdata02 STORAGE (INITIAL 10M NEXT 10M) NOLOGGING;

PROMPT Creating $TABLE_NAME table
!date

INSERT INTO $TABLE_NAME
        SELECT /*+ FIRST_ROWS */ bill.ban,
               bill.cycle_code,
               bill.cycle_run_month,
               bill.cycle_run_year,
               charge.subscriber_no,
               charge.feature_code,
               charge.ftr_revenue_code,
               SUM(charge.actv_amt) as sumofactv_amt
        FROM   bill, charge, all_ban_per_date driver
        WHERE  bill.ban             = driver.ban
        AND    bill.cycle_code      = driver.cycle_code
        AND    bill.cycle_run_year  = driver.cycle_run_year
        AND    bill.cycle_run_month = driver.cycle_run_month
        AND    bill.ban             = charge.ban
        AND    bill.bill_seq_no     = charge.actv_bill_seq_no
        GROUP BY bill.cycle_code,bill.cycle_run_month,
                 bill.cycle_run_year,bill.ban,
                 charge.subscriber_no,charge.feature_code,
                 charge.ftr_revenue_code;

COMMIT;

PROMPT Created $TABLE_NAME table:
!date

EXIT;
CREATE_TABLE

echo "+++++++++++++++++++++++++++++++++++++"    >> $LOG_FILE
echo "+ CHARGES_BY_SUBS PROCESS COMPLETED +"    >> $LOG_FILE
echo "+++++++++++++++++++++++++++++++++++++"    >> $LOG_FILE
echo "Completed: `date`\n"                      >> $LOG_FILE

#check status 
checkSQLStatus "$PROCESS_NAME" $LOG_FILE

#####################################################
exit 0