#!/bin/sh

SYBASE="/export/sybase11"
pwdir="/export/syb_ops"

sapw11=`cat $pwdir/.prod11sapw`
sapw10=`cat $pwdir/.prodsapw`
sapwcrimson=`cat $pwdir/.crimsonsapw`
sapwnew_crimson=`cat $pwdir/.new_crimsonsapw`

P11ISSA="/export/sybase11/bin/isql -Usa -Sprod11"
PETEISSA="/export/sybase11/bin/isql -Usa -Speter" 
PRODISSA="/export/sybase/bin/isql -Usa -Sprod"
CRIMISSA="/export/sybase/bin/isql -Usa -Scrimson"
NCRIMISSA="/export/sybase/bin/isql -Usa -Snew_crimson"

prod11_dblist="CDB cc_lan cc_web_apps cdr common dtm edison estar hss \
 issc issc_cdr issc_test master mis model prrc sybsecurity \
 sybsyntax sybsystemprocs tempdb"
peter_dblist="CDB cc_lan cc_web_apps cdr cimp cimphist common db1 dtm dtm2 \
 edison erl estar gqlreportdb hss issc issc_cdr issc_test master model mrips \
 pn prrc prrc2 prrc_old pubs2 rdb reference sybsecurity sybsyntax \
 sybsystemprocs tel tempdb vis"
prod_dblist="ARSystem ambis edison erl estar estar1 master model \
 sandbox sybsecurity sybsystemprocs tempdb"
crimson_dblist="cimp cimphist db1 gqlreportdb master model pn reference \
 tel tempdb vis"

##############################

quick_dirty ()
{
#$P10ISSA << EOTSQL
#$PRODISSA << EOTSQL
#$CRIMISSA << EOTSQL
#$P11ISSA << EOTSQL
$PETEISSA << EOTSQL
$sapw11
--sp_helpdb
--sp_configure
--select distinct segmap, size from sysusages group by segmap
--select name from syslogins
--dbcc checkalloc(hss)
--dbcc checkalloc(hss, fix)
--dbcc traceon(3604)
--dbcc page(10,1024000,0,0,1)
--dbcc page(10,1034240,0,0,1)
--select name,dbname from syslogins
sp_sysmon 10
--dump tran issc with no_log
--dump tran issc with truncate_only
--!!buildmaster -yall -d/dev/rdsk/c0t2d0s5
--select name,password,dbname from syslogins
--select name from sysobjects where type = "P" order by name
go
EOTSQL
}

##############################

all_dbs ()
{
#for name in `cat prod10/prod10.dblist`
#for name in `cat prod11.devlist`
#for name in `cat prod11.dblist`
#for name in issc
#for name in $prod_dblist
#for name in $prod11_dblist
#for name in $crimson_dblist
for name in $peter_dblist
do
echo "*** $name ***"

#$P10ISSA << EOTSQL
#$PRODISSA << EOTSQL
#$P11ISSA << EOTSQL
$PETEISSA << EOTSQL
$sapw11
use $name
go
--sp_helpdb $name
--select name from sysobjects where type = "U" order by name
--sp_helpdevice $name
--sp_adduser tboss
--sp_dropuser tboss
--checkpoint
--dbcc checkcatalog($name)
--dbcc checkdb($name)
--dbcc checkalloc($name)
--sp_dboption $name, "single user", false
--sp_helpuser dbo
--dump database $name to "/export/home/tboss/$name.dmp" with init
--sp_helpthreshold
--sp_help sp_block
--select name from sysobjects where type = "P" and name like "sp%" order by name
--select "table" = o.name,"column" = c.name from syscolumns c, sysobjects o
--  where c.id = o.id and convert(bit, c.status & 0x80)=1  order by o.name
--select name, maxrowsperpage from sysindexes where maxrowsperpage > 0
--sp_spaceused 
--sp_addalias daiy, dbo
--sp_dboption $name, "trunc log on chkpt", true
sp_showfrag
go

EOTSQL

done
}

p11_logins ()
{
cat prod10/login.list | while read line
do
   #echo $line
   login=`echo $line | awk '{ print $1 }'`
   loginpw=$login
   dbname=`echo $line | awk '{ print $2 }'`
   #echo "login name = $login, default database = $dbname"
   logincnt=`echo $loginpw | wc -C`
   logincount=`echo $logincnt | awk '{ print $1 }'`
   if [ $logincount -le 6 ]
   then
      #echo $loginpw $logincount
      case $logincount in
      "6") 
         loginpw="$login""x" 
         ;;
      "5") 
         loginpw="$login""xx" 
         ;;
      "4") 
         loginpw="$login""xxx" 
         ;;
      "3") 
         loginpw="$login""xxxx" 
         ;;
      "2")
         loginpw="$login""xxxxx" 
         ;;
      esac

   echo "$login, $loginpw, $logincount"
   fi

