#!/bin/bash

# TITLE: DAM - Device administration menu

# VERSION: 5.0

# AUTHOR: Julian Ward

# DESCRIPTION: Used to set device attributes within Trilliant headend DB

#

# INFO: Can be run from anywhere script is located by using ./DAM

# Related files: *.tmp files are generated within local directory each time the menu is run

# If any do remain these can be safely removed.

# mm*.log files are also generated within the local directory, and provide a record

# of actions carried out in each session. Not to be removed.

#

# trap Ctrl Z to stop users crashing out into shell if they do Ctrl Z

stty susp ^-

#

VERSION="DAMv5"

TIMESTAMP=`date +%H%M%S`

DATESTAMP=`date +%d%m%y%H%M%S`

INCIDENTREF=$datestamp

# Define DB connection

#DBCONNECT="sqlplus -s he_write/hfgf87FFG@racprdPSMHE2.uk.centricaplc.com"

DBCONNECT="sqlplus -s smmhe/phase3db082012@racprdPSMHE2.uk.centricaplc.com"

ACTION="No action"

#Generate a unique id for this user/session so multiple instances dont clash with same tmp files

USERID=$USER

ID=$USERID$$$TIMESTAMP

LOGFILE=/smart_metering/metermod/DAMLOGS/$VERSION$ID".log"

CDATE="Version4.1,05/10/2014,1076594803268"

echo $INCIDENTREF "Started session...." >> $LOGFILE

clear

# Get current date

CC=`expr $CDATE | cut -c23,27,30`

CC1=`expr $CC + 10`

#

validate()

{

valid="n"

echo "set echo off

set feedback off

set heading off

spool doesexist$ID.tmp

select device_id from SMMHE.DEVICE where device_id= '$meterref';

spool off

exit;" | $DBCONNECT

if (grep $meterref doesexist$ID.tmp)

then

valid="y"

dt=`echo $meterref | cut -c1`

if [ $dt = "Z" ]

then

devtype="Electric"

fi

if [ $dt = "A" -o $dt = "B" ]

then

devtype="IHU(In home unit)"

fi

if [ $dt = "E" ]

then

devtype="Gas"

fi

if [ $dt = "N" ]

then

devtype="Comms hub"

fi

#getdetails

else

echo "Cannot find "$meterref"!, Please ensure the device ref is correct"

fi

}

#

validateTaccount()

(

#Validates a Trilliant account ref (ACCOUNT_ID) which is 19 digitS and uses the ACCOUNT_ID field in DB

# -------------------------

valida="n"

echo "set echo off

set feedback off

set heading off

spool doesaccexist$ID.tmp

select ACCOUNT_ID from SMMHE.TBLACCOUNT where ACCOUNT_ID='$accountref';

spool off

exit;" | $DBCONNECT

if (grep $accountref doesaccexist$ID.tmp)

then

valida="y"

else

echo "Cannot find "$accountref"! Please ensure the account id is correct"

fi

)

# VALIDATES A UTILITYACOUNTNUMBER which is 10 digit or NAME

# -------------------------

validateUaccount()

(

validu="n"

echo "set echo off

set feedback off

set heading off

spool doesaccexist$ID.tmp

select NAME from SMMHE.TBLACCOUNT where NAME='$accountref';

spool off

exit;" | $DBCONNECT

if (grep $accountref doesaccexist$ID.tmp)

then

validu="y"

else

echo "Cannot find "$accountref"! Please ensure the utility account id is correct!"

fi

)

#

#

enteraccountref()

{

accountref="n"

validu="n"

while [ $validu != "y" ]

do

echo "Enter Utility account ref (Ctrl C to quit)"

read rr

#accountref=`echo $rr | tr '[:lower:]' '[:upper:]'| cut -c1-10`

accountref=$rr

while [ -z "$accountref" ]

do

echo "Enter Utility account ref (Ctrl C to quit)"

read $rr

#accountref=`echo $rr | tr '[:lower:]' '[:upper:]'| cut -c1-10`

accountref=$rr

done

validateUaccount

if (grep $accountref doesaccexist$ID.tmp)

then

validu="y"

fi

done

clear

}

log()

{

echo $DATESTAMP $ACTION >> $LOGFILE

}

enterdev()

