package model.atc;
import model.tc.util.*;
import java.sql.*;
import java.io.*;
import java.util.*;
public class ExcelFileOperations{
Connection con;
PreparedStatement ps;
Statement stmt;
ResultSet rs;
ResultSetMetaData rsmd;
Map m;
String query;
/*
public static void main(String args[]) throws Exception{
ImportExcelFile f = new ImportExcelFile();
f.exportExcelFile("C:\\Sep.01.xls","VOBG");
//f.importFile("C:\\Sep.01.xls");
//model.common.DBAccess db = new model.common.DBAccess();
//db.makeConnection("VOBG");
//f.con = db.getConnection();
//f.loadFile(f.rs,con);
//f.exportFile(f.con);
}*/
public java.util.Collection getMap(){
return m.values();
}
public String getMapSize(){
String size="";
size=m.size()+"";
System.out.println("SIZE:getMap"+size.toString());
return size;
}
public void importExcelFile(String excelFileName,String airportCd) throws Exception{
//Step 1. Reads the contents from excel file and stores in a temporary variable.
//Step 2. Takes the contents from temporary variable and stores temporarily in database.
//Step 3. Finally temporarily saved data is exported permanently.
//Step 1.
try{
importFile(excelFileName);
model.common.DBAccess db = new model.common.DBAccess();
db.makeConnection(airportCd);
con = db.getConnection();
//Step 2.
loadFile(con);
if(airportCd.equals("VOHY"))
insertingRouteGCD(con,"VOHS");
}
catch(Exception ex){
System.out.println(ex.toString());
throw ex;
}
}
public void importFile(String fileName) throws Exception{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
con = DriverManager.getConnection("jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ="+fileName);
stmt = con.createStatement();
query = "Select * from [Sheet1$]";
rs = stmt.executeQuery( query );
rsmd = rs.getMetaData();
int rowNo = 1;
int totalCols = rsmd.getColumnCount();
//System.out.println("Total Coumns:"+totalCols);
String row[] = new String[totalCols+1];
m = new HashMap();
while(rs.next()){
row = new String[totalCols+1];
row[0]=" ";
for(int i=1;i<=totalCols;i++) {
row[i] = rs.getString(i);
// System.out.println("row[i]"+row[i]+"& i:("+i+")");
}
System.out.println("ROWS::::::::::"+rowNo);
if(rowNo<=300) {
System.out.println(" Importing the Rows ...");
m.put(String.valueOf(rowNo++),row);
}
else {
m.clear();
System.out.println(" else Cant Import Rows:"+rowNo);
if(rs!=null)rs.close();
if(stmt!=null)stmt.close();
if(con!=null)con.close();
System.out.println("con closed:"+rowNo);
throw new Exception("Can't Import more than 300 Row(s) in a Sheet ");
}
}
System.out.println(".....row no.........."+rowNo+"& Map Size"+m.size());
if(rs!=null)rs.close();
if(stmt!=null)stmt.close();
if(con!=null)con.close();
}
public void loadFile(Connection con) throws Exception{
String insertIntoArrival= "Insert Into Tc_Arrival_Log (fmuid,";
String insertIntoDeparture = "Insert Into Tc_Departure_Log (fmuid,";
String colsInExcelForArrival = "";
String colsInExcelForDeparture = "";
String exceptionMessage = "";
boolean excelFileConfigured = false;
System.out.println(" sample insert test");
query = "Select column_no colNo,Type type,Component_Column colName From ArrivalDeparture_Components comp,ArrivalDeparture_Configuration conf Where comp.Sno=conf.component_sno Order by Type,Column_No";
ps = con.prepareStatement(query);
rs = ps.executeQuery();
while(rs.next()){
if(!excelFileConfigured)
excelFileConfigured = true;
if(rs.getString("type").equals("A")){
insertIntoArrival+= rs.getString("colName")+",";
colsInExcelForArrival += rs.getString("colNo")+",";
}else if(rs.getString("type").equals("D")){
insertIntoDeparture+= rs.getString("colName")+",";
colsInExcelForDeparture += rs.getString("colNo")+",";
}
}
if(!excelFileConfigured)
throw new Exception("Excel file has not configured");
insertIntoArrival = insertIntoArrival.substring(0,insertIntoArrival.length()-1)+") Values (";
colsInExcelForArrival = colsInExcelForArrival.substring(0,colsInExcelForArrival.length()-1);
insertIntoDeparture = insertIntoDeparture.substring(0,insertIntoDeparture.length()-1)+") Values (";
colsInExcelForDeparture = colsInExcelForDeparture.substring(0,colsInExcelForDeparture.length()-1);
System.out.println("colsInExcelForArrival:::::::::::::"+colsInExcelForArrival);
int rows = m.size();
System.out.println(".....rows........"+rows);
int colNo=1;
int maxNo = 1;
String col;
String row[];
StringTokenizer st;
String colValues;
stmt = con.createStatement();
rs = stmt.executeQuery("select max(val) from ((select Nvl(max(To_Number(substr(fmuid, 2))), 0) val from tc_departure_log where fmuid like 'E%') union (select Nvl(max(To_Number(substr(fmuid, 2))), 0) val from tc_arrival_log where fmuid like 'E%'))");
if(rs.next())
maxNo = rs.getInt(1);
if(rs!=null)rs.close();
for(int i=1;i<=rows;i++){
row = (String[])m.get(String.valueOf(i));
//System.out.println(".....row........"+row);
//System.out.println(".....row11111........"+row[1]);
//System.out.println(".....row2222........"+row[2]);
if(row[1]!=null && row[2]!=null){
//System.out.println("....."+i+"....."+row[1]+"...."+row[2]);
colValues = "'E"+(maxNo+i)+"',";
st = new StringTokenizer(colsInExcelForArrival,",");
while(st.hasMoreTokens()){
try{
colNo = Integer.parseInt(st.nextToken());
col = row[colNo];
}catch(ArrayIndexOutOfBoundsException aib_ex){
throw new Exception(" Column No "+colNo+" for Arrivals is exceeding with the no.of columns in the Excel file "+(row.length-1));
}
if(col==null || col.equalsIgnoreCase("null") || col.equals(""))
col = "";
colValues+= "trim('"+col+"'),";
}
colValues = colValues.substring(0,colValues.length()-1);
//stmt.addBatch(insertIntoArrival+colValues);
System.out.println(insertIntoArrival+colValues+")");
try{
stmt.executeUpdate(insertIntoArrival+colValues+")");
}catch(Exception ex){
System.out.println("Problem in Import With the Arrival Movement: "+colValues+" "+ex.toString());
//throw new Exception("Problem in Import With the Arrival Movement: "+colValues+" "+ex.toString());
exceptionMessage +="Problem in Import With the Arrival Movement Row No. "+(maxNo+i+1)+": "+colValues+" "+ex.toString()+"\n";
}
colValues = "'E"+(maxNo+i)+"',";
st = new StringTokenizer(colsInExcelForDeparture,",");
while(st.hasMoreTokens()){
try{
colNo = Integer.parseInt(st.nextToken());
col = row[colNo];
}catch(ArrayIndexOutOfBoundsException aib_ex){
throw new Exception(" Column No "+colNo+" for Departures is exceeding with the no.of columns in the Excel file "+(row.length-1));
}
if(col==null || col.equalsIgnoreCase("null") || col.equals(""))
col = "";
colValues+= "trim('"+col+"'),";
}
colValues = colValues.substring(0,colValues.length()-1);
//stmt.addBatch(insertIntoDeparture+colValues);
//System.out.println(insertIntoDeparture+colValues+")");
try{
stmt.executeUpdate(insertIntoDeparture+colValues+")");
}catch(Exception ex){
System.out.println("Problem in Import With the Departure Movement: "+colValues+" "+ex.toString());
//throw new Exception("Problem in Import With the Departure Movement: "+colValues+" "+ex.toString());
exceptionMessage +="Problem in Import With the Departure Movement Row No. "+(maxNo+i+1)+": "+colValues+" "+ex.toString()+"\n";
}
}
}
//stmt.executeBatch();
/*stmt.executeUpdate("Update Tc_Arrival_Log arr Set (Route_Cd,Arr_Gcd)=(Select Route_Cd,Gcd From Tc_Route_Mt where Route in (Select Route From Tc_Flightschedule_Mt where flight_no = arr.flight_no AND DEP_LOCATION = ARR.DEP_LOCATION and route is not null and rownum<=1) ) Where Route_Cd is null and Fmuid like 'E%'");
stmt.executeUpdate("Update Tc_Departure_Log dep Set (Route_Cd,Dep_Gcd)=(Select Route_Cd,Gcd From Tc_Route_Mt where Route in (Select Route From Tc_Flightschedule_Mt where flight_no = dep.flight_no AND DEST_LOCATION = DEP.DEST_LOCATION and route is not null and rownum<=1) ) Where Route_Cd is null and Fmuid like 'E%'");*/
/*change on 09/04/2008- picking routes and gcd based on Varible Schedules */
CallableStatement csArr = con.prepareCall("{call vfs_Arr_route_gcd_update(?)}");
csArr.setString(1,"A");
csArr.executeUpdate();
CallableStatement csDep = con.prepareCall("{call vfs_Dep_route_gcd_update(?)}");
csDep.setString(1,"D");
csDep.executeUpdate();
stmt.executeUpdate("Update Tc_Arrival_Log Set Bay_Allocated=Nvl(Bay_Allocated,get_Description('Bay_Cd','Ap_Bay_Mt','Rownum<=1')), Nature=get_description('Decode(location_type,''N'',''D'',''F'',''I'')','tc_location_mt','location_cd='''||dep_location||''''),Scheduled=Nvl(Nvl(Scheduled,get_description('Decode(Sch_Category,''P'',''S'',''C'',''C'',''N'')','Tc_FlightSchedule_Mt','Trim(Flight_No)='''||Trim(Flight_No)||''' and rownum<=1')),'N'),Operator_Cd=Nvl(Operator_Cd,get_description('Operator_Cd','Tc_FlightSchedule_Mt','Trim(Flight_No)='''||Trim(Flight_No)||''' and rownum<=1')) Where fmuid like 'E%'");
stmt.executeUpdate("Update Tc_Departure_Log Set Uncontrolled_Location=Get_Description('Decode(Uncontrolled_Flag,''C'',''N'',''Y'')','Tc_Location_Mt','Location_Cd='''||Dest_Location||''''), Use_AAI_Xray=Nvl(Use_AAI_XRay,Get_Description('Use_AAI_XRay','Tc_Operator_Dt','Operator_Cd='''||Nvl(Operator_Cd,get_description('Operator_Cd','Tc_FlightSchedule_Mt','Trim(Flight_No)='''||Trim(Flight_No)||''' and rownum<=1'))||'''')),Common_Counter=Nvl(Common_Counter,get_description('Common_Counter','Tc_Operator_Dt','Operator_Cd='''||Nvl(Operator_Cd,get_description('Operator_Cd','Tc_FlightSchedule_Mt','Trim(Flight_No)='''||Trim(Flight_No)||''' and rownum<=1'))||'''')), Pob=Nvl(Pob,Get_Description('SeatingCapacity','Tc_Aircraft_Mt','Reg_No='''||Dep_Reg_No||'''')), Bay_Allocated=Nvl(Bay_Allocated,get_Description('Bay_Cd','Ap_Bay_Mt','Rownum<=1')), Nature=get_description('Decode(location_type,''N'',''D'',''F'',''I'')','tc_location_mt','location_cd='''||Dest_Location||''''),Scheduled=Nvl(Nvl(Scheduled,get_description('Decode(Sch_Category,''P'',''S'',''C'',''C'',''N'')','Tc_FlightSchedule_Mt','Trim(Flight_No)='''||Trim(Flight_No)||''' and rownum<=1')),'N'),Operator_Cd=Nvl(Operator_Cd,get_description('Operator_Cd','Tc_FlightSchedule_Mt','Trim(Flight_No)='''||Trim(Flight_No)||''' and rownum<=1')) Where (Nature is null or scheduled is null or Operator_Cd is null or common_counter is null or use_aai_xray is null) and fmuid like 'E%'");
//CallableStatement cs = con.prepareCall("{call Export_Excel_FileData(?,?)}");
//1 -- Entered By. 2 -- current location.
//cs.setString(1,"00003");
//cs.setString(2,"VOBG");
//cs.execute();
if(!exceptionMessage.equals(""))
throw new Exception(exceptionMessage);
}
public void insertingRouteGCD(Connection con,String airportCd) throws Exception{
String arrQuery = "Select fmuid,DEP_LOCATION from tc_arrival_log where fmuid like 'E%' and ROUTE_CD is null";
String depQuery = "Select fmuid,DEST_LOCATION from tc_departure_log where fmuid like 'E%' and ROUTE_CD is null";
String routeQuery = "Select ROUTE_CD,nvl(GCD,0) GCD,upper(Entrypoint),upper(exitpoint) from TC_ROUTE_MT";
Statement stmt = con.createStatement();
Statement statement=con.createStatement();//to update the route and gcd for non sch movemnts
ResultSet rs = stmt.executeQuery(routeQuery);
HashMap hm = new HashMap();
while(rs.next()){
hm.put(rs.getString(3)+"-"+rs.getString(4),rs.getString(1)+"::"+rs.getString(2));
}
if(rs != null)
rs.close();
rs = stmt.executeQuery(arrQuery);
String updateQuery = "";
while(rs.next()){
System.out.println("--------A-----------"+rs.getString(1));
String route = airportCd+"-"+rs.getString(2);
if(hm.containsKey(route))
updateQuery = "Update tc_arrival_log set ROUTE_CD='"+getRoute((String)hm.get(route))+"',ARR_GCD='"+getGCD((String)hm.get(route))+"' where fmuid='"+rs.getString(1)+"'";
route = rs.getString(2)+"-"+airportCd;
if(hm.containsKey(route))
updateQuery = "Update tc_arrival_log set ROUTE_CD='"+getRoute((String)hm.get(route))+"',ARR_GCD='"+getGCD((String)hm.get(route))+"' where fmuid='"+rs.getString(1)+"'";
if( updateQuery!=null&&!updateQuery.equals(""))
statement.executeUpdate(updateQuery);
}
if(rs != null)
rs.close();
rs = stmt.executeQuery(depQuery);
while(rs.next()){
System.out.println("-----D--------------"+rs.getString(1));
String route = airportCd+"-"+rs.getString(2);
if(hm.containsKey(route))
updateQuery = "Update tc_departure_log set ROUTE_CD='"+getRoute((String)hm.get(route))+"',DEP_GCD='"+getGCD((String)hm.get(route))+"' where fmuid='"+rs.getString(1)+"'";
route = rs.getString(2)+"-"+airportCd;
if(hm.containsKey(route))
updateQuery = "Update tc_departure_log set ROUTE_CD='"+getRoute((String)hm.get(route))+"',DEP_GCD='"+getGCD((String)hm.get(route))+"' where fmuid='"+rs.getString(1)+"'";
if( updateQuery!=null&&!updateQuery.equals(""))
statement.executeUpdate(updateQuery);
}
if(rs != null)
rs.close();
if(stmt !=null)
stmt.close();
}
public String getRoute(String route){
return route.substring(0,route.indexOf("::"));
}
public String getGCD(String gcd){
return gcd.substring(gcd.indexOf("::")+2);
}
public void exportFile(Connection con,String airportCd,String user) throws Exception{
System.out.println("user:::::::::::::::"+user);
try{
CallableStatement cs = con.prepareCall("{call Export_Excel_FileData(?,?)}");
//1 -- Entered By. 2 -- current location.
cs.setString(1,user);
cs.setString(2,airportCd);
cs.execute();
if(cs!=null)
cs.close();
}catch(SQLException ex){
System.out.println("ex:::::::::::::::"+ex);
}
}
public void exportFileNew(String airportCd,String user) throws Exception{
new ExportExcelFileData(user,airportCd);
}
}
No comments:
Post a Comment