ArticlesDataSource.java

package com.mysite.datasources;

import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import javax.xml.parsers.SAXParser;
import javax.xml.parsers.SAXParserFactory;

import org.apache.commons.lang3.StringEscapeUtils;
import org.xml.sax.SAXParseException;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.DatabaseUtils;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;
import android.widget.Toast;

import com.mysite.models.Article;
import com.mysite.models.Photo;
import com.mysite.models.Section;
import com.mysite.utilities.DateHelper;
import com.mysite.utilities.DbHelper;
import com.mysite.utilities.RSSHandler;
import com.mysite.utilities.RSSHandler.NewsItem;
import com.mysite.utilities.RSSHandler.PhotoItem;

public class ArticlesDataSource
{

    private final Context mContext;

    // Database fields
    private SQLiteDatabase database;
    private DbHelper dbHelper;
    private PhotosDataSource photosdatasource;
    private SectionsDataSource sectionsdatasource;

    private final String[] allColumns = {
        Article.TABLE + "." + Article.COLUMN_ID,
        Article.TABLE + "." + Article.COLUMN_TITLE,
        Article.TABLE + "." + Article.COLUMN_SUBTITLE,
        Article.TABLE + "." + Article.COLUMN_LINK,
        Article.TABLE + "." + Article.COLUMN_BYLINE,
        Article.TABLE + "." + Article.COLUMN_DESCRIPTION,
        Article.TABLE + "." + Article.COLUMN_STORYTEXT,
        Article.TABLE + "." + Article.COLUMN_TRIBID,
        Article.TABLE + "." + Article.COLUMN_PUBDATE,
        Article.TABLE + "." + Article.COLUMN_KEYWORDS,
        Article.TABLE + "." + Article.COLUMN_CATEGORY,
        Article.TABLE + "." + Article.COLUMN_SUBCATEGORY,
        Article.TABLE + "." + Article.COLUMN_WEIGHT,
        Article.TABLE + "." + Article.COLUMN_MODIFIED,
        Article.TABLE + "." + Article.COLUMN_S_TOP,
        Article.TABLE + "." + Article.COLUMN_S_UTAH,
        Article.TABLE + "." + Article.COLUMN_S_SPORTS,
        Article.TABLE + "." + Article.COLUMN_S_MONEY,
        Article.TABLE + "." + Article.COLUMN_S_OPINION,
        Article.TABLE + "." + Article.COLUMN_S_MIX,
        Article.TABLE + "." + Article.COLUMN_S_POPULAR,
        Article.TABLE + "." + Article.COLUMN_S_BAGLEY,
        Article.TABLE + "." + Article.COLUMN_S_OBITS,
    };

    private final String[] listColumns = {
        Article.TABLE + "." + Article.COLUMN_ID,
        Article.TABLE + "." + Article.COLUMN_TITLE,
        Article.TABLE + "." + Article.COLUMN_SUBTITLE,
        Article.TABLE + "." + Article.COLUMN_BYLINE,
        Article.TABLE + "." + Article.COLUMN_DESCRIPTION,
        Article.TABLE + "." + Article.COLUMN_PUBDATE,
        Photo.TABLE + "." + Photo.COLUMN_FILEPATH + " AS filepath"
    };

    public ArticlesDataSource(Context context)
    {
        mContext = context;
        dbHelper = new DbHelper(mContext);
        photosdatasource = new PhotosDataSource(context);
        sectionsdatasource = new SectionsDataSource(context);
    }

    public void open() throws SQLException
    {
        database = dbHelper.getWritableDatabase();
    }

    public void close()
    {
        dbHelper.close();
    }


