#! /usr/local/bin/sybperl 

##########
# read in required files
#require "spawn.pl";
require "sybperl.pl";
#require "/export/home/tboss/sybperl/sql.pl";
require "sql.pl";

# test of parameter passing

#local($servertest)=@_;
#$servertest=$ARGV[0];
#printf "parameter is $servertest\n";
#if ($servertest eq "") {
#   printf "Usage: run.pl \n";
#   exit 1
#}
   

##########
# set variables

$ENV{'SYBASE'} = "/export/sybase";
$ENV{'DSQUERY'} = "peter";

#printf "$SYBASE, $ENV{'DSQUERY'} \n";

##########
# subroutine defenitions

sub populate_sa_pws
{
   open(PWFILE,"/export/syb_ops/.prod11sapw");
   $sapwprod11 = ;
   chop($sapwprod11);
   close(PWFILE);
   open(PWFILE,"/export/syb_ops/.petersapw");
   $sapwpeter = ;
   chop($sapwpeter);
   close(PWFILE);
   open(PWFILE,"/export/syb_ops/.prodsapw");
   $sapwprod = ;
   chop($sapwprod);
   close(PWFILE);
   open(PWFILE,"/export/syb_ops/.new_crimsonsapw");
   $sapwnew_crimson = ;
   chop($sapwnew_crimson);
   close(PWFILE);
}
   
sub pop_pw_alt {
   print "Enter server name : ";
   $server = <>; chop($server);
   print "\nEnter sa password : ";
   #`stty -echo`;
   #system("stty -echo");
   #stty -echo;

   $sapw = <>; chop($sapw);

   #`stty echo`;
   #system("stty echo");
   #stty echo;

   printf "\n $server $sapw\n";
}

sub everydb
{
   #open(LOG,"dblist.logfile");
   #$dbproc = &dblogin("sa", $sapwprod11, "prod11");
   #$dbproc = &dblogin("sa", $sapwpeter, "peter");
   #$dbproc = &dblogin("sa", $sapwprod, "prod");
   $dbproc = &dblogin("sa", $sapwnew_crimson, "new_crimson");
   @dbs = &sql($dbproc, "select name from sysdatabases order by name");

   #printf "@dbs\n";
   foreach $db (@dbs) {
      &dbuse($dbproc, "$db");
      @output = &sql($dbproc, "select name from sysobjects where name='sp_thresholdaction'");
      printf ("$db: @output\n");
   } 

   &dbclose($dbproc);
   #close(LOG);
}

