I know this is long, but there is a lot of comments. I am the only one at my company that knows Java so I wanted to make sure it could be followed easily. (I know we are supposed to do this anyway). I also removed repetitive lines, and any company specific references.
import lotus.domino.*;
import java.sql.*;
import java.util.Date;
public class JavaAgent extends AgentBase {
public void NotesMain() {
try {
//Standard code for every Notes java agent
Session session = getSession();
AgentContext agentContext = session.getAgentContext();
//Establish connection to Oracle
System.out.println("Import agent starting...");
String cs = "jdbc:oracle:thin:@10oratst07.companyname.com:port#:database";
DriverManager.registerDriver (new oracle.jdbc.OracleDriver());
Connection conn = DriverManager.getConnection(cs, "username", "password");
//Quit if no connection, write status to console
if(conn!=null){
System.out.println("Connected to Oracle");
}
else {
System.out.println("Not connected to Oracle");
System.exit(0);
}
//Get handle to current database, where invoices will be written to
Database db = session.getCurrentDatabase();
//Get handle to config database and create two view entry collections for using later
Database configDb = session.getDatabase("Notes Server", "config.nsf");
View configViewStore = configDb.getView("(StoreLookupByNumber)");
View configViewDept = configDb.getView("(DeptLookupByNumber)");
ViewEntryCollection colStore = configViewStore.getAllEntries();
ViewEntryCollection colDept = configViewDept.getAllEntries();
ViewEntry ve = null;
//Break very long query into readable strings
String fetch1 = "select lots of columns, ";
String fetch2 = "a lot more columns ";
String fetch3 = "even more columns
String fetch4 = "from Oracle View where sent_to_estore is null and NOTES_DOC_ID is null";
String fetch5 = " and rownum < 10001";
String fetch = fetch1 + fetch2 + fetch3 + fetch4 + fetch5;
//Fetch result set, must set to updatable. Default is read only.
Statement statement = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
ResultSet rs = statement.executeQuery(fetch);
int rows = 0;
//With Oracle, the only way to navigate through a result is forward using next()
while(rs.next()){
Document doc = db.createDocument();
doc.replaceItemValue("Form", "Oracle Invoice");
COPY MORE COLUMNS TO FIELDS
//The below line uses a Double wrapper class to pass the value to Notes
//The method only accepts an Object, not a primitive. Wrapper object is anonymous.
//This is done for all Number data types.
doc.replaceItemValue("InvoiceAmount", new Double(rs.getDouble("INVOICE_AMOUNT")));
doc.replaceItemValue("InvoiceNum", rs.getString("INVOICE_NUM"));
//Format the store number to 2 digits (Note: automatic casting happening here)
String storeNumber = "00000000" + new Double(rs.getDouble("STORE_NBR")).intValue();
String storeNumberString = storeNumber.substring(storeNumber.length() - 2);
doc.replaceItemValue("StoreNumber", storeNumberString);
//Get the Store name from the config database
ve = colStore.getFirstEntry();
String storeName = "";
while(ve != null){
//note: to compare two strings the '==' operator doesn't work, you need to use compareTo
//number is in first column (0) and name is in second column (1)
if(storeNumberString.compareTo(ve.getColumnValues().elementAt(0).toString()) == 0){
storeName = ve.getColumnValues().elementAt(1).toString();
}ve = colStore.getNextEntry(ve);
}
if(storeName == ""){
storeName = "No Store Found"; //This should never happen
}
doc.replaceItemValue("Store", storeName);
ve = null; //set null after each usage
//Format the dept number to 4 digits
String deptNumber = "00000000" + new Double(rs.getDouble("DEPT")).intValue();
String deptNumberString = deptNumber.substring(deptNumber.length() - 4);
doc.replaceItemValue("DeptNumber", deptNumber.substring(deptNumber.length() - 4));
//Get the Dept name from the config database
ve = colDept.getFirstEntry();
String deptName = "";
while(ve != null){
if(deptNumberString.compareTo(ve.getColumnValues().elementAt(0).toString()) == 0){
deptName = ve.getColumnValues().elementAt(1).toString();
}
ve = colDept.getNextEntry(ve);
}
if(deptName == ""){
deptName = "No Department Listed"; //This will often be the case
}
doc.replaceItemValue("Dept", deptName);
ve = null; //set null after each usage
//I was getting runtime exceptions when setting java.sql.Date object back to Notes using replaceItemValue
//To get around this I create DateTime object using static method of session
DateTime invoiceDate = session.createDateTime(rs.getDate("INVOICE_DATE"));
DateTime dueDate = session.createDateTime(rs.getDate("DUE_DATE"));
//Convert dates to text and then remove time value
doc.replaceItemValue("InvoiceDate", invoiceDate.toString().substring(0, 10));
doc.replaceItemValue("DueDate", dueDate.toString().substring(0, 10));
doc.replaceItemValue("ImportTime", new java.util.Date().toString());
//used to create InvoiceType
double invoiceAmount = new Double(rs.getDouble("INVOICE_AMOUNT")).doubleValue();
if(invoiceAmount >= 0){ doc.replaceItemValue("InvoiceType", "Invoice"); }
if(invoiceAmount < 0){ doc.replaceItemValue("InvoiceType", "Credit"); }
doc.save();
//Notes document must be saved prior to grabbing UNID
String UNID = doc.getUniversalID();
//Create SQL statement to perform update
String updateSQL1 = "update Oracle view set notes_doc_id =" + "'" + UNID + "' ";
String updateSQL2 = "where invoice_num = '" + rs.getString("INVOICE_NUM") + "' and key_nbr = '"
+ rs.getString("KEY_NBR") + "'";
String updateSQL = updateSQL1 + updateSQL2;
Statement updateStatement = conn.createStatement();
updateStatement.executeUpdate(updateSQL);
rows++;
//Recycle all local variables created in this loop
invoiceDate.recycle();
dueDate.recycle();
doc.recycle();
UNID = null;
updateSQL1 = null;
updateSQL2 = null;
updateSQL = null;
updateStatement.close();
updateStatement = null;
}
//Clean up after myself
rs.close();
statement.close();
conn.close();
System.out.println("End Import agent.... " + rows + " rows have been processed.");
} catch(Exception e) {
e.printStackTrace();
Session session = getSession();
try{
Database db = session.getCurrentDatabase();
Agent agent = db.getAgent("Send Email upon agent failure");
agent.run();
}
catch(Exception f){}
}
}
}
No comments:
Post a Comment