{

meterref="n"

valid="n"

while [ $valid != "y" ]

do

echo "Enter device ref (Ctrl C to quit)"

read i

meterref=`echo $i | tr '[:lower:]' '[:upper:]'| cut -c1-14`

while [ -z "$meterref" ]

do

echo "Enter device ref (Ctrl C to quit)"

read i

meterref=`echo $i | tr '[:lower:]' '[:upper:]'| cut -c1-14`

done

log

validate

done

clear

}

enterSDPid()

{

echo "Enter MPxN ref (Ctrl C to quit)"

read SDPid

while [ -z "$SDPid" ]

do

echo "Enter MPxN ref (10 digit gas, 13 digit electric) (Ctrl C to quit)"

read SDPid

done

}

viewmeter()

{

#cc

enterdev

getdetails

}

getdetails()

{

ACTION="Get device details "$meterref

#GET ACTIVE STATUS for device

# -------------------------

#

echo "set echo off

set feedback off

set heading off

spool active$ID.tmp

select ACTIVE from SMMHE.DEVICE where device_id= '$meterref';

spool off

exit;" | $DBCONNECT

clear

#GET COMM MODULE ID for DEVICE

# -------------------------

#

echo "set echo off

set feedback off

set heading off

spool commid$ID.tmp

select COMM_MODULE_ID from SMMHE.DEVICE where DEVICE_ID= '$meterref';

spool off

exit;" | $DBCONNECT

clear

#Strip out leading whitespace

cat commid$ID.tmp |awk '{print $1}'| tr -d '[:cntrl:]' > c$ID.tmp

cat c$ID.tmp |tr -d '[:blank:]' > c2$ID.tmp

devdev=`cat c2$ID.tmp | awk '{print $1}'`

commmodid=`cat c2$ID.tmp | awk '{print $1}'`

#

# GET ACCOUNT_ID and SDP for COMM HUB

# -------------------------

#

echo "set echo off

set feedback off

set heading off

spool allforcommmod$ID.tmp

select ACCOUNT_ID,SDP_ID from SMMHE.TBLDEVICE_INFO where DEVICE_NAME= '$commmodid';

spool off

exit;" | $DBCONNECT

clear

# GET ACCOUNT_ID(Trilliant) and UTILITY account no for COMMM id

# -------------------------

#

echo "set echo off

set feedback off

set heading off

spool commacc$ID.tmp

select ACCOUNT_ID from SMMHE.TBLDEVICE_INFO where DEVICE_NAME= '$commmodid';

spool off

exit;" | $DBCONNECT

cat commacc$ID.tmp |awk '{print $1}'| tr -d '[:cntrl:]' > cacc$ID.tmp

cat cacc$ID.tmp |tr -d '[:blank:]' > cacc2$ID.tmp

cat cacc2$ID.tmp |tr -d '[:space:]' > cacc3$ID.tmp

commaccountid=`cat cacc3$ID.tmp | awk '{print $1}'`

echo "set echo off

set feedback off

set heading off

spool commaccU$ID.tmp

select NAME from SMMHE.TBLACCOUNT where ACCOUNT_ID= '$commaccountid';

clear

spool off

exit;" | $DBCONNECT

cat commaccU$ID.tmp |awk '{print $1}'| tr -d '[:cntrl:]' > cU$ID.tmp

cat cU$ID.tmp |tr -d '[:blank:]' > cU2$ID.tmp

cat cU2$ID.tmp |tr -d '[:space:]' > cU3$ID.tmp

commaccountidU=`cat cU3$ID.tmp | awk '{print $1}'`

#

# GET COMMMISSIONED STATUS for device

# -------------------------

#

echo "set echo off

set feedback off

set heading off

spool commissioned$ID.tmp

select IS_COMMISSIONED from SMMHE.TBLDEVICE_INFO where device_name= '$meterref';

spool off

exit;" | $DBCONNECT

clear

# GET COMMISSIONED DATE for device

# -------------------------

echo "set echo off

set feedback off

set heading off

spool commissioneddate$ID.tmp

select COMMISSIONED_DATE from SMMHE.TBLDEVICE_INFO where device_name= '$meterref';

spool off

exit;" | $DBCONNECT

clear

cat commissioneddate$ID.tmp | tr -d '[:cntrl:]' > cdateU$ID.tmp

cdate=`cat cdateU$ID.tmp | cut -c1-9`

chour=`cat cdateU$ID.tmp | cut -c11-12`

cmin=`cat cdateU$ID.tmp | cut -c14-15`

campm=`cat cdateU$ID.tmp | cut -c27-`

#

# GET ACCOUNT_ID and UTILITY ACCOUNT for device

# -------------------------

echo "set echo off

set feedback off

set heading off

spool account$ID.tmp

select ACCOUNT_ID from SMMHE.TBLDEVICE_INFO where DEVICE_NAME= '$meterref';

spool off

exit;" | $DBCONNECT

cat account$ID.tmp |awk '{print $1}'| tr -d '[:cntrl:]' > accU$ID.tmp

cat accU$ID.tmp |tr -d '[:blank:]' > accU2$ID.tmp

cat accU2$ID.tmp |tr -d '[:space:]' > accU3$ID.tmp

accountU=`cat accU3$ID.tmp | awk '{print $1}'`

echo "set echo off

set feedback off

set heading off

spool accountU$ID.tmp

select NAME from SMMHE.TBLACCOUNT where ACCOUNT_ID= '$accountU';

spool off

exit;" | $DBCONNECT

cat accountU$ID.tmp |awk '{print $1}'| tr -d '[:cntrl:]' > aU$ID.tmp

cat aU$ID.tmp |tr -d '[:blank:]' > aU2$ID.tmp

cat aU2$ID.tmp |tr -d '[:space:]' > aU3$ID.tmp

aU=`cat aU3$ID.tmp | awk '{print $1}'`

clear

#

# GET ACCOUNT_ID and UTILITY ACCOUNT for COMMS hub

# ----------------------------

# ACCOUNT_ID is Trilliant 19 digit reference to the utility account id

#

echo "set echo off

set feedback off

set heading off

spool accountCOM$ID.tmp

select ACCOUNT_ID from SMMHE.TBLDEVICE_INFO where DEVICE_NAME= '$commmodid';

spool off

exit;" | $DBCONNECT

clear

cat accountCOM$ID.tmp |tr -d '[:cntrl:]' > aC$ID.tmp

cat aC$ID.tmp |tr -d '[:blank:]' > a2C$ID.tmp

cat a2C$ID.tmp |tr -d '[:space:]' > a3C$ID.tmp

isaccountC=`cat a3C$ID.tmp | awk '{print$1}'`

echo "set echo off

set feedback off

set heading off

spool accountCOMU$ID.tmp

select NAME from SMMHE.TBLACCOUNT where ACCOUNT_ID= '$isaccountC';

spool off

exit;" | $DBCONNECT

clear

cat accountCOMU$ID.tmp |tr -d '[:cntrl:]' > aCcu$ID.tmp

cat aCcu$ID.tmp |tr -d '[:blank:]' > aCcu1$ID.tmp

cat aCcu1$ID.tmp |tr -d '[:space:]' > aCcu2$ID.tmp

isaccountCcu=`cat aCcu2$ID.tmp | awk '{print$1}'`

#

# GET last read and read type for device

# ------------------------

#select TIME,READING_TYPE from SMMHE.READING where DEVICE_ID= '$meterref';

echo "set echo off

set heading off

set feedback off

spool read$ID.tmp

select max(TIME) from SMMHE.READING where DEVICE_ID= '$meterref';

spool off

exit;" | $DBCONNECT

clear

tail -4l read$ID.tmp | tr -d '[:space:]' > r2$ID.tmp

rdate=`cat r2$ID.tmp | cut -c1-9`

rtime=`cat r2$ID.tmp | cut -c10-14`

rampm=`cat r2$ID.tmp | cut -c25-26`

rtype=`cat r2$ID.tmp | cut -c27-`

#

# GET SDP for device

# ------------------

#

echo "set echo off

set heading off

set feedback off

spool sdp$ID.tmp

select SDP_ID from SMMHE.TBLDEVICE_INFO where device_name= '$meterref';

spool off

exit;" | $DBCONNECT

clear

if grep 0 active$ID.tmp

then

devactive="Inactive"

else

devactive="Active"

fi

#new bit

if grep 1 commissioned$ID.tmp

then

devcomm="Commissioned"

else

devcomm="Not commissioned"

fi

#Strip out ctrl chars and blanks

cat account$ID.tmp |tr -d '[:cntrl:]' > a$ID.tmp

cat a$ID.tmp |tr -d '[:blank:]' > a2$ID.tmp

cat a2$ID.tmp |tr -d '[:space:]' > a3$ID.tmp

isaccountT=`cat a3$ID.tmp | awk '{print$1}'`

isaccount=$aU

#if [ -z $isaccount ]

#then

# accountpresent="No associated account id"

#else

accountpresent=$isaccount

#fi

# GET SDP FOR device

# --------------------

echo "set echo off

set feedback off

set heading off

spool devs$ID.tmp

select DEVICE_NAME,SDP_ID from SMMHE.TBLDEVICE_INFO where device_name='$meterref';

spool off

exit;" | $DBCONNECT

clear

# GET ADDRESS FOR ACCOUNT ID

# ----------------------------

echo "set echo off

set feedback off

set heading off

spool address$ID.tmp

select address1,address_city,address_zip from SMMHE.TBLACCOUNT where account_id='$isaccount';

spool off

exit;" | $DBCONNECT

clear

# GET ADDRESS FOR COMM HUB ID

# -----------------------------

echo "set echo off

set feedback off

set heading off

spool addressC$ID.tmp

select address1,address_city,address_zip from SMMHE.TBLACCOUNT where NAME='$commaccountidU';

spool off

exit;" | $DBCONNECT

clear

# GET ALL DEVICES for account id

# --------------------------------

echo "set echo off

set feedback off

set heading off

spool Odevs2$ID.tmp

select DEVICE_NAME,SDP_ID from SMMHE.TBLDEVICE_INFO where account_id='$isaccountT';

spool off

exit;" | $DBCONNECT

clear

cat Odevs2$ID.tmp | tr -d '[:blank:]' > devs2$ID.tmp

# GET firmware vers

# --------------------------------

echo "set echo off

set feedback off

set heading off

spool firm$ID.tmp

select VALUE from SMMHE.DEVICE_PARAMETER where INFORMATION_TYPE='operationalFirmware' and DEVICE_ID='$meterref';

spool off

exit;" | $DBCONNECT

clear

cat firm$ID.tmp | tr -d '[:blank:]' > firm2$ID.tmp

fv=`cat firm2$ID.tmp | awk '{print $1}'`

#

# GET meter balance for prepay

echo "set echo off

set feedback off

set heading off

spool balance$ID.tmp

select CREDIT_BALANCE FROM (select * from prepayment_balances where device_id='$meterref' order by READ_DATE desc) xxxxx

where rownum <2

order by rownum desc;

spool off

exit;" | $DBCONNECT

clear

balance=`cat balance$ID.tmp | awk '{print $1}'`

rm balance$ID.tmp

#

cat sdp$ID.tmp | tr -d '[:cntrl:]' > s$ID.tmp

cat s$ID.tmp |tr -d '[:blank:]' > s2$ID.tmp

issdp=`cat s2$ID.tmp | awk '{print$1}'`

if [ -z $issdp ]

then

sdppresent="No associated MPxN reference"

else

sdppresent=$issdp

fi

clear

echo $devtype "device "$meterref" is" $devactive "and "$devcomm "(Commissioned: "$cdate $chour":"$cmin" "$campm")" > status$ID.tmp

echo "MPxN: "$sdppresent >> status$ID.tmp

echo "Trilliant account id: "$isaccountT >> status$ID.tmp

echo "Utility account id: "$isaccount >> status$ID.tmp

#echo "Last read communication:"$rdate $rtime$rampm "Type: "$rtype >> status$ID.tmp

echo "Last read communication:"$rdate $rtime$rampm "Balance:"$balance >> status$ID.tmp

echo "Operational firmware version: " $fv >> status$ID.tmp

echo "" >> status$ID.tmp

echo "Associated Devices / MPxN / Account :" >> status$ID.tmp

echo "--------------------------------------------" >> status$ID.tmp

cat allforcommmod$ID.tmp >> status$ID.tmp

#cat devs$ID.tmp >> status$ID.tmp

cat devs2$ID.tmp >> status$ID.tmp

echo "Address:" >> status$ID.tmp

#does file exist and greater than 0

if [ -s address$ID.tmp ]

then

cat address$ID.tmp >> status$ID.tmp

else

cat addressC$ID.tmp >> status$ID.tmp

fi

cat status$ID.tmp

read f

rm *$ID.tmp

#END OF getdetails

}

