MyEntityManager.java

/*******************************************************************************
 * Copyright (c) 2010 Denis Solonenko.
 * All rights reserved. This program and the accompanying materials
 * are made available under the terms of the GNU Public License v2.0
 * which accompanies this distribution, and is available at
 * http://www.gnu.org/licenses/old-licenses/gpl-2.0.html
 *
 * Contributors:
 *     Denis Solonenko - initial API and implementation
 ******************************************************************************/
package ru.orangesoftware.financisto2.db;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.support.v4.util.LongSparseArray;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import ru.orangesoftware.financisto2.blotter.BlotterFilter;
import ru.orangesoftware.financisto2.datetime.Period;
import ru.orangesoftware.financisto2.filter.Criteria;
import ru.orangesoftware.financisto2.filter.WhereFilter;
import ru.orangesoftware.financisto2.model.Account;
import ru.orangesoftware.financisto2.model.Attribute;
import ru.orangesoftware.financisto2.model.Budget;
import ru.orangesoftware.financisto2.model.Category;
import ru.orangesoftware.financisto2.model.Currency;
import ru.orangesoftware.financisto2.model.MyEntity;
import ru.orangesoftware.financisto2.model.Payee;
import ru.orangesoftware.financisto2.model.Project;
import ru.orangesoftware.financisto2.model.SystemAttribute;
import ru.orangesoftware.financisto2.model.Transaction;
import ru.orangesoftware.financisto2.model.TransactionAttributeInfo;
import ru.orangesoftware.financisto2.model.TransactionInfo;
import ru.orangesoftware.financisto2.utils.MyPreferences;
import ru.orangesoftware.financisto2.utils.MyPreferences.AccountSortOrder;
import ru.orangesoftware.financisto2.utils.RecurUtils;
import ru.orangesoftware.financisto2.utils.RecurUtils.Recur;
import ru.orangesoftware.financisto2.utils.Utils;
import ru.orangesoftware.orb.EntityManager;
import ru.orangesoftware.orb.Expression;
import ru.orangesoftware.orb.Expressions;
import ru.orangesoftware.orb.Query;

import static ru.orangesoftware.financisto2.db.DatabaseHelper.ACCOUNT_TABLE;
import static ru.orangesoftware.financisto2.db.DatabaseHelper.ATTRIBUTES_TABLE;
import static ru.orangesoftware.financisto2.db.DatabaseHelper.AccountColumns;
import static ru.orangesoftware.financisto2.db.DatabaseHelper.BUDGET_TABLE;
import static ru.orangesoftware.financisto2.db.DatabaseHelper.CATEGORY_ATTRIBUTE_TABLE;
import static ru.orangesoftware.financisto2.db.DatabaseHelper.CURRENCY_TABLE;
import static ru.orangesoftware.financisto2.db.DatabaseHelper.TRANSACTION_ATTRIBUTE_TABLE;
import static ru.orangesoftware.financisto2.utils.StringUtil.capitalize;

class MyEntityManager extends EntityManager {

    public final Context context;

    MyEntityManager(Context context) {
        this.context = context;
    }

    private <T extends MyEntity> ArrayList<T> getAllEntitiesList(Class<T> clazz, boolean include0) {
        Query<T> q = createQuery(clazz);
        q.where(include0 ? Expressions.gte("id", 0) : Expressions.gt("id", 0));
        q.asc("title");
        Cursor c = q.execute();
        try {
            T e0 = null;
            ArrayList<T> list = new ArrayList<T>();
            while (c.moveToNext()) {
                T e = EntityManager.loadFromCursor(c, clazz);
                if (e.id == 0) {
                    e0 = e;
                } else {
                    list.add(e);
                }
            }
            if (e0 != null) {
                list.add(0, e0);
            }
            return list;
        } finally {
            c.close();
        }
    }

    private <T extends MyEntity> ArrayList<T> getAllEntitiesList(Class<T> clazz, boolean include0, boolean onlyActive) {
        Query<T> q = createQuery(clazz);
        Expression include0Ex = include0 ? Expressions.gte("id", 0) : Expressions.gt("id", 0);
        if (onlyActive) {
            q.where(Expressions.and(include0Ex, Expressions.eq("isActive", 1)));
        } else {
            q.where(include0Ex);
        }
        q.asc("title");
        Cursor c = q.execute();
        try {
            T e0 = null;
            ArrayList<T> list = new ArrayList<T>();
            while (c.moveToNext()) {
                T e = EntityManager.loadFromCursor(c, clazz);
                if (e.id == 0) {
                    e0 = e;
                } else {
                    list.add(e);
                }
            }
            if (e0 != null) {
                list.add(0, e0);
            }
            return list;
        } finally {
            c.close();
        }
    }

