/** CompDeliveryStat.java      

This application will query the ship, ship_items, items for all component that have been shipped to 
an institute.

Author   : Didier Ferrere (Didier.Ferrere@cern.ch)
Created  : 02-09-04 11:50AM
Modified : 

This a draft code as of 02-09-04 11:51AM

**/

import java.awt.*;
import java.awt.event.*;
import java.io.*;
import java.sql.*; 
import java.math.*; 
import java.lang.*;
import java.util.*;

public class CompDeliveryStat {

    static String FileShipNo="Test.txt";
    private static String DBpwd="sctro";

 public static void main(String[] args) { 
    try {
       if (args.length == 4) {
         new CompDeliveryStat(args[0],args[1],args[2],args[3]); 
       }
       else {System.out.print("3 arguements are requested admitted and the command should be as follow:\n");
             System.out.println("java CompDeliveryStat InstituteName ItemType FileOut DBUserName\n");
       }
    }   
       catch (Exception e) {      
       System.err.print("Execution failed ! " + e);
    } 
	} 		
       public CompDeliveryStat(String InstituteName, String ItemType,String FileOut, String DBUserName) {

        FileOutputStream fos;           // destination file
        TimeZone.getDefault();
        Calendar calendar = new GregorianCalendar();
        calendar.add(Calendar.MONTH,1);
        Connection conn;
        PreparedStatement statement,statement1,statement2;  
        CallableStatement callStatement;
        ResultSet result,result1,result2;    
        String ShipNoInf ="";
        File fi = new File(FileShipNo);
        String LastShipNo="",OutString;
        int SpineTotTrash=0,SpineTotLocn=0,SpineTot=0,SpineTotAssm=0,SpineTotRet=0,SpineTotDiff=0;
        
/** Read the last shipno used in the previous query to lighten the next query        
        try{
        FileReader fr = new FileReader(fi);
        BufferedReader br = new BufferedReader(fr);
         int i=0;
         String Ligne;
         while((Ligne = br.readLine()) != null){
            LastShipNo=Ligne;
            System.out.print(LastShipNo);
            i++;
            if (i>=1 && LastShipNo.length()!=9) {
            	System.out.print("Execution failed due to format error in file: " + FileShipNo+"!!!");
            	throw new Exception("");
        	}
         }
        } catch (Exception e) {System.out.print("The file does not exist!!!");}
**/

// Prepare connection to the SCT production DB 
        try{
          fos = new FileOutputStream(FileOut);
        String connName="jdbc:oracle:thin:@bird.unige.ch:1521:acaprd";         
        // DB part
        // Load Oracle driver

        try  {          
          DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());      
          // Connect to the database
          
          
          conn = DriverManager.getConnection (connName, DBUserName, DBpwd);
          System.out.print("DB connection established..."); 
          OutString = "Ship Number\tShipment Date\tFrom\tDelivered\tAssembled\tReturned\tTrashed\tin "+InstituteName+"\tDiff\n";
          System.out.print(OutString);
          fos.write(OutString.getBytes());   
          statement = conn.prepareStatement ("SELECT ship_no,ship_date,locn_name FROM ship WHERE ship_date>? AND dest_locn_name=? and owner='Sent'"); 
          statement.setString(1,"01-Dec-02");
          statement.setString(2,InstituteName);                 
          result=statement.executeQuery();
          while (result.next()){
              int ShipNo = result.getInt(1);
              String ShipDate=result.getString(2);
              String SourceLoc=result.getString(3);
              ShipDate = ShipDate.substring(0,10);
//             System.out.print("ShipNO: "+ShipNo);
          statement1 = conn.prepareStatement ("SELECT trashed,items.ser_no,locn_name,assembled FROM ship_items,items WHERE ship_no=? AND items.ctype=? AND items.ser_no=ship_items.ser_no"); 
          statement1.setInt(1,ShipNo);  
          statement1.setString(2,ItemType);                       
          result1=statement1.executeQuery();
          int SpineTrash=0, SpineLocn=0;  
          int SpineNb=0, SpineAssm=0,SpineRet=0, Diff=0;         
          while (result1.next()){
           SpineNb++;
           String trash=result1.getString(1); 
           String ItemSerNo=result1.getString(2); 
           String locn=result1.getString(3);
           String assembled=result1.getString(4);  

          statement2 = conn.prepareStatement ("SELECT ship_no FROM ship_items WHERE ser_no=? AND ship_no>?"); 
          statement2.setString(1,ItemSerNo);
          statement2.setInt(2,ShipNo);                         
          result2=statement2.executeQuery();

          if (result2.next()){
          	SpineRet++;
           } else {          
           if (trash.startsWith("YES")){
             SpineTrash++;
//             System.out.print("This shipno "+ShipNo+" contient "+SpineTrash+"!");
           }
           if (locn.startsWith(InstituteName)){
             SpineLocn++;
//             System.out.print("This shipno "+ShipNo+" contient "+SpineLocn+"!");
           }           
           if (assembled.startsWith("YES")){
             SpineAssm++;
//             System.out.print("This shipno "+ShipNo+" contient "+SpineAssm+"!");
           }           
	}
	}	
	  if (SpineNb!=0){
	  SpineTot+=SpineNb;
	  SpineTotTrash+=SpineTrash;
	  SpineTotLocn+=SpineLocn;
	  SpineTotAssm+=SpineAssm;
	  SpineTotRet+=SpineRet;
	  Diff= SpineRet+SpineTrash+SpineAssm-SpineNb;
	  SpineTotDiff+=Diff;
	  OutString = ShipNo+"\t" +ShipDate+"\t"+SourceLoc+"\t"+SpineNb+"\t"+SpineAssm+"\t"+SpineRet+"\t"+SpineTrash+"\t"+SpineLocn+"\t"+Diff+"\n";
          System.out.print(OutString);
          fos.write(OutString.getBytes()); 
	}}
        }  catch( SQLException sqle )  { sqle.printStackTrace();}
        OutString="---------\t----------\t-------\t---\t---\t---\t---\t---\t---\n";
        System.out.print(OutString);
        fos.write(OutString.getBytes());
        OutString="Total:    \t          \t    \t"+SpineTot+"\t"+SpineTotAssm+"\t"+SpineTotRet+"\t"+SpineTotTrash+"\t"+SpineTotLocn+"\t"+SpineTotDiff+"\n";
        System.out.print(OutString); 
        fos.write(OutString.getBytes());  
        OutString="\nQuery made on: "+calendar.get(Calendar.DAY_OF_MONTH)+"/"+calendar.get(Calendar.MONTH)+"/"+calendar.get(Calendar.YEAR);                         
        System.out.print(OutString);
        fos.write(OutString.getBytes());          
        fos.close();  // close the file output
        } catch (Exception e) {System.out.print("Problem with output file...");}
}    
}