#!/usr/local/bin/perl
#script 'ppcd_approval_ora.pl'
#

#   chop($WEBSERVER_NAME=`uname -n`);
   $WEBSERVER_NAME="pngedms1.amd.com:7780";
   $LOCALTIME=localtime;

#2-21-2001 Modified By SK Lau
#Mail Tracking Log file generate
   open(LOGFILE,">>ppcd_approval_ora.log") or die "Can't find the ppcd_approval_ora.log file, please touch the approve file in current dir";
   print LOGFILE "\n******************************* \n";
   print LOGFILE "$LOCALTIME  ";

   $ENV{ORACLE_BASE} = "/oracle/dbapp";
   $ENV{ORACLE_SID} = "equal";
   $ENV{ORACLE_HOME} = "/oracle/dbapp/product/9.2.0";
   $ENV{NLS_LANG} = "AMERICAN";


   if($ENV{'REQUEST_METHOD'} eq "GET" ) {
        $_ = $ENV{'QUERY_STRING'};
       ($one,$two,$three,$four,$five,$six) = split /&/,$_,6;
        ($temp1,$add_id )= split /=/,$one,2;
        ($temp2,$ppcd_no)= split /=/,$two,2;
        ($temp2,$status) = split /=/,$three,2;
        ($temp2,$folder_name) = split /=/,$four,2;
        ($temp2,$ppcd_id) = split /=/,$five,2;
        ($temp2,$notification_list) = split /=/,$six,2;

#######################################
use Oraperl;
#$local_db = $ENV{'ORACLE_SID'};
$lda = &ora_login('','PPCD@equal.dev.edms2','dlbest') or die "database connection failed";
$csr = &ora_open($lda,"select brief_desc,detail_desc
         from ppcd_plan
         where ppcd_id = $ppcd_id");
        ($ppcd_description,$change_description) = &ora_fetch($csr);
#$ore = &ora_open($lda,"select distinct r.email_address 
#    from   ppcd_main m, ppcd_plan p, cr_categories c, ppcd_wf.wf_indexes i
#    , ppcd_wf.wf_history h, wf_user_roles r
#    where  m.ppcd_id = $ppcd_id 
#    and m.ppcd_id = p.ppcd_id
#    and m.group_id = c.ctg_id
#    and i.spec_no = $ppcd_id 
#    and i.wf_id = h.wf_id
#    and (h.processno = 1)
#    and r.role_name = h.approverid");  
#        ($originator_email) = &ora_fetch($ore);
$epr = &ora_open($lda,"select released_date
         from ppcd_qual_plan
         where ppcd_id = $ppcd_id");
        ($epr_date) = &ora_fetch($epr);
$det = &ora_open($lda,"select u.user_name 
         from ppcd_main m, cr_users u
         where m.author = u.user_id and ppcd_id = $ppcd_id");
        ($det_author) = &ora_fetch($det);
$res = &ora_open($lda,"select responsible_person 
         from ppcd_qual_plan  
         where ppcd_id = $ppcd_id");
        ($res_person) = &ora_fetch($res);
$rel = &ora_open($lda,"select released_date 
         from ppcd_qual_plan 
         where ppcd_id = $ppcd_id");
        ($rel_date) = &ora_fetch($rel);
$ct1 = &ora_open($lda,"select c.ctg_parent_id,c.ctg_name
         from   ppcd_main m, ppcd_plan p, cr_categories c
         where  m.ppcd_id = $ppcd_id 
         and m.ppcd_id = p.ppcd_id
         and m.group_id = c.ctg_id");
        ($ct1_no,$ct1_name) = &ora_fetch($ct1);
$fd2 = &ora_open($lda,"select ctg_name 
       from cr_categories
       where ctg_id = $ct1_no"); 
       ($fd2_name) = &ora_fetch($fd2);
$ct2 = &ora_open($lda,"select ctg_parent_id 
       from cr_categories
       where ctg_id = $ct1_no"); 
       ($ct2_no) = &ora_fetch($ct2);
$fd1 = &ora_open($lda,"Select ctg_name 
       from cr_categories 
       where ctg_id = $ct2_no"); 
       ($fd1_name) = &ora_fetch($fd1);   
$ori = &ora_open($lda,"select u.email_address
       from ppcd_main m, cr_users u
       where m.author = u.user_id and ppcd_id = $ppcd_id"); 
       ($ori_email) = &ora_fetch($ori);


#######################################
 
        print "Content-type: text/html\n";
        print "Refresh:1;  URL=http://$WEBSERVER_NAME/ppcd/ppcd_search.listing_page\n\n";

        #12-Jan-2001 Modified By SKLAU
        #Split the whole comment string and convert to the right sentences#

        $folder_name =~s/%(\w\w)/pack'H2',$1/eg;
        $ppcd_description =~s/%(\w\w)/pack'H2',$1/eg;
        $status =~s/%(\w\w)/pack'H2',$1/eg;
        $fd1_name =~s/%(\w\w)/pack'H2',$1/eg;
        $fd2_name =~s/%(\w\w)/pack'H2',$1/eg;
        $ct1_name =~s/%(\w\w)/pack'H2',$1/eg;
        $res_person =~s/%(\w\w)/pack'H2',$1/eg;
        $_1 = join '/', $fd1_name,$fd2_name,$ct1_name;
        $_1 =~s/%(\w\w)/pack'H2',$1/eg;
 
        print LOGFILE "PPCD ID: $ppcd_id  ";
        print LOGFILE "PPCD NO: $ppcd_no ";
        print LOGFILE "STATUS: $status \n";
        print LOGFILE "RES PERSON: $res_person \n";
        
 
        $pid=$$;
        open(OUTP,">temp/$pid");
        print OUTP "\n";
        print OUTP "PPCD $ppcd_no has moved to $status status. Please approve/disapprove as soon as \n";
        print OUTP "possible. \n";
        print OUTP "\n";
        print OUTP "PPCD Owner (Originator): $det_author                                            \n"; 
        print OUTP "Project Title: $ppcd_description                                                \n";
        print OUTP "Detail Description of Change: $change_description                               \n"; 
        print OUTP "PPCD Originator Group: $_1"; 
        print OUTP "                                                                                \n"; 
        print OUTP "Person Responsible to Release: $res_person                                      \n";
        print OUTP "Estimated Production Release Date: $rel_date \n";
        print OUTP "\n";
        print OUTP "Click link to view PPCD. \n";
        print OUTP "http://$WEBSERVER_NAME/ppcd/ppcd_approval.approval_confirm_page?ppcd_id=$ppcd_id \n";
        print OUTP "\n";
        print OUTP "AMD Confidential \n";
        close(OUTP);

        while ($add_id =~ /%/) {
           ($email_id,$add_id) = split /%/,$add_id,2;
           if ($all_email eq ""){ 
              $all_email = $email_id;
           }
           else
           { 
              $all_email = join ',', $all_email,$email_id;
           }
        }
            $out=`/usr/ucb/mail -s "PPCD Approval Notice - $ppcd_no" -b "wei-ling.chong\@amd.com"  -c "$ori_email" $all_email < temp/$pid `;
            print LOGFILE "PID : $pid \n";
            print LOGFILE "~~ APPROVAL LIST ~~\n";
            print LOGFILE "$all_email : O.K \n";
            print LOGFILE "~~ ORIGINATOR LIST ~~\n";
            print LOGFILE "$ori_email : O.K \n";

        
        $pid2=$$;
        open(OUTP,">temp/$pid2");
        print OUTP "\n";
        print OUTP "THIS EMAIL IS FOR INFORMATION ONLY. No action is required by you. \n";
        print OUTP "PPCD $ppcd_no has moved to $status status. \n";
        print OUTP "\n";
        print OUTP "PPCD Owner (Originator): $det_author                                            \n";
        print OUTP "Project Title: $ppcd_description \n";
        print OUTP "Detail Description of Change: $change_description                               \n";
        print OUTP "PPCD Originator Group: $_1";
        print OUTP "                                                                                \n";
        print OUTP "Person Responsible to Release: $res_person                                      \n";
        print OUTP "Estimated Production Release Date: $rel_date \n";
        print OUTP "\n";
        print OUTP "Click link to view PPCD. \n";
        print OUTP "http://$WEBSERVER_NAME/ppcd/ppcd_file_content.ppcd_properties?ppcd_id=$ppcd_id \n";
        print OUTP "\n";
        print OUTP "AMD Confidential \n";
        close(OUTP);
 
#       print($add_id);
        while ($notification_list =~ /%/) {
            ($notification_email_id,$notification_list) = split /%/,$notification_list,2;
            if ($all_email_notice eq ""){
                $all_email_notice = $notification_email_id;
             }
             else
             {
                $all_email_notice = join ',', $all_email_notice,$notification_email_id;
             }
        }
#            $_ = $add_id;
#        print($email_id);
           # print $_ ;
           # s/\+/ /g;
           # s/%(..)/pack("c",hex($1))/ge;
           if ($all_email_notice eq ""){ 
            $out=`/usr/ucb/mail -s "PPCD Notification - $ppcd_no" -b "wei-ling.chong\@amd.com" $ori_email < temp/$pid2 `;
            print LOGFILE "PID : $pid2 \n";
            print LOGFILE "~~ NOTIFICATION LIST ~~\n";
            print LOGFILE "$all_email_notice : O.K \n";
            print LOGFILE "~~ ORIGINATOR LIST ~~\n";
            print LOGFILE "$ori_email : O.K \n";
            }
            else
            {
            $out=`/usr/ucb/mail -s "PPCD Notification - $ppcd_no" -b "wei-ling.chong\@amd.com"  -c "$ori_email" $all_email_notice < temp/$pid2 `;
            print LOGFILE "PID : $pid2 \n";
            print LOGFILE "~~ NOTIFICATION LIST ~~\n";
            print LOGFILE "$all_email_notice : O.K \n";
            print LOGFILE "~~ ORIGINATOR LIST ~~\n";
            print LOGFILE "$ori_email : O.K \n";
            }

        print "<HTML>";
        print "<body bgcolor=linen>";
        print "<img src=\"../ppcd-img/email7.gif\">";
        print "<Strong>";
        print " ...HAS BEEN SENT OUT";
   }
   else {
        print "<Strong>";
        print " SENT MAIL TO : " ;
        read(STDIN,$_,10);
        while ($_ =~ /~/) {
           ($email_id,$_) = split /~/,$_,2;
            $out=`/usr/ucb/mail -s "PPCD Approval Notice - $ppcd_no" $email_id, < ewe-keong.tan@amd.com temp/$pid `;
           print "$_";
           print "\n";
        }
   }
   close(LOGFILE);