#

setmeteractive()

{

enterdev

ACTION="Set "$meterref" Active"

echo $ACTION

echo "set echo off

set feedback off

spool setactive$ID.tmp

update SMMHE.device set active=1 where device_id= '$meterref';

commit;

spool off

exit;" | $DBCONNECT

clear

log

getdetails

}

setmetercommissioned()

{

enterdev

ACTION="Set "$meterref" as commissioned"

echo $ACTION

echo "set echo off

set feedback off

spool setcommissioned$ID.tmp

update SMMHE.tbldevice_info set is_commissioned=1 where device_id in (

select pkey from SMMHE.device_pkey where device_id= '$meterref');

commit;

spool off

exit;" | $DBCONNECT

clear

log

getdetails

}

setmeterinactive()

{

enterdev

ACTION="Set "$meterref" Inactive"

echo $ACTION

echo "set echo off

set feedback off

spool setinactive$ID.tmp

update SMMHE.device set active=0 where device_id= '$meterref';

commit;

spool off

exit;" | $DBCONNECT

clear

log

getdetails

}

setmeternotcommissioned()

{

enterdev

ACTION="Set "$meterref" as not commissioned"

echo $ACTION

echo "set echo off

set feedback off

spool setnotcommissioned$ID.tmp

update SMMHE.tbldevice_info set is_commissioned=0 where device_id in (

select pkey from SMMHE.device_pkey where device_id= '$meterref');

commit;

spool off

exit;" | $DBCONNECT

clear

log

getdetails

}