    public void importArticles(String[] section, Boolean forceImport)
    {
        try
        {
            this.open();
            //photosdatasource.open();

            Boolean shouldImport = true;

            if(!forceImport)
            {
                //check if it's been at least an hour since last update
                String lastUpdated = sectionsdatasource.last_updated(section[Section.INDEX_SLUG]);
                int minutesSinceLastUpdate = 0;
                try
                {
                    minutesSinceLastUpdate = DateHelper.minutesAgo(lastUpdated);
                }
                catch (Exception e)
                {
                    minutesSinceLastUpdate = 9999;
                }


                if(minutesSinceLastUpdate < 60)
                {
                    shouldImport = false;
                }
            }

            if(shouldImport)
            {

                //Parse the response data using SAX
                SAXParserFactory factory = SAXParserFactory.newInstance();
                SAXParser p = factory.newSAXParser();
                RSSHandler parser = new RSSHandler();

                //Run the parsing operation
                try
                {
                    p.parse(section[1], parser);
                }
                catch (SAXParseException e)
                {
                    e.printStackTrace();
                }
                catch (IOException e)
                {
                    e.printStackTrace();
                }

                //Add all items from the parsed XML
                for(NewsItem item : parser.getParsedItems())
                {
                    Article a1 = new Article();
                    if(item.title != null) { a1.title = item.title.trim(); } else { a1.title = ""; }
                    if(item.subhead != null) { a1.subtitle = item.subhead.trim(); } else { a1.subtitle = ""; }
                    if(item.link != null) { a1.link = item.link.trim(); } else { a1.link = ""; }
                    if(item.byline != null) { a1.byline = item.byline.trim(); } else { a1.byline = ""; }
                    if(item.description != null) { a1.description = item.description.trim(); } else { a1.description = ""; }
                    if(item.storytext != null) { a1.storytext = item.storytext.trim(); } else { a1.storytext = ""; }
                    if(item.cmsstoryid != null) { a1.tribid = item.cmsstoryid.trim(); } else { a1.tribid = ""; }
                    if(item.pubDate != null) { a1.pubdate = item.pubDate.trim(); } else { a1.pubdate = ""; }
                    if(item.keyword != null) { a1.keywords = item.keyword.trim(); } else { a1.keywords = ""; }
                    if(item.category != null) { a1.category = item.category.trim(); } else { a1.category = ""; }
                    if(item.subcategory != null) { a1.subcategory = item.subcategory.trim(); } else { a1.subcategory = ""; }
                    if(item.slotreference != null && item.slotreference.length() > 0) { a1.weight = Integer.parseInt(item.slotreference.trim()); } else { a1.weight = 0; }
                    if(item.lastModTime != null) { a1.modified = item.lastModTime.trim(); } else { a1.modified = ""; }

                    if(section[Section.INDEX_SLUG].equals(Section.TOP[Section.INDEX_SLUG])) { a1.s_top = 1; } //else { a1.s_top = 0; }
                    if(section[Section.INDEX_SLUG].equals(Section.UTAH[Section.INDEX_SLUG])) { a1.s_utah = 1; } //else { a1.s_utah = 0; }
                    if(section[Section.INDEX_SLUG].equals(Section.SPORTS[Section.INDEX_SLUG])) { a1.s_sports = 1; } //else { a1.s_sports = 0; }
                    if(section[Section.INDEX_SLUG].equals(Section.MONEY[Section.INDEX_SLUG])) { a1.s_money = 1; } //else { a1.s_money = 0; }
                    if(section[Section.INDEX_SLUG].equals(Section.OPINION[Section.INDEX_SLUG])) { a1.s_opinion = 1; } //else { a1.s_opinion = 0; }
                    if(section[Section.INDEX_SLUG].equals(Section.MIX[Section.INDEX_SLUG])) { a1.s_mix = 1; } //else { a1.s_mix = 0; }
                    if(section[Section.INDEX_SLUG].equals(Section.POPULAR[Section.INDEX_SLUG])) { a1.s_popular = 1; } //else { a1.s_popular = 0; }
                    if(section[Section.INDEX_SLUG].equals(Section.BAGLEY[Section.INDEX_SLUG])) { a1.s_bagley = 1; } //else { a1.s_bagley = 0; }
                    if(section[Section.INDEX_SLUG].equals(Section.OBITS[Section.INDEX_SLUG])) { a1.s_obits = 1; } //else { a1.s_obits = 0; }

                    //add or update (check if it already exists)
                    if(this.exists(a1.tribid))
                    {
                        if(this.needsUpdating(a1.tribid, a1.modified))
                        {
                            this.updateArticle(a1);
                        }
                        else
                        {
                            Log.d("MYSITE", "[/] Article doesn't need updating.");
                        }
                    }
                    else
                    {
                        Log.d("MYSITE", "[+] Article: " + a1.title);
                        this.createArticle(a1);
                    }


                    //repeats through any images and adds them to the database (with related article_id field populated)

                    for(PhotoItem photo : item.photos)
                    {
                        Photo p1 = new Photo();
                        if(photo.photoid != null) { p1.tribid = photo.photoid.trim(); } else { p1.tribid = ""; }
                        if(photo.caption != null) { p1.caption = photo.caption.trim(); } else { p1.caption = ""; }
                        if(photo.height != null && photo.height.length() > 0) { p1.height = Math.round(Float.parseFloat(photo.height.trim())); } else { p1.height = 0; }
                        if(photo.width != null && photo.width.length() > 0) { p1.width =  Math.round(Float.parseFloat(photo.width.trim())); } else { p1.width = 0; }
                        if(photo.photo != null) { p1.filepath = photo.photo.trim(); } else { p1.filepath = ""; }
                        if(a1.tribid != null) { p1.articletribid = a1.tribid; } else { p1.articletribid = ""; }
                        if(photo.photolastmodified != null) { p1.modified = photo.photolastmodified.trim(); } else { p1.modified = ""; }

                        //either add or update the photo
                        if(photosdatasource.exists(p1.filepath))
                        {
                            photosdatasource.updatePhoto(p1);
                        }
                        else
                        {
                            photosdatasource.createPhoto(p1);
                        }

                    }

                }

                //Updates section's last_updated field
                sectionsdatasource.updated(section[Section.INDEX_SLUG]);
            } //end "should import
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }
        finally
        {
            //photosdatasource.close();
            this.close();
        }

    }

