create or replace and compile java source named sms."AccidentReport2" as // // Modified 11/03/2002 Strip "'" from Vehicle Plate Number // Modified 11/30/2002 Replace "'" with "''" on OffsetDistance when loading into clob_report_case table // Added "'" around all Case data to handle null situations. // hazmatPlacard changed to hazmatHasPlacard // Substring on Reference Marker at Case level to get only first 12 Chars. // Substring on Investigator Name at Case level to get only first 50 Chars. // Substring on Registrant Name at Vehicle level to get only first 50 Chars. // Substring on Name at Individual level to get only first 50 Chars. // Modified 12/02/2004 Added Intersecting Street at Case Level // Fixed "IF" for vehicle Direction_Travel and PreAccdAction // Modified 12/09/2004 Added GRANTS to SMS Roles. // Modified 02/09/2005 Substring NCIC Organization on first 5, extra spaces was coming in the XML file // Modified 06/14/2005 Separated Java from SQL Package in the source. // Check for value of "Null" in imageLoad of Signature and Diagram // Added elements for Ticket Numbers, Violations and Tracs Case Num (Local Code 2) // Extraction of Deceased Date change from "Deceased_Date" to "Death_Date" // Modified 07/05/2005 Added extraction of EMST_CDE and Hospital_CDE on Individual // Modified 05/03/2010 Add clob handling since notes column was changed from varchar2(4000) to CLOB. // Modified 10/23/2012 Changed import oracle.jdbc.driver.* to import oracle.jdbc.* after 11G // DB upgrade to elliminate this error: // // accidentreport2:84: oracle.jdbc.driver.OracleResultSet is not public in oracle.jdbc.driver; // cannot be accessed from outside package Clob accidentreport2 = ((OracleResultSet)rset).getClob(2); // // Modified 09/09/2013 Altered the imageLoad method to use the sms_app.Base64 class. This Base64 class // decodes the clob and checks for a valid image. The previous imageLoad code // on decoded and returned junk images at times causing the Oracle Report to crash. // // Since Base64 is in sms_app and not in sms schema use the following to compile this class.. // Notice the create statement does not compile, but simply puts the source into the DB. // Notice the two alter java commands that acutally compile the class. // ----------------------------------------------------------------------------------------- // // set define off // // create or replace java source named sms."accidentreport2" as // ... accidentreport2 code here ... // } //Class accidentreport2 // ; // / // // alter java class sms."accidentreport2" resolver ((* SMS)(* SMS_APP) (*PUBLIC)) authid definer; // alter java class sms."accidentreport2" resolver ((* SMS)(* SMS_APP) (*PUBLIC)) compile; // select * from all_errors where name = 'accidentreport2' order by sequence asc; import java.net.URL; import java.sql.*; import java.io.*; import oracle.sql.*; //import oracle.jdbc.driver.*; import oracle.jdbc.*; import oracle.xml.parser.v2.*; // import javax.mail.internet.MimeUtility; // import java.net.*; import org.w3c.dom.*; import sun.misc.BASE64Decoder; //import org.xml.sax.*; import java.util.StringTokenizer; public class accidentreport2 { public static void create(String inDotCaseNum, NUMBER inDocXmlId, NUMBER inTempReportPk, NUMBER inMarrsReportPk, String[] outError) { // String ver; // System.out.println("at the beginning"); // ver = System.getProperty("java.version"); // System.out.println("The JDK version is " + ver); DOMParser theParser = new DOMParser(); try { // System.out.println("do connection"); Connection conn = new OracleDriver().defaultConnection(); // System.out.println("connection made"); conn.setAutoCommit(false); InputStream XMLStreamToParse = null; XMLDocument theXMLDoc = null; BLOB diagramBlob = null; BLOB signatureBlob = null; CaseRecord caseData = new CaseRecord(inTempReportPk.intValue()); // =========== Get the Data ================================ String sql = "select img_id," + " docxml_frm" + " from accident_image " + " where case_num = '" + inDotCaseNum + "'" + " and docxml_id = " + inDocXmlId.intValue(); Statement stmt = conn.createStatement(); ResultSet rset = stmt.executeQuery(sql); while (rset.next()) { Clob accidentreport2 = ((OracleResultSet)rset).getClob(2); // System.out.println(accidentreport2.length()); // System.out.println("DATA!!!"); // This will print out clob: // System.out.println(accidentreport2.getSubString(1,(int)accidentreport2.length())); // Create Parser and Parse the XML CLOB. XMLStreamToParse = accidentreport2.getAsciiStream(); theParser.parse(XMLStreamToParse); theXMLDoc = theParser.getDocument(); // this will print the Doc // theXMLDoc.print(System.out); // Extract Case Level Element Data from XML caseData.setLocalCode(getXMLString(theXMLDoc,"Local_Code","Value")); caseData.setAmended(getXMLString(theXMLDoc,"Amended_Report","Value")); caseData.setAccdDate(getXMLDate(theXMLDoc,"Accident_Date")); caseData.setAccdTime(getXMLString(theXMLDoc,"Accident_Time","Hour") + ":" + getXMLString(theXMLDoc,"Accident_Time","Minute")); caseData.setNumVehicles(getXMLString(theXMLDoc,"Number_Vehicles","Value")); caseData.setNumInjured(getXMLString(theXMLDoc,"Number_Injured","Value")); caseData.setNumKilled(getXMLString(theXMLDoc,"Number_Killed","Value")); caseData.setPoliceInvestigated(getXMLString(theXMLDoc,"Police_Investigated","Value")); caseData.setAccdReconstructed(getXMLString(theXMLDoc,"Accident_Reconstructed","Value")); caseData.setDriverLeftScene(getXMLString(theXMLDoc,"Driver_Left_Scene","Value")); caseData.setPolicePhotographed(getXMLString(theXMLDoc,"Police_Photographed","Value")); caseData.setCollisionType(getXMLString(theXMLDoc,"Collision_Manner_Type","Code")); caseData.setVehDamageOver1000(getXMLString(theXMLDoc,"Any_Vehicle_Damage_Over_1000","Value")); caseData.setReferenceMarker(getXMLString(theXMLDoc,"Reference_Marker","Value")); caseData.setLatitude(getXMLString(theXMLDoc,"Reported_Latitude","Value")); caseData.setLongitude(getXMLString(theXMLDoc,"Reported_Longitude","Value")); caseData.setAccdCounty(getXMLString(theXMLDoc,"Accident_County","Code")); caseData.setLocationType(getXMLString(theXMLDoc,"Location_Type","Code")); caseData.setMunicipality(getXMLString(theXMLDoc,"Municipality","Value")); caseData.setAccdStreet(getXMLString(theXMLDoc,"AccidentStreet","Value")); caseData.setIntersection(getXMLString(theXMLDoc,"Intersection","Value")); caseData.setIntersectingStreet(getXMLString(theXMLDoc,"Intersecting_Street","Value")); caseData.setOffsetDistance(getXMLString(theXMLDoc,"Offset_Distance","Value")); caseData.setDistanceType(getXMLString(theXMLDoc,"Distance_Type","Code")); caseData.setDirection(getXMLString(theXMLDoc,"Direction","Code")); caseData.setNearestIntersectPt(getXMLString(theXMLDoc,"Nearest_Intersection_Point","Value")); caseData.setPoliceRank(getXMLString(theXMLDoc,"Investigator","Police_Rank")); caseData.setInvestigatorName(getXMLString(theXMLDoc,"Investigator","First_Name") + " " + getXMLString(theXMLDoc,"Investigator","Middle_Name") + " " + getXMLString(theXMLDoc,"Investigator","Last_Name")); caseData.setBadgeNumber(getXMLString(theXMLDoc,"Investigator","Badge_Number")); caseData.setNCICOrganization(getXMLString(theXMLDoc,"NCIC_Organization","Value")); caseData.setPrecPostTroopZone(getXMLString(theXMLDoc,"Investigator","Precinct_Post_Troop_Zone")); caseData.setStationBeatSector(getXMLString(theXMLDoc,"Investigator","Station_Beat_Sector")); caseData.setReviewingOfficer(getXMLString(theXMLDoc,"Investigator","Reviewing_Officer")); caseData.setPoliceReviewDate(getXMLDate(theXMLDoc,"Police_Review_Date")); caseData.setPoliceReviewTime(getXMLString(theXMLDoc,"Police_Review_Time","Hour") + ":" + getXMLString(theXMLDoc,"Police_Review_Time","Minute")); caseData.setPedBikeLocation(getXMLString(theXMLDoc,"Pedestrian_Bicyclist_Location","Code")); caseData.setPedBikeAction(getXMLString(theXMLDoc,"Pedestrian_Bicyclist_Action","Code")); caseData.setTrafficControlType(getXMLString(theXMLDoc,"Traffic_Control_Type","Code")); caseData.setLightCondition(getXMLString(theXMLDoc,"Light_Condition","Code")); caseData.setRoadCharacteristics(getXMLString(theXMLDoc,"Road_Characteristics","Code")); caseData.setRoadSurfaceCondition(getXMLString(theXMLDoc,"Roadway_Surface_Condition","Code")); caseData.setWeatherCondition(getXMLString(theXMLDoc,"Weather_Conditions","Code")); caseData.setOnRoad(getXMLString(theXMLDoc,"OnRoad","Value")); caseData.setFirstEvent(getXMLString(theXMLDoc,"First_Event","Code")); caseData.setWorkRelated(getXMLString(theXMLDoc,"Work_Related","Value")); caseData.setRoadSurface(getXMLString(theXMLDoc,"Roadway_Surface","Code")); caseData.setNonHighway(getXMLString(theXMLDoc,"Non_Highway","Value")); caseData.setTowing(getXMLString(theXMLDoc,"Towing","Value")); caseData.setNumTravelLanes(getXMLString(theXMLDoc,"Number_Travel_Lanes","Value")); caseData.setPostedSpeedLimit(getXMLString(theXMLDoc,"Posted_Speed_Limit","Value")); caseData.setTRACSCaseNum(getXMLString(theXMLDoc,"TRACS_Case_Number","Value")); // caseData.setFormType(getXMLString(theXMLDoc,"Form_Type","Name")); // caseData.setFormVersion(getXMLString(theXMLDoc,"Form_Type","Version")); // Concatenate seperate Notes into one String // Text Node child of Element Node "Notes" String notes = new String(); NodeList notesNodes = theXMLDoc.getElementsByTagName("Notes"); for (int n=0; n < notesNodes.getLength(); n++) { Element noteElement = (Element)notesNodes.item(n); if (n==0) notes = noteElement.getFirstChild().getNodeValue(); else notes = notes + " " + noteElement.getFirstChild().getNodeValue(); } // insert case level data into Oracle Table, empty for BLOBS stmt = conn.createStatement(); stmt.executeUpdate( "insert into sms.clob_report_case " + "(MARRS_REPORT_PK, CLOB_REPORT_PK, DOT_CASE_NUM, LOCAL_CODE, AMENDED_REPORT, ACCIDENT_DATE, ACCIDENT_TIME," + "NUMBER_VEHICLES, NUMBER_INJURED, NUMBER_KILLED, POLICE_INVESTIGATED, ACCIDENT_RECONSTRUCTED," + "DRIVER_LEFT_SCENE, POLICE_PHOTOGRAPHED, COLLISION_MANNER_TYPE, DIAGRAM, ANY_VEHICLE_DAMAGE_OVER_1000," + "REFERENCE_MARKER, REPORTED_LATITUDE, REPORTED_LONGITUDE, ACCIDENT_COUNTY, LOCATION_TYPE," + "MUNICIPALITY, ACCIDENT_STREET, INTERSECTION, INTERSECTING_STREET, OFFSET_DISTANCE, DISTANCE_TYPE," + "DIRECTION, NEAREST_INTERSECTION_POINT, INVESTIGATOR_SIGNATURE, POLICE_RANK," + "INVESTIGATOR_NAME, BADGE_NUMBER, NCIC_ORGANIZATION, PRECINCT_POST_TROOP_ZONE, STATION_BEAT_SECTOR," + "REVIEWING_OFFICER, POLICE_REVIEW_DATE, POLICE_REVIEW_TIME, PED_BICYCLIST_LOCATION_1, PED_BICYCLIST_ACTION_2," + "TRAFFIC_CONTROL_TYPE_3, LIGHT_CONDITION_4, ROAD_CHARACTERISTICS_5, ROADWAY_SURFACE_CONDITION_6, WEATHER_CONDITIONS_7," + "ONROAD_27, FIRST_EVENT_28, WORK_RELATED, ROADWAY_SURFACE, NON_HIGHWAY," + "TOWING, NUMBER_TRAVEL_LANES, POSTED_SPEED_LIMIT, FORM_TYPE, FORM_VERSION, TRACS_CASE_NUM) " + "values (" + inMarrsReportPk.intValue() + "," + // MARRS_REPORT_PK inTempReportPk.intValue() + "," + // CLOB_REPORT_PK "'" + inDotCaseNum + "'," + // DOT_CASE_NUM "'" + caseData.getLocalCode() + "'," + // LOCAL_CODE "'" + caseData.getAmended() + "'," + // AMENDED_REPORT "to_date('" + caseData.getAccdDate() + "','MM/DD/YYYY')," + // ACCIDENT_DATE "'" + caseData.getAccdTime() + "'," + // ACCIDENT_TIME "'" + caseData.getNumVehicles() + "'," + // NUMBER_VEHICLES "'" + caseData.getNumInjured() + "'," + // NUMBER_INJURED "'" + caseData.getNumKilled() + "'," + // NUMBER_KILLED "'" + caseData.getPoliceInvestigated() + "'," + // POLICE_INVESTIGATED "'" + caseData.getAccdReconstructed() + "'," + // ACCIDENT_RECONSTRUCTED "'" + caseData.getDriverLeftScene() + "'," + // DRIVER_LEFT_SCENE "'" + caseData.getPolicePhotographed() + "'," + // POLICE_PHOTOGRAPHED "'" + caseData.getCollisionType() + "'," + // COLLISION_MANNER_TYPE "empty_blob()," + // DIAGRAM "'" + caseData.getVehDamageOver1000() + "'," + // ANY_VEHICLE_DAMAGE_OVER_1000 "substr('" + caseData.getReferenceMarker() + "',1,12)," + // REFERENCE_MARKER "'" + caseData.getLatitude() + "'," + // REPORTED_LATITUDE "'" + caseData.getLongitude() + "'," + // REPORTED_LONGITUDE "'" + caseData.getAccdCounty() + "'," + // ACCIDENT_COUNTY "'" + caseData.getLocationType() + "'," + // LOCATION_TYPE "'" + replace(caseData.getMunicipality() ,"'","''") + "'," + // MUNICIPALITY "'" + replace(caseData.getAccdStreet() ,"'","''") + "'," + // ACCIDENT_STREET "'" + replace(caseData.getIntersection() ,"'","''") + "'," + // INTERSECTION "'" + replace(caseData.getIntersectingStreet(),"'","''") + "'," + // INTERSECTION "'" + replace(caseData.getOffsetDistance() ,"'","''") + "'," + // OFFSET_DISTANCE "'" + caseData.getDistanceType() + "'," + // DISTANCE_TYPE "'" + caseData.getDirection() + "'," + // DIRECTION "'" + replace(caseData.getNearestIntersectPt(),"'","''") + "'," + // NEAREST_INTERSECTION_POINT "empty_blob()," + // INVESTIGATOR_SIGNATURE "'" + caseData.getPoliceRank() + "'," + // POLICE_RANK "substr('" + replace(caseData.getInvestigatorName(),"'","''") + "',1,50)," + // INVESTIGATOR_NAME "'" + caseData.getBadgeNumber() + "'," + // BADGE_NUMBER "substr(ltrim('" + caseData.getNCICOrganization() + "'),1,5)," + // NCIC_ORGANIZATION "'" + caseData.getPrecPostTroopZone() + "'," + // PRECINCT_POST_TROOP_ZONE "'" + caseData.getStationBeatSector() + "'," + // STATION_BEAT_SECTOR "'" + replace(caseData.getReviewingOfficer(),"'","''") + "'," + // REVIEWING_OFFICER "to_date('" + caseData.getPoliceReviewDate() + "','MM/DD/YYYY')," + // POLICE_REVIEW_DATE "'" + caseData.getPoliceReviewTime() + "'," + // POLICE_REVIEW_TIME "'" + caseData.getPedBikeLocation() + "'," + // PED_BICYCLIST_LOCATION_1 "'" + caseData.getPedBikeAction() + "'," + // PED_BICYCLIST_ACTION_2 "'" + caseData.getTrafficControlType() + "'," + // TRAFFIC_CONTROL_TYPE_3 "'" + caseData.getLightCondition() + "'," + // LIGHT_CONDITION_4 "'" + caseData.getRoadCharacteristics() + "'," + // ROAD_CHARACTERISTICS_5 "'" + caseData.getRoadSurfaceCondition() + "'," + // ROADWAY_SURFACE_CONDITION_6 "'" + caseData.getWeatherCondition() + "'," + // WEATHER_CONDITIONS_7 "'" + caseData.getOnRoad() + "'," + // ONROAD_27 "'" + caseData.getFirstEvent() + "'," + // FIRST_EVENT_28 "'" + caseData.getWorkRelated() + "'," + // WORK_RELATED "'" + caseData.getRoadSurface() + "'," + // ROADWAY_SURFACE "'" + caseData.getNonHighway() + "'," + // NON_HIGHWAY "'" + caseData.getTowing() + "'," + // TOWING "'" + caseData.getNumTravelLanes() + "'," + // NUMBER_TRAVEL_LANES "'" + caseData.getPostedSpeedLimit() + "'," + // POSTED_SPEED_LIMIT "''," + // FORM_TYPE "''," + // FORM_VERSION "'" + caseData.getTRACSCaseNum() + "')" // TRACS_CASE_NUM ); if (notes != null) { String sqlText = null; Statement stmt1 = null; ResultSet rset1 = null; CLOB notes_clob = null; long pos = 1; stmt1 = conn.createStatement(); sqlText = "SELECT notes " + "FROM sms.clob_report_case " + "WHERE clob_report_pk = " + inTempReportPk.intValue() + " FOR UPDATE"; rset1 = stmt1.executeQuery(sqlText); rset1.next(); notes_clob = ((OracleResultSet) rset1).getCLOB(1); StringTokenizer tokenizer = new StringTokenizer(notes); int count = 1; int tokencnt = tokenizer.countTokens(); while (tokenizer.hasMoreTokens()) { String tempStr = null; if(count < tokencnt) tempStr = tokenizer.nextToken() + " "; else tempStr = tokenizer.nextToken(); notes_clob.putString(pos, tempStr); pos = pos + tempStr.length(); count = count + 1; } stmt1.execute("commit"); rset1.close(); stmt1.close(); } // System.out.println("Case inserted complete"); // Get Blob columns from database // use as OutputStreams to write to from XML data ResultSet rsetBlobs = stmt.executeQuery( "select diagram, " + " investigator_signature" + " from sms.clob_report_case " + " where clob_report_pk = " + inTempReportPk.intValue() + " for update"); while(rsetBlobs.next()) { diagramBlob = ((OracleResultSet)rsetBlobs).getBLOB(1); signatureBlob = ((OracleResultSet)rsetBlobs).getBLOB(2); // ImageLoad will take Blob column from data base and write // to it from the XML from the document Element specified. imageLoad(theXMLDoc, diagramBlob, "Diagram", conn); imageLoad(theXMLDoc, signatureBlob, "Investigator_Signature", conn); } stmt.execute("commit"); // *************** Case Data insert complete *************** // // ******* Begin Vehicle Inserts ******* // // Get Node List of vehicles NodeList nodeListVeh = theXMLDoc.getElementsByTagName("Vehicle"); // System.out.println("Vehicle Count: " + nodeListVeh.getLength()); int vehicleCount = nodeListVeh.getLength(); // Loop through the vehicles for (int n=0; n < nodeListVeh.getLength(); n++) { Node vehNode = nodeListVeh.item(n); NodeList vehChildren = vehNode.getChildNodes(); VehicleRecord vehRecord = new VehicleRecord(inTempReportPk.intValue()); for (int vc=0; vc < vehChildren.getLength(); vc++) { Node vcNode = vehChildren.item(vc); if (vcNode.getNodeType() == 1) { Element vehChildElement = (Element)vcNode; populateVehicle(vehRecord,vehChildElement); } } // for Vehicle Children insertVehicle(conn, vehRecord); } // for each Vehicle Loop // ******* Begin Individual Inserts ******* // // Get Node List of Individuals NodeList nodeListInd = theXMLDoc.getElementsByTagName("Individual"); // System.out.println("Individual Count: " + nodeListInd.getLength()); int individualCount = nodeListInd.getLength(); // Loop through the individuals for (int i=0; i < nodeListInd.getLength(); i++) { Node indNode = nodeListInd.item(i); NodeList indChildren = indNode.getChildNodes(); IndividualRecord indRecord = new IndividualRecord(inTempReportPk.intValue()); for (int ic=0; ic < indChildren.getLength(); ic++) { Node icNode = indChildren.item(ic); if (icNode.getNodeType() == 1) { Element indChildElement = (Element)icNode; populateIndividual(indRecord,indChildElement); } } // for Individual Children insertIndividual(conn, indRecord); } // for each Individual Loop } //end while from select XML CLob from accident_image // System.out.println("closing connection"); conn.close(); } catch(Exception e) { e.printStackTrace(); outError[0] = "Error " + e; } } // ******************************************************************* // ***** getXMLString ***** // ******************************************************************* public static String getXMLString(XMLDocument inXMLDoc, String inElementName, String inAttrName) { NodeList theNode = inXMLDoc.getElementsByTagName(inElementName); if (theNode.getLength() > 0) { Element elemNode = (Element)theNode.item(0); return(elemNode.getAttribute(inAttrName)); } else { return(""); } } // ******************************************************************* // ***** getXMLDate ***** // ******************************************************************* public static String getXMLDate(XMLDocument inXMLDoc, String inElementName) { NodeList theNode = inXMLDoc.getElementsByTagName(inElementName); Element elemNode = (Element)theNode.item(0); return(elemNode.getAttribute("Month") + "/" + elemNode.getAttribute("Day") + "/" + elemNode.getAttribute("Year")); } // ******************************************************************* // ***** imageLoad ***** // ******************************************************************* public static void imageLoad(XMLDocument inXMLDoc, Blob databaseBlob, String inImageName, Connection conn) { try { NodeList nd = inXMLDoc.getElementsByTagName(inImageName); Node theNode = nd.item(0).getFirstChild(); // first child is Text Node of the Image String tempStr = theNode.getNodeValue(); oracle.sql.CLOB newClob = oracle.sql.CLOB.createTemporary(conn, false, oracle.sql.CLOB.DURATION_CALL); newClob.setString(1, tempStr); //System.out.println("String: " + inImageName + ":" + tempStr); //System.out.println("clob : " + inImageName + ":" + newClob.getSubString(1, (int) newClob.length())); //System.out.println("databaseBlob.length(): " + databaseBlob.length()); Base64 myImage = new Base64(); BLOB myImageBlob = myImage.decode(newClob); if (myImageBlob != null) { OutputStream out = databaseBlob.setBinaryStream(0L); InputStream in = myImageBlob.getBinaryStream(); byte[] buf = new byte[ (int) myImageBlob.length() + 1]; in.read(buf, 1, (int) buf.length); out.write(buf, 1, (int) buf.length); in.close(); out.flush(); out.close(); } } catch (Exception e) { e.printStackTrace(); } /* BASE64Decoder base64 = new BASE64Decoder(); OutputStream outstrm = null; NodeList nd = inXMLDoc.getElementsByTagName(inImageName); try // Output streams { Node theNode = nd.item(0).getFirstChild(); // first child is Text Node of the Image String tempStr = theNode.getNodeValue(); if (!tempStr.equals("Null")) { byte theBytes[] = tempStr.getBytes(); ByteArrayInputStream xmlDiagram = new ByteArrayInputStream(theBytes); // outstrm is the empty BLOB within the database outstrm = databaseBlob.getBinaryOutputStream(); try // Input stream to output Stream { base64.decodeBuffer(xmlDiagram, outstrm); } catch (Exception e) { e.printStackTrace(); // System.out.println("converstion error: " + e); } outstrm.close(); } } // if the Node is null, just catch the exception and ignore it catch (NullPointerException npe) { } catch (Exception e) // Output Streams { e.printStackTrace(); // System.out.println("converstion error: " + e); } */ } // Public imageLoad // ******************************************************************* // ***** populateVehicle ***** // ******************************************************************* public static void populateVehicle(VehicleRecord inVehRecord, Element inElement) { if (inElement.getLocalName() == "Vehicle_Id") inVehRecord.setVehicleID(inElement.getAttribute("Value")); else if (inElement.getLocalName() == "Passenger_Count") inVehRecord.setPassengerCount(inElement.getAttribute("Value")); else if (inElement.getLocalName() == "Public_Property_Damage") inVehRecord.setPubPropertyDamage(inElement.getAttribute("Value")); else if (inElement.getLocalName() == "Registrant_First_Name") inVehRecord.setRegistrantFirstName(inElement.getAttribute("Value")); else if (inElement.getLocalName() == "Registrant_Middle_Name") inVehRecord.setRegistrantMiddleName(inElement.getAttribute("Value")); else if (inElement.getLocalName() == "Registrant_Last_Name") inVehRecord.setRegistrantLastName(inElement.getAttribute("Value")); else if (inElement.getLocalName() == "Registrant_Sex") inVehRecord.setRegistrantSex(inElement.getAttribute("Value")); else if (inElement.getLocalName() == "Registrant_Birth_Date") inVehRecord.setRegistrantBirthDate(inElement.getAttribute("Month") + "/" + inElement.getAttribute("Day") + "/" + inElement.getAttribute("Year")); else if (inElement.getLocalName() == "Hazmat") { inVehRecord.setHazmatCode(inElement.getAttribute("Code")); inVehRecord.setHazmatReleased(inElement.getAttribute("Released")); inVehRecord.setHazmatHasPlacard(inElement.getAttribute("Has_Placard")); } else if (inElement.getLocalName() == "Registrant_Street_Address_1") inVehRecord.setRegistrantAddress(inElement.getAttribute("Value")); else if (inElement.getLocalName() == "Registrant_City") inVehRecord.setRegistrantCity(inElement.getAttribute("Value")); else if (inElement.getLocalName() == "Registrant_State") inVehRecord.setRegistrantState(inElement.getAttribute("Code")); else if (inElement.getLocalName() == "Registrant_Zip_Code") inVehRecord.setRegistrantZipCode(inElement.getAttribute("Value")); else if (inElement.getLocalName() == "Plate_Number") inVehRecord.setPlateNumber(inElement.getAttribute("Value")); else if (inElement.getLocalName() == "Plate_State") inVehRecord.setPlateState(inElement.getAttribute("Code")); else if (inElement.getLocalName() == "Year") inVehRecord.setYear(inElement.getAttribute("Value")); else if (inElement.getLocalName() == "Insurance_Carrier_Code") inVehRecord.setInsCarrierCode(inElement.getAttribute("Code")); else if (inElement.getLocalName() == "Vehicle_Make") inVehRecord.setVehMake(inElement.getAttribute("Value")); else if (inElement.getLocalName() == "Body_Type") inVehRecord.setBodyType(inElement.getAttribute("Value")); else if (inElement.getLocalName() == "Vehicle_Wide") inVehRecord.setVehWide(inElement.getAttribute("Value")); else if (inElement.getLocalName() == "Vehicle_Long") inVehRecord.setVehLong(inElement.getAttribute("Value")); else if (inElement.getLocalName() == "Vehicle_Overweight_Permit") inVehRecord.setVehOverweight(inElement.getAttribute("Value")); else if (inElement.getLocalName() == "Vehicle_Over_Dimension_Permit") inVehRecord.setVehOverdimension(inElement.getAttribute("Value")); else if (inElement.getLocalName() == "Damage") { String damageCat = inElement.getAttribute("Category"); if (damageCat.equals("1")) inVehRecord.setDamage1(inElement.getAttribute("Type")); else if (damageCat.equals("2")) inVehRecord.setDamage2(inElement.getAttribute("Type")); else if (damageCat.equals("-") && inVehRecord.getDamage3().equals("")) inVehRecord.setDamage3(inElement.getAttribute("Type")); else if (damageCat.equals("-") && inVehRecord.getDamage4().equals("")) inVehRecord.setDamage4(inElement.getAttribute("Type")); else if (damageCat.equals("-") && inVehRecord.getDamage5().equals("")) inVehRecord.setDamage5(inElement.getAttribute("Type")); } else if (inElement.getLocalName() == "Towed_By") inVehRecord.setTowedBy(inElement.getAttribute("Value")); else if (inElement.getLocalName() == "Towed_To") inVehRecord.setTowedTo(inElement.getAttribute("Value")); else if (inElement.getLocalName() == "Vehicle_Contributing_Factor") { if (inVehRecord.getContributingFactor1() == "") inVehRecord.setContributingFactor1(inElement.getAttribute("Code")); else if (inVehRecord.getContributingFactor2() == "") inVehRecord.setContributingFactor2(inElement.getAttribute("Code")); } else if (inElement.getLocalName() == "Direction_Travel") inVehRecord.setDirectionTravel(inElement.getAttribute("Code")); else if (inElement.getLocalName() == "Pre_Accident_Action") inVehRecord.setPreAccdAction(inElement.getAttribute("Code")); else if (inElement.getLocalName() == "Second_Event") inVehRecord.setSecondEvent(inElement.getAttribute("Code")); else if (inElement.getLocalName() == "Private_Property_Damage") inVehRecord.setPrivatePropDamage(inElement.getAttribute("Value")); else if (inElement.getLocalName() == "Vehicle_Towed") inVehRecord.setVehTowed(inElement.getAttribute("Value")); else if (inElement.getLocalName() == "Vehicle_Placard") inVehRecord.setVehPlacard(inElement.getAttribute("Value")); else if (inElement.getLocalName() == "VIN") inVehRecord.setVIN(inElement.getAttribute("Value")); else if (inElement.getLocalName() == "Unknown_Speed_Indicator") inVehRecord.setUnknownSpeedInd(inElement.getAttribute("Value")); else if (inElement.getLocalName() == "Carrier_Type") inVehRecord.setCarrierType(inElement.getAttribute("Code")); else if (inElement.getLocalName() == "Bus_Type") inVehRecord.setBusType(inElement.getAttribute("Code")); } // End populateVehicle // ******************************************************************* // ***** insertVehicle ***** // ******************************************************************* public static void insertVehicle(Connection inConn, VehicleRecord inVehRecord) { // System.out.println("inserting vehicle " + inVehRecord.getVehicleID()); try { Statement vStmt = inConn.createStatement(); vStmt.executeUpdate("insert into sms.clob_report_vehicle " + "(CLOB_REPORT_PK, VEHICLE_ID, PASSENGER_COUNT, PUBLIC_PROPERTY_DAMAGE, REGISTRANT_NAME, " + "REGISTRANT_SEX, REGISTRANT_BIRTH_DATE, HAZMAT_CODE, HAZMAT_RELEASED, HAZMAT_HAS_PLACARD, " + "REGISTRANT_ADDRESS, REGISTRANT_CITY, REGISTRANT_STATE, REGISTRANT_ZIP_CODE, PLATE_NUMBER, " + "PLATE_STATE, YEAR, VEHICLE_MAKE, BODY_TYPE, INS_CARRIER_CODE, " + "VEH_WIDE, VEH_LONG, VEH_OVERWEIGHT_PERMIT, VEH_OVER_DIM_PERMIT, DAMAGE_1, " + "DAMAGE_2, DAMAGE_3, DAMAGE_4, DAMAGE_5, TOWED_BY, " + "TOWED_TO, CONTRIBUTING_FACTOR_1, CONTRIBUTING_FACTOR_2, DIRECTION_TRAVEL, PRE_ACCIDENT_ACTION, " + "SECOND_EVENT, PRIVATE_PROP_DAMAGE, VEHICLE_TOWED, VEHICLE_PLACARD, VIN, " + "UNKNOWN_SPEED_IND, CARRIERT_ID, BUST_ID) " + "values (" + inVehRecord.getCasePk() + "," + // CLOB_REPORT_PK "'" + inVehRecord.getVehicleID() + "'," + // VEHICLE_ID "'" + inVehRecord.getPassengerCount() + "'," + // PASSENGER_COUNT "'" + inVehRecord.getPubPropertyDamage() + "'," + // PUBLIC_PROPERTY_DAMAGE "substr(ltrim('" + replace(inVehRecord.getRegistrantName(),"'","''") + "'),1,50)," + // REGISTRANT_NAME "'" + inVehRecord.getRegistrantSex() + "'," + // REGISTRANT_SEX "to_date('" + inVehRecord.getRegistrantBirthDate() + "','MM/DD/YYYY')," + // REGISTRANT_BIRTH_DATE "'" + inVehRecord.getHazmatCode() + "'," + // HAZMAT_CODE "'" + inVehRecord.getHazmatReleased() + "'," + // HAZMAT_RELEASED "'" + inVehRecord.getHazmatHasPlacard() + "'," + // HAZMAT_HAS_PLACARD "'" + replace(inVehRecord.getRegistrantAddres(),"'","''") + "'," + // REGISTRANT_ADDRESS "'" + replace(inVehRecord.getRegistrantCity() ,"'","''") + "'," + // REGISTRANT_CITY "'" + inVehRecord.getRegistrantState() + "'," + // REGISTRANT_STATE "'" + inVehRecord.getRegistrantZipCode() + "'," + // REGISTRANT_ZIP_CODE "'" + replace(inVehRecord.getPlateNumber(),"'","") + "'," + // PLATE_NUMBER "'" + inVehRecord.getPlateState() + "'," + // PLATE_STATE "'" + inVehRecord.getYear() + "'," + // YEAR "'" + inVehRecord.getVehMake() + "'," + // VEHICLE_MAKE "'" + inVehRecord.getBodyType() + "'," + // BODY_TYPE "'" + inVehRecord.getInsCarrierCode() + "'," + // INS_CARRIER_CODE "'" + inVehRecord.getVehWide() + "'," + // VEH_WIDE "'" + inVehRecord.getVehLong() + "'," + // VEH_LONG "'" + inVehRecord.getVehOverweight() + "'," + // VEH_OVERWEIGHT_PERMIT "'" + inVehRecord.getVehOverdimension() + "'," + // VEH_OVER_DIM_PERMIT "'" + inVehRecord.getDamage1() + "'," + // DAMAGE_1 "'" + inVehRecord.getDamage2() + "'," + // DAMAGE_2 "'" + inVehRecord.getDamage3() + "'," + // DAMAGE_3 "'" + inVehRecord.getDamage4() + "'," + // DAMAGE_4 "'" + inVehRecord.getDamage5() + "'," + // DAMAGE_5 "'" + replace(inVehRecord.getTowedBy(),"'","''") + "'," + // TOWED_BY "'" + replace(inVehRecord.getTowedTo(),"'","''") + "'," + // TOWED_TO "'" + inVehRecord.getContributingFactor1() + "'," + // CONTRIBUTING_FACTOR_1 "'" + inVehRecord.getContributingFactor2() + "'," + // CONTRIBUTING_FACTOR_2 "'" + inVehRecord.getDirectionTravel() + "'," + // DIRECTION_TRAVEL "'" + inVehRecord.getPreAccdAction() + "'," + // PRE_ACCIDENT_ACTION "'" + inVehRecord.getSecondEvent() + "'," + // SECOND_EVENT "'" + inVehRecord.getPrivatePropDamage() + "'," + // PRIVATE_PROP_DAMAGE "'" + inVehRecord.getVehTowed() + "'," + // VEHICLE_TOWED "'" + inVehRecord.getVehPlacard() + "'," + // VEHICLE_PLACARD "'" + inVehRecord.getVIN() + "'," + // VIN "'" + inVehRecord.getUnknownSpeedInd() + "'," + // UNKNOWN_SPEED_IND "'" + inVehRecord.getCarrierType() + "'," + // CARRIERT_ID "'" + inVehRecord.getBusType() + "')"); // BUST_ID vStmt.execute("commit"); vStmt.close(); } catch (Exception e) { e.printStackTrace(); // System.out.println("vehicle error: " + e) } } // Insert Vehicle Record // ******************************************************************* // ***** populateIndividual ***** // ******************************************************************* public static void populateIndividual(IndividualRecord inIndRecord, Element inElement) { if (inElement.getLocalName() == "Individual_Vehicle_Id") inIndRecord.setVehicleID(inElement.getAttribute("Value")); else if (inElement.getLocalName() == "Emotional_Status") inIndRecord.setEmotionalStatus(inElement.getAttribute("Code")); else if (inElement.getLocalName() == "Seat_Position") inIndRecord.setSeatPostion(inElement.getAttribute("Code")); else if (inElement.getLocalName() == "Ejected") inIndRecord.setEjected(inElement.getAttribute("Code")); else if (inElement.getLocalName() == "Left_Scene") inIndRecord.setLeftScene(inElement.getAttribute("Value")); else if (inElement.getLocalName() == "First_Name") inIndRecord.setFirstName(inElement.getAttribute("Value")); else if (inElement.getLocalName() == "Middle_Name") inIndRecord.setMiddleName(inElement.getAttribute("Value")); else if (inElement.getLocalName() == "Last_Name") inIndRecord.setLastName(inElement.getAttribute("Value")); else if (inElement.getLocalName() == "Street_Address_1") inIndRecord.setStreetAddress(inElement.getAttribute("Value")); else if (inElement.getLocalName() == "City") inIndRecord.setCity(inElement.getAttribute("Value")); else if (inElement.getLocalName() == "State") inIndRecord.setState(inElement.getAttribute("Code")); else if (inElement.getLocalName() == "Zip_Code") inIndRecord.setZipCode(inElement.getAttribute("Value")); else if (inElement.getLocalName() == "License_Number") inIndRecord.setLicenseNumber(inElement.getAttribute("Value")); else if (inElement.getLocalName() == "License_State") inIndRecord.setLicenseState(inElement.getAttribute("Code")); else if (inElement.getLocalName() == "EMST_CDE") inIndRecord.setEMSTCde(inElement.getAttribute("Value")); else if (inElement.getLocalName() == "Hospital_CDE") inIndRecord.setHospitalCde(inElement.getAttribute("Value")); else if (inElement.getLocalName() == "Birth_Date") inIndRecord.setBirthDate(inElement.getAttribute("Month") + "/" + inElement.getAttribute("Day") + "/" + inElement.getAttribute("Year")); else if (inElement.getLocalName() == "Sex") inIndRecord.setSex(inElement.getAttribute("Value")); else if (inElement.getLocalName() == "Age") inIndRecord.setAge(inElement.getAttribute("Value")); else if (inElement.getLocalName() == "Unlicensed_Driver") inIndRecord.setUnlicensedDriver(inElement.getAttribute("Value")); else if (inElement.getLocalName() == "Deceased") inIndRecord.setDeceasedInd(inElement.getAttribute("Value")); else if (inElement.getLocalName() == "Death_Date") inIndRecord.setDeceasedDate(inElement.getAttribute("Month") + "/" + inElement.getAttribute("Day") + "/" + inElement.getAttribute("Year")); else if (inElement.getLocalName() == "Injury") { inIndRecord.setInjLocation(inElement.getAttribute("Location")); inIndRecord.setInjType(inElement.getAttribute("Type")); } else if (inElement.getLocalName() == "Ticket") { if (inIndRecord.getTicketNumbers() == "") inIndRecord.setTicketNumbers(inElement.getAttribute("Number")); else inIndRecord.setTicketNumbers(inIndRecord.getTicketNumbers() + ", " + inElement.getAttribute("Number")); if (inIndRecord.getTicketViolations() == "") inIndRecord.setTicketViolations(inElement.getAttribute("Violation")); else inIndRecord.setTicketViolations(inIndRecord.getTicketViolations() + ", " + inElement.getAttribute("Violation")); } else if (inElement.getLocalName() == "Safety_Equipment") inIndRecord.setSafetyEquipment(inElement.getAttribute("Type")); } // End populateIndividual // ******************************************************************* // ***** insertIndividual ***** // ******************************************************************* public static void insertIndividual(Connection inConn, IndividualRecord inIndRecord) { // System.out.println("Individual " + inIndRecord.getName()); try { Statement iStmt = inConn.createStatement(); // Update vehicle record with the Driver Information if Seat Position = '1' if (inIndRecord.getSeatPosition().equals("1")) { iStmt.executeUpdate("update sms.clob_report_vehicle set " + "license_number = '" + inIndRecord.getLicenseNumber() + "', " + "license_state = '" + inIndRecord.getLicenseState() + "', " + "driver_name = '" + replace(inIndRecord.getName(),"'","''") + "', " + "driver_address = '" + replace(inIndRecord.getStreetAddress(),"'","''") + "', " + "driver_city = '" + replace(inIndRecord.getCity(),"'","''") + "', " + "driver_state = '" + inIndRecord.getState() + "', " + "driver_zip_code = '" + inIndRecord.getZipCode() + "', " + "driver_birth_date = to_date('" + inIndRecord.getBirthDate()+ "','MM/DD/YYYY')," + "driver_sex = '" + inIndRecord.getSex() + "', " + "unlicensed_driver = '" + inIndRecord.getUnlicensedDriver() + "', " + "left_scene = '" + inIndRecord.getLeftScene() + "', " + "ticket_numbers = substr('" + inIndRecord.getTicketNumbers() + "',1,60), " + "violations = substr('" + inIndRecord.getTicketViolations() + "',1,50)" + " where clob_report_pk = " + inIndRecord.getCasePk() + " and vehicle_id = '" + inIndRecord.getVehicleID() + "'"); } iStmt.executeUpdate("insert into sms.clob_report_individual " + "(CLOB_REPORT_PK, VEHICLE_ID, SEAT_POSITION, SAFETY_EQUIPMENT, EJECTED, " + " AGE, SEX, INJURY_LOCATION, INJURY_TYPE, EMOTIONAL_STATUS, " + " EMST_CDE, HOSPITAL_CDE, NAME, DECEASED_IND, DECEASED_DATE) " + "values (" + inIndRecord.getCasePk() + "," + // CLOB_REPORT_PK "'" + inIndRecord.getVehicleID() + "'," + // VEHICLE_ID "'" + inIndRecord.getSeatPosition() + "'," + // SEAT_POSITION "'" + inIndRecord.getSafetyEquipment() + "'," + // SAFETY_EQUIPMENT "'" + inIndRecord.getEjected() + "'," + // EJECTED "'" + inIndRecord.getAge() + "'," + // AGE "'" + inIndRecord.getSex() + "'," + // SEX "'" + inIndRecord.getInjLocation() + "'," + // INJURY_LOCATION "'" + inIndRecord.getInjType() + "'," + // INJURY_TYPE "'" + inIndRecord.getEmotionalStatus() + "'," + // EMOTIONAL_STATUS "substr('" + inIndRecord.getEMSTCde() + "',1,8)," + // EMST_CDE "substr('" + inIndRecord.getHospitalCde() + "',1,8)," + // HOSPITAL_CDE "substr('" + replace(inIndRecord.getName(),"'","''") + "',1,50)," + // NAME "'" + inIndRecord.getDeceasedInd() + "'," + // DECEASED_IND "to_date('" + inIndRecord.getDeceasedDate()+ "','MM/DD/YYYY'))"); //DECEASED_DATE iStmt.execute("commit"); iStmt.close(); } catch (Exception e) { e.printStackTrace(); // System.out.println("vehicle error: " + e) } } // End insertIndividual // ******************************************************************* // ***** replace ***** // ******************************************************************* static String replace(String str, String pattern, String replace) { int s = 0; int e = 0; StringBuffer result = new StringBuffer(); while ((e = str.indexOf(pattern, s)) >= 0) { result.append(str.substring(s, e)); result.append(replace); s = e+pattern.length(); } result.append(str.substring(s)); return result.toString(); } // end Replace } //Class accidentreport2 ; /