setcommshub()

{

enterdev

echo "Enter comms hub id for $meterref"

read comm_module_id

ACTION="Set "$meterref" comms hub"

echo $ACTION

log

backuptable

log

echo "set echo off

spool setcommshub$ID.tmp

update SMMHE.device set COMM_MODULE_ID='$comm_module_id' where device_id='$meterref';

commit;

spool off

exit;" | $DBCONNECT

}

RemoveUTIL-TRILLaccountAndMpxn()

{

enterdev

getdetails

ACTION="Remove Trilliant,Utility account and MPxN refs from device "$meterref

echo $ACTION

log

backuptable

log

echo "set echo off

set feedback off

spool removeaccount$ID.tmp

update SMMHE.tbldevice_info set ACCOUNT_ID=NULL where device_name='$meterref';

update SMMHE.tblaccount set UTILITYACCOUNTNUMBER=NULL where account_id='$commaccountid';

update SMMHE.tblaccount set NAME=NULL where account_id='$commaccountid';

update SMMHE.tbldevice_info set SDP_ID=NULL where device_name='$meterref';

commit;

spool off

exit;" | $DBCONNECT

clear

getdetails

}

RemoveTRILLaccountAndMpxn()

{

enterdev

getdetails

ACTION="Remove Trilliant account id and MPxN ref from device "$meterref

echo $ACTION

log

backuptable

log

echo "set echo off

set feedback off

spool removeaccount$ID.tmp

update SMMHE.tbldevice_info set ACCOUNT_ID=NULL where device_name='$meterref';

update SMMHE.tbldevice_info set SDP_ID=NULL where device_name='$meterref';

commit;

spool off

exit;" | $DBCONNECT

clear

getdetails

}

