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()
// 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()
/**
* 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);
}
}
// 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()
