protected Statement createStatement(Connection c) throws SQLException {
Statement statement =
c.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
statement.setFetchSize(batchSize);
statement.setMaxRows(maxRows);
return statement;
}
/**
* Method declaration
*
* @param ev
*/
public void actionPerformed(ActionEvent ev) {
String s = ev.getActionCommand();
if (s == null) {
if (ev.getSource() instanceof MenuItem) {
MenuItem i;
s = ((MenuItem) ev.getSource()).getLabel();
}
}
if (s.equals("Execute")) {
execute();
} else if (s.equals("Exit")) {
windowClosing(null);
} else if (s.equals("Transfer")) {
Transfer.work(null);
} else if (s.equals("Dump")) {
Transfer.work(new String[] {"-d"});
/* NB - 26052002 Restore is not implemented yet in the transfer tool */
/*
} else if (s.equals("Restore")) {
Transfer.work(new String[]{"-r"});
*/
} else if (s.equals("Logging on")) {
jdbcSystem.setLogToSystem(true);
} else if (s.equals("Logging off")) {
jdbcSystem.setLogToSystem(false);
} else if (s.equals("Refresh Tree")) {
refreshTree();
} else if (s.startsWith("#")) {
int i = Integer.parseInt(s.substring(1));
txtCommand.setText(sRecent[i]);
} else if (s.equals("Connect...")) {
connect(ConnectionDialog.createConnection(fMain, "Connect"));
refreshTree();
} else if (s.equals("Results in Grid")) {
iResult = 0;
pResult.removeAll();
pResult.add("Center", gResult);
pResult.doLayout();
} else if (s.equals("Open Script...")) {
FileDialog f = new FileDialog(fMain, "Open Script", FileDialog.LOAD);
// (ulrivo): set default directory if set from command line
if (defDirectory != null) {
f.setDirectory(defDirectory);
}
f.show();
String file = f.getFile();
if (file != null) {
txtCommand.setText(DatabaseManagerCommon.readFile(f.getDirectory() + file));
}
} else if (s.equals("Save Script...")) {
FileDialog f = new FileDialog(fMain, "Save Script", FileDialog.SAVE);
// (ulrivo): set default directory if set from command line
if (defDirectory != null) {
f.setDirectory(defDirectory);
}
f.show();
String file = f.getFile();
if (file != null) {
DatabaseManagerCommon.writeFile(f.getDirectory() + file, txtCommand.getText());
}
} else if (s.equals("Save Result...")) {
FileDialog f = new FileDialog(fMain, "Save Result", FileDialog.SAVE);
// (ulrivo): set default directory if set from command line
if (defDirectory != null) {
f.setDirectory(defDirectory);
}
f.show();
String file = f.getFile();
if (file != null) {
showResultInText();
DatabaseManagerCommon.writeFile(f.getDirectory() + file, txtResult.getText());
}
} else if (s.equals("Results in Text")) {
iResult = 1;
pResult.removeAll();
pResult.add("Center", txtResult);
pResult.doLayout();
showResultInText();
} else if (s.equals("AutoCommit on")) {
try {
cConn.setAutoCommit(true);
} catch (SQLException e) {
}
} else if (s.equals("AutoCommit off")) {
try {
cConn.setAutoCommit(false);
} catch (SQLException e) {
}
} else if (s.equals("Enlarge Tree")) {
Dimension d = tTree.getMinimumSize();
d.width += 20;
tTree.setMinimumSize(d);
fMain.pack();
} else if (s.equals("Shrink Tree")) {
Dimension d = tTree.getMinimumSize();
d.width -= 20;
if (d.width >= 0) {
tTree.setMinimumSize(d);
}
fMain.pack();
} else if (s.equals("Enlarge Command")) {
txtCommand.setRows(txtCommand.getRows() + 1);
fMain.pack();
} else if (s.equals("Shrink Command")) {
int i = txtCommand.getRows() - 1;
txtCommand.setRows(i < 1 ? 1 : i);
fMain.pack();
} else if (s.equals("Commit")) {
try {
cConn.commit();
} catch (SQLException e) {
}
} else if (s.equals("Insert test data")) {
insertTestData();
} else if (s.equals("Rollback")) {
try {
cConn.rollback();
} catch (SQLException e) {
}
} else if (s.equals("Disable MaxRows")) {
try {
sStatement.setMaxRows(0);
} catch (SQLException e) {
}
} else if (s.equals("Set MaxRows to 100")) {
try {
sStatement.setMaxRows(100);
} catch (SQLException e) {
}
} else if (s.equals("SELECT")) {
showHelp(DatabaseManagerCommon.selectHelp);
} else if (s.equals("INSERT")) {
showHelp(DatabaseManagerCommon.insertHelp);
} else if (s.equals("UPDATE")) {
showHelp(DatabaseManagerCommon.updateHelp);
} else if (s.equals("DELETE")) {
showHelp(DatabaseManagerCommon.deleteHelp);
} else if (s.equals("CREATE TABLE")) {
showHelp(DatabaseManagerCommon.createTableHelp);
} else if (s.equals("DROP TABLE")) {
showHelp(DatabaseManagerCommon.dropTableHelp);
} else if (s.equals("CREATE INDEX")) {
showHelp(DatabaseManagerCommon.createIndexHelp);
} else if (s.equals("DROP INDEX")) {
showHelp(DatabaseManagerCommon.dropIndexHelp);
} else if (s.equals("CHECKPOINT")) {
showHelp(DatabaseManagerCommon.checkpointHelp);
} else if (s.equals("SCRIPT")) {
showHelp(DatabaseManagerCommon.scriptHelp);
} else if (s.equals("SHUTDOWN")) {
showHelp(DatabaseManagerCommon.shutdownHelp);
} else if (s.equals("SET")) {
showHelp(DatabaseManagerCommon.setHelp);
} else if (s.equals("Test Script")) {
showHelp(DatabaseManagerCommon.testHelp);
}
}
/**
* <code>getUserSites</code> returns a vector of SiteView's meeting the criteria of the
* parameters.
*
* @param pStoreId a <code>int</code> ID value (from Main DB) of Store selected
* @param pUserId a <code>int</code> ID value (from Main DB) of Reporting User selected (appUser
* if user is not selected or 0 - if user assigned to All Accounts( has property RepOA^))
* @param pNameTempl <code>String</code> NANE filter value
* @param nameBeginsFl <code>int</code> NANE filter type (QueryRequest.BEGINS or
* QueryRequest.CONTAINS)
* @param pCity <code>String</code> filter value for CITY
* @param pState <code>String</code> filter value for STATE
* @param pAccountIdv <code>IdVector</code> filter of AccountDimIds (null if no filter)
* @return a <code>SiteViewVector</code> value
* <p>/**
* ========================================================================================
*/
public SiteViewVector getUserSites(
int pStoreId,
int pUserId,
Integer pSiteDimId,
String pNameTempl,
int nameBeginsFl,
String pCity,
String pState,
IdVector pAccountIdv,
boolean showInactiveFl,
int pResultLimit)
throws RemoteException {
Connection conn = null;
SiteViewVector siteV = new SiteViewVector();
try {
String storeDimFilter =
"(select STORE_DIM_ID from DW_STORE_DIM \n" + " where STORE_ID = " + pStoreId + " ) \n";
String userFilterForAccounts = ""; // if user is not RepOA^ and accounts where not selected
if (pUserId > 0 && (pAccountIdv == null || pAccountIdv.size() == 0)) {
userFilterForAccounts = getUserFilterForAccounts(pUserId);
}
String siteNameFilter = getSearchSqlByFilterName("SITE_NAME", pNameTempl, nameBeginsFl);
String accountIdsFilter =
(pAccountIdv != null && pAccountIdv.size() > 0)
? "and ACCOUNT_DIM_ID in (" + convertToString(pAccountIdv) + ")"
: "";
String cityFilter =
(Utility.isSet(pCity)) ? " and UPPER(SITE_CITY) LIKE UPPER('" + pCity + "%')" : "";
String stateFilter =
(Utility.isSet(pState)) ? " and UPPER(SITE_STATE) LIKE UPPER('" + pState + "%')" : "";
// conn = getConnection();
conn = getReportConnection();
String sql =
"select SITE_DIM_ID, \n"
+ " ACCOUNT_DIM_ID, ACCOUNT_ID, \n"
+ " (Select JD_ACCOUNT_NAME from DW_ACCOUNT_DIM a \n"
+ " where a.ACCOUNT_DIM_ID = s.ACCOUNT_DIM_ID) JD_ACCOUNT_NAME, \n"
+ " SITE_ID, \n"
+ " SITE_NAME, SITE_STREET_ADDRESS, SITE_CITY, SITE_STATE, SITE_STATUS_CD \n"
+ "from DW_SITE_DIM s\n"
+ "where \n"
+ " STORE_DIM_ID = "
+ storeDimFilter
+ " \n"
+ userFilterForAccounts
+ accountIdsFilter
+ siteNameFilter
+ cityFilter
+ stateFilter
+ "order by SITE_NAME ";
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
if (pResultLimit != QueryRequest.UNLIMITED) {
stmt.setMaxRows(Constants.MAX_SITES_TO_RETURN);
}
while (rs.next()) {
SiteView sv = SiteView.createValue();
sv.setId(rs.getInt("SITE_DIM_ID"));
sv.setName(rs.getString("SITE_NAME"));
sv.setAccountId(rs.getInt("ACCOUNT_ID"));
sv.setAccountName(rs.getString("JD_ACCOUNT_NAME"));
sv.setAddress(rs.getString("SITE_STREET_ADDRESS"));
sv.setCity(rs.getString("SITE_CITY"));
sv.setState(rs.getString("SITE_STATE"));
sv.setStatus(rs.getString("SITE_STATUS_CD"));
String targetS = null; // rs.getString("SITE_RANK");
int target = 0;
if (targetS != null) {
try {
target = Integer.parseInt(targetS);
} catch (NumberFormatException e) {
}
}
sv.setTargetFacilityRank(target);
siteV.add(sv);
}
stmt.close();
} catch (Exception exc) {
exc.printStackTrace();
throw processException(exc);
} finally {
closeConnection(conn);
}
return siteV;
}
/**
* <code>getAccountsUIByCriteria</code> returns a vector of AccountUIView's meeting the criteria
* of the BusEntitySearchCriteria.
*
* @param pCrit a <code>BusEntitySearchCriteria</code> value the criteria
* @return a <code>AccountUIViewVector</code> value
* @exception SQLException if an error occurs
*/
public AccountUIViewVector getAccountsUIByCriteria(BusEntitySearchCriteria pCrit)
throws RemoteException {
Connection conn = null;
AccountUIViewVector acctVec = new AccountUIViewVector();
try {
// USER FILTER ==========================================================//
Integer userId = null;
String userFilterForAccounts = "";
if (pCrit.getUserIds() != null && pCrit.getUserIds().size() > 0) {
userId = (Integer) pCrit.getUserIds().get(0);
userFilterForAccounts = getUserFilterForAccounts(userId.intValue());
}
// STORE FILTER===========================================================//
Integer storeId = null;
if (pCrit.getStoreBusEntityIds() != null && pCrit.getStoreBusEntityIds().size() > 0) {
storeId = (Integer) pCrit.getStoreBusEntityIds().get(0);
} else {
throw new RemoteException("Store ID can't be null. ");
}
String storeFilter =
"(select STORE_DIM_ID from DW_STORE_DIM \n" + " where STORE_ID = " + storeId + " ) \n";
String storeFilterForAccounts = getStoreFilterForAccounts(storeId.intValue());
// ACCOUNT NAMES FILTER==================================================//
String accountNameFilter =
getSearchSqlByFilterName(
"JD_ACCOUNT_NAME", pCrit.getSearchName(), pCrit.getSearchNameType());
String accountIdFilter =
(Utility.isSet(pCrit.getSearchId())) ? " and JD_ACCOUNT_ID = " + pCrit.getSearchId() : "";
// ========================================================================//
conn = getReportConnection();
String sql =
"select \n "
+ " wm_concat(ACCOUNT_DIM_ID) ACCOUNT_DIM_IDS, \n"
+ " ACCOUNT_ID, \n"
+ " JD_ACCOUNT_NAME, JD_ACCOUNT_ID, \n"
+ " JD_ACCOUNT_CITY, JD_ACCOUNT_STATE, \n"
+ " JD_MARKET, JD_ACCOUNT_STATUS_CD \n"
+ "from DW_ACCOUNT_DIM where \n"
+ " STORE_DIM_ID = "
+ storeFilter
+ " \n"
+
/* " and account_ID in ( " + storeFilterForAccounts +") \n"
*/
userFilterForAccounts
+ accountIdFilter
+ accountNameFilter
+ " group by \n "
+ " ACCOUNT_ID, \n"
+ " JD_ACCOUNT_NAME, JD_ACCOUNT_ID, \n"
+ " JD_ACCOUNT_CITY, JD_ACCOUNT_STATE, \n"
+ " JD_MARKET, JD_ACCOUNT_STATUS_CD \n"
+ " order by JD_ACCOUNT_NAME ";
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
if (pCrit.getResultLimit() != QueryRequest.UNLIMITED) {
stmt.setMaxRows(2000);
}
int rowId = 0;
while (rs.next()) {
AccountUIView aui = AccountUIView.createValue();
rowId--;
String accountDimIds =
(rs.getString("ACCOUNT_DIM_IDS") != null) ? rs.getString("ACCOUNT_DIM_IDS") : "";
BusEntityData be = new BusEntityData();
be.setBusEntityId(rowId);
be.setShortDesc(
(rs.getString("JD_ACCOUNT_NAME") != null) ? rs.getString("JD_ACCOUNT_NAME") : "");
be.setBusEntityStatusCd(
(rs.getString("JD_ACCOUNT_STATUS_CD") != null)
? rs.getString("JD_ACCOUNT_STATUS_CD")
: "");
PropertyData pd = new PropertyData();
pd.setValue((rs.getString("JD_MARKET") != null) ? rs.getString("JD_MARKET") : "");
AddressData ad = new AddressData();
ad.setAddress1("");
ad.setCity(
(rs.getString("JD_ACCOUNT_CITY") != null) ? rs.getString("JD_ACCOUNT_CITY") : "");
ad.setStateProvinceCd(
(rs.getString("JD_ACCOUNT_STATE") != null) ? rs.getString("JD_ACCOUNT_STATE") : "");
aui.setAccountDimIds(accountDimIds);
aui.setBusEntity(be);
aui.setAccountType(pd);
aui.setPrimaryAddress(ad);
acctVec.add(aui);
}
stmt.close();
} catch (Exception exc) {
exc.printStackTrace();
throw processException(exc);
} finally {
closeConnection(conn);
}
return acctVec;
}
public void actionPerformed(ActionEvent ev) {
String s = ev.getActionCommand();
if (s == null) {
if (ev.getSource() instanceof JMenuItem) {
JMenuItem i;
s = ((JMenuItem) ev.getSource()).getText();
}
}
/*
// button replace by toolbar
if (s.equals("Execute")) {
execute();
} else
*/
if (s.equals("Exit")) {
windowClosing(null);
} else if (s.equals("Transfer")) {
Transfer.work(null);
} else if (s.equals("Dump")) {
Transfer.work(new String[] {"-d"});
} else if (s.equals("Restore")) {
Transfer.work(new String[] {"-r"});
} else if (s.equals("Logging on")) {
javaSystem.setLogToSystem(true);
} else if (s.equals("Logging off")) {
javaSystem.setLogToSystem(false);
} else if (s.equals("Refresh Tree")) {
refreshTree();
} else if (s.startsWith("#")) {
int i = Integer.parseInt(s.substring(1));
txtCommand.setText(sRecent[i]);
} else if (s.equals("Connect...")) {
connect(ConnectionDialogSwing.createConnection(fMain, "Connect"));
refreshTree();
} else if (s.equals("Results in Grid")) {
iResult = 0;
pResult.removeAll();
pResult.add(gScrollPane, BorderLayout.CENTER);
pResult.doLayout();
gResult.fireTableChanged(null);
pResult.repaint();
} else if (s.equals("Open Script...")) {
JFileChooser f = new JFileChooser(".");
f.setDialogTitle("Open Script...");
// (ulrivo): set default directory if set from command line
if (defDirectory != null) {
f.setCurrentDirectory(new File(defDirectory));
}
int option = f.showOpenDialog(fMain);
if (option == JFileChooser.APPROVE_OPTION) {
File file = f.getSelectedFile();
if (file != null) {
StringBuffer buf = new StringBuffer();
ifHuge = DatabaseManagerCommon.readFile(file.getAbsolutePath());
if (4096 <= ifHuge.length()) {
buf.append("This huge file cannot be edited. Please execute\n");
txtCommand.setText(buf.toString());
} else {
txtCommand.setText(ifHuge);
}
}
}
} else if (s.equals("Save Script...")) {
JFileChooser f = new JFileChooser(".");
f.setDialogTitle("Save Script");
// (ulrivo): set default directory if set from command line
if (defDirectory != null) {
f.setCurrentDirectory(new File(defDirectory));
}
int option = f.showSaveDialog(fMain);
if (option == JFileChooser.APPROVE_OPTION) {
File file = f.getSelectedFile();
if (file != null) {
DatabaseManagerCommon.writeFile(file.getAbsolutePath(), txtCommand.getText());
}
}
} else if (s.equals("Save Result...")) {
JFileChooser f = new JFileChooser(".");
f.setDialogTitle("Save Result...");
// (ulrivo): set default directory if set from command line
if (defDirectory != null) {
f.setCurrentDirectory(new File(defDirectory));
}
int option = f.showSaveDialog(fMain);
if (option == JFileChooser.APPROVE_OPTION) {
File file = f.getSelectedFile();
if (file != null) {
showResultInText();
DatabaseManagerCommon.writeFile(file.getAbsolutePath(), txtResult.getText());
}
}
} else if (s.equals("Results in Text")) {
iResult = 1;
pResult.removeAll();
pResult.add(txtResultScroll, BorderLayout.CENTER);
pResult.doLayout();
showResultInText();
pResult.repaint();
} else if (s.equals("AutoCommit on")) {
try {
cConn.setAutoCommit(true);
} catch (SQLException e) {
}
} else if (s.equals("AutoCommit off")) {
try {
cConn.setAutoCommit(false);
} catch (SQLException e) {
}
} else if (s.equals("Commit")) {
try {
cConn.commit();
} catch (SQLException e) {
}
} else if (s.equals("Insert test data")) {
insertTestData();
} else if (s.equals("Rollback")) {
try {
cConn.rollback();
} catch (SQLException e) {
}
} else if (s.equals("Disable MaxRows")) {
try {
sStatement.setMaxRows(0);
} catch (SQLException e) {
}
} else if (s.equals("Set MaxRows to 100")) {
try {
sStatement.setMaxRows(100);
} catch (SQLException e) {
}
} else if (s.equals("SELECT")) {
showHelp(DatabaseManagerCommon.selectHelp);
} else if (s.equals("INSERT")) {
showHelp(DatabaseManagerCommon.insertHelp);
} else if (s.equals("UPDATE")) {
showHelp(DatabaseManagerCommon.updateHelp);
} else if (s.equals("DELETE")) {
showHelp(DatabaseManagerCommon.deleteHelp);
} else if (s.equals("CREATE TABLE")) {
showHelp(DatabaseManagerCommon.createTableHelp);
} else if (s.equals("DROP TABLE")) {
showHelp(DatabaseManagerCommon.dropTableHelp);
} else if (s.equals("CREATE INDEX")) {
showHelp(DatabaseManagerCommon.createIndexHelp);
} else if (s.equals("DROP INDEX")) {
showHelp(DatabaseManagerCommon.dropIndexHelp);
} else if (s.equals("CHECKPOINT")) {
showHelp(DatabaseManagerCommon.checkpointHelp);
} else if (s.equals("SCRIPT")) {
showHelp(DatabaseManagerCommon.scriptHelp);
} else if (s.equals("SHUTDOWN")) {
showHelp(DatabaseManagerCommon.shutdownHelp);
} else if (s.equals("SET")) {
showHelp(DatabaseManagerCommon.setHelp);
} else if (s.equals("Test Script")) {
showHelp(DatabaseManagerCommon.testHelp);
}
}
