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()