	/* ===============================================
	 * TRANSACTION INFO
	 * =============================================== */

    public TransactionInfo getTransactionInfo(long transactionId) {
        return get(TransactionInfo.class, transactionId);
    }

    public List<TransactionAttributeInfo> getAttributesForTransaction(long transactionId) {
        Query<TransactionAttributeInfo> q = createQuery(TransactionAttributeInfo.class).asc("name");
        q.where(Expressions.and(
                Expressions.eq("transactionId", transactionId),
                Expressions.gte("attributeId", 0)
        ));
        Cursor c = q.execute();
        try {
            List<TransactionAttributeInfo> list = new LinkedList<TransactionAttributeInfo>();
            while (c.moveToNext()) {
                TransactionAttributeInfo ti = loadFromCursor(c, TransactionAttributeInfo.class);
                list.add(ti);
            }
            return list;
        } finally {
            c.close();
        }

    }

    public TransactionAttributeInfo getSystemAttributeForTransaction(SystemAttribute sa, long transactionId) {
        Query<TransactionAttributeInfo> q = createQuery(TransactionAttributeInfo.class);
        q.where(Expressions.and(
                Expressions.eq("transactionId", transactionId),
                Expressions.eq("attributeId", sa.id)
        ));
        Cursor c = q.execute();
        try {
            if (c.moveToFirst()) {
                return loadFromCursor(c, TransactionAttributeInfo.class);
            }
            return null;
        } finally {
            c.close();
        }
    }

	/* ===============================================
	 * ACCOUNT
	 * =============================================== */

    public Account getAccount(long id) {
        return get(Account.class, id);
    }

    public Cursor getAccountsForTransaction(Transaction t) {
        return getAllAccounts(true, t.fromAccountId, t.toAccountId);
    }

    public Cursor getAllActiveAccounts() {
        return getAllAccounts(true);
    }

    public Cursor getAllAccounts() {
        return getAllAccounts(false);
    }

    private Cursor getAllAccounts(boolean isActiveOnly, long... includeAccounts) {
        AccountSortOrder sortOrder = MyPreferences.getAccountSortOrder(context);
        Query<Account> q = createQuery(Account.class);
        if (isActiveOnly) {
            int count = includeAccounts.length;
            if (count > 0) {
                Expression[] ee = new Expression[count + 1];
                for (int i = 0; i < count; i++) {
                    ee[i] = Expressions.eq("id", includeAccounts[i]);
                }
                ee[count] = Expressions.eq("isActive", 1);
                q.where(Expressions.or(ee));
            } else {
                q.where(Expressions.eq("isActive", 1));
            }
        }
        q.desc("isActive");
        if (sortOrder.asc) {
            q.asc(sortOrder.property);
        } else {
            q.desc(sortOrder.property);
        }
        return q.asc("title").execute();
    }

    public long saveAccount(Account account) {
        return saveOrUpdate(account);
    }

    public List<Account> getAllAccountsList() {
        return getAllAccountsList(false);
    }

    public List<Account> getAllAccountsList(boolean activeOnly) {
        List<Account> list = new ArrayList<Account>();
        Cursor c = getAllAccounts(activeOnly);
        try {
            while (c.moveToNext()) {
                Account a = EntityManager.loadFromCursor(c, Account.class);
                list.add(a);
            }
        } finally {
            c.close();
        }
        return list;
    }

    public LongSparseArray<Account> getAllAccountsMap() {
        LongSparseArray<Account> accountsMap = new LongSparseArray<Account>();
        List<Account> list = getAllAccountsList(false);
        for (Account account : list) {
            accountsMap.put(account.id, account);
        }
        return accountsMap;
    }

	/* ===============================================
	 * CURRENCY
	 * =============================================== */

    private static final String UPDATE_DEFAULT_FLAG = "update currency set is_default=0";

