#!/usr/local/bin/oraperl
####################################################################
#   File:    import2.pl
#   Input:   Data from the File WCType6.DAT
#   Output:  The script writes all the data in the file to Oracle table 
#    	     tempWarrentyClaimsErrorsMF
#   Author:  Zhijing Zhou         
####################################################################

$ENV{'ORACLE_HOME'}= "/usr/oracle";
$ENV{'PATH'} = "$path /usr/oracle/bin /bin /usr/bin /usr/ucb/usr/local/bin . ";
$ENV{'WOW_UID'}= "$user/$user";
$ENV{'ORACLE_SID'}= "cmuser";
$user="ns_user";
$passwd="ns_user";                                                 

# ==========================================================
# ================= oraperl setup ==========================
# ------------------------------------------------------------
# First make sure that we are running under some form of perl.
# ------------------------------------------------------------
  eval "echo 'You must specify oraperl or coraperl.' ; exit" if 0;
# -----------------------------------------------
# make sure that we really are running (c)oraperl
# -----------------------------------------------
  die ("You should use oraperl, not perl\n") unless defined &ora_login;
# -----------------
# Set up debugging
# -----------------
# -----------------
  $ora_debug = shift if $ARGV[0] =~ /^-#/;
# ----------------
# get error codes
# ----------------
  require('/usr/oracle/oraperl/src/oraperl.ph');
# ---------------------
# login to the database
# ---------------------
  $lda = &ora_login('mindy', $user, $passwd)      || die $ora_errstr;
# ==============  end of oraperl setup =======================  
# ============================================================
&Update_Table;
#&Check_Err;

 sub Update_Table{
 
   # Delete all record from table tempWarrentyClaimsErrorsMF
   $del_rec = "DELETE FROM test_my";
   #Open a complicate query cursor
   $csr= &ora_open($lda, $del_rec);
   &ora_commit($lda);
   &ora_close($csr);
   
   #Read the data in the file 
   open (DBFILE, "WCType.DAT");
       # flock(DBFILE, 2);
        while ( $record = <DBFILE> ) {
                chop($record);
		$record .= " ";
		$Header_Claim_no= substr($record, 0, 7);
		$Header_rec_Type = substr($record, 8, 8);
		$Server_Err=substr($record, 8, 9);
		

		$INSERT_rec = 
		   "INSERT INTO test_my 
		    VALUES ('9' , :1, :2, :3 )";
		#Insert data into Table
		 $csr = &ora_open($lda, $INSERT_rec) || die $ora_errstr;

		# Assign values in SQL statement
		&ora_bind($csr, $Header_Claim_no, $Header_rec_Type, $Sever_Err);
 
		# Close 	
		&ora_close($csr);

		# Commit the insertion
		&ora_commit($lda);
  	}
     #flock (DBFILE, 8);
     close (DBFILE);
     print  $Header_Claim_no;
     print $Header_rec_Type;
     print $Server_Err;

 }
 
 sub Check_Err{
   # Select all record from table tempWarrentyClaimsErrorsMF
   $select_rec = "SELECT a.* 
		  FROM	tempWarrentyClaimsErrorsMF a, WarrentyClaims b				
		  WHERE b.SAMIIReturnStatus='REJECT' AND
			a.Header_Claim_No=b.Claim_Num";

   #Open a complicate query cursor
   $csr= &ora_open($lda, $select_rec);
    
   while(@data=&ora_fetch($csr)){
	$i = 0;
	foreach $attr (@data) {
        	$i = $i + 1;
   		if($i==2){
		  $Header_Claim_No = $attr;
		}
		if($i>3){
		  if($attr>0){
			$ErrMessage = &Lookup_Err($attr);
			&Insert_Table($Header_Claim_No, $ErrMessage);
		  }
		}
	}
   }
  &ora_close($csr);
  
 }

 sub Lookup_Err{
   local($Error)= @_;

   # Select record from table ErrorMessageList
   $select_err1 = "SELECT * FROM ErrorMessageList where ErrorMessageCode= :1";

   #Open a complicate query cursor
   $csr1= &ora_open($lda, $select_err1);
  
   # Assign values in SQL statement
   &ora_bind($csr1, $select_err1);
   
   $ErrorMessage = ""; 
   @data1 = &ora_fetch($csr1);
   foreach $attr1 (@data1){
	$ErrorMessage .= $attr1;
   }
   &ora_close($csr1);
   return $ErrorMessage;
 } 

 sub Insert_Table{
   local($Claim_No, $Errormessage) = @_;
   
   #Insert data into table WarrentyClaimErrors
   $insert_rec1 = "INSERT INTO WarrentyClaimErros VALUES (:1, :2)";
   
   #Open a complicate fquery cursor
   $csr2= &ora_open($lda, $insert_rec1);
   
   # Assign values in SQL statement
   &ora_bind($csr2, $Claim_No, $Errormessage);
   
   # close
   &ora_close($csr2);
   return 1;
  }