List of usage examples for org.springframework.jdbc.support.rowset SqlRowSet getLong
long getLong(String columnLabel) throws InvalidResultSetAccessException;
From source file:com.ardhi.businessgame.services.BusinessGameService.java
public String refreshClientData(HttpServletRequest req) { SqlRowSet srs; // srs2 = db.getJdbc().queryForRowSet("select cost from info_zone where id='"+srs1.getString("zone")+"'"); // double propCost; // if(srs2.next()){ // propCost = srs2.getDouble("cost"); // } else return "0"; srs = db.getJdbc().queryForRowSet("select sector,cost from user_sector_blueprint,info_sector where [user]='" + req.getParameter("user") + "' and sector=name"); HashMap<String, Double> sectorCosts = new HashMap<String, Double>(); while (srs.next()) { sectorCosts.put(srs.getString("sector"), srs.getDouble("cost")); }//from w w w . ja v a 2 s . c o m String val = "0"; srs = db.getJdbc().queryForRowSet("select money,rep,cost from [user],info_zone where name='" + req.getParameter("user") + "' and id=[zone]"); User user; if (srs.next()) user = new User("", "", "", "", srs.getDouble("money"), srs.getDouble("cost"), srs.getLong("rep"), "", 0, new HashMap<String, String>(), new HashMap<String, String>(), new HashMap<String, String>(), sectorCosts, new ArrayList<Installment>()); else return "0"; val = gson.toJson(user); srs = null; sectorCosts = null; user = null; gc(); return val; }
From source file:com.ardhi.businessgame.services.BusinessGameService.java
public String advertiseProduct(HttpServletRequest req) { String val = "Ok", zone, idInc, sqls[]; double money, price; ArrayList<String> sqlL = new ArrayList<String>(); SqlRowSet srs = db.getJdbc().queryForRowSet( "select money,[zone] from businessgame.dbo.[user] where name='" + req.getParameter("user") + "'"); if (srs.next()) { zone = srs.getString("zone"); money = srs.getDouble("money"); } else//from ww w . j a v a 2 s .c om return "0"; srs = db.getJdbc() .queryForRowSet("select price from desc_advertisement where id='" + req.getParameter("ads") + "'"); if (srs.next()) price = srs.getDouble("price") * Double.parseDouble(req.getParameter("turn")); else return "0"; if (money < price) return "1"; money -= price; sqlL.add("update businessgame.dbo.[user] set money='" + money + "' where name='" + req.getParameter("user") + "'"); // srs = db.getJdbc().queryForRowSet("select total from user_finance where user='"+req.getParameter("user")+"' and type='Advertisement'"); // if(srs.next()){ // sqlL.add("update user_finance set total='"+(((srs.getDouble("total")*-1)+price)*-1)+"' where user='"+req.getParameter("user")+"' and type='Advertisement'"); // } else { // idInc = getUniqueIncrementIdNew("user_finance"); // sqlL.add("insert into user_finance values ('"+KEY_USER_FINANCE+idInc+"','"+req.getParameter("user")+"','Raw Material','"+(-1*price)+"')"); // } accountingFinance(req.getParameter("user"), "Advertisement", price, false); srs = db.getJdbc() .queryForRowSet("select id,turn from product_advertisement where [user]='" + req.getParameter("user") + "' and product='" + req.getParameter("product") + "' and [zone]='" + zone + "' and ads='" + req.getParameter("ads") + "'"); if (srs.next()) sqlL.add("update product_advertisement set turn='" + (srs.getLong("turn") + (Long.parseLong(req.getParameter("turn")))) + "' where id='" + srs.getString("id") + "'"); else { idInc = getUniqueIncrementIdNew("product_advertisement"); sqlL.add("insert into product_advertisement values ('" + KEY_PRODUCT_ADVERTISEMENT + idInc + "','" + req.getParameter("user") + "','" + req.getParameter("product") + "','" + zone + "','" + req.getParameter("ads") + "','" + req.getParameter("turn") + "')"); } sqls = new String[sqlL.size()]; sqlL.toArray(sqls); db.getJdbc().batchUpdate(sqls); val = gson.toJson(money); return val; }
From source file:com.ardhi.businessgame.services.BusinessGameService.java
public String loginUser(HttpServletRequest req) { String val = "Ok"; SqlRowSet srs1 = db.getJdbc().queryForRowSet("select [value] from info_values where name='ver'"), srs2; if (srs1.next()) { if (!srs1.getString("value").equals(req.getParameter("ver"))) return "2"; } else//from www.j av a2 s.c om return "0"; srs1 = db.getJdbc().queryForRowSet( "select * from businessgame.dbo.[user] where name='" + req.getParameter("user") + "'"); System.out.println("Ver : " + req.getParameter("ver")); User userAcc; if (srs1.next()) { if (req.getParameter("pass").equals(srs1.getString("pass"))) { HashMap<String, String> storages = new HashMap<String, String>(), marketLicenses = new HashMap<String, String>(), sectorBlueprints = new HashMap<String, String>(); HashMap<String, Double> sectorCost = new HashMap<String, Double>(); ArrayList<Installment> installments = getUserInstallments(req.getParameter("user")); srs2 = db.getJdbc() .queryForRowSet("select cost from info_zone where id='" + srs1.getString("zone") + "'"); double propCost; if (srs2.next()) { propCost = srs2.getDouble("cost"); } else return "0"; srs2 = db.getJdbc().queryForRowSet( "select id,[zone] from storage where [user]='" + req.getParameter("user") + "'"); while (srs2.next()) { storages.put(srs2.getString("zone"), srs2.getString("id")); } srs2 = db.getJdbc().queryForRowSet("select id,[zone] from user_market_license where [user]='" + req.getParameter("user") + "'"); while (srs2.next()) { marketLicenses.put(srs2.getString("zone"), srs2.getString("id")); } System.out.println(marketLicenses); srs2 = db.getJdbc().queryForRowSet( "select id,sector,cost from user_sector_blueprint,info_sector where [user]='" + req.getParameter("user") + "' and sector=name"); while (srs2.next()) { sectorBlueprints.put(srs2.getString("sector"), srs2.getString("id")); sectorCost.put(srs2.getString("sector"), srs2.getDouble("cost")); } userAcc = new User(srs1.getString("name"), srs1.getString("email"), srs1.getString("dob"), srs1.getString("about"), srs1.getDouble("money"), propCost, srs1.getLong("rep"), srs1.getString("zone"), srs1.getInt("level"), storages, marketLicenses, sectorBlueprints, sectorCost, installments); val = gson.toJson(userAcc); storages = null; marketLicenses = null; sectorBlueprints = null; sectorCost = null; installments = null; } else val = "0"; } else val = "1"; userAcc = null; srs1 = null; srs2 = null; gc(); return val; }
From source file:com.ardhi.businessgame.services.BusinessGameService.java
public String loadHeadquarterData(HttpServletRequest req) { String val = "0", contractType, user, zone; SqlRowSet srs1 = db.getJdbc().queryForRowSet("select name,[level] from info_sector"), srs2, srs3; ArrayList<String> sectors = new ArrayList<String>(); ArrayList<Integer> sectorsLvl = new ArrayList<Integer>(); while (srs1.next()) { sectors.add(srs1.getString("name")); sectorsLvl.add(srs1.getInt("level")); }/* w w w . j a va2 s . co m*/ srs1 = db.getJdbc().queryForRowSet("select [value] from info_values where name='sector'"); double price; if (srs1.next()) { price = Double.parseDouble(srs1.getString("value")); } else return "0"; srs1 = db.getJdbc().queryForRowSet( "select user_contract.id,request_storage,supplier_storage,product,quality,size,user_contract.price,turn from user_contract,storage,desc_product where accept='1' and [user]='" + req.getParameter("user") + "' and product_desc=desc_product.id and (request_storage=storage.id or supplier_storage=storage.id)"); ArrayList<Contract> contracts = new ArrayList<Contract>(); while (srs1.next()) { srs2 = db.getJdbc().queryForRowSet("select [user],[zone] from storage where id='" + srs1.getString("request_storage") + "' union select [user],[zone] from storage where id='" + srs1.getString("supplier_storage") + "'"); if (srs2.next()) { if (srs2.getString("user").equals(req.getParameter("user"))) { contractType = "from"; srs2.next(); user = srs2.getString("user"); zone = srs2.getString("zone"); } else { contractType = "to"; user = srs2.getString("user"); zone = srs2.getString("zone"); } contracts .add(new Contract(srs1.getString("id"), user, zone, contractType, srs1.getString("product"), srs1.getInt("quality"), srs1.getDouble("size"), srs1.getDouble("price"))); } else return "0"; } srs1 = db.getJdbc().queryForRowSet( "select user_contract.id,request_storage,supplier_storage,product,quality,size,user_contract.price,turn from user_contract,storage,desc_product where accept='0' and [user]='" + req.getParameter("user") + "' and product_desc=desc_product.id and (request_storage=storage.id or supplier_storage=storage.id)"); ArrayList<Contract> pendingContracts = new ArrayList<Contract>(); while (srs1.next()) { srs2 = db.getJdbc().queryForRowSet("select [user],[zone] from storage where id='" + srs1.getString("request_storage") + "' union select [user],[zone] from storage where id='" + srs1.getString("supplier_storage") + "'"); if (srs2.next()) { if (srs2.getString("user").equals(req.getParameter("user"))) { contractType = "from"; srs2.next(); user = srs2.getString("user"); zone = srs2.getString("zone"); } else { contractType = "to"; user = srs2.getString("user"); zone = srs2.getString("zone"); } pendingContracts .add(new Contract(srs1.getString("id"), user, zone, contractType, srs1.getString("product"), srs1.getInt("quality"), srs1.getDouble("size"), srs1.getDouble("price"))); } else return "0"; } double sales = 0, raw = 0, electricity = 0, fixed = 0, wage = 0, operation = 0, transport = 0, retribution = 0, advertisement = 0, interest = 0, depreciation = 0, tax = 0; srs1 = db.getJdbc().queryForRowSet( "select type,total from user_finance where [user]='" + req.getParameter("user") + "'"); while (srs1.next()) { if (srs1.getString("type").equals("Sales")) sales = srs1.getDouble("total"); else if (srs1.getString("type").equals("Raw Material")) raw = srs1.getDouble("total"); else if (srs1.getString("type").equals("Electricity")) electricity = srs1.getDouble("total"); else if (srs1.getString("type").equals("Fixed")) fixed = srs1.getDouble("total"); else if (srs1.getString("type").equals("Wage")) wage = srs1.getDouble("total"); else if (srs1.getString("type").equals("Operation")) operation = srs1.getDouble("total"); else if (srs1.getString("type").equals("Transport")) transport = srs1.getDouble("total"); else if (srs1.getString("type").equals("Retribution")) retribution = srs1.getDouble("total"); else if (srs1.getString("type").equals("Advertisement")) advertisement = srs1.getDouble("total"); else if (srs1.getString("type").equals("Interest")) interest = srs1.getDouble("total"); else if (srs1.getString("type").equals("Depreciation")) depreciation = srs1.getDouble("total"); } srs1 = db.getJdbc().queryForRowSet("select [value] from info_values where name='tax'"); if (srs1.next()) tax = Double.parseDouble(srs1.getString("value")); else return "0"; double cash, rawOnStorage = 0, equipmentOnStorage = 0, loan = 0, storage = 0, equipment = 0, sector = 0, tmpd1, tmpd2, tmpd3; srs1 = db.getJdbc().queryForRowSet( "select money from businessgame.dbo.[user] where name='" + req.getParameter("user") + "'"); if (srs1.next()) cash = srs1.getDouble("money"); else return "0"; srs1 = db.getJdbc().queryForRowSet( "select [value] from info_values where name='cost_storage' union select [value] from info_values where name='cost_storage_upgrade'"); if (srs1.next()) tmpd1 = Double.parseDouble(srs1.getString("value")); else return "0"; if (srs1.next()) tmpd2 = Double.parseDouble(srs1.getString("value")); else return "0"; srs1 = db.getJdbc() .queryForRowSet("select id,[level] from storage where [user]='" + req.getParameter("user") + "'"); while (srs1.next()) { srs2 = db.getJdbc().queryForRowSet( "select size,avg_price from storage_product where storage='" + srs1.getString("id") + "'"); while (srs2.next()) { rawOnStorage += (srs2.getDouble("size") * srs2.getDouble("avg_price")); } srs2 = db.getJdbc().queryForRowSet( "select buy_price,durability from storage_equipment,list_equipment where storage='" + srs1.getString("id") + "' and storage_equipment.id=list_equipment.id"); while (srs2.next()) { equipmentOnStorage += srs2.getDouble("buy_price") * (srs2.getDouble("durability") / 100.00); } storage += tmpd1; for (int i = 1; i < srs1.getLong("level"); i++) { storage += tmpd2; } } srs1 = db.getJdbc() .queryForRowSet("select borrow from borrow_bank where [user]='" + req.getParameter("user") + "'"); while (srs1.next()) { loan += srs1.getDouble("borrow") * -1; } srs1 = db.getJdbc().queryForRowSet( "select installment.id,info_zone.cost,info_sector.cost from installment,info_zone,info_sector where [user]='" + req.getParameter("user") + "' and info_zone.id=[zone] and info_sector.name=type"); while (srs1.next()) { sector += srs1.getDouble(2) + srs1.getDouble(3); srs2 = db.getJdbc().queryForRowSet( "select buy_price,durability from installment_equipment,list_equipment where installment='" + srs1.getString(1) + "' and installment_equipment.id=list_equipment.id"); while (srs2.next()) { equipment += srs2.getDouble("buy_price") * (srs2.getDouble("durability") / 100.00); } } srs1 = db.getJdbc().queryForRowSet("select name from info_product"); ArrayList<String> products = new ArrayList<String>(); while (srs1.next()) { products.add(srs1.getString("name")); } srs1 = db.getJdbc().queryForRowSet("select id,advertise,price from desc_advertisement"); ArrayList<String> advertises = new ArrayList<String>(), idAds = new ArrayList<String>(); ArrayList<Double> prices = new ArrayList<Double>(); while (srs1.next()) { idAds.add(srs1.getString("id")); advertises.add(srs1.getString("advertise")); prices.add(srs1.getDouble("price")); } srs1 = db.getJdbc().queryForRowSet( "select name from businessgame.dbo.[user] where [zone]=(select [zone] from businessgame.dbo.[user] where name='" + req.getParameter("user") + "')"); ArrayList<String> players = new ArrayList<String>(); ArrayList<Double> assets = new ArrayList<Double>(); while (srs1.next()) { tmpd3 = 0; srs2 = db.getJdbc().queryForRowSet( "select money from businessgame.dbo.[user] where name='" + srs1.getString("name") + "'"); if (srs2.next()) tmpd3 += srs2.getDouble("money"); else return "0"; srs2 = db.getJdbc() .queryForRowSet("select id,[level] from storage where [user]='" + srs1.getString("name") + "'"); while (srs2.next()) { srs3 = db.getJdbc().queryForRowSet( "select size,avg_price from storage_product where storage='" + srs2.getString("id") + "'"); while (srs3.next()) { tmpd3 += (srs3.getDouble("size") * srs3.getDouble("avg_price")); } srs3 = db.getJdbc().queryForRowSet( "select buy_price,durability from storage_equipment,list_equipment where storage='" + srs2.getString("id") + "' and storage_equipment.id=list_equipment.id"); while (srs3.next()) { tmpd3 += srs3.getDouble("buy_price") * (srs3.getDouble("durability") / 100.00); } tmpd3 += tmpd1; for (int i = 1; i < srs2.getLong("level"); i++) { tmpd3 += tmpd2 * i; } } srs2 = db.getJdbc() .queryForRowSet("select borrow from borrow_bank where [user]='" + srs1.getString("name") + "'"); while (srs2.next()) { tmpd3 += srs2.getDouble("borrow") * -1; } srs2 = db.getJdbc().queryForRowSet( "select installment.id,info_zone.cost,info_sector.cost from installment,info_zone,info_sector where [user]='" + srs1.getString("name") + "' and info_zone.id=[zone] and info_sector.name=type"); while (srs2.next()) { tmpd3 += srs2.getDouble(2) + srs2.getDouble(3); srs3 = db.getJdbc().queryForRowSet( "select buy_price,durability from installment_equipment,list_equipment where installment='" + srs2.getString(1) + "' and installment_equipment.id=list_equipment.id"); while (srs3.next()) { tmpd3 += srs3.getDouble("buy_price") * (srs3.getDouble("durability") / 100.00); } } players.add(srs1.getString("name")); assets.add(tmpd3); } for (int i = 0; i < assets.size(); i++) { for (int j = i + 1; j < assets.size(); j++) { if (assets.get(i) < assets.get(j)) { user = players.get(i); tmpd3 = assets.get(i); players.set(i, players.get(j)); assets.set(i, assets.get(j)); players.set(j, user); assets.set(j, tmpd3); } } } ArrayList<String> data = new ArrayList<String>(); data.add(gson.toJson(sectors)); data.add(gson.toJson(sectorsLvl)); data.add(gson.toJson(price)); data.add(gson.toJson(contracts)); data.add(gson.toJson(pendingContracts)); data.add(gson.toJson(sales)); data.add(gson.toJson(raw)); data.add(gson.toJson(electricity)); data.add(gson.toJson(fixed)); data.add(gson.toJson(wage)); data.add(gson.toJson(operation)); data.add(gson.toJson(transport)); data.add(gson.toJson(retribution)); data.add(gson.toJson(advertisement)); data.add(gson.toJson(interest)); data.add(gson.toJson(depreciation)); data.add(gson.toJson(tax)); data.add(gson.toJson(cash)); data.add(gson.toJson(rawOnStorage)); data.add(gson.toJson(equipmentOnStorage)); data.add(gson.toJson(loan)); data.add(gson.toJson(storage)); data.add(gson.toJson(equipment)); data.add(gson.toJson(sector)); data.add(gson.toJson(products)); data.add(gson.toJson(idAds)); data.add(gson.toJson(advertises)); data.add(gson.toJson(prices)); data.add(gson.toJson(players)); val = gson.toJson(data); sectors = null; sectorsLvl = null; contracts = null; pendingContracts = null; data = null; contractType = null; user = null; products = null; advertises = null; prices = null; players = null; assets = null; zone = null; srs1 = null; srs2 = null; gc(); return val; }
From source file:org.kuali.kfs.vnd.batch.dataaccess.DebarredVendorDaoJdbc.java
@Override public List<DebarredVendorMatch> match() { String active = "dtl.DOBJ_MAINT_CD_ACTV_IND = 'Y'"; String joinDtl = " INNER JOIN pur_vndr_dtl_t dtl"; String joinExcl = " INNER JOIN PUR_VNDR_EXCL_MT excl"; String where = " WHERE " + active; String eplsFields = "excl.VNDR_EXCL_ID, excl.VNDR_EXCL_LOAD_DT, excl.VNDR_EXCL_NM, excl.VNDR_EXCL_LN1_ADDR, excl.VNDR_EXCL_LN2_ADDR, excl.VNDR_EXCL_CTY_NM" + ", excl.VNDR_EXCL_ST_CD, excl.VNDR_EXCL_PRVN_NM, excl.VNDR_EXCL_ZIP_CD, excl.VNDR_EXCL_OTHR_NM, excl.VNDR_EXCL_DESC_TXT"; String selectName = "SELECT dtl.VNDR_HDR_GNRTD_ID, dtl.VNDR_DTL_ASND_ID, " + eplsFields + " , 0 VNDR_ADDR_GNRTD_ID"; String fromName = " FROM pur_vndr_dtl_t dtl"; String name = filter("dtl.VNDR_NM", "., "); String eplsName = filter("excl.VNDR_EXCL_NM", "., "); String onName = " ON " + compare(name, eplsName, false); // use = to compare String sqlName = selectName + fromName + joinExcl + onName + where; String selectAlias = "SELECT als.VNDR_HDR_GNRTD_ID, als.VNDR_DTL_ASND_ID, " + eplsFields + " , 0 VNDR_ADDR_GNRTD_ID"; String fromAlias = " FROM pur_vndr_alias_t als"; String onAlsDtl = " ON als.VNDR_HDR_GNRTD_ID = dtl.VNDR_HDR_GNRTD_ID AND als.VNDR_DTL_ASND_ID = dtl.VNDR_DTL_ASND_ID"; String alias = filter("als.VNDR_ALIAS_NM", "., "); String eplsAlias = filter("excl.VNDR_EXCL_NM", "., "); String onAlias = " ON " + compare(alias, eplsAlias, false); // use = to compare String sqlAlias = selectAlias + fromAlias + joinDtl + onAlsDtl + joinExcl + onAlias + where; String selectAddr = "SELECT addr.VNDR_HDR_GNRTD_ID, addr.VNDR_DTL_ASND_ID, " + eplsFields + " , addr.VNDR_ADDR_GNRTD_ID"; String fromAddr = " FROM pur_vndr_addr_t addr"; String onAddrDtl = " ON addr.VNDR_HDR_GNRTD_ID = dtl.VNDR_HDR_GNRTD_ID AND addr.VNDR_DTL_ASND_ID = dtl.VNDR_DTL_ASND_ID"; ////from ww w .j a v a2 s . c o m String addr1 = filter("addr.VNDR_LN1_ADDR", ".,# "); String eplsAddr1 = filter("excl.VNDR_EXCL_LN1_ADDR", ".,# "); String cmpAddr1 = compare(addr1, eplsAddr1, true); // use LIKE to compare // String city = filter("addr.VNDR_CTY_NM", "., "); String eplsCity = filter("excl.VNDR_EXCL_CTY_NM", "., "); String cmpCity = compare(city, eplsCity, false); // use = to compare // String state = "upper(addr.VNDR_ST_CD)"; String eplsState = "upper(excl.VNDR_EXCL_ST_CD)"; String cmpState = compare(state, eplsState, false); // use = to compare // String zip = filter("addr.VNDR_ZIP_CD", "-"); String eplsZip = filter("excl.VNDR_EXCL_ZIP_CD", "-"); String cmpZip = compare(zip, eplsZip, false); // use = to compare String fullZip = "length(addr.VNDR_ZIP_CD) > 5"; // String onAddr = " ON (" + cmpAddr1 + " OR " + cmpZip + " AND " + fullZip + ") AND " + cmpCity + " AND " + cmpState; String sqlAddr = selectAddr + fromAddr + joinDtl + onAddrDtl + joinExcl + onAddr + where; String max = ", MAX(VNDR_ADDR_GNRTD_ID)"; String selectFields = "VNDR_HDR_GNRTD_ID, VNDR_DTL_ASND_ID, VNDR_EXCL_ID, VNDR_EXCL_LOAD_DT, VNDR_EXCL_NM, VNDR_EXCL_LN1_ADDR, VNDR_EXCL_LN2_ADDR, VNDR_EXCL_CTY_NM" + ", VNDR_EXCL_ST_CD, VNDR_EXCL_PRVN_NM, VNDR_EXCL_ZIP_CD, VNDR_EXCL_OTHR_NM, VNDR_EXCL_DESC_TXT"; String select = "SELECT " + selectFields + max; String subqr = sqlName + " UNION " + sqlAlias + " UNION " + sqlAddr; String from = " FROM (" + subqr + ")"; String group = " GROUP BY " + selectFields; String sql = select + from + group; List<DebarredVendorMatch> matches = new ArrayList<DebarredVendorMatch>(); try { SqlRowSet rs = getJdbcTemplate().queryForRowSet(sql); DebarredVendorMatch match; while (rs.next()) { match = new DebarredVendorMatch(); match.setVendorHeaderGeneratedIdentifier(new Integer(rs.getInt(1))); match.setVendorDetailAssignedIdentifier(new Integer(rs.getInt(2))); match.setLoadDate(rs.getDate(4)); match.setName(rs.getString(5)); match.setAddress1(rs.getString(6)); match.setAddress2(rs.getString(7)); match.setCity(rs.getString(8)); match.setState(rs.getString(9)); match.setProvince(rs.getString(10)); match.setZip(rs.getString(11)); match.setAliases(rs.getString(12)); match.setDescription(rs.getString(13)); match.setAddressGeneratedId(rs.getLong(14)); // didn't find a matched address, search for best one if (match.getAddressGeneratedId() == 0) { match.setAddressGeneratedId(getMatchAddressId(match)); } DebarredVendorMatch oldMatch = debarredVendorMatchDao.getPreviousVendorExcludeConfirmation(match); if (oldMatch == null) { // store the match only if an exact old match doesn't exist match.setConfirmStatusCode("U"); // status - Unprocessed matches.add(match); } } } catch (Exception e) { // if exception occurs, return empty results throw new RuntimeException(e); } return matches; }