    public long saveOrUpdate(Currency currency) {
        SQLiteDatabase db = db();
        db.beginTransaction();
        try {
            if (currency.isDefault) {
                db.execSQL(UPDATE_DEFAULT_FLAG);
            }
            long id = super.saveOrUpdate(currency);
            db.setTransactionSuccessful();
            return id;
        } finally {
            db.endTransaction();
        }
    }

    public int deleteCurrency(long id) {
        String sid = String.valueOf(id);
        Currency c = load(Currency.class, id);
        writeDeleteLog(CURRENCY_TABLE, c.remoteKey);
        return db().delete(CURRENCY_TABLE, "_id=? AND NOT EXISTS (SELECT 1 FROM " + ACCOUNT_TABLE + " WHERE " + AccountColumns.CURRENCY_ID + "=?)",
                new String[]{sid, sid});
    }

    public Cursor getAllCurrencies(String sortBy) {
        Query<Currency> q = createQuery(Currency.class);
        return q.desc("isDefault").asc(sortBy).execute();
    }

    public List<Currency> getAllCurrenciesList() {
        return getAllCurrenciesList("name");
    }

    public List<Currency> getAllCurrenciesList(String sortBy) {
        Query<Currency> q = createQuery(Currency.class);
        return q.desc("isDefault").asc(sortBy).list();
    }

    public Map<String, Currency> getAllCurrenciesByTtitleMap() {
        return entitiesAsTitleMap(getAllCurrenciesList("name"));
    }

	/* ===============================================
	 * TRANSACTIONS
	 * =============================================== */

//	public Cursor getBlotter(WhereFilter blotterFilter) {
//		long t0 = System.currentTimeMillis();
//		try {
//			Query<TransactionInfo> q = createQuery(TransactionInfo.class);
//			if (!blotterFilter.isEmpty()) {
//				q.where(blotterFilter.toWhereExpression());
//			}
//			q.desc("dateTime");
//			return q.list();
//		} finally {
//			Log.d("BLOTTER", "getBlotter executed in "+(System.currentTimeMillis()-t0)+"ms");
//		}
//	}
//
//	public Cursor getTransactions(WhereFilter blotterFilter) {
//		return null;
//	}

//	public Cursor getAllProjects(boolean includeNoProject) {
//		Query<Project> q = createQuery(Project.class);
//		if (!includeNoProject) {
//			q.where(Expressions.neq("id", 0));
//		}
//		return q.list();
//	}

    public Project getProject(long id) {
        return get(Project.class, id);
    }

    public ArrayList<Project> getAllProjectsList(boolean includeNoProject) {
        return getAllEntitiesList(Project.class, includeNoProject);
    }

    public ArrayList<Project> getActiveProjectsList(boolean includeNoProject) {
        return getAllEntitiesList(Project.class, includeNoProject, true);
    }

    public Map<String, Project> getAllProjectsByTitleMap(boolean includeNoProject) {
        return entitiesAsTitleMap(getAllProjectsList(includeNoProject));
    }

    public LongSparseArray<Project> getAllProjectsByIdMap(boolean includeNoProject) {
        return entitiesAsIdMap(getAllProjectsList(includeNoProject));
    }

//	public Category getCategoryByLeft(long left) {
//		Query<Category> q = createQuery(Category.class);
//		q.where(Expressions.eq("left", left));
//		return q.uniqueResult();
//	}
//
//	public Cursor getAllCategories(boolean includeNoCategory) {
//		Query<CategoryInfo> q = createQuery(CategoryInfo.class);
//		if (!includeNoCategory) {
//			q.where(Expressions.neq("id", 0));
//		}
//		return q.list();
//	}
//	
//	public Cursor getAllCategoriesWithoutSubtree(long id) {
//		Category c = load(Category.class, id);
//		Query<CategoryInfo> q = createQuery(CategoryInfo.class);
//		q.where(Expressions.not(Expressions.and(
//				Expressions.gte("left", c.left),
//				Expressions.lte("right", c.right)
//		)));
//		return q.list();
//	}