    public long createArticle(Article article)
    {
        return database.insert(Article.TABLE, null, this.buildContentValues(article));
    }

    public Article getArticleData(String id, Boolean showToast)
    {
        Article a1 = new Article();
        try
        {
            this.open();
            Cursor cursor = database.query(Article.TABLE,
                allColumns,
                Article.COLUMN_ID + "=?",
                new String[] {id},
                null, null, null, null); //WHY CAN'T I USE "LIMIT 1" ?????
            cursor.moveToFirst();
            a1 = cursorToArticle(cursor);
            cursor.close();
        }
        catch (Exception e)
        {
            e.printStackTrace();
            if(showToast)
            {
                //show "couldn't load" message
                Toast toast = Toast.makeText(
                    this.mContext.getApplicationContext(),
                    "NOTICE: Could not load selected article.",
                    Toast.LENGTH_LONG);
                toast.show();
            }
        }
        finally
        {
            this.close();
        }
        return a1;
    }

    public int updateArticle(Article article)
    {
        return database.update(Article.TABLE, buildContentValues(article), Article.COLUMN_ID + " = ?", new String[] { String.valueOf(article.id) });
    }

    private ContentValues buildContentValues(Article article)
    {
        ContentValues values = new ContentValues();
        values.put(Article.COLUMN_TITLE, article.title);
        values.put(Article.COLUMN_SUBTITLE, article.subtitle);
        values.put(Article.COLUMN_LINK, article.link);
        values.put(Article.COLUMN_BYLINE, article.byline);
        values.put(Article.COLUMN_DESCRIPTION, article.description);
        values.put(Article.COLUMN_STORYTEXT, article.storytext);
        values.put(Article.COLUMN_TRIBID, article.tribid);
        values.put(Article.COLUMN_PUBDATE, article.pubdate);
        values.put(Article.COLUMN_KEYWORDS, article.keywords);
        values.put(Article.COLUMN_CATEGORY, article.category);
        values.put(Article.COLUMN_SUBCATEGORY, article.subcategory);
        values.put(Article.COLUMN_WEIGHT, article.weight);
        values.put(Article.COLUMN_MODIFIED, article.modified);
        values.put(Article.COLUMN_S_TOP, article.s_top);
        values.put(Article.COLUMN_S_UTAH, article.s_utah);
        values.put(Article.COLUMN_S_SPORTS, article.s_sports);
        values.put(Article.COLUMN_S_MONEY, article.s_money);
        values.put(Article.COLUMN_S_OPINION, article.s_opinion);
        values.put(Article.COLUMN_S_MIX, article.s_mix);
        values.put(Article.COLUMN_S_POPULAR, article.s_popular);
        values.put(Article.COLUMN_S_BAGLEY, article.s_bagley);
        values.put(Article.COLUMN_S_OBITS, article.s_obits);
        return values;
    }