#

setaccountid()

{

enterdev

echo "Enter NEW utility account number"

read AR

accountid=$AR

accountref=$accountid

ACTION="Set utility account id to $accountid for device $meterref"

echo $ACTION

log

backuptable

log

echo "set echo off

set feedback off

set heading off

spool tac$ID.tmp

select ACCOUNT_ID from SMMHE.tblaccount where NAME='$accountref';

spool off

exit;" | $DBCONNECT

cat tac$ID.tmp |tr -d '[:cntrl:]' > ta$ID.tmp

cat ta$ID.tmp |tr -d '[:blank:]' > ta2$ID.tmp

cat ta2$ID.tmp |tr -d '[:space:]' > ta3$ID.tmp

tac=`cat ta3$ID.tmp | awk '{print$1}'`

echo "set echo off

set feedback off

spool addaccountid$ID.tmp

update SMMHE.TBLACCOUNT set NAME='$AR' where ACCOUNT_ID='$tac';

update SMMHE.TBLACCOUNT set UTILITYACCOUNTNUMBER='$AR' where ACCOUNT_ID='$tac';

update SMMHE.TBLDEVICE_INFO set ACCOUNT_ID='$tac' where DEVICE_NAME='$meterref';

commit;

spool off

exit;" | $DBCONNECT

clear

}

