@Override protected void onCreate(Bundle savedInstanceState) { // TODO Auto-generated method stub super.onCreate(savedInstanceState); setContentView(R.layout.activity_search_list); list = getListView(); backBtn = (ImageButton) findViewById(R.id.backBtn); priceBtn = (Button) findViewById(R.id.orderByPriceBtn); timeBtn = (Button) findViewById(R.id.orderByTimeBtn); tvnomatchfound = (TextView) findViewById(R.id.tvnomatchfound); backBtn.setOnClickListener( new View.OnClickListener() { @Override public void onClick(View arg0) { // TODO Auto-generated method stub new HomeFragment().refresh(); shared.fromString = ""; shared.toString = ""; shared.through1String = ""; shared.fromPrice = ""; shared.toPrice = ""; shared.departureDate = ""; shared.day = ""; if (shared.through2String.length() > 0) { shared.through2String = ""; } if (shared.through3String.length() > 0) { shared.through3String = ""; } if (HomeFragment.throughPath2 != null) { HomeFragment.throughPath2.setText(""); HomeFragment.throughPath2.setEnabled(true); HomeFragment.throughPath2.setFocusableInTouchMode(true); HomeFragment.throughPath2.setError(null); } if (HomeFragment.throughPath3 != null) { HomeFragment.throughPath3.setText(""); HomeFragment.throughPath3.setEnabled(true); HomeFragment.throughPath3.setFocusableInTouchMode(true); HomeFragment.throughPath3.setError(null); } HomeActivity.mFrameLayout.setVisibility(View.GONE); finish(); } }); priceBtn.setOnClickListener( new OnClickListener() { @Override public void onClick(View arg0) { // TODO Auto-generated method stub @SuppressWarnings("unused") String arrange = selectQuery("cost", true); Toast.makeText( getApplicationContext(), "Transports ordered in price", Toast.LENGTH_SHORT) .show(); if (price_clicked == false) { price_clicked = true; time_clicked = false; cur = dbHelper.getData(myquery + " order by transportation.cost" + " asc"); adpater1.cursor = cur; System.out.println("arrange"); adpater1.notifyDataSetChanged(); } } }); timeBtn.setOnClickListener( new OnClickListener() { @Override public void onClick(View arg0) { // TODO Auto-generated method stub @SuppressWarnings("unused") String arrange = selectQuery("cost", true); Toast.makeText( getApplicationContext(), "Transports ordered in time", Toast.LENGTH_SHORT) .show(); if (time_clicked == false) { price_clicked = false; time_clicked = true; cur = dbHelper.getData(myquery + " order by transportation.interval" + " asc"); adpater1.cursor = cur; adpater1.notifyDataSetChanged(); } } }); dbHelper = new MySQLiteHelper(getApplicationContext()); if (shared.vehicle != "all") v_id = dbHelper.getIdForName(dbHelper.TAG_TYPE, shared.vehicle); try { myquery = selectQuery("cost", true); } catch (Exception e) { } cur = dbHelper.getData(myquery); try { if (cur.getCount() == 0) { // here cur is null so we will have to check if direct path is // possible without other search criteria before giving // suggestions myquery = selectQuery("cost", false); Cursor cur1 = dbHelper.getData(myquery); if (cur1.getCount() == 0) { head = true; Intent intentnomatch = new Intent(getApplicationContext(), NoMatchFound.class); startActivityForResult(intentnomatch, 2); } else { // -------------------- list.setVisibility(View.GONE); tvnomatchfound.setVisibility(View.VISIBLE); } } } catch (Exception e) { } adpater1 = new MyArrayAdapter1(getApplicationContext(), cur); setListAdapter(adpater1); new HomeFragment().refresh(); }
public String selectQuery(String order, boolean check) { String MULTIPLE_VALID_ROUTE = ""; String single_route = ""; String ourCase = ""; String myquery = "select " + tablehelper.TransportationColumns.CONTACT_PHONE + ", " + tablehelper.TransportationColumns.DEPART_TIME + ", " + tablehelper.TransportationColumns.ARRIVAL_TIME + ", " + tablehelper.TransportationColumns.COST + ", " + tablehelper.TransportationColumns.REMARKS + ", " + tablehelper.TransportationColumns.TYPE_ID + ", " + tablehelper.TransportationColumns.ID + ", " + tablehelper.TransportationColumns.INTERVAL + ", " + tablehelper.TransportationColumns.ROUTE_ID + ", " + tablehelper.CompanyColumns.NAME + ", " + tablehelper.RouteColumns.FULL_ROUTE + " from " + tablehelper.TransportationColumns.TABLENAME + " JOIN " + tablehelper.ScheduleColumns.TABLENAME + " ON " + tablehelper.ScheduleColumns.TRANSPORTATION_ID + " = " + tablehelper.TransportationColumns.ID + " JOIN " + tablehelper.CompanyColumns.TABLENAME + " ON " + tablehelper.CompanyColumns.ID + " = " + tablehelper.TransportationColumns.COMPANY_ID + " JOIN " + tablehelper.RouteColumns.TABLENAME + " ON " + tablehelper.TransportationColumns.ROUTE_ID + " = " + tablehelper.RouteColumns.ID + " where route.full_route like '%" + shared.fromString + "%' and route.full_route like '%" + shared.toString + "%' and ('" + shared.fromString + "' = (select name from node where name = '" + shared.fromString + "')) and ('" + shared.toString + "' = (select name from node where name = '" + shared.toString + "'))"; if (check) myquery = appendParameterToQuery(myquery); myquery = myquery + " order by transportation." + order + " asc"; cur = dbHelper.getData(myquery); cur.moveToFirst(); int row_count = cur.getCount(); if (cur.getCount() == 0) { ourCase = "NO_ROUTE_ATALL_JPT"; } if (row_count == 1) { ourCase = "SINGLE_ROUTE"; int r_id = Integer.parseInt(cur.getString(cur.getColumnIndex("route_id"))); String quer1 = "select route_node.id from route_node where route_id = " + r_id + " and node_id = (select id from node where name = '" + shared.fromString + "')"; String quer2 = "select route_node.id from route_node where route_id = " + r_id + " and node_id = (select id from node where name = '" + shared.toString + "')"; Cursor cur1 = dbHelper.getData(quer1); Cursor cur2 = dbHelper.getData(quer2); cur1.moveToFirst(); cur2.moveToFirst(); List<Integer> r_n_id = new ArrayList<Integer>(); r_n_id.add(Integer.parseInt(cur1.getString(cur1.getColumnIndex("id")))); r_n_id.add(Integer.parseInt(cur2.getString(cur2.getColumnIndex("id")))); if (r_n_id.get(0) < r_n_id.get(1)) { single_route = "select " + tablehelper.TransportationColumns.CONTACT_PHONE + ", " + tablehelper.TransportationColumns.DEPART_TIME + ", " + tablehelper.TransportationColumns.ARRIVAL_TIME + ", " + tablehelper.TransportationColumns.COST + ", " + tablehelper.TransportationColumns.REMARKS + ", " + tablehelper.TransportationColumns.TYPE_ID + ", " + tablehelper.TransportationColumns.ID + ", " + tablehelper.TransportationColumns.INTERVAL + ", " + tablehelper.TransportationColumns.ROUTE_ID + ", " + tablehelper.CompanyColumns.NAME + ", " + tablehelper.RouteColumns.FULL_ROUTE + " from " + tablehelper.TransportationColumns.TABLENAME + " JOIN " + tablehelper.ScheduleColumns.TABLENAME + " ON " + tablehelper.ScheduleColumns.TRANSPORTATION_ID + " = " + tablehelper.TransportationColumns.ID + " JOIN " + tablehelper.CompanyColumns.TABLENAME + " ON " + tablehelper.CompanyColumns.ID + " = " + tablehelper.TransportationColumns.COMPANY_ID + " JOIN " + tablehelper.RouteColumns.TABLENAME + " ON " + tablehelper.TransportationColumns.ROUTE_ID + " = " + tablehelper.RouteColumns.ID + " where route.full_route like '%" + shared.fromString + "%' and route.full_route like '%" + shared.toString + "%' and route_id = " + r_id; } else { ourCase = "NO_ROUTE_ATALL_JPT"; // return "select * from route where name = 'aaa'"; } } if (row_count > 1) { ourCase = "MULTIPLE_ROUTE"; int correct_count = 0; List<Integer> r_id = new ArrayList<Integer>(); List<Integer> r_id_valid = new ArrayList<Integer>(); cur.moveToFirst(); while (!cur.isAfterLast()) { r_id.add(Integer.parseInt(cur.getString(cur.getColumnIndex("route_id")))); cur.moveToNext(); } int i = 0; while (i < r_id.size()) { String quer1 = "select route_node.id from route_node where route_id = " + r_id.get(i) + " and node_id = (select id from node where name = '" + shared.fromString + "')"; String quer2 = "select route_node.id from route_node where route_id = " + r_id.get(i) + " and node_id = (select id from node where name = '" + shared.toString + "')"; Cursor cur1 = dbHelper.getData(quer1); Cursor cur2 = dbHelper.getData(quer2); List<Integer> r_n_id = new ArrayList<Integer>(); cur1.moveToFirst(); cur2.moveToFirst(); r_n_id.add(Integer.parseInt(cur1.getString(cur1.getColumnIndex("id")))); r_n_id.add(Integer.parseInt(cur2.getString(cur2.getColumnIndex("id")))); Log.d("crossvoer", "values of r_n_id:" + r_n_id); if (r_n_id.get(0) < r_n_id.get(1)) { // save r_id in another list r_id_valid.add(r_id.get(i)); correct_count++; } i++; } if (correct_count == 0) { ourCase = "NO_ROUTE_ATALL_JPT"; } if (correct_count > 0) { ourCase = "MULTIPLE_ROUTE"; } StringBuilder MULTIPLE_ROUTE_VALID_ROUTE = new StringBuilder(); MULTIPLE_ROUTE_VALID_ROUTE.append( "select " + tablehelper.TransportationColumns.CONTACT_PHONE + ", " + tablehelper.TransportationColumns.DEPART_TIME + ", " + tablehelper.TransportationColumns.ARRIVAL_TIME + ", " + tablehelper.TransportationColumns.COST + ", " + tablehelper.TransportationColumns.REMARKS + ", " + tablehelper.TransportationColumns.TYPE_ID + ", " + tablehelper.TransportationColumns.ID + ", " + tablehelper.TransportationColumns.INTERVAL + ", " + tablehelper.TransportationColumns.ROUTE_ID + ", " + tablehelper.CompanyColumns.NAME + ", " + tablehelper.RouteColumns.FULL_ROUTE + " from " + tablehelper.TransportationColumns.TABLENAME + " JOIN " + tablehelper.ScheduleColumns.TABLENAME + " ON " + tablehelper.ScheduleColumns.TRANSPORTATION_ID + " = " + tablehelper.TransportationColumns.ID + " JOIN " + tablehelper.CompanyColumns.TABLENAME + " ON " + tablehelper.CompanyColumns.ID + " = " + tablehelper.TransportationColumns.COMPANY_ID + " JOIN " + tablehelper.RouteColumns.TABLENAME + " ON " + tablehelper.TransportationColumns.ROUTE_ID + " = " + tablehelper.RouteColumns.ID + " where route.full_route like '%" + shared.fromString + "%' and route.full_route like '%" + shared.toString + "%' and route_id in ("); Iterator<Integer> it = r_id_valid.iterator(); while (it.hasNext()) { MULTIPLE_ROUTE_VALID_ROUTE.append(it.next()); MULTIPLE_ROUTE_VALID_ROUTE.append(","); } MULTIPLE_ROUTE_VALID_ROUTE.append("0)"); MULTIPLE_VALID_ROUTE = MULTIPLE_ROUTE_VALID_ROUTE.toString(); } // end of if if (ourCase.equals("NO_ROUTE_ATALL_JPT")) { return "select full_route from route where name = 'aaa'"; } else if (ourCase.equals("SINGLE_ROUTE")) { return single_route; } else if (ourCase.equals("MULTIPLE_ROUTE")) { return MULTIPLE_VALID_ROUTE; } else return ""; }