    public long insertBudget(Budget budget) {
        SQLiteDatabase db = db();
        db.beginTransaction();
        try {
            if (budget.id > 0) {
                deleteBudget(budget.id);
            }
            long id = 0;
            Recur recur = RecurUtils.createFromExtraString(budget.recur);
            Period[] periods = RecurUtils.periods(recur);
            for (int i = 0; i < periods.length; i++) {
                Period p = periods[i];
                budget.id = -1;
                budget.parentBudgetId = id;
                budget.recurNum = i;
                budget.startDate = p.start;
                budget.endDate = p.end;
                long bid = super.saveOrUpdate(budget);
                if (i == 0) {
                    id = bid;
                }
            }
            db.setTransactionSuccessful();
            return id;
        } finally {
            db.endTransaction();
        }
    }

    public void deleteBudget(long id) {
        SQLiteDatabase db = db();
        Budget b = load(Budget.class, id);
        writeDeleteLog(BUDGET_TABLE, b.remoteKey);
        db.delete(BUDGET_TABLE, "_id=?", new String[]{String.valueOf(id)});
        String sql = "select remote_key from " + BUDGET_TABLE + " where parent_budget_id=" + id + "";
        Cursor cursorCursor = db.rawQuery(sql, null);
        if (cursorCursor.moveToFirst()) {
            do {
                String rKey = cursorCursor.getString(0);
                writeDeleteLog(BUDGET_TABLE, rKey);
            } while (cursorCursor.moveToNext());
        }
        cursorCursor.close();
        db.delete(BUDGET_TABLE, "parent_budget_id=?", new String[]{String.valueOf(id)});
    }

    public void deleteBudgetOneEntry(long id) {
        SQLiteDatabase db = db();
        Budget b = load(Budget.class, id);
        writeDeleteLog(BUDGET_TABLE, b.remoteKey);
        db.delete(BUDGET_TABLE, "_id=?", new String[]{String.valueOf(id)});
    }

    public ArrayList<Budget> getAllBudgets(WhereFilter filter) {
        Query<Budget> q = createQuery(Budget.class);
        Criteria c = filter.get(BlotterFilter.DATETIME);
        if (c != null) {
            long start = c.getLongValue1();
            long end = c.getLongValue2();
            q.where(Expressions.and(Expressions.lte("startDate", end), Expressions.gte("endDate", start)));
        }
        Cursor cursor = q.execute();
        try {
            ArrayList<Budget> list = new ArrayList<Budget>();
            while (cursor.moveToNext()) {
                Budget b = MyEntityManager.loadFromCursor(cursor, Budget.class);
                list.add(b);
            }
            return list;
        } finally {
            cursor.close();
        }
    }

    public void deleteProject(long id) {
        SQLiteDatabase db = db();
        db.beginTransaction();
        try {
            delete(Project.class, id);
            ContentValues values = new ContentValues();
            values.put("project_id", 0);
            db.update("transactions", values, "project_id=?", new String[]{String.valueOf(id)});
            db.setTransactionSuccessful();
        } finally {
            db.endTransaction();
        }
    }

    public ArrayList<TransactionInfo> getAllScheduledTransactions() {
        Query<TransactionInfo> q = createQuery(TransactionInfo.class);
        q.where(Expressions.and(
                Expressions.eq("isTemplate", 2),
                Expressions.eq("parentId", 0)));
        return (ArrayList<TransactionInfo>) q.list();
    }

    public ArrayList<Category> getAllCategoriesList(boolean includeNoCategory) {
        return getAllEntitiesList(Category.class, includeNoCategory);
    }

    public Payee insertPayee(String payee) {
        if (Utils.isEmpty(payee)) {
            return Payee.EMPTY;
        } else {
            Payee p = getPayee(payee);
            if (p == null) {
                p = new Payee();
                p.title = payee;
                p.id = saveOrUpdate(p);
            }
            return p;
        }
    }

    public Payee getPayee(String payee) {
        Query<Payee> q = createQuery(Payee.class);
        q.where(Expressions.eq("title", payee));
        return q.uniqueResult();
    }

    public Cursor getAllPayees() {
        Query<Payee> q = createQuery(Payee.class);
        return q.asc("title").execute();
    }

    public List<Payee> getAllPayeeList() {
        return getAllEntitiesList(Payee.class, true);
    }

    public Map<String, Payee> getAllPayeeByTitleMap() {
        return entitiesAsTitleMap(getAllPayeeList());
    }

    public LongSparseArray<Payee> getAllPayeeByIdMap() {
        return entitiesAsIdMap(getAllPayeeList());
    }

