public final void populateDivisionColumn() throws SQLException {
Sql sql = sqlhand.als_mysoftsql();
if (sql == null) return;
List<GroovyRowResult> tlist = null;
String sqlstm =
"select distinct stock_cat from stockmasterdetails where item_type='Service Item' and nominal_code like '5%' order by stock_cat";
try {
tlist = sql.rows(sqlstm);
sql.close();
} catch (SQLException e) {
}
if (tlist.size() == 0) return;
Listbox newlb = lbhand.makeVWListbox(division_holder, blankish_lb_headers, division_lbid, 20);
newlb.addEventListener("onSelect", new divisionOnClick());
String[] strarray = new String[1];
String tp_division = luhand.getRec_Origid("TP_DIVISIONS"); // hardcoded
// debugLabel.setValue("getrec_origid: " + tp_division);
for (GroovyRowResult ilist : tlist) {
strarray[0] =
luhand.matchLookup_ReturnStr("TP_DIVISIONS", (String) ilist.get("stock_cat"), 1);
// strarray[0] = (String)ilist.get("stock_cat");
lbhand.insertListItems(newlb, strarray, "true", "");
}
} // end of populateDivisionColumn()
private Sql makeSQL(T t, String[] fields, Object[] values) throws SqlCreateException {
Sql sql = new Sql();
Class<?> clazz = t.getClass();
String table = ORMConfigBeanUtil.getTable(clazz, false);
ReflectUtil ru = new ReflectUtil(t);
String[] columns = ORMConfigBeanUtil.getColumns(clazz, fields);
String idColumn = ORMConfigBeanUtil.getIdColumn(clazz);
String idField = ORMConfigBeanUtil.getIdField(clazz);
Method idGetter = ru.getGetter(idField);
if (idGetter == null) throw new SqlCreateException("can not find id getter.");
Object idValue = null;
try {
idValue = idGetter.invoke(t);
} catch (Exception e) {
throw new SqlCreateException(idGetter + " invoke exception " + e.toString(), e);
}
StringBuilder sb = new StringBuilder();
for (int i = 0; i < columns.length; ++i) {
String column = columns[i];
if (sb.length() > 0) sb.append(", ");
// sb.append(column).append(" = '").append(values[i]).append("'");
sb.append(column).append(" = ?");
sql.args.add(values[i]);
}
// String condition = new StringBuilder().append(idColumn).append(" =
// ").append("'").append(idValue).append("'").toString();
String condition = new StringBuilder().append(idColumn).append(" = ? ").toString();
sql.args.add(idValue);
sql.sql = String.format("UPDATE %s SET %s WHERE %s ;", table, sb.toString(), condition);
return sql;
}
// Populate division column - refer to mysoft.stockmasterdetails.stock_cat
// nominal_code=glcode=5xxxxx = services we sell
// istock_cat = as in stockmasterdetails.stock_cat
public final void populateSectionColumn(String istock_cat) {
Sql sql = sqlhand.als_mysoftsql();
if (sql == null) return;
String sqlstm =
"select distinct groupcode from stockmasterdetails where item_type='Service Item' and nominal_code like '5%' "
+ "and stock_cat='"
+ istock_cat
+ "' order by groupcode";
List<GroovyRowResult> tlist = null;
try {
tlist = sql.rows(sqlstm);
sql.close();
} catch (SQLException e) {
}
if (tlist == null) return;
selected_category = istock_cat; // save for later usage
if (tlist.size() == 0) return;
Listbox newlb = lbhand.makeVWListbox(section_holder, blankish_lb_headers, section_lbid, 20);
newlb.addEventListener("onSelect", new sectionOnClick());
String[] strarray = new String[1];
for (GroovyRowResult ilist : tlist) {
strarray[0] = (String) ilist.get("groupcode");
lbhand.insertListItems(newlb, strarray, "true", "");
}
} // end of populateSectionColumn()
private Sql makeSQL(T t, String condition) {
Sql sql = new Sql();
Class<?> clazz = t.getClass();
String table = ORMConfigBeanUtil.getTable(clazz, false);
sql.sql = String.format("UPDATE %s %s ;", table, condition);
return sql;
}
public void generateReport() throws IOException {
System.out.println("Test Report Gen");
Sheet sheet = workbook.getSheet("sheet1");
try {
String strSql = "Select * from participant;";
Sql db = new Sql();
ResultSet rs = db.executeQuery(strSql);
ResultSetMetaData rsMeta = rs.getMetaData();
Row row = sheet.createRow(0);
Cell cell;
// get table columns which will act as title columns
for (int i = 1; i <= rsMeta.getColumnCount(); i++) {
String colName = rsMeta.getColumnName(i);
int dataType = rsMeta.getColumnType(i);
cell = row.createCell(i - 1);
cell.setCellValue(colName);
}
int i = 1;
while (rs.next()) {
// create row to write data to
row = sheet.createRow(i);
// create cells for writing data to
for (int c = 1; c <= rsMeta.getColumnCount(); c++) {
cell = row.createCell(c - 1);
String colName = rsMeta.getColumnName(c);
cell.setCellValue(rs.getString(colName));
}
i++;
}
JOptionPane.showMessageDialog(null, "Excel Report of All Participants Generated");
} catch (SQLException ex) {
ex.printStackTrace();
// log Error
FacesFingerPrintProject.logger.log(Level.SEVERE, "ERROR", ex);
}
/// Write the output to a file
FileOutputStream fileOut = new FileOutputStream("reportTest.xls");
workbook.write(fileOut);
fileOut.close();
}
public DatabaseConnectorPool(final DatabaseConnectorFactory factory, final int size) {
this.factory = factory;
connectorPool = new Vector<DatabaseConnector>();
for (int i = 0; i < size; i++) {
newConnector();
}
LOG.info("Created an intial pool of " + size + " database connections");
final DatabaseConnector connection = acquire();
Sql.setMetaData(connection.getMetaData());
release(connection);
}
public static void main(String[] args) throws IOException {
Sql reader = new Sql();
final String emisora = "BIMBO";
final String serie = "";
final Calendar day = new GregorianCalendar(2010, 10, 17);
ArrayList<Orden> ordenes = reader.readIniciales(emisora, serie, day);
reader.read(ordenes, emisora, serie, day);
String datetext =
day.get(Calendar.YEAR) + "" + (day.get(Calendar.MONTH) + 1) + "" + day.get(Calendar.DATE);
FileWriter fw = new FileWriter("ordenes" + emisora + datetext + ".txt");
final BufferedWriter bw = new BufferedWriter(fw);
FileWriter fw2 = new FileWriter("ordenes2" + emisora + datetext + ".txt");
final BufferedWriter bw2 = new BufferedWriter(fw2);
FileWriter fwc = new FileWriter("compra" + emisora + datetext + ".txt");
final BufferedWriter bwc = new BufferedWriter(fwc);
FileWriter fwv = new FileWriter("venta" + emisora + datetext + ".txt");
final BufferedWriter bwv = new BufferedWriter(fwv);
FileWriter fwd = new FileWriter("distancia" + emisora + datetext + ".txt");
final BufferedWriter bwd = new BufferedWriter(fwd);
JFrame frame = new JFrame();
frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
final DefaultTableModel modelVenta =
new DefaultTableModel() {
@Override
public Class getColumnClass(int column) {
if (column == 1) {
return BigDecimal.class;
}
return Object.class;
}
};
final DefaultTableModel modelCompra =
new DefaultTableModel() {
@Override
public Class getColumnClass(int column) {
if (column == 1) {
return BigDecimal.class;
}
return Object.class;
}
};
final DefaultTableModel modelExec = new DefaultTableModel();
final DefaultTableModel modelExec2 = new DefaultTableModel();
final DefaultTableModel modelOrdenes = new DefaultTableModel();
final JTable tableVenta = new JTable(modelVenta);
tableVenta.setAutoCreateRowSorter(true);
final JTable tableCompra = new JTable(modelCompra);
tableCompra.setAutoCreateRowSorter(true);
final JTable tableExec = new JTable(modelExec);
final JTable tableExec2 = new JTable(modelExec2);
final JTable tableOrdenes = new JTable(modelOrdenes);
JScrollPane scrollPaneVenta = new JScrollPane(tableVenta);
JScrollPane scrollPaneCompra = new JScrollPane(tableCompra);
JScrollPane scrollPaneExec = new JScrollPane(tableExec);
JScrollPane scrollPaneExec2 = new JScrollPane(tableExec2);
JScrollPane scrollPaneOrdenes = new JScrollPane(tableOrdenes);
scrollPaneVenta.setBounds(0, 0, 400, 250);
scrollPaneCompra.setBounds(0, 250, 400, 250);
scrollPaneExec.setBounds(400, 0, 400, 250);
scrollPaneExec2.setBounds(400, 250, 400, 250);
scrollPaneOrdenes.setBounds(0, 500, 800, 200);
frame.setLayout(null);
frame.add(scrollPaneVenta);
frame.add(scrollPaneCompra);
frame.add(scrollPaneExec);
frame.add(scrollPaneExec2);
frame.add(scrollPaneOrdenes);
frame.setSize(806, 792);
frame.setVisible(true);
frame.setResizable(false);
ArrayList<SortKey> sortKeysv = new ArrayList<RowSorter.SortKey>();
sortKeysv.add(new RowSorter.SortKey(1, SortOrder.DESCENDING));
sortKeysv.add(new RowSorter.SortKey(3, SortOrder.DESCENDING));
modelVenta.addColumn("");
modelVenta.addColumn("Precio");
modelVenta.addColumn("Volumen");
modelVenta.addColumn("Folio");
tableVenta.getRowSorter().setSortKeys(sortKeysv);
ArrayList<SortKey> sortKeysc = new ArrayList<RowSorter.SortKey>();
sortKeysc.add(new RowSorter.SortKey(1, SortOrder.DESCENDING));
sortKeysc.add(new RowSorter.SortKey(3, SortOrder.ASCENDING));
modelCompra.addColumn("");
modelCompra.addColumn("Precio");
modelCompra.addColumn("Volumen");
modelCompra.addColumn("Folio");
tableCompra.getRowSorter().setSortKeys(sortKeysc);
modelExec.addColumn("Hora");
modelExec.addColumn("Precio");
modelExec.addColumn("Volumen");
modelExec2.addColumn("Hora");
modelExec2.addColumn("Precio");
modelExec2.addColumn("Volumen");
modelOrdenes.addColumn("Tipo");
modelOrdenes.addColumn("Precio");
modelOrdenes.addColumn("Volumen");
modelOrdenes.addColumn("Hora");
modelOrdenes.addColumn("Folio");
modelOrdenes.addColumn("Folio Ant.");
modelOrdenes.addColumn("Casa de Bolsa");
Object[] columns = {
"Tipo", "Precio", "Volumen", "Hora", "Folio", "Folio Ant.", "Casa de Bolsa", "Orden"
};
Vector<Object> columnNames = convertToVector(columns);
Vector<Object> data = new Vector<Object>(6000);
for (int i = 0; i < ordenes.size(); i++) {
Vector<Object> row = convertToVector(ordenes.get(i).toRow());
data.add(row);
}
modelOrdenes.setDataVector(data, columnNames);
DateRenderer2 render = new DateRenderer2();
tableOrdenes.getColumnModel().getColumn(3).setCellRenderer(render);
tableExec.getColumnModel().getColumn(0).setCellRenderer(render);
tableExec2.getColumnModel().getColumn(0).setCellRenderer(render);
JButton buttonNext = new JButton("Siguiente");
buttonNext.setBounds(30, 716, 100, 30);
frame.add(buttonNext);
buttonNext.addActionListener(
new ActionListener() {
public void actionPerformed(ActionEvent e) {
// Execute when button is pressed
if (modelOrdenes.getRowCount() > 0)
action(
day,
modelCompra,
modelVenta,
modelOrdenes,
modelExec,
modelExec2,
tableCompra,
tableVenta,
tableExec,
tableExec2,
bw,
bw2,
bwc,
bwv,
bwd);
}
});
JButton buttonDeleteCompra = new JButton("Cancelar Compra");
buttonDeleteCompra.setBounds(150, 716, 200, 30);
frame.add(buttonDeleteCompra);
buttonDeleteCompra.addActionListener(
new ActionListener() {
public void actionPerformed(ActionEvent e) {
// Execute when button is pressed
int d = tableCompra.getSelectedRow();
try {
modelCompra.removeRow(tableCompra.convertRowIndexToModel(d));
} catch (Exception ex) {
System.out.println("Cannot delete row: " + ex);
}
}
});
JButton buttonDeleteVenta = new JButton("Cancelar Venta");
buttonDeleteVenta.setBounds(370, 716, 200, 30);
frame.add(buttonDeleteVenta);
buttonDeleteVenta.addActionListener(
new ActionListener() {
public void actionPerformed(ActionEvent e) {
// Execute when button is pressed
int d = tableVenta.getSelectedRow();
try {
modelVenta.removeRow(tableVenta.convertRowIndexToModel(d));
} catch (Exception ex) {
System.out.println("Cannot delete row: " + ex);
}
}
});
JButton buttonPlay = new JButton("Play");
buttonPlay.setBounds(600, 716, 100, 30);
frame.add(buttonPlay);
buttonPlay.addActionListener(
new ActionListener() {
public void actionPerformed(ActionEvent e) {
// Execute when button is pressed
int i = 0;
// while(tableOrdenes.getRowCount() > 0 && i < 100) {
while (tableOrdenes.getRowCount() > 0) {
Date cal = (Date) modelOrdenes.getValueAt(0, 3);
action(
day,
modelCompra,
modelVenta,
modelOrdenes,
modelExec,
modelExec2,
tableCompra,
tableVenta,
tableExec,
tableExec2,
bw,
bw2,
bwc,
bwv,
bwd);
// modelCompra.getValueAt(0, 1)
i++;
}
if (tableOrdenes.getRowCount() <= 0)
try {
bw.close();
bw2.close();
bwc.close();
bwv.close();
bwd.close();
} catch (IOException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
}
});
}
/**
* Compares given {@link Sql} instances.
*
* @param first the first.
* @param second the second.
* @return a positive number if the first is considered 'greater' than the second; 0 if they're
* equal; a negative number otherwise.
*/
protected int compareThem(@NotNull final Sql<T> first, @NotNull final Sql<T> second) {
return ("" + first.getId()).compareTo(("" + second.getId()));
}
private Sql makeSQL(T t) throws SqlCreateException {
Sql sql = new Sql();
Class<?> clazz = t.getClass();
String table;
String[] columns;
String[] fields;
Object[] values = null;
String idColumn;
String idField;
Object idValue = null;
HashMap<String, Object> map = null;
if (Map.class.isAssignableFrom(clazz)) {
map = (HashMap) t;
table = (String) map.get("table");
idColumn = (String) map.get("idColumn");
idField = idColumn;
idValue = map.get("idValue");
columns = (String[]) map.get("columns");
fields = columns;
values = (Object[]) map.get("values");
} else {
table = ORMConfigBeanUtil.getTable(clazz, false);
columns = ORMConfigBeanUtil.getColumns(clazz);
fields = ORMConfigBeanUtil.getFields(clazz);
idColumn = ORMConfigBeanUtil.getIdColumn(clazz);
idField = ORMConfigBeanUtil.getIdField(clazz);
}
StringBuilder valuesSb = new StringBuilder();
ReflectUtil ru = new ReflectUtil(t);
try {
if (map == null) {
Method idGetter = ru.getGetter(idField);
if (idGetter == null) throw new SqlCreateException("can not find id getter");
idValue = idGetter.invoke(t);
}
for (int i = 0; i < columns.length; i++) {
String column = columns[i];
String field = fields[i];
Object value = null;
// id 字段不允许
if (idColumn != null && idColumn.equalsIgnoreCase(column)) continue;
if (map != null && values != null) {
value = values[i];
} else {
Method getter = ru.getGetter(field);
if (getter == null) continue;
Object _value = getter.invoke(t);
if (_value == null) continue;
if (ClassUtil.isPojo(_value.getClass())) {
Field f = ru.getField(field);
OneToOne oneAnn = getter.getAnnotation(OneToOne.class);
if (oneAnn == null) oneAnn = f.getAnnotation(OneToOne.class);
ManyToOne manyToOneAnn = null;
if (oneAnn == null) {
manyToOneAnn = getter.getAnnotation(ManyToOne.class);
if (manyToOneAnn == null) manyToOneAnn = f.getAnnotation(ManyToOne.class);
}
if (oneAnn != null || manyToOneAnn != null) {
JoinColumn joinColAnn = getter.getAnnotation(JoinColumn.class);
if (joinColAnn == null) joinColAnn = f.getAnnotation(JoinColumn.class);
if (joinColAnn != null && joinColAnn.referencedColumnName().trim().length() > 0) {
String refCol = joinColAnn.referencedColumnName();
String refField = ORMConfigBeanUtil.getField(_value.getClass(), refCol);
ReflectUtil tarRu = new ReflectUtil(_value);
Method tarFKGetter = tarRu.getGetter(refField);
value = tarFKGetter.invoke(_value);
} else {
ReflectUtil tarRu = new ReflectUtil(_value);
String tarFKField = ORMConfigBeanUtil.getIdField(_value.getClass());
if (tarFKField != null) {
Method tarFKGetter = tarRu.getGetter(tarFKField);
value = tarFKGetter.invoke(_value);
}
}
}
if (value == null) continue;
} else value = _value;
}
if (valuesSb.length() > 0) valuesSb.append(",");
// valuesSb.append(column).append(" = '").append(value).append("'");
valuesSb.append(column).append(" = ? ");
sql.args.add(value);
}
} catch (Exception e) {
throw new SqlCreateException("" + e.toString(), e);
}
// String condition = new StringBuilder().append(idColumn).append(" =
// ").append("'").append(idValue).append("'").toString();
String condition = new StringBuilder().append(idColumn).append(" = ? ").toString();
sql.args.add(idValue);
sql.sql = String.format("UPDATE %s SET %s WHERE %s ;", table, valuesSb, condition);
return sql;
}
private Sql makeSQL(T t, String[] fields) throws SqlCreateException {
Sql sql = new Sql();
Class<?> clazz = t.getClass();
// if fields is empty
if (fields == null || fields.length == 0) {
fields = ORMConfigBeanUtil.getFields(clazz);
}
String table = ORMConfigBeanUtil.getTable(clazz, false);
StringBuilder values = new StringBuilder();
ReflectUtil ru = new ReflectUtil(t);
String[] columns = ORMConfigBeanUtil.getColumns(clazz, fields);
String idColumn = ORMConfigBeanUtil.getIdColumn(clazz);
String idField = ORMConfigBeanUtil.getIdField(clazz);
Method idGetter = ru.getGetter(idField);
if (idGetter == null) throw new SqlCreateException("can not find id getter.");
Object idValue = null;
try {
idValue = idGetter.invoke(t);
} catch (Exception e) {
throw new SqlCreateException(idGetter + " invoke exception " + e.toString(), e);
}
for (int i = 0; i < fields.length; i++) {
String field = fields[i];
String column = columns[i];
Method getter = ru.getGetter(field);
if (getter == null) continue;
try {
Object _value = getter.invoke(t);
if (_value == null) continue;
Object value = null;
if (ClassUtil.isPojo(_value.getClass())) {
Field f = ru.getField(field);
OneToOne oneAnn = getter.getAnnotation(OneToOne.class);
if (oneAnn == null) oneAnn = f.getAnnotation(OneToOne.class);
ManyToOne manyToOneAnn = null;
if (oneAnn == null) {
manyToOneAnn = getter.getAnnotation(ManyToOne.class);
if (manyToOneAnn == null) manyToOneAnn = f.getAnnotation(ManyToOne.class);
}
if (oneAnn != null || manyToOneAnn != null) {
JoinColumn joinColAnn = getter.getAnnotation(JoinColumn.class);
if (joinColAnn == null) joinColAnn = f.getAnnotation(JoinColumn.class);
if (joinColAnn != null && joinColAnn.referencedColumnName().trim().length() > 0) {
String refCol = joinColAnn.referencedColumnName();
String refField = ORMConfigBeanUtil.getField(_value.getClass(), refCol);
ReflectUtil tarRu = new ReflectUtil(_value);
Method tarFKGetter = tarRu.getGetter(refField);
value = tarFKGetter.invoke(_value);
} else {
ReflectUtil tarRu = new ReflectUtil(_value);
String tarFKField = ORMConfigBeanUtil.getIdField(_value.getClass());
if (tarFKField != null) {
Method tarFKGetter = tarRu.getGetter(tarFKField);
value = tarFKGetter.invoke(_value);
}
}
}
if (value == null) continue;
} else {
value = _value;
}
if (values.length() > 0) values.append(", ");
// values.append(column).append(" = '").append(value).append("'");
values.append(column).append(" = ? ");
sql.args.add(value);
} catch (Exception e) {
throw new SqlCreateException(idGetter + " invoke exception " + e.toString(), e);
}
}
// String condition = new StringBuilder().append(idColumn).append(" =
// ").append("'").append(idValue).append("'").toString();
String condition = new StringBuilder().append(idColumn).append(" = ? ").toString();
sql.args.add(idValue);
sql.sql = String.format("UPDATE %s SET %s WHERE %s ;", table, values, condition);
return sql;
}
public static Boolean doUserCommand(String command, String jid, String admin)
throws XMPPException, IOException, NumberFormatException, ClassNotFoundException,
SQLException, ParseException {
Boolean ans = false;
String msg = null;
if (command.startsWith("!report") && !ans) {
command = new StringBuffer(command).delete(0, 7).toString();
msg = command + " - " + jid;
XmppNet.sendMessage(admin, msg);
ans = true;
}
if (command.startsWith("!list") && !ans) {
java.util.Date today = new java.util.Date();
long time = (System.currentTimeMillis());
msg = Sql.listOfTimer(jid, time);
XmppNet.sendMessage(jid, msg);
ans = true;
}
if (command.startsWith("!remind") && !ans) {
command = command.replaceAll("!remind ", "");
java.util.Date today = new java.util.Date();
long time = (System.currentTimeMillis());
StringTokenizer st = new StringTokenizer(command, "@");
String noteU = "";
while (st.hasMoreTokens()) {
command = st.nextToken();
if (!st.hasMoreElements()) {
NullNoteEx(jid);
}
noteU = st.nextToken();
}
SimpleDateFormat sdf = new SimpleDateFormat("dd.MM.yyyy HH:mm");
Date dt = null;
try {
dt = sdf.parse(command);
} catch (ParseException e) {
msg = "Wrong date!";
XmppNet.sendMessage(jid, msg);
ans = true;
}
long dt1 = dt.getTime() / 1000;
if (!ans) {
if (dt1 > (time / 1000)) {
Sql.add(dt1, jid, Main.id, noteU);
msg = "Timer is set!";
} else {
msg = "Wrong date";
}
}
XmppNet.sendMessage(jid, msg);
ans = true;
Main.id++;
}
if (command.startsWith("!note") && !ans) {
command = command.replaceAll("!note ", "");
Long time = 0L;
if (!ans) {
Sql.add(time, jid, Main.id, command);
}
ans = true;
msg = "Writed!";
XmppNet.sendMessage(jid, msg);
Main.id++;
}
if (command.startsWith("!my") && !ans) {
msg = Sql.listOfNote(jid);
ans = true;
XmppNet.sendMessage(jid, msg);
Main.id++;
}
if (command.startsWith("!del") && !ans) {
command = command.replaceAll("!del #", "");
if (!ans) {
Sql.deleteNote(jid, command);
}
ans = true;
msg = "Command complete";
XmppNet.sendMessage(jid, msg);
Main.id++;
}
try {
if (command.startsWith("!timer") && !ans) {
command = command.replaceAll("!timer ", "");
java.util.Date today = new java.util.Date();
long time = (System.currentTimeMillis());
StringTokenizer st = new StringTokenizer(command, "@");
String noteU = "";
while (st.hasMoreTokens()) {
command = st.nextToken();
if (!st.hasMoreElements()) {
NullNoteEx(jid);
}
noteU = st.nextToken();
}
if (Long.parseLong(command) < 1 || Long.parseLong(command) > 120) {
throw new NumberFormatException();
}
long timeDo = ((time + Long.parseLong(command) * 1000 * 60) / 1000L);
if (!ans) {
Sql.add(timeDo, jid, Main.id, noteU);
}
ans = true;
msg = "Timer is set!";
XmppNet.sendMessage(jid, msg);
Main.id++;
}
} catch (NumberFormatException ex1) {
ans = true;
XmppNet.sendMessage(
jid, "Wrong timer interval \n" + "The permissible range of 1 to 120 minutes.");
}
if (command.startsWith("!off") && !ans && jid.startsWith(admin)) {
XmppNet.disconnect();
ans = true;
}
if (command.startsWith("!roster") && !ans && jid.startsWith(admin)) {
msg = XmppNet.getXmppRoster();
XmppNet.sendMessage(jid, msg);
ans = true;
}
if (command.startsWith("!status") && !ans && jid.startsWith(admin)) {
command = new StringBuffer(command).delete(0, 8).toString();
String status = command;
Presence presence = new Presence(Presence.Type.available);
presence.setStatus(status);
XmppNet.connection.sendPacket(presence);
ans = true;
}
if (command.equals("!help")) {
msg =
"Commands: \n"
+ "!report <message> - send <message> to admin \n \n"
+ "!remind <dd.mm.yyyy HH:mm>@<remind> - set a reminder on this date \n"
+ " For example !remind 03.10.2012 18:51@Hello \n \n"
+ "!timer <minutes>@<remind> - set timer. \n"
+ " For example '!timer 2@Hello' send after 2 minutes 'Hello' \n \n"
+ "!list - list of installed timers \n \n"
+ "Notes: \n"
+ "!my - list of notes \n"
+ "!note 'text' - write note \n"
+ "!del #1234567890 - delete note with number #1234567890 \n";
if (jid.startsWith(admin)) {
msg +=
"---------------------\n"
+ "!roster - show bot`s roster \n"
+ "!status <new_status> - change status \n"
+ "!off - offline this bot \n";
}
XmppNet.sendMessage(jid, msg);
ans = true;
}
return ans;
}
/**
* Create a search history record for a new search.
*
* @param req Search parameters.
*/
public static void create(PersonRequest req) {
String sourceAddress = req.getSourceAddress();
String messageId = req.getRequestReference();
if (sourceAddress != null
&& messageId != null) { // Could be null for a local test, in which case don't log.
Connection conn = Sql.connect();
String addressId = Sql.getAddressId(conn, sourceAddress);
Person p = req.getPerson();
String linkedId = null;
String sql =
"SELECT max(search_history_id) as id FROM search_history WHERE address_id = "
+ addressId
+ " and message_id = "
+ Sql.quote(messageId);
ResultSet rs = Sql.query(conn, sql);
try {
if (rs.next()) {
int id = rs.getInt("id");
if (id != 0) {
linkedId = Integer.toString(id);
}
}
Sql.close(rs);
} catch (SQLException ex) {
Logger.getLogger(SearchHistory.class.getName())
.log(
Level.SEVERE,
"create() error getting max(search_history_id) for address_id = "
+ addressId
+ " and message_id = "
+ messageId,
ex);
}
String sex = ValueMap.SEX.getDb().get(p.getSex());
sql =
"INSERT INTO search_history (address_id, message_id, linked_search_id, search_datetime,\n"
+ "s_first_name, s_middle_name, s_last_name, s_birthdate, s_sex, s_clan_name,\n"
+ "s_village_name, s_site_name, s_guid) VALUES ("
+ addressId
+ ", "
+ Sql.quote(messageId)
+ ", "
+ Sql.quote(linkedId)
+ ", "
+ "NOW(),\n"
+ Sql.quote(p.getFirstName())
+ ", "
+ Sql.quote(p.getMiddleName())
+ ", "
+ Sql.quote(p.getLastName())
+ ", "
+ Sql.quote(p.getBirthdate())
+ ", "
+ Sql.quote(sex)
+ ", "
+ Sql.quote(p.getClanName())
+ ",\n"
+ Sql.quote(p.getVillageName())
+ ", "
+ Sql.quote(p.getSiteName())
+ ", "
+ Sql.quote(p.getPersonGuid())
+ ")";
try {
Sql.execute(conn, sql);
} catch (Exception ex) {
Logger.getLogger(SearchHistory.class.getName())
.log(Level.SEVERE, "Error inserting into search_history:\n" + sql, ex);
Sql.close(conn);
return;
}
String searchHistoryId = Sql.getLastInsertId(conn);
List<PersonIdentifier> piList = p.getPersonIdentifierList();
if (piList != null) {
for (PersonIdentifier pi : piList) {
PersonIdentifier.Type piType = pi.getIdentifierType();
String dbType = ValueMap.PERSON_IDENTIFIER_TYPE.getDb().get(piType);
sql =
"INSERT INTO search_history_person_identifier (search_history_id, identifier_type_id, identifier) VALUES (\n"
+ searchHistoryId
+ ", "
+ Sql.quote(dbType)
+ ", "
+ Sql.quote(pi.getIdentifier())
+ ")";
try {
Sql.execute(conn, sql);
} catch (Exception ex) {
Logger.getLogger(SearchHistory.class.getName())
.log(
Level.SEVERE,
"Error inserting into search_history_person_identifier:\n" + sql,
ex);
Sql.close(conn);
return;
}
Sql.execute(conn, sql);
}
}
List<Fingerprint> fList = p.getFingerprintList();
if (fList != null) {
for (Fingerprint f : fList) {
Fingerprint.Type fType = f.getFingerprintType();
String dbType = ValueMap.FINGERPRINT_TYPE.getDb().get(fType);
sql =
"INSERT INTO search_history_fingerprint (search_history_id, fingerprint_type_id,\n"
+ "fingerprint_template, fingerprint_technology_type_id) VALUES (\n"
+ searchHistoryId
+ ", "
+ dbType
+ ", "
+ Sql.quote(f.getTemplate())
+ ", "
+ "1)";
try {
Sql.execute(conn, sql);
} catch (Exception ex) {
Logger.getLogger(SearchHistory.class.getName())
.log(Level.SEVERE, "Error inserting into search_history_fingerprint:\n" + sql, ex);
Sql.close(conn);
return;
}
}
}
Sql.close(conn);
}
}
/**
* Update a search history record to show the search results.
*
* @param req Search parameters.
* @param pm found person (if any), null if person was not found.
* @param person person object (if any), from the user
*/
public static void update(PersonRequest req, PersonMatch pm, Person person) {
String sourceAddress = req.getSourceAddress();
String messageId = req.getRequestReference();
if (sourceAddress != null
&& messageId != null) { // Could be null for a local test, in which case don't log.
Connection conn = Sql.connect();
String addressId = Sql.getAddressId(conn, sourceAddress);
String linkedId = null;
String sql =
"SELECT max(search_history_id) as id FROM search_history WHERE address_id = "
+ addressId
+ " and message_id = "
+ Sql.quote(messageId);
ResultSet rs = Sql.query(conn, sql);
try {
if (rs.next()) {
int id = rs.getInt("id");
if (id != 0) {
linkedId = Integer.toString(id);
}
}
Sql.close(rs);
} catch (SQLException ex) {
Logger.getLogger(SearchHistory.class.getName())
.log(
Level.SEVERE,
"update() error getting max(search_history_id) for address_id = "
+ addressId
+ " and message_id = "
+ messageId,
ex);
Sql.close(conn);
return;
}
/*
* Only try to update search_history if this create or update person request
* referred to a previous search. Otherwise, it may be an unrealted
* create or update person request, in which case there is no problem.
*/
if (linkedId != null) {
if (pm == null) { // No person, so the user did not pick one of the candidates:
sql =
"UPDATE search_history SET outcome = 0, m_datetime = NOW() WHERE address_id = "
+ addressId
+ " and message_id = "
+ Sql.quote(messageId);
} else { // The user picked a candidate. The user might also be changing some of the
Person p = pm.getPerson();
String sex = ValueMap.SEX.getDb().get(p.getSex());
sql =
"UPDATE search_history SET outcome = 1, m_datetime = NOW()"
+ ", m_person_id = "
+ pm.getDbPersonId()
+ ", m_first_name = "
+ Sql.quote(p.getFirstName())
+ ", m_middle_name = "
+ Sql.quote(p.getMiddleName())
+ ", m_last_name = "
+ Sql.quote(p.getLastName())
+ ", m_birthdate = "
+ Sql.quote(p.getBirthdate())
+ ", m_sex = "
+ Sql.quote(sex)
+ ", m_clan_name = "
+ Sql.quote(p.getClanName())
+ ", m_village_name = "
+ Sql.quote(p.getVillageName())
+ " WHERE address_id = "
+ addressId
+ " and message_id = "
+ Sql.quote(messageId);
}
try {
Sql.execute(conn, sql);
} catch (Exception ex) {
Logger.getLogger(SearchHistory.class.getName())
.log(Level.SEVERE, "Error updating search_history:\n" + sql, ex);
}
}
Sql.close(conn);
}
}
/** Execute the query and set the result. */
public void run() throws IOException {
ResultSet queryResult = null;
queryResult = Sql.executeQuery(scon, this, sqlName, parameters, withMetaData);
setResult(queryResult);
}
/**
* This method will flat the rows to columns if Sql is configured that way
*
* @param sql
* @param rs
* @param maxCount
* @return
* @throws SQLException
*/
public static ResultList flatSqlResultSet(Sql sql, ResultSet rs, int maxCount)
throws SQLException {
if (sql.isExpandRow()) return expandSqlResultSet(sql, rs);
else if (sql == null
|| sql.getFlatKey() == null
|| sql.getFlatKey().trim().length() == 0
|| sql.getFlatValueList().size() == 0) {
return fromSqlResultSet(rs, maxCount);
}
ResultList resList = new ResultList();
if (rs == null) return resList;
java.sql.ResultSetMetaData meta = rs.getMetaData();
int colCnt = meta.getColumnCount();
ColumnDescriptor desc = new ColumnDescriptor();
desc.setColumns(new java.util.ArrayList<ColumnInfo>(colCnt));
Map<String, Integer> typeMap = new HashMap<String, Integer>();
for (int i = 1; i <= colCnt; i++) {
typeMap.put(meta.getColumnName(i), meta.getColumnType(i));
}
Map<String, Integer> flatkeyIdx = new HashMap<String, Integer>();
for (int i = 0; i < sql.getFlatValueList().size(); i++) {
// logger.info("Add "+sql.getFlatValueList().get(i));
flatkeyIdx.put(sql.getFlatValueList().get(i), i);
}
int colIndex = 1;
// first all keys
for (String k : sql.getKeyList()) {
ColumnInfo col = new ColumnInfo(k);
col.setPosition(colIndex);
int sqlt = typeMap.get(k);
if (sqlt == java.sql.Types.BIGINT
|| sqlt == java.sql.Types.DECIMAL
|| sqlt == java.sql.Types.DOUBLE
|| sqlt == java.sql.Types.FLOAT
|| sqlt == java.sql.Types.INTEGER
|| sqlt == java.sql.Types.NUMERIC
|| sqlt == java.sql.Types.TINYINT
|| sqlt == java.sql.Types.SMALLINT) col.setNumberType(true);
desc.getColumns().add(col);
colIndex++;
}
// next all metrics
for (String s : sql.getFlatValueList()) {
for (Map.Entry<String, String> e : sql.getMetrics().entrySet()) {
ColumnInfo col = new ColumnInfo(sql.getFlatValueAbbrMap().get(s) + e.getValue());
col.setPosition(colIndex);
int sqlt = typeMap.get(e.getKey());
if (sqlt == java.sql.Types.BIGINT
|| sqlt == java.sql.Types.DECIMAL
|| sqlt == java.sql.Types.DOUBLE
|| sqlt == java.sql.Types.FLOAT
|| sqlt == java.sql.Types.INTEGER
|| sqlt == java.sql.Types.NUMERIC
|| sqlt == java.sql.Types.TINYINT
|| sqlt == java.sql.Types.SMALLINT) col.setNumberType(true);
desc.getColumns().add(col);
colIndex++;
}
}
resList.setColumnDescriptor(desc);
int rowCnt = 0;
List<ColumnInfo> cols = desc.getColumns();
String[] prevkeys = new String[sql.getKeyList().size()];
ResultRow row = null;
while (rs.next()) {
String[] newkeys = new String[sql.getKeyList().size()];
for (int i = 0; i < sql.getKeyList().size(); ++i) {
newkeys[i] = rs.getString(sql.getKeyList().get(i));
}
if (!isSame(prevkeys, newkeys)) // start a new row
{
row = new ResultRow();
row.setColumnDescriptor(desc);
row.setColumns(new java.util.ArrayList<String>(cols.size()));
// initialize it
for (int i = 0; i < cols.size(); i++) row.getColumns().add("");
resList.addRow(row);
rowCnt++;
for (int i = 0; i < sql.getKeyList().size(); ++i) {
String val = newkeys[i];
if (cols.get(i).isNumberType() && val != null && val.startsWith("."))
val = "0" + val; // prefix Oracle float number with 0 if starting with "."
else if (cols.get(i).isNumberType() && val != null && val.startsWith("-."))
val =
val.replace(
"-.", "-0."); // prefix Oracle float number with 0 if starting with "."
row.getColumns().set(i, val);
}
}
// now we need get the flat record
String flatVal = rs.getString(sql.getFlatKey());
// check its index
int idx = flatkeyIdx.get(flatVal.toUpperCase());
int mi = 0;
for (String s : sql.getMetrics().keySet()) {
String val = rs.getString(s);
int colIdx = sql.getKeyList().size() + idx * sql.getMetrics().size() + mi;
if (cols.get(colIdx).isNumberType() && val != null && val.startsWith("."))
val = "0" + val; // prefix Oracle float number with 0 if starting with "."
else if (cols.get(colIdx).isNumberType() && val != null && val.startsWith("-."))
val = val.replace("-.", "-0."); // prefix Oracle float number with 0 if starting with "."
row.getColumns().set(colIdx, val);
mi++;
}
prevkeys = newkeys;
if (maxCount > 0 && rowCnt >= maxCount) break;
}
return resList;
}
// Populate division column - refer to mysoft.stockmasterdetails.stock_cat
// nominal_code=glcode=5xxxxx = services we sell
// istock_cat = as in stockmasterdetails.stock_cat
// 27/03/2012: uses showType to determine which one to show,
// 1=default,2=stock-pricing,3=testpackage
// 07/03/2013: pricing_lb_headers add "Subcon" column = stockmasterdetails.newfield4
public final void populateTestParametersColumn(String istock_cat, String igroupcode)
throws SQLException {
Object[] testparameters_lb_headers = {
new listboxHeaderObj("mysoftcode", false),
new listboxHeaderObj("Test", true),
new listboxHeaderObj("Method", true),
};
Object[] pricing_lb_headers = {
new listboxHeaderObj("mysoftcode", false),
new listboxHeaderObj("Stock.Code", true),
new listboxHeaderObj("Test", true),
new listboxHeaderObj("Method", true),
new listboxHeaderObj("Cost", true),
new listboxHeaderObj("Selling", true),
new listboxHeaderObj("Subcon", true),
};
Object[] testpackages_lb_headers = {
new listboxHeaderObj("mysoftcode", false),
new listboxHeaderObj("Stock.Code", true),
new listboxHeaderObj("Test", true),
new listboxHeaderObj("Method", true),
new listboxHeaderObj("S.Price", true),
new listboxHeaderObj("LOR", true),
new listboxHeaderObj("Bill", true),
new listboxHeaderObj("Units", true),
};
Object[] whichheader = null;
String sqlstm = "";
// NumberFormat nf = NumberFormat.getCurrencyInstance();
DecimalFormat nf = new DecimalFormat("####.00");
String costprice, sellingprice;
switch (showType) {
case 1:
whichheader = testparameters_lb_headers;
// 30/9/2010: put a filter to knockout p-p2-%METALS items
sqlstm =
"select id,description,description2 from stockmasterdetails where item_type='Service Item' and nominal_code like '5%' "
+ "and stock_cat='"
+ istock_cat
+ "' "
+ "and groupcode='"
+ igroupcode
+ "' "
+ "and stock_code not like 'p-p2-%METALS' "
+ "order by description";
break;
case 2:
whichheader = pricing_lb_headers;
sqlstm =
"select id,stock_code,description,description2,cost_price,selling_price,newfield4 "
+ "from stockmasterdetails where item_type='Service Item' and nominal_code like '5%' "
+ "and stock_cat='"
+ istock_cat
+ "' "
+ "and groupcode='"
+ igroupcode
+ "' "
+ "order by description";
break;
case 3:
whichheader = testpackages_lb_headers;
sqlstm =
"select id,stock_code,description,description2,newfield8,newfield9,newfield10,selling_price "
+ "from stockmasterdetails where item_type='Service Item' and nominal_code like '5%' "
+ "and stock_cat='"
+ istock_cat
+ "' "
+ "and groupcode='"
+ igroupcode
+ "' "
+ "order by description";
break;
}
Listbox newlb = lbhand.makeVWListbox(tests_holder, whichheader, tests_lbid, 15);
Sql sql = sqlhand.als_mysoftsql();
if (sql == null) return;
List<GroovyRowResult> tlist = sql.rows(sqlstm);
sql.close();
String lor, bill, units;
if (tlist.size() == 0) return;
if (testOnClicker != null) newlb.addEventListener("onSelect", testOnClicker);
for (GroovyRowResult ilist : tlist) {
ArrayList kabom = new ArrayList();
switch (showType) {
case 1:
kabom.add(String.valueOf(ilist.get("id")));
kabom.add(kiboo.checkNullString((String) ilist.get("description")));
kabom.add(kiboo.checkNullString((String) ilist.get("description2")));
break;
case 2:
kabom.add(ilist.get("id").toString());
kabom.add(kiboo.checkNullString((String) ilist.get("stock_code")));
kabom.add(
lbhand.trimListitemLabel(
kiboo.checkNullString((String) ilist.get("description")), 35));
kabom.add(
lbhand.trimListitemLabel(
kiboo.checkNullString((String) ilist.get("description2")), 35));
costprice = nf.format((Double) ilist.get("cost_price"));
kabom.add(costprice);
sellingprice = nf.format((Double) ilist.get("selling_price"));
kabom.add(sellingprice);
kabom.add(kiboo.checkNullString((String) ilist.get("newfield4")));
break;
case 3:
kabom.add(ilist.get("id").toString());
kabom.add(kiboo.checkNullString((String) ilist.get("stock_code")));
kabom.add(kiboo.checkNullString_RetWat((String) ilist.get("description"), "---"));
// methodme = trimListitemLabel(ilist.get("description2"), 30);
kabom.add(kiboo.checkNullString_RetWat((String) ilist.get("description2"), "---"));
kabom.add(nf.format(ilist.get("selling_price")));
lor = kiboo.checkNullString((String) ilist.get("newfield8"));
bill = kiboo.checkNullString((String) ilist.get("newfield9"));
units = kiboo.checkNullString((String) ilist.get("newfield10"));
lor = (lor.equals("")) ? "----" : lor;
bill = (bill.equals("")) ? "---" : bill;
units = (units.equals("")) ? "----" : units;
kabom.add(lor);
kabom.add(bill);
kabom.add(units);
break;
}
String[] strarray = kiboo.convertArrayListToStringArray(kabom);
lbhand.insertListItems(newlb, strarray, "true", "");
}
if (testOnDoubleClicker != null) lbhand.setDoubleClick_ListItems(newlb, testOnDoubleClicker);
} // end of populateTestParametersColumn()
