public static String processCustomerOrder(String strPassURL, String strPassLogin, String strPassPassword, String strPassOrderNumber, String strPassVersionNumber)
{
int numRecords = 0;
int intRowCount = 0;
Connection conBase = null;
Statement stmtT = null;
ResultSet rsT = null;
Statement stmtSP = null;
ResultSet rsSP = null;
Database pDB = null;
View pView = null;
DocumentCollection pDC = null;
Document pDoc = null;
Database custDB = null;
View custView = null;
Document custDoc = null;
String strGetCustomerCode = null;
String strGetContact = null;
String strGetContactPhone = null;
String strGetCustPONumber = null;
String strGetCustJobDesc = null;
String strGetSalesCode = null;
String strGetSalesPerson = null;
String strGetAE = null;
String strGetAETeam = null;
String strGetOrderCurrency = null;
String strGetClientID = null;
String strGetTerritory = null;
String strGetFaxNum = null;
String strGetPaymentTerms = null;
String strGetTerms = null;
String strGetBillSeeds = null;
String strGetCustRating = null;
String strGetInvoiceType = null;
String strGetTaxExempt = null;
String strGetTaxID = null;
String strGetCSR = null;
String strGetFreight = null;
String strGetContactFName = null;
String strGetContactLName = null;
double dGetTOTAL_AMT_ORDERED = 0;
double dTOTAL_AMT_SHIPPED = 0;
String strGetSQL = null;
PreparedStatement pstmt = null;
try
{
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
conBase = DriverManager.getConnection(strPassURL, strPassLogin, strPassPassword);
stmtT = conBase.createStatement();
Session s = NotesFactory.createSession();
pDB = s.getDatabase(strServer, "<Your DB>");
pView = pDB.getView("LKAllOrders");
pDC = pView.getAllDocumentsByKey(strPassOrderNumber, true);
pDoc = pDC.getFirstDocument();
while (pDoc != null)
{
intRowCount = intRowCount + 1;
strGetCustomerCode = pDoc.getItemValueString("customercode");
strGetContact = pDoc.getItemValueString("contact");
strGetContactPhone = pDoc.getItemValueString("contactPhone");
strGetCustPONumber = pDoc.getItemValueString("CustPONumber");
strGetCustJobDesc = pDoc.getItemValueString("CustJobDesc");
strGetSalesCode = pDoc.getItemValueString("vSalesCode");
strGetSalesPerson = pDoc.getItemValueString("salesperson");
strGetAE = pDoc.getItemValueString("AE");
strGetAETeam = pDoc.getItemValueString("AETeam");
strGetOrderCurrency = pDoc.getItemValueString("ordercurrency");
dGetTOTAL_AMT_ORDERED = pDoc.getItemValueDouble("orderprice");
dTOTAL_AMT_SHIPPED = 0;
//Get Customer Profile
custDB = s.getDatabase(strServer, "lookup\custprofile.nsf");
custView = custDB.getView("LKCustomerAbbrev");
custDoc = custView.getDocumentByKey(strGetCustomerCode, true);
if (custDoc != null)
{
strGetClientID = custDoc.getItemValueString("ClientID");
strGetTerritory = custDoc.getItemValueString("Territory");
strGetFaxNum = custDoc.getItemValueString("faxNum");
strGetPaymentTerms = custDoc.getItemValueString("PaymentTerms");
strGetTerms = custDoc.getItemValueString("Terms");
strGetBillSeeds = custDoc.getItemValueString("BillSeeds");
strGetCustRating = custDoc.getItemValueString("custRating");
strGetInvoiceType = custDoc.getItemValueString("InvoiceType");
strGetTaxExempt = custDoc.getItemValueString("TaxExempt");
strGetCSR = custDoc.getItemValueString("CSR");
strGetFreight = "B";
strGetContactFName = custDoc.getItemValueString("contactFName1");
strGetContactLName = custDoc.getItemValueString("contactLName1");
strGetTaxID = custDoc.getItemValueString("TaxID");
}
rsT = stmtT.executeQuery("SELECT * FROM CUSTOMER_ORDER Where ID = '" + strPassOrderNumber + "'");
while (rsT.next())
{
numRecords = numRecords + 1;
System.out.println("Found Docs " + String.valueOf(numRecords));
}
if(numRecords > 0)
{
System.out.println("Start Update");
pstmt = conBase.prepareStatement("update CUSTOMER_ORDER set "
+ "CUSTOMER_ID=?, "
+ "CUSTOMER_PO_REF=?, "
+ "CONTACT_FIRST_NAME=?, "
+ "CONTACT_LAST_NAME=?, "
+ "CONTACT_PHONE=?, "
+ "CONTACT_FAX=?, "
+ "TERRITORY=?, "
+ "DISCOUNT_CODE=?, "
+ "SALESREP_ID=?, "
+ "ENTITY_ID=?, "
+ "TERMS_NET_TYPE=?, "
+ "TERMS_DISC_TYPE=?, "
+ "FREIGHT_TERMS=?, "
+ "ORDER_DATE=?, "
+ "BACK_ORDER=?, "
+ "STATUS=?, "
+ "SELL_RATE=?, "
+ "BUY_RATE=?, "
+ "POSTING_CANDIDATE=?, "
+ "TOTAL_AMT_ORDERED=?, "
+ "TOTAL_AMT_SHIPPED=?, "
+ "MARKED_FOR_PURGE=?, "
+ "EDI_FLAG=?, "
+ "EXCH_RATE_FIXED=?, "
+ "EDI_BLANKET_FLAG=?, "
+ "CURRENCY_ID=?, "
+ "ACCEPT_EARLY=?, "
+ "CREATE_DATE=? "
+ "WHERE ID=?");
conBase.setAutoCommit(false);
BigDecimal bdTOTAL_AMT_ORDERED = new BigDecimal(String.valueOf("0"));
BigDecimal bdTOTAL_AMT_SHIPPED = new BigDecimal(String.valueOf("0"));
BigDecimal bdBUY_RATE = new BigDecimal(String.valueOf("0"));
BigDecimal bdSell_RATE = new BigDecimal(String.valueOf("0"));
pstmt.setString(29, strPassOrderNumber);
pstmt.setString(1, strGetClientID);
pstmt.setString(2, strGetCustPONumber);
pstmt.setString(3, strGetContactFName);
pstmt.setString(4, strGetContactLName);
pstmt.setString(5, strGetContactPhone);
pstmt.setString(6, strGetFaxNum);
pstmt.setString(7, strGetAETeam);
pstmt.setString(8, strGetCustRating);
pstmt.setString(9, strGetSalesPerson);
pstmt.setString(10, "QA");
pstmt.setString(11, "A");
pstmt.setString(12, "A");
pstmt.setString(13, strGetFreight);
pstmt.setDate(14, new java.sql.Date(System.currentTimeMillis()));
pstmt.setString(15, "N");
pstmt.setString(16, "R");
pstmt.setBigDecimal(17, bdBUY_RATE);
pstmt.setBigDecimal(18, bdSell_RATE);
pstmt.setString(19, "Y");
pstmt.setBigDecimal(20, bdTOTAL_AMT_ORDERED);
pstmt.setBigDecimal(21, bdTOTAL_AMT_SHIPPED);
pstmt.setString(22, "N");
pstmt.setString(23, "N");
pstmt.setString(24, "N");
pstmt.setString(25, "N");
pstmt.setString(26, strGetOrderCurrency);
pstmt.setString(27, "Y");
pstmt.setDate(28, new java.sql.Date(System.currentTimeMillis()));
conBase.commit();
conBase.setAutoCommit(true);
pstmt.close();
System.out.println("Updated " + String.valueOf(intRowCount));
}
else
{ //Set up Prepared Statement
System.out.println("Start Insert");
strGetSQL = "INSERT INTO CUSTOMER_ORDER("
+ "ID, "
+ "CUSTOMER_ID, "
+ "CUSTOMER_PO_REF, "
+ "CONTACT_FIRST_NAME, "
+ "CONTACT_LAST_NAME, "
+ "CONTACT_PHONE, "
+ "CONTACT_FAX, "
+ "TERRITORY, "
+ "DISCOUNT_CODE, "
+ "SALESREP_ID, "
+ "ENTITY_ID, "
+ "TERMS_NET_TYPE, "
+ "TERMS_DISC_TYPE, "
+ "FREIGHT_TERMS, "
+ "ORDER_DATE, "
+ "BACK_ORDER, "
+ "STATUS, "
+ "SELL_RATE, "
+ "BUY_RATE, "
+ "POSTING_CANDIDATE, "
+ "TOTAL_AMT_ORDERED, "
+ "TOTAL_AMT_SHIPPED, "
+ "MARKED_FOR_PURGE, "
+ "EDI_FLAG, "
+ "EXCH_RATE_FIXED, "
+ "EDI_BLANKET_FLAG, "
+ "CURRENCY_ID, "
+ "ACCEPT_EARLY, "
+ "CREATE_DATE) "
+ "VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
BigDecimal bdTOTAL_AMT_ORDERED2 = new BigDecimal(String.valueOf("0"));
BigDecimal bdTOTAL_AMT_SHIPPED2 = new BigDecimal(String.valueOf("0"));
BigDecimal bdBUY_RATE2 = new BigDecimal(String.valueOf("0"));
BigDecimal bdSell_RATE2 = new BigDecimal(String.valueOf("0"));
pstmt = conBase.prepareStatement(strGetSQL);
pstmt.setString(1, qaUtil.cleanString(strPassOrderNumber, 15));
pstmt.setString(2, qaUtil.cleanString(strGetClientID, 15));
pstmt.setString(3, qaUtil.cleanString(strGetCustPONumber, 40));
pstmt.setString(4, qaUtil.cleanString(strGetContactFName, 30));
pstmt.setString(5, qaUtil.cleanString(strGetContactLName, 30));
pstmt.setString(6, qaUtil.cleanString(strGetContactPhone, 20));
pstmt.setString(7, qaUtil.cleanString(strGetFaxNum, 20));
pstmt.setString(8, qaUtil.cleanString(strGetAETeam, 15));
pstmt.setString(9, qaUtil.cleanString(strGetCustRating, 15));
pstmt.setString(10, qaUtil.cleanString(strGetSalesCode, 15));
pstmt.setString(11, "QA");
pstmt.setString(12, "A");
pstmt.setString(13, "A");
pstmt.setString(14, qaUtil.cleanString(strGetFreight, 1));
pstmt.setDate(15, new java.sql.Date(System.currentTimeMillis()));
pstmt.setString(16, "N");
pstmt.setString(17, "R");
pstmt.setBigDecimal(18, bdBUY_RATE2);
pstmt.setBigDecimal(19, bdSell_RATE2);
pstmt.setString(20, "Y");
pstmt.setBigDecimal(21, bdTOTAL_AMT_ORDERED2);
pstmt.setBigDecimal(22, bdTOTAL_AMT_SHIPPED2);
pstmt.setString(23, "N");
pstmt.setString(24, "N");
pstmt.setString(25, "N");
pstmt.setString(26, "N");
pstmt.setString(27, qaUtil.cleanString(strGetOrderCurrency, 15));
pstmt.setString(28, "Y");
pstmt.setDate(29, new java.sql.Date(System.currentTimeMillis()));
pstmt.executeUpdate();
pstmt.close();
//System.out.println("Wrote " + String.valueOf(intRowCount));
}
pDoc = pDC.getNextDocument();
}
//return value
return "OK";
}
catch(Exception e)
{
return e.getMessage();
}
finally
{
}
}