Conor Smith Conor Smith - 3 months ago 23
Android Question

android sqlite database pie chart

I am just wondering if you could help me with the issue I have been having.

I created a SQLite database and I am looking to create a pie chart based based on whether the medication was taken on a particular date.

Code for creating the table

// sql query to create table for daily routine
String sql6 = "CREATE TABLE "
+ TABLE_DAILY_ROUTINE + " ( "
+ COL_DAILY_ROUTINE_DATE + " VARCHAR, "
+ COL_DAILY_ROUTINE_TIME_ID + " INTEGER,"
+ COL_DAILY_ROUTINE_TIME_HOUR + " INTEGER , "
+ COL_DAILY_ROUTINE_TIME_MIN + " INTEGER , "
+ COL_DAILY_ROUTINE_MED_TAKEN + " TEXT ,"
+ COL_DAILY_ROUTINE_MED_QUANTITY + " INTEGER, "
+ COL_DAILY_ROUTINE_MED_ID + " VARCHAR, "
+ "PRIMARY KEY ( " + COL_DAILY_ROUTINE_DATE + ", " + COL_DAILY_ROUTINE_TIME_ID + "),"
+ "FOREIGN KEY ( " + COL_DAILY_ROUTINE_MED_ID + ") REFERENCES " + TABLE_MEDICATION + "("
+ COL_MEDICATION_ID + ") ON DELETE CASCADE);";


Pie chart Activity

public class PieChart extends Activity {

SQLiteDatabase ourDatabase;
Database db;
DatabaseHelper dbHelper;
String date;

@Override
public void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.pie_chart_layout);
db = new Database(this);
db.open();
dbHelper = new DatabaseHelper(this);
ourDatabase = dbHelper.getWritableDatabase();


String sql = "SELECT DAILY_ROUTINE_MED_TAKEN, COUNT(DAILY_ROUTINE_MED_TAKEN) FROM DAILY_ROUTINE\n" +
"WHERE DAILY_ROUTINE_DATE = ' + date + ' GROUP BY DAILY_ROUTINE_MED_TAKEN " ;
Calendar calendar = Calendar.getInstance();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
calendar.add(Calendar.DATE, 1);
date = sdf.format(calendar.getTime());
Cursor c = ourDatabase.rawQuery(sql, null);
int count = c.getCount();
int[] colors={Color.RED, Color.GREEN};

double[] values = new double[count];
String[] categoryNames = new String[count];

for (int i = 0; i < count; i++) {
c.moveToNext();
categoryNames[i] = c.getString(0);
values[i] = c.getDouble(1);
}
// Instantiating CategorySeries to plot Pie Chart
CategorySeries distributionSeries = new CategorySeries(" Android version distribution as on October 1, 2012");
for (int i = 0; i < categoryNames.length; i++) {
// Adding a slice with its values and name to the Pie Chart
distributionSeries.add(categoryNames[i], values[i]);
}

// Instantiating a renderer for the Pie Chart
DefaultRenderer defaultRenderer = new DefaultRenderer();
for (int i = 0; i < categoryNames.length; i++) {
SimpleSeriesRenderer seriesRenderer = new SimpleSeriesRenderer();
seriesRenderer.setColor(colors[i]);
seriesRenderer.setDisplayChartValues(true);
defaultRenderer.addSeriesRenderer(seriesRenderer);
}

defaultRenderer.setChartTitle("Patient Daily Adherance to Medication");
defaultRenderer.setChartTitleTextSize(60);
defaultRenderer.setZoomButtonsVisible(false);
defaultRenderer.setLegendTextSize(70);
defaultRenderer.setLabelsTextSize(80);

Intent intent = ChartFactory.getPieChartIntent(getApplicationContext(), distributionSeries, defaultRenderer, "AChartEnginePieChartDemo");

// Start Activity
startActivity(intent);

c.close();
ourDatabase.close();
}
}


The database is correct and when I hard code a date in to replace the string date I get a pie chart displayed.

The string date is a day in front as this is when the date is in the database. This would not be the issue.

I am being given no error.

The title is showing but there is no pie chart and could you please help me with the issue and what the problem is.

I'm sure it has something to do with the initial for loop, but can't put my finger on it.

Answer

I think you need to move around some lines of code, and also concatenate the value of the date variable.

Right now, you are trying to query where the column explicitly equals the string of ' + date + '.

// What is 'date' here? null, most likely... 
String sql = "SELECT DAILY_ROUTINE_MED_TAKEN, COUNT(DAILY_ROUTINE_MED_TAKEN) FROM DAILY_ROUTINE\n" +
        "WHERE DAILY_ROUTINE_DATE = ' + date + ' GROUP BY DAILY_ROUTINE_MED_TAKEN " ;

// Here's where you do get the value of the date string 
Calendar calendar = Calendar.getInstance();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
calendar.add(Calendar.DATE, 1);
date = sdf.format(calendar.getTime()); 

// move the sql down here 

I'd also suggest trying to use the query method of the SqliteDatabase class instead of execSQL or rawQuery but that's personal preference

Comments