sub do_dbcc
{
   $ENV{'SYBASE'} = "/export/sybase11";
   $ENV{'DSQUERY'} = "prod11";

   $dbproc = &dblogin("sa", $sapw11, "prod11");
   &dbuse($dbproc,"master");

   #&dbcmd("sp_helpdb prrc\n");
   #&dbsqlexec;
   #&dbresults;
   #while (@data = &dbnextrow) {
   #   printf("@data\n");
   #}

   #@data = &sql($dbproc, "sp_helpdb prrc");
   #printf @data;

   system("isql -Usa -P$sapw -o prcc.dbcc.log << EOTSQL
use master
go
dbcc checkdb(prrc)
go
EOTSQL");

#    &dbcmd("dbcc checkdb(prrc)\n");

#    @output = &sql($dbproc, "dbcc checkdb(prrc)" );
#    printf "@output\n";
}

#sub generic_code
#{
#   use Sybase::DBlib;
#   $dbh = Sybase::DBlib->dblogin('sa', $pwd, $server, 'test_app');
#   $dbh->dbcmd("select * from sysprocesses\n");
#   $dbh->dbsqlexec;
#   $dbh->dbresults;
#   while(@data = $dbh->dbnextrow)
#   {
#   .... do something with @data ....
#   }
#}

# something to try...
#system ("isql -Usa -P$sa_password") << "EOF"
#-- start of SQL code
#use $my_db_var
#go
#select foo from bar
#go
#EOF    

### hss mail problem on dali ###

sub mail_error{
       local($error,$address,$subject) = @_;
  $MAIL = '/usr/lib/sendmail';
  $MAIL_TO = $address;
  open (MAIL, "| $MAIL $MAIL_TO") || die "MAIL: $!";
  print MAIL "From: HSS\n\n";
  print MAIL "Subject:  $subject\n\n";
  print MAIL "Error:\n $error\n\n";
  close (MAIL);
}           

### runs on every table in the databases specified

sub every_table
{
   $dbproc = &dblogin("sa", $sapw11);
   &dbuse($dbproc, "master");
   #@dbs = &sql($dbproc, "select name from sysdatabases order by name");
   #@dbs = ("edison");
   #@dbs = ("hss","common");
   @dbs = ("prrc_old","prrc2","prrc");
   #printf "@dbs\n";
   #printf ("database..table: indid indexnamename maxrowsperpage\n");
   foreach $db (@dbs) {
      #printf "$db\n\n";
      &sql($dbproc, "use $db");
      @tables = &sql($dbproc, "select name from sysobjects where type = 'U' 
                                 order by name");
      #printf ("@tables\n");
      #printf ("name rowtotal reservedKB dataKB index_sizeKB unusedKB\n\n");
      foreach $table (@tables) {
         #&sql($dbproc, "update statistics $table");
         #&sql($dbproc, "sp_recompile $table");

         #@out=&sql($dbproc, "sp_spaceused cdr_owner.$table"," ");
         #printf("@out\n");

	 #&sql($dbproc,"dbcc traceon(3604)");
	 #&sql($dbproc,"dbcc tab(production,$table,0)");

         #&sql($dbproc,"grant select on $table to issc");
         #&sql($dbproc,"grant all on $table to cc_lan_admin");

         &sql($dbproc,"dbcc reindex($table)");

	 #@out=&sql($dbproc,"select * from $table where 1=2");
         #@out=&sql($dbproc,"select indid,name,maxrowsperpage from sysindexes 
         #        where id=object_id('$table') and maxrowsperpage>0 "," ");
         #$num=@out;
         #if ($num != 0 ) {
         #   for ($i=0; $i<$num; $i++) {
         #      @splitarray=split(" ",@out[$i]);
         #      printf ("$db..$table: indid=@splitarray[0]: @out[$i]\n");
         #      &sql($dbproc, "update sysindexes set maxrowsperpage=0 where 
         #           id=object_id('$table') and indid=@splitarray[0]");
         #      #printf ("$db..$table: $num lines $i counter: @out[$i]\n");
         #      #printf ("$db..$table: @out[$i]\n");
         #   }
         #}
      }
   } 

   &dbclose($dbproc);
}

sub delete_procs {
   $dbproc = &dblogin("sa", $sapw11);
   @dbs = ("CDB");
   foreach $db (@dbs) {
      &sql($dbproc, "use $db");
      @procs = &sql($dbproc, "select name from sysobjects where type = 'P'");
      foreach $proc (@procs) {
         &sql($dbproc, "drop procedure $proc");
      }
   }
}
   
sub pop_p11_logins {
   $ENV{'SYBASE'} = "/export/sybase";
   $ENV{'DSQUERY'} = "prod10";
   $dbproc10 = &dblogin("sa", $sapw10);

   $ENV{'SYBASE'} = "/export/sybase11";
   $ENV{'DSQUERY'} = "prod11";
   $dbproc11 = &dblogin("sa", $sapw11);

   &dbuse($dbproc10, "master");
   @logins = &sql($dbproc10, "select name from syslogins");
   foreach $login (@logins) {
      &sql($dbproc11, "sp_addlogin $login $login");
   }
   @dbs = &sql($dbproc10, "select name from sysdatabases order by name");
}

sub test {
#   $ENV{'SYBASE'} = "/export/sybase";
#   $ENV{'DSQUERY'} = "prod10";
   $dbproc10 = &dblogin("sa", $sapw10);
   &dbuse($dbproc10, "master");
   @test = &sql($dbproc10, "select name from sysdatabases"); print @test;

   $ENV{'SYBASE'} = "/export/sybase11";
   $ENV{'DSQUERY'} = "prod11";
   $dbproc11 = &dblogin("sa", $sapw11);
   &dbuse($dbproc11, "master");
   @test = &sql($dbproc11, "select name from sysdatabases"); print @test;

}

sub parametertest {
   printf "server is $servertest \n";
}

sub testpwpop {
@servers = ("prod", "prod11"); 
foreach $server (@servers) {

   $ENV{'DSQUERY'} = $server;

   if ($server eq "prod") {
     $sapw = "pinetree"; 
   }
   else {
     $sapw = "p11tree";
   }

   printf "$server, $sapw\n";

   $d = &dblogin("sa", $sapw);  
   #@out=&sql($d, "sp_who");
   #printf @out;
   }
}

sub test_logins {
   $total=0;
   $bad=0;
   $d = &dblogin("sa", $sapw11);
   @loginlist=&sql($d, "select name from syslogins");
   #&dbclose($d);
   foreach $login (@loginlist) {
      $d = &dblogin($login, $login);
      $total+=1;
      if ($d ne -1) {
         #printf "login name = $login, return code = $d\n";
         &dbclose($d);
      }
      else {
         $bad+=1;
      }
   }
   printf "Out of $total logins, $bad were bad\n";
}

sub jezek {
$DirName="tboss";
$SecondaryAddresses = "$DirName\@dtm.cc.nih.gov";

printf "$SecondaryAddresses\n";
}

sub prrctest {

@exception_protocol_list="91-AR-0196,92-AR-0065,94-AR-0066,94-AR-0048,\
95-AR-0082,84-D-0056,94-D-0097,97-D-0123,95-H-0116,93-H-0128,93-H-0111,\
94-H-0052,94-H-0093,94-H-0099,95-H-0099,87-CH-0152,90-CH-0123,91-CH-0046,\
91-CH-0147,92-CH-0011,93-CH-0054,94-CH-0103,94-CH-0104,96-CH-0033";

   printf "@exception_protocol_list\n";
   $protocol_id = "93-CH-0054";
   $exists = grep(/$protocol_id/, @exception_protocol_list);
   printf "protocol id is: $protocol_id, exists is $exists\n";
   $protocol_id = "booga";
   $exists=grep(/$protocol_id/, @exception_protocol_list);
   printf "protocol id is: $protocol_id, exists is $exists\n";

}

sub testprrcemail {
   $d = &dblogin("sa", $sapw11);
   &dbuse($d, "common");
   @testemailaddresses = &sql($d,"select distinct(ri_email_address) \
     from cc_researcher_info order by ri_email_address");
   foreach $address (@testemailaddresses) {
      printf "$address ";
      #open(MAIL, "| /usr/lib/sendmail $address");
      #print MAIL "To: $address\n";
      #print MAIL "From: tboss\@cc.nih.gov\n";
      #print MAIL "Reply-To: tboss\@cc.nih.gov\n";
      #print MAIL "Errors-To: tboss\@cc.nih.gov\n";
      #print MAIL "Subject: test message\n\n";
      #print MAIL
#"Dear Researcher; we're conducting a validity test of the email address we have
#for you in our database system.  If you received this, your email address is
#valid and you may disregard this message.  Sorry for the intrusion.\n
#Thanks,
#  Todd Boss, CC/ISD";
#      close(MAIL);
#      sleep 3;
   }
}         

sub gotest {
   $d = &dblogin("sa", $sapw11);
   &dbuse(master);
   push(@ddl_array, "use hss\n");
   push(@ddl_array, "go\n");
   push(@ddl_array, "select name from sysobjects where type = 'U' order by name\n");
   push(@ddl_array, "go\n");

   printf "@ddl_array\n";
   printf "***\n";
   while ($line = shift(@ddl_array)) {
      @res = &sql($d,$line)
         unless ($line eq "go\n");
      printf "@res\n";
   }
}

##########
# execute!

#&pop_pw_alt 

&populate_sa_pws;

#&do_dbcc;
#&mail_error("booga booga booga", "tboss@pop.cc.nih.gov", "test mail");
#&test;
#¶metertest ;
#&testpwpop ;
#&every_table;
#&test_logins;
#&jezek;
#&prrctest;
#&testprrcemail;
#&gotest;

&everydb;