/pcq/media/agency_attachments/L8pkS8gpnodJThOdAfv1.jpg)
Follow Us
New Update
The smartphone that everyone carries with is more potent than we thought. There are unexplored features in it that we all can use to our benefit. And not to mention the dozen applications out there that we have not yet downloaded and installed. Plus, you can develop your own applications with features none of the existing applications in the market provide, at least not the way you want. So it all boils down to that one question: what do you want? Because in the world of smartphones, sky is the limit in terms of what you can ask for, and there is a chance you can get what you are asking. In this article we will work out one of the requirements.
The requirement
All of us have contacts on our phones. Can the phone then tell you there are these friends of yours you have not called in for a long time? Your phone after all has a call log, and it can calculate. That means if you tell it with what frequency you want to call which contact, your phone would be able to work out which of your friends is overdue for a call from you. And that is what the application we are going to develop together in this article.
I use Android, and the article expects you to know Android programming. If you do not know Android programming but know programming, you still should be able to follow this article reasonably with some help from Internet searches. This article is a continuation of two of my previous articles, “Transferring Call Logs to Calendars in Android” in May 2013 issue of PCQuest and “Creating an Android Service to Copy Call Log to a Calendar” in June 2013 issue. I am continuing with the application I had started in these two articles.
The solution
True, your phone has a call log. But the call log has limited storage and gets overwritten over time. We will copy your call details of specified contacts to a SQLite database for persistent storage. We will use the SQLiteOpenHelper class to set up the database.
Copy the CallLog2Calendar2 project folder and name the new copy RegularContacts. Open the new project in Eclipse. Create a class ContactsDBOpenHelper inherited from SQLiteOpenHelper. Create the first explicit constructor to get rid of the error Eclipse throws. Define the following constants in your open helper class.
private static final String DATABASE_NAME = “RegularContacts.db”;
private static final int DATABASE_VERSION = 1;
public static final String TABLE_TRANSACTION = “ContactsTransaction”;
public static final String COLUMN_ID = “_id”;
public static final String COLUMN_DATE = “TransactionDate”;
public static final String COLUMN_CONTACT = “Contact”;
private static final String TABLE_CREATE = “CREATE TABLE “ + TABLE_TRANSACTION + “ (“ + COLUMN_ID + “ INTEGER PRIMARY KEY AUTOINCREMENT, “
+ COLUMN_DATE + “ TEXT, “ + COLUMN_CONTACT + “ TEXT)”;
We will store extracts from the call log in this transaction table. Modify the explicit constructor as follows:
public ContactsDBOpenHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
We would then be able to create an instance by only specifying the context. Modify the onCreate() method as follows:
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL(TABLE_CREATE);
}
The transaction table would then get created. Declare the following variables in the service class”
ContactsDBOpenHelper dohContacts;
SQLiteDatabase sdbContacts;
This is the same service we had created in the previous article to copy the call log to calendar. We will use the same service to copy the call log to the SQLite database. The service has already been set up to run once a day at off-peak hours. Add the following two lines to the onCreate() of the service:
dohContacts = new ContactsDBOpenHelper(this);
sdbContacts = dohContacts.getWritableDatabase();
This would create the database if it is not there and would return a reference to the database if it is already there. Run the application from Eclipse, and restart the device to make sure the service runs now. We had previously configured the service to run once during device reboot also.
In the service class, create a new method as follow:
public void newTableRow(long lngStart, String strContact) {
ContentValues cnvTransaction = new ContentValues();
Calendar calTransactionDate = Calendar.getInstance();
calTransactionDate.setTimeInMillis(lngStart);
SimpleDateFormat sdfTransaction = new SimpleDateFormat(“yyyy-MM-dd”, Locale.getDefault());
sdfTransaction.setTimeZone(TimeZone.getDefault());
String strTransactionDate = sdfTransaction.format(calTransactionDate.getTime());
cnvTransaction.put(ContactsDBOpenHelper.COLUMN_DATE, strTransactionDate);
cnvTransaction.put(ContactsDBOpenHelper.COLUMN_CONTACT, strContact);
sdbContacts.insert(ContactsDBOpenHelper.TABLE_TRANSACTION, null, cnvTransaction);
}
This method, when invoked, will insert its parameters as a new row to the transaction table. Add the following method also to the service class:
public boolean DoesNotExistInDB(long lngStart, String strContact) {
String<> strResults = new String<> { ContactsDBOpenHelper.COLUMN_DATE, ContactsDBOpenHelper.COLUMN_CONTACT };
Calendar calTransactionDate = Calendar.getInstance();
calTransactionDate.setTimeInMillis(lngStart);
SimpleDateFormat sdfTransaction = new SimpleDateFormat(“yyyy-MM-dd”, Locale.getDefault());
sdfTransaction.setTimeZone(TimeZone.getDefault());
String strTransactionDate = sdfTransaction.format(calTransactionDate.getTime());
String strWhere = ContactsDBOpenHelper.COLUMN_DATE + “ = ‘” + strTransactionDate + “’ AND “ + ContactsDBOpenHelper.COLUMN_CONTACT
+ “ = ‘” + strContact + “’”;
Cursor curTransaction = sdbContacts.query(ContactsDBOpenHelper.TABLE_TRANSACTION, strResults, strWhere, null, null, null, null);
if (curTransaction.getCount() == 0) return true;
else return false;
}
The method above checks if the parameters already exist in the database by querying the database and getting the count of the number of rows where the parameters exist in the table. While invoking the method to insert new rows, we will make this check so that the same call details do not get entered twice when the service runs again.
Check the onHandleIntent() method of the service class. Before the call to move to the next row of the cursor, add the following lines:
intIndex = curCallLog.getColumnIndex(CallLog.Calls.CACHED_NAME);
if (DoesNotExistInDB(lngStart, curCallLog.getString(intIndex))) newTableRow(lngStart, curCallLog.getString(intIndex));
Here we are relying on the code developed in the previous articles to scan the call log and pick relevant entries to enter into the database. Run the application from Eclipse and reboot the device to run the service. We are building the application in parts so that debugging is easier for you as you carry along.
We will then work on displaying the contacts overdue for a call in the activity of the application. In the activity onCreate(), remove the call to finish().
In the database helper class, add the following constants:
public static final String TABLE_PERIOD = “Period”;
public static final String COLUMN_PERIOD = “Period”;
public static final String COLUMN_LENGTH = “Length”;
public static final String TABLE_CREATE_PERIOD = “CREATE TABLE “ + TABLE_PERIOD + “ (“ + COLUMN_PERIOD + “ TEXT, “ + COLUMN_LENGTH +
“ TEXT)”;
This table would be a lookup table that will be used to translate periods like ‘Monthly’ to their numeric equivalents. Add the following lines to onCreate():
db.execSQL(TABLE_CREATE_PERIOD);
db.execSQL(“INSERT INTO “ + TABLE_PERIOD + “ VALUES(‘Monthly’, 30)”);
db.execSQL(“INSERT INTO “ + TABLE_PERIOD + “ VALUES(‘Quarterly’, 90)”);
db.execSQL(“INSERT INTO “ + TABLE_PERIOD + “ VALUES(‘Weekly’, 7)”);
db.execSQL(“INSERT INTO “ + TABLE_PERIOD + “ VALUES(‘Yearly’, 365)”);
We will not change the database version, so from Settings->Applications Manager->DOWNLOADED->CallLog2Calendar on your phone, select Clear Data. The database will be cleared and that will force onCreate() to run again. Run the Eclipse project. You need to restart the device if you want the service to run immediately and populate the database from the call log. Add constants for the next table:
public static final String TABLE_MASTER = “ContactPeriod”;
public static final String TABLE_CREATE_MASTER = “CREATE TABLE “ + TABLE_MASTER + “ (“ + COLUMN_CONTACT + “ TEXT, “ + COLUMN_PERIOD +
“ TEXT)”;
We will use this table to specify which contact you would call with what periodicity. In onCreate(), execute the SQL to create the table.
db.execSQL(TABLE_CREATE_MASTER);
Also insert the new records into the table:
db.execSQL(“INSERT INTO “ + TABLE_MASTER + “ VALUES(‘Manisha Saldanha’,’Weekly’)”);
db.execSQL(“INSERT INTO “ + TABLE_MASTER + “ VALUES(‘Tejeswar Das’,’Quarterly’)”);
db.execSQL(“INSERT INTO “ + TABLE_MASTER + “ VALUES(‘K Murali Krishna’,’Quarterly’)”);
db.execSQL(“INSERT INTO “ + TABLE_MASTER + “ VALUES(‘Sabitri Sahu’,’Monthly’)”);
db.execSQL(“INSERT INTO “ + TABLE_MASTER + “ VALUES(‘Shyamala B’,’Quarterly’)”);
db.execSQL(“INSERT INTO “ + TABLE_MASTER + “ VALUES(‘Deepa Agrawal’,’Monthly’)”);
db.execSQL(“INSERT INTO “ + TABLE_MASTER + “ VALUES(‘Swati Ranade’,’Monthly’)”);
db.execSQL(“INSERT INTO “ + TABLE_MASTER + “ VALUES(‘Kiran Shah’,’Quarterly’)”);
db.execSQL(“INSERT INTO “ + TABLE_MASTER + “ VALUES(‘Kashappa Mashal’,’Quarterly’)”);
db.execSQL(“INSERT INTO “ + TABLE_MASTER + “ VALUES(‘Virendra Dharwadker’,’Monthly’)”);
db.execSQL(“INSERT INTO “ + TABLE_MASTER + “ VALUES(‘Vinod Joshi’,’Monthly’)”);
db.execSQL(“INSERT INTO “ + TABLE_MASTER + “ VALUES(‘Ravi Kumar’,’Monthly’)”);
db.execSQL(“INSERT INTO “ + TABLE_MASTER + “ VALUES(‘Saroj Mallik’,’Monthly’)”);
db.execSQL(“INSERT INTO “ + TABLE_MASTER + “ VALUES(‘Vandrangi Aparna’,’Quarterly’)”);
db.execSQL(“INSERT INTO “ + TABLE_MASTER + “ VALUES(‘Rohan Toraskar’,’Quarterly’)”);
db.execSQL(“INSERT INTO “ + TABLE_MASTER + “ VALUES(‘Melita Saldanha’,’Quarterly’)”);
db.execSQL(“INSERT INTO “ + TABLE_MASTER + “ VALUES(‘Sanchaita Biswas’,’Quarterly’)”);
db.execSQL(“INSERT INTO “ + TABLE_MASTER + “ VALUES(‘Vinodan Raghavan’,’Quarterly’)”);
db.execSQL(“INSERT INTO “ + TABLE_MASTER + “ VALUES(‘Rovina Noronha’,’Quarterly’)”);
db.execSQL(“INSERT INTO “ + TABLE_MASTER + “ VALUES(‘Duaa Shemna’,’Quarterly’)”);
Here you can use your actual contacts instead of my examples. Clear the application data and reboot the device. Modify the onCreate() of the activity to group the contacts and show the last date of call for each contact. Here is the revised onCreate():
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_call_log2_calendar);
ContactsDBOpenHelper dohContacts;
SQLiteDatabase sdbContacts;
ListView lstContacts;
String<> strFrom = new String<> { “_id”, “MaxDate” };
int<> intLayout = new int<> { R.id.txtCallLog, R.id.txtName };
dohContacts = new ContactsDBOpenHelper(this);
sdbContacts = dohContacts.getWritableDatabase();
Cursor curTransaction = sdbContacts.rawQuery(“SELECT “ + ContactsDBOpenHelper.COLUMN_CONTACT + “ AS _id, MAX(“ + ContactsDBOpenHelper.COLUMN_DATE
+ “) AS MaxDate FROM “ + ContactsDBOpenHelper.TABLE_TRANSACTION + “ GROUP BY “ + ContactsDBOpenHelper.COLUMN_CONTACT, null);
SimpleCursorAdapter scaTransactions = new SimpleCursorAdapter(this, R.layout.callloglist, curTransaction, strFrom,
intLayout, SimpleCursorAdapter.FLAG_REGISTER_CONTENT_OBSERVER);
lstContacts = (ListView)findViewById(R.id.lstCallLog);
lstContacts.setAdapter(scaTransactions);
}
Check if you are able to see the details correctly in your activity. We will now make the same query more involved, so that we will join the three tables and list out the contacts where your call is overdue. Here is the revised code for onCreate():
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_call_log2_calendar);
ContactsDBOpenHelper dohContacts;
SQLiteDatabase sdbContacts;
ListView lstContacts;
String<> strFrom = new String<> { “_id”, “MaxDate” };
int<> intLayout = new int<> { R.id.txtCallLog, R.id.txtName };
dohContacts = new ContactsDBOpenHelper(this);
sdbContacts = dohContacts.getWritableDatabase();
Cursor curTransaction = sdbContacts.rawQuery(“SELECT “ + ContactsDBOpenHelper.TABLE_MASTER + “.” + ContactsDBOpenHelper.COLUMN_CONTACT
+ “ AS _id, MaxDate FROM “ + ContactsDBOpenHelper.TABLE_MASTER + “ LEFT OUTER JOIN (SELECT “ +
ContactsDBOpenHelper.COLUMN_CONTACT + “ , “ + “MAX(“ + ContactsDBOpenHelper.COLUMN_DATE + “) AS MaxDate FROM “ +
ContactsDBOpenHelper.TABLE_TRANSACTION + “ GROUP BY “ + ContactsDBOpenHelper.COLUMN_CONTACT + “) AS TransactionSummary ON “ +
ContactsDBOpenHelper.TABLE_MASTER + “.” + ContactsDBOpenHelper.COLUMN_CONTACT + “ = TransactionSummary.” +
ContactsDBOpenHelper.COLUMN_CONTACT + “ INNER JOIN “ + ContactsDBOpenHelper.TABLE_PERIOD + “ ON “ + ContactsDBOpenHelper.TABLE_MASTER
+ “.” + ContactsDBOpenHelper.COLUMN_PERIOD + “ = “ + ContactsDBOpenHelper.TABLE_PERIOD + “.” + ContactsDBOpenHelper.COLUMN_PERIOD
+ “ WHERE julianday(‘now’) - julianday(ifnull(MaxDate, ‘1990-01-01’)) > “ + ContactsDBOpenHelper.COLUMN_LENGTH, null);
SimpleCursorAdapter scaTransactions = new SimpleCursorAdapter(this, R.layout.callloglist, curTransaction, strFrom, intLayout, SimpleCursorAdapter.FLAG_REGISTER_CONTENT_OBSERVER);
lstContacts = (ListView)findViewById(R.id.lstCallLog);
lstContacts.setAdapter(scaTransactions);
}
We would need to factor in the possibility that for some of your contacts, there may be no entry in the log now and hence no entry in the database. I have made the assumption that you have called the contact last in 1990. There is no basis for this assumption, and you can put your own scheme in terms of how to handle null values.
We are now done with the application. I have not followed many of the best practices recommended for the sake of simplicity of understanding, and the idea is only to make you aware of what all can be done. You can also develop this application from scratch if you do not want to copy your call log to calendar simultaneously.