$P11ISSA << EOTSQL
$sapw11
use master
go
--sp_addlogin $login, $loginpw, $dbname
sp_droplogin $login
go
EOTSQL

done
}

test ()
{
DOW=`date +%w`
echo "day of week is : $DOW"
case $DOW in
   0)
   echo "in 0"
   ;;
   1|4)
   echo "in 1 or 4"
   ;;
   2|5)
   echo "in 2 or 5"
   ;;
   3|6)
   echo "in 3 or 6"
   ;;
esac              
}

test2 ()
{

normal_dbs=`ls /export/datatools/full_phys/prod11`
special_dbs=`ls /export/datatools/special/prod11`
for name in $normal_dbs $special_dbs
do
   echo $name
done
}

crimsonlogins ()
{
#$CRIMISSA -o crimson.logins << EOTSQL
#$sapwibm
#select convert(varchar(20),name),convert(varchar(15),password),dbname 
#from syslogins order by suid
#go
#EOTSQL

cat crimson.logins | while read line
do
   name=`echo $line | awk '{ print $1 }'`
   pw=`echo $line | awk '{ print $2 }'`
   db=`echo $line | awk '{ print $3 }'`
   logincnt=`echo $pw | wc -C`
   logincount=`echo $logincnt | awk '{ print $1 }'`
   if [ $logincount -le 6 ]
   then                           
      pw="changeme"
   fi
   echo "$name,$pw,$db"
   $NCRIMISSA << EOTSQL
$sapwnew_crimson
sp_addlogin $name,"$pw",$db
go

--use $db
--go
--sp_adduser $name
--go

EOTSQL

done
}

deletecimpprocs ()
{
cat proc.tmp | while read line
do
isql -Usa -Sprod11 << EOTSQL
$sapw11
use db1
go
drop proc $line
go
EOTSQL
done
}

checkprrc ()
{
for name in AdmittedProtocolsOfPatients AskedQuestions Call Caller Case DeclinedProtocols Diagnosis Doctor FollowUp KeyStorage L_DeclinedReason L_FOS L_InfoInquired L_specialty MatchingProtocols Patient PatientDoctor ReferredProtocols ScreeningComment ScreeningQuestions pbcatcol pbcatedt pbcatfmt pbcattbl pbcatvld
do
   isql -Usa -Sprod11 << EOTSQL
$sapw11
use prrc
go
sp_help $name
go
EOTSQL
done
}

killerl ()
{
for name in NonProvAppNum_rule PCTNum_rule PatentNum_rule ProvAppNum_rule r_Date r_EIRGRANTS r_EIRNum r_EIR_PatentExtenReq r_EIR_TitleExtenReq r_GranteeCode r_Inst_BatchProc r_Inst_Number r_PatForeignFiling_Status r_Patent_DomWaivDecision r_Patent_NonProvAppNum r_Patent_PCTNum r_Patent_PatentNum r_Patent_ProvAppNum r_Patent_TypeOFApplic r_Utilize_LatestStageDev        r_Utilize_Number r_Utilize_Royalties r_Utilize_Sales r_type

do
isql -Usa -Speter << EOTSQL
$sapw11
use erl
go
drop rule $name
go
EOTSQL
done
}

#### Main ########

bcp_defn ()
{

#bcp hss..hss out hss.dat -Usa -Sprod10 -I/export/sybase/interfaces
#bcp master..syslogins in p10syslogins.dat -Usa -Sprod11 -c -b1
#bcp issc..progressnotehist in progressnotehist.dat -Usa -Sprod11 \
#  -Jcp437 -fprogressnotehist.fmt

defncopy -Usa -Sprod11 -I/export/sybase/interfaces out \
 sp_helpconstraint.sql sybsystemprocs sp_helpconstraint

#bcp CDB.cdr_owner.lab_results out lab_results.dat -Usa -Sprod11
#-I/export/sybase/interfaces

#bcp prrc..Patient_test in patient.dat -Usa -Sprod11 \
#-I/export/sybase/interfaces -fpatient.fmt
}

#######################

#p11_logins 
#blocker
#test
#test2

crimsonlogins 

#quick_dirty 
#deletecimpprocs 

#checkprrc
#killerl

#bcp_defn 

#all_dbs