setSDPid()

{

#update SMMHE.tblaccount_rate_program set SDP_ID='$SDPid' where ACCOUNT_ID='$commaccountid';

enterdev

enterSDPid

ACTION="Set MPxN to $SDPid for device $meterref"

echo $ACTION

log

backuptable

log

echo "set echo off

set feedback off

spool addsdpid$ID.tmp

update SMMHE.tbldevice_info set SDP_ID='$SDPid' where device_name='$meterref';

update SMMHE.tblopt_in_out_preferences set MPXN='$SDPid' where device_id='$meterref';

commit;

spool off

exit;" | $DBCONNECT

clear

}

setmeterinactivenotcommremoveacc()

#option 10

{

enterdev

ACTION="Set $meterref inactive, not commissioned and remove account and MPxN ref"

echo $ACTION

log

backuptable

log

echo "set echo off

set feedback off

spool removeall$ID.tmp

update SMMHE.tbldevice_info set rateprogram_id=NULL where device_name='$meterref';

update SMMHE.tbldevice_info set account_id=NULL where device_name='$meterref';

update SMMHE.tbldevice_info set SDP_ID=NULL where device_name='$meterref';

update SMMHE.tbldevice_info set is_commissioned=0 where device_name='$meterref';

update SMMHE.device set active=0 where device_id='$meterref';

commit;

spool off

exit;" | $DBCONNECT

getdetails

}

backuptable()

{

echo "If there is an incident reference please enter now as just incident number i.e 01234567. If not enter n"

read rep

if [ $rep = "n" ]

then

ir=$INCIDENTREF

else

ir=$rep

fi

#additional new entry to include tblaccount_rate_program SDP_ID

irb=$ir"b"

ACTION="Device entry $meterref backed up in DB as $ir"

echo "

spool backup$ID.tmp

insert into SMMHE.incident

(DEVICE_ID

,LOCATION

,DONOTSYNC

,PRODUCT_ID

,ACCOUNT_ID

,PARTNER_ID

,RATEPROGRAM_ID

,CREATED_BY_ID

,CREATED_DATE

,LAST_MODIFIED_BY_ID

,LAST_MODIFIED_BY_DATE

,SDP_ID

,IS_COMMISSIONED

,COMMISSIONED_DATE

,IS_COTMOVED_OUT

,IS_COS_LOSS

,MAC_ADDRESS

,INSTALL_CODE

,PAYMENT_CARD_NO

,DEVICE_NAME

,COT_ACTION_TYPE

,INCIDENTREF

,DATESTAMP

)

select DEVICE_ID

,LOCATION

,DONOTSYNC

,PRODUCT_ID

,ACCOUNT_ID

,PARTNER_ID

,RATEPROGRAM_ID

,CREATED_BY_ID

,CREATED_DATE

,LAST_MODIFIED_BY_ID

,LAST_MODIFIED_BY_DATE

,SDP_ID

,IS_COMMISSIONED

,COMMISSIONED_DATE

,IS_COTMOVED_OUT

,IS_COS_LOSS

,MAC_ADDRESS

,INSTALL_CODE

,PAYMENT_CARD_NO

,DEVICE_NAME

,COT_ACTION_TYPE

,'$ir',sysdate from SMMHE.tbldevice_info where device_name='$meterref';

commit;

spool off

exit;" | $DBCONNECT

clear

}

help()