    public void deleteArticle(Article article)
    {
        System.out.println("Article deleted with id: " + article.id);
        database.delete(Article.TABLE, Article.COLUMN_ID + " = " + article.id, null);
    }

    public long getArticleCount()
    {
        return DatabaseUtils.queryNumEntries(database, Article.TABLE);
    }

    public boolean exists(String tribid)
    {
        String countQuery = "SELECT count(" + Article.COLUMN_ID + ") FROM " + Article.TABLE + " WHERE " + Article.COLUMN_TRIBID + " = '" + tribid + "' LIMIT 1";
        if(DatabaseUtils.longForQuery(database, countQuery, null) > 0)
        {
            return true;
        }
        else
        {
            return false;
        }
    }

    public boolean needsUpdating(String tribid, String modified)
    {
        String countQuery = "SELECT count(" + Article.COLUMN_ID + ") FROM " + Article.TABLE + " WHERE " + Article.COLUMN_TRIBID + " = '" + tribid + "' AND " + Article.COLUMN_MODIFIED + " < '" + modified + "' LIMIT 1";
        if(DatabaseUtils.longForQuery(database, countQuery, null) > 0)
        {
            return true;
        }
        else
        {
            return false;
        }
    }

    public List<Article> getArticles(String[] section)
    {
        this.open();
        List<Article> articles = new ArrayList<Article>();

        //queries the database
        Log.d("MYSITE", "ArticlesDataSource: getArticles(): field: s_" + Section.currentSection[Section.INDEX_SLUG]);
        Cursor cursor = database.query(
                Article.TABLE + " LEFT JOIN " + Photo.TABLE +
                    " ON (" + Article.TABLE + "." + Article.COLUMN_TRIBID + " = " + Photo.TABLE + "." + Photo.COLUMN_ARTICLETRIBID + ")",
                listColumns,
                "s_" + section[Section.INDEX_SLUG] + "=1", //KLUDGE: builds the field name dynamically instead of referencing the model
                null, //
                Article.TABLE + "." + Article.COLUMN_ID, //group by
                null, //having
                Article.COLUMN_WEIGHT+" DESC ", //order
                null);


        //sets the cursor to the first article
        cursor.moveToFirst();
        //repeat through and adds each article (via cursor) to the arciesl array
        while(!cursor.isAfterLast())
        {
            Article article = cursorToArticleSummary(cursor);
            articles.add(article);
            cursor.moveToNext();
        }
        // Make sure to close the cursor
        cursor.close();
        this.close();
        return articles;
    }