    public Cursor getAllPayeesLike(CharSequence constraint) {
        Query<Payee> q = createQuery(Payee.class);
        q.where(Expressions.or(
                Expressions.like("title", "%" + constraint + "%"),
                Expressions.like("title", "%" + capitalize(constraint.toString()) + "%")
        ));
        return q.asc("title").execute();
    }

    public List<Transaction> getSplitsForTransaction(long transactionId) {
        Query<Transaction> q = createQuery(Transaction.class);
        q.where(Expressions.eq("parentId", transactionId));
        return q.list();
    }

    public List<TransactionInfo> getSplitsInfoForTransaction(long transactionId) {
        Query<TransactionInfo> q = createQuery(TransactionInfo.class);
        q.where(Expressions.eq("parentId", transactionId));
        return q.list();
    }

    public List<TransactionInfo> getTransactionsForAccount(long accountId) {
        Query<TransactionInfo> q = createQuery(TransactionInfo.class);
        q.where(Expressions.and(
                Expressions.eq("fromAccount.id", accountId),
                Expressions.eq("parentId", 0)
        ));
        q.desc("dateTime");
        return q.list();
    }

    public void reInsertCategory(Category c) {
        reInsert(c);
    }

    public Currency getHomeCurrency() {
        Query<Currency> q = createQuery(Currency.class);
        q.where(Expressions.eq("isDefault", "1")); //uh-oh
        Currency homeCurrency = q.uniqueResult();
        if (homeCurrency == null) {
            homeCurrency = Currency.EMPTY;
        }
        return homeCurrency;
    }

    private long writeDeleteLog(String tableName, String remoteKey) {
        if (remoteKey == null || remoteKey.length() == 0) {
            return 0;
        }
        ContentValues row = new ContentValues();
        row.put(DatabaseHelper.deleteLogColumns.TABLE_NAME, tableName);
        row.put(DatabaseHelper.deleteLogColumns.REMOTE_KEY, remoteKey);
        row.put(DatabaseHelper.deleteLogColumns.DELETED_ON, System.currentTimeMillis());
        return db().insert(DatabaseHelper.DELETE_LOG_TABLE, null, row);
    }

    private static <T extends MyEntity> Map<String, T> entitiesAsTitleMap(List<T> entities) {
        Map<String, T> map = new HashMap<String, T>();
        for (T e : entities) {
            map.put(e.title, e);
        }
        return map;
    }

    private static <T extends MyEntity> LongSparseArray<T> entitiesAsIdMap(List<T> entities) {
        LongSparseArray<T> map = new LongSparseArray<T>();
        for (T e : entities) {
            map.put(e.id, e);
        }
        return map;
    }

    public void deleteAttribute(long id) {
        SQLiteDatabase db = db();
        db.beginTransaction();
        try {
            Attribute attr = get(Attribute.class, id);
            String key = attr.remoteKey;
            String[] p = new String[]{String.valueOf(id)};
            db.delete(ATTRIBUTES_TABLE, DatabaseHelper.AttributeColumns.ID + "=?", p);
            db.delete(CATEGORY_ATTRIBUTE_TABLE, DatabaseHelper.CategoryAttributeColumns.ATTRIBUTE_ID + "=?", p);
            db.delete(TRANSACTION_ATTRIBUTE_TABLE, DatabaseHelper.TransactionAttributeColumns.ATTRIBUTE_ID + "=?", p);
            db.setTransactionSuccessful();
            writeDeleteLog(ATTRIBUTES_TABLE, key);
        } finally {
            db.endTransaction();
        }
    }

    public Attribute getSystemAttribute(SystemAttribute a) {
        Attribute sa = get(Attribute.class, a.id);
        sa.name = context.getString(a.titleId);
        return sa;
    }

    public List<Attribute> getAllAttributes() {
        return createQuery(Attribute.class).where(Expressions.gt("id", 0)).asc("name").list();
    }

    public LongSparseArray<Attribute> getAllAttributesMap() {
        LongSparseArray<Attribute> array = new LongSparseArray<Attribute>();
        List<Attribute> attributes = getAllAttributes();
        for (Attribute attribute : attributes) {
            array.put(attribute.getId(), attribute);
        }
        return array;
    }
}