{

clear

echo "This menu is designed for the process of changing specific device attributes within the SMART metering Headend DB"

echo ""

echo "1. View details View current device attributes."

echo " Requires a device ref e.g ZN01234567"

echo "2. Set active Sets device to active."

echo " Requires a device ref e.g ZN01234567"

echo "3. Set commissioned Sets device to commissioned."

echo " Requires a device ref e.g ZN01234567"

echo "4. Set inactive Sets device status to inactive"

echo " Requires a device ref e.g ZN01234567"

echo "5. Set decommissioned Sets device status to decommissioned"

echo " Requires a device ref e.g ZN01234567"

echo "6. Set utility account ref for device. Sets Utility account ref for device"

echo " Requires a device ref e.g ZN01234567"

echo "7. Set MPxN for device. Set MPxN for device"

echo " Requires a device ref e.g ZN01234567"

echo "8. Remove Trilliant account & MPxN ref for device."

echo " Requires a valid device."

echo "9. Remove Utility,Trilliant account & MPxN ref for device."

echo " Requires a valid device"

echo "11. Remove Trilliant account,MPxN,Rate program refs,Set inactive,not commissioned."

echo " Requires a valid device ref."

echo "12. Lookup by address. Displays devices associated to an address"

echo " Requires 1st line of address and postcode"

echo "13. Lookup by account ref. Displays devices associated to a Utility account id"

echo " Requires a valid 10 digit Utility account id"

echo "14. Lookup by MPxN ref. Displays devices associated to an MPxN"

echo " Requires a valid MPxN reference"

echo "h Help Displays this help"

echo "q Quit Exit"

echo ""

echo "More........press enter"

read x

echo "Definitions:"

echo "____________"

echo "MPxN = Meter point number - Premise Id 13 digit"

echo "MPAN = Meter point number - Electric Meter 13 digit"

echo "MPRN = Meter point number - Gas Meter 10 digit"

echo ""

echo "NDPD0123456 = Comms Hub 11 Alpha-numeric, 'NDPD' character prefix, plus 7 digits"

echo "A012345678 = In Home unit(IHU) 10 Alpha-numeric, 'A' character prefix, plus 9 digits"

echo "BB0123456789012 = In Home unit(IHU) 14 Alpha-numeric, 'BB' character prefix, plus 12 numeric"

echo "ZN01234567 = Electric meter 10 Alpha-numeric, 'Z' character prefix, plus 9 alpha-numeric"

echo "E0123456789012 = Gas meter l4 Alpha-numeric, 'E' character prefix, plus 13 alpha-numeric"

echo "0123456789 = Utility account 10 numeric"

echo "0123456789012345678 = Trilliant account 19 numeric"

read x

}

enteradd()

{

add1=""

while [ -z "$add1" ]

do

echo "Enter 1st line of address e.g 24 IMAGINARY ROAD ( Ctrl C to quit )"

read ad

add1=`echo $ad | tr '[:lower:]' '[:upper:]'`

done

}

enterzip()

{

zip=""

while [ -z "$zip" ]

do

echo "Enter POST CODE e.g with space, as SQ44 2BR ( Ctrl C to quit )"

read z

zip=`echo $z | tr '[:lower:]' '[:upper:]'`

done

}

reverselookupbyaddress()

{

enteradd

enterzip

echo "Checking........."

echo "set echo off

set feedback off

set heading off

spool revbyaddress$ID.tmp

select NAME from TBLACCOUNT where ADDRESS1='$add1';

spool off

exit;" | $DBCONNECT

cat revbyaddress$ID.tmp |tr -d '[:blank:]' > UA$ID.tmp

cat UA$ID.tmp |tr -d '[:space:]' > UA2$ID.tmp

Uaccount=`cat UA2$ID.tmp`

if [ -z "$Uaccount" ]

then

echo "Nothing found for $add1 postcode $zip, check address and post code are correct"

read f

rm *$ID.tmp

else

echo "set echo off

set feedback off

set heading off

spool byaddressA$ID.tmp

select ACCOUNT_ID from TBLACCOUNT where UTILITYACCOUNTNUMBER='$Uaccount';

spool off

exit;" | $DBCONNECT

cat byaddressA$ID.tmp |tr -d '[:cntrl:]' > r$ID.tmp

cat r$ID.tmp |tr -d '[:blank:]' > r2$ID.tmp

cat r2$ID.tmp |tr -d '[:space:]' > r3$ID.tmp

Taccount=`cat r3$ID.tmp | awk '{print $1}'`

echo $add1 $zip > status$ID.tmp

echo "Utility account ID="$Uaccount >> status$ID.tmp

echo "Trilliant account ID="$Taccount >> status$ID.tmp

echo "set echo off

set feedback off

set heading off

spool revres$ID.tmp

select DEVICE_NAME, SDP_ID, PAYMENT_CARD_NO from TBLDEVICE_INFO where ACCOUNT_ID='$Taccount';

spool off

exit;" | $DBCONNECT

clear

if [ -z revres$ID.tmp ]

then

echo "No devices found" >> status$ID.tmp

cat status$ID.tmp

read f

rm *$ID.tmp

else

echo "Associated devices / MPxN" >> status$ID.tmp

cat revres$ID.tmp >> status$ID.tmp

cat status$ID.tmp

read f

rm *$ID.tmp

fi

fi

}

reverselookupbyaccountid()

{

#enteraccountref

echo "Enter utility account ref"

read accountref

echo "set echo off

set feedback off

set heading off

spool revacc$ID.tmp

select ACCOUNT_ID,NAME,ADDRESS1,ADDRESS_CITY,ADDRESS_ZIP from TBLACCOUNT where NAME='$accountref';

spool off

exit;" | $DBCONNECT

clear

cat revacc$ID.tmp |tr -d '[:cntrl:]' > reva$ID.tmp

cat reva$ID.tmp |tr -d '[:blank:]' > reva1$ID.tmp

cat reva1$ID.tmp |tr -d '[:space:]' > reva2$ID.tmp

revUaccount=`cat reva2$ID.tmp | awk '{print $1}'| cut -c20-29`

revTaccount=`cat reva2$ID.tmp | awk '{print $1}'| cut -c1-19`

revAD1account=`cat reva2$ID.tmp | awk '{print $1}'| cut -c30-`

echo "set echo off

set feedback off

set heading off

spool revdevsfromaccountid$ID.tmp

select DEVICE_NAME,SDP_ID from TBLDEVICE_INFO where ACCOUNT_ID='$revTaccount';

spool off

exit;" | $DBCONNECT

clear

cat revacc$ID.tmp > status$ID.tmp

echo "Devices/MPxN associated to utility account ref "$accountref >> status$ID.tmp

cat revdevsfromaccountid$ID.tmp >> status$ID.tmp

cat status$ID.tmp

read f

}

#show program version and date for log file

cc()

{

rm=0

while [ $rm = "0" ]

do

Versionh=`date +%H`

Versions=`date +%S`

Versionv=`expr $Versionh "+" $Versions`

VDATE="Version4.1,05/10/2014,1076594803268"

Versiondate=`expr $VDATE | cut -c23,31,24`

Versiont=`expr $Versionv "+" $Versiondate`

echo $Versionv" Code?"

read ic

if [ $ic -eq "$Versiont" ]

then

rm=1

clear

else

exit

fi

done

}

reverselookupbySDPid()

{

enterSDPid

echo "set echo off

set feedback off

set heading off

spool revSDP$ID.tmp

select DEVICE_NAME, SDP_ID from TBLDEVICE_INFO where SDP_ID='$SDPid';

spool off

exit;" | $DBCONNECT

clear

echo "Lookup devices associated to MPxN "$SDPid > status$ID.tmp

cat revSDP$ID.tmp >> status$ID.tmp

cat status$ID.tmp

read f

}

quit()

{

rm -f *$ID.tmp

exit

}

cleanup()

# cleanup *.tmp temp files created during run

{

rm -r *$ID.tmp

exit $?

# return $?

}

control_c()

# run if user hits control c

{

cleanup

}

# trap Cntrl C keyboard interrupt (control-c)

trap control_c SIGINT

# main() loop

fs=`wc -c $0| awk '{print $1}'`

#cc

#if [ $fs = 26927 -a $0 = "./DAMv5" ]

#then

while [ 1 ]

do

#Do menu

cat <<-EOT

HEADEND DB DEVICE ADMINISTRATION MENU ($VERSION)

1. View device details

2. Set device active

3. Set device commissioned

4. Set device inactive

5. Set device not commissioned

6. Set device utility account id

7. Set device MPxN id

8. Set device comms hub

9. Remove Trilliant account and MpxN refs for device

10. Remove Utility,Trilliant account and MpxN refs for device

11. Remove Trilliant account,MPxN,Rate program refs,inactive,not commissioned

12. Lookup by address

13. Lookup by account

14. Lookup by MPxN

h. Help

q. Exit

EOT

read choice

case $choice in

1) viewmeter

;;

2) setmeteractive

;;

3) setmetercommissioned

;;

4) setmeterinactive

;;

5) setmeternotcommissioned

;;

6) setaccountid

;;

7) setSDPid

;;

8) setcommshub

;;

9) RemoveTRILLaccountAndMpxn

;;

10) RemoveUTIL-TRILLaccountAndMpxn

;;

11) setmeterinactivenotcommremoveacc

;;

12) reverselookupbyaddress

;;

13) reverselookupbyaccountid

;;

14) reverselookupbySDPid

;;

h) help

;;

q) quit

;;

*)

esac

clear

done

#else

# exit 0

#fi