    private Article cursorToArticle(Cursor cursor)
    {
        Article article = new Article();
        article.id = cursor.getLong(cursor.getColumnIndex(Article.COLUMN_ID));
        article.title = StringEscapeUtils.unescapeXml(cursor.getString(cursor.getColumnIndex(Article.COLUMN_TITLE)));
        article.subtitle = StringEscapeUtils.unescapeXml(cursor.getString(cursor.getColumnIndex(Article.COLUMN_SUBTITLE)));
        article.link = StringEscapeUtils.unescapeXml(cursor.getString(cursor.getColumnIndex(Article.COLUMN_LINK)));
        article.byline = StringEscapeUtils.unescapeXml(cursor.getString(cursor.getColumnIndex(Article.COLUMN_BYLINE)));
        article.description = StringEscapeUtils.unescapeXml(cursor.getString(cursor.getColumnIndex(Article.COLUMN_DESCRIPTION)));
        article.storytext = StringEscapeUtils.unescapeXml(cursor.getString(cursor.getColumnIndex(Article.COLUMN_STORYTEXT)));
        article.tribid = StringEscapeUtils.unescapeXml(cursor.getString(cursor.getColumnIndex(Article.COLUMN_TRIBID)));
        article.pubdate = StringEscapeUtils.unescapeXml(cursor.getString(cursor.getColumnIndex(Article.COLUMN_PUBDATE)));
        article.keywords = StringEscapeUtils.unescapeXml(cursor.getString(cursor.getColumnIndex(Article.COLUMN_KEYWORDS)));
        article.category = StringEscapeUtils.unescapeXml(cursor.getString(cursor.getColumnIndex(Article.COLUMN_CATEGORY)));
        article.subcategory = StringEscapeUtils.unescapeXml(cursor.getString(cursor.getColumnIndex(Article.COLUMN_SUBCATEGORY)));
        article.weight = cursor.getInt(cursor.getColumnIndex(Article.COLUMN_WEIGHT));
        article.modified = StringEscapeUtils.unescapeXml(cursor.getString(cursor.getColumnIndex(Article.COLUMN_MODIFIED)));

        //capitalize byline
        article.byline = article.byline.toUpperCase();

        //clean up
        if(article.storytext != null && article.storytext.length() != 0) {
            article.storytext = article.storytext.replace("\r", "\n");
            article.storytext = article.storytext.replace("\n\n", "\n");
            article.storytext = article.storytext.replace("\n\n", "\n");
            article.storytext = article.storytext.replace("\n\n", "\n");
            article.storytext = article.storytext.replace("\n", "\n\n");
        }
        return article;
    }

    private Article cursorToArticleSummary(Cursor cursor)
    {
        Article article = new Article();
        //sets data for the list item's object
        article.id = cursor.getLong(cursor.getColumnIndex(Article.COLUMN_ID));
        article.title = StringEscapeUtils.unescapeXml(cursor.getString(cursor.getColumnIndex(Article.COLUMN_TITLE)));
        article.subtitle = StringEscapeUtils.unescapeXml(cursor.getString(cursor.getColumnIndex(Article.COLUMN_SUBTITLE)));
        article.description = StringEscapeUtils.unescapeXml(cursor.getString(cursor.getColumnIndex(Article.COLUMN_DESCRIPTION)));
        article.pubdate = StringEscapeUtils.unescapeXml(cursor.getString(cursor.getColumnIndex(Article.COLUMN_PUBDATE)));
        article.filepath = StringEscapeUtils.unescapeXml(cursor.getString(cursor.getColumnIndex(Article.COLUMN_THUMBPATH)));

        //cleans up (or replaces with "" if empty)
        if(article.title != null && article.title.length() != 0) { article.title = article.title.replace("\n", ""); } else { article.title = ""; }
        if(article.subtitle != null && article.subtitle.length() != 0) { article.subtitle = article.subtitle.replace("\n", ""); } else { article.subtitle = ""; }
        if(article.description != null && article.description.length() != 0) { article.description = article.description.replace("\n", ""); } else { article.description = ""; }

        //truncates texts
        if(article.subtitle != null && article.subtitle.length() != 0 && article.subtitle.length() > 90) { article.subtitle = article.subtitle.substring(0,90) + "..."; }
        if(article.description != null && article.description.length() != 0 && article.description.length() > 90) { article.description = article.description.substring(0,90) + "..."; }

        return article;
    }

}