waterprice DS

package id.tukang.database.dao;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;

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

import id.tukang.database.MySQLiteHelper;
import id.tukang.database.model.WaterPrice;

public class WaterPricesDS {

    // table fields
    private SQLiteDatabase database;
    private MySQLiteHelper dbHelper;

    private String[] allColumnsByRankingParsing = {
            MySQLiteHelper.COLUMN_RANKING
            , MySQLiteHelper.COLUMN_ID
            , MySQLiteHelper.COLUMN_VENDOR
            , MySQLiteHelper.COLUMN_PRICE
            , MySQLiteHelper.COLUMN_NAME_BRAND
            , MySQLiteHelper.COLUMN_IMAGE_NAME
    };
    private String[] allColumnsByRanking = {
            MySQLiteHelper.COLUMN_ID
            , MySQLiteHelper.COLUMN_VENDOR
            , MySQLiteHelper.COLUMN_JSON_PRODUCT_VENDOR
    };
    private String[] allColumns = {
            MySQLiteHelper.COLUMN_ID
            , MySQLiteHelper.COLUMN_PRICE
            , MySQLiteHelper.COLUMN_NAME_BRAND
            , MySQLiteHelper.COLUMN_IMAGE_NAME
    };

    public WaterPricesDS(Context context) {
        dbHelper = new MySQLiteHelper(context);
    }

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

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

    public void addPrice(int id, long price) {

        ContentValues values = new ContentValues();
        values.put(MySQLiteHelper.COLUMN_ID, id);
        values.put(MySQLiteHelper.COLUMN_PRICE, price);
        database.insert(MySQLiteHelper.TABLE_WATER_PRICES, null,
                values);
    }

    //Septian: override method
    public void addPrice(int id, long price, String name) {

        ContentValues values = new ContentValues();
        values.put(MySQLiteHelper.COLUMN_ID, id);
        values.put(MySQLiteHelper.COLUMN_PRICE, price);
        values.put(MySQLiteHelper.COLUMN_NAME_BRAND, name);
        database.insert(MySQLiteHelper.TABLE_WATER_PRICES, null,
                values);
    }

    //Septian: override method
    public void addPrice(int id, long price, String name, String url) {
        ContentValues values = new ContentValues();
        values.put(MySQLiteHelper.COLUMN_ID, id);
        values.put(MySQLiteHelper.COLUMN_PRICE, price);
        values.put(MySQLiteHelper.COLUMN_NAME_BRAND, name);
        values.put(MySQLiteHelper.COLUMN_IMAGE_NAME, url);
        database.insert(MySQLiteHelper.TABLE_WATER_PRICES, null,
                values);
    }

    //Septian: override method vendor ranking
    public void addPrice(int id, String vendor, String jsonproduct) {
        ContentValues values = new ContentValues();
        values.put(MySQLiteHelper.COLUMN_ID, id);
        values.put(MySQLiteHelper.COLUMN_VENDOR, vendor);
        values.put(MySQLiteHelper.COLUMN_JSON_PRODUCT_VENDOR, jsonproduct);

        database.insert(MySQLiteHelper.TABLE_WATER_PRICE_BY_RANKING, null,
                values);
    }

    //septian: vendor ranking parsing
    public void addPrice(int ranking, String vendor, int id, long price, String name, String url) {
        ContentValues values = new ContentValues();
        values.put(MySQLiteHelper.COLUMN_RANKING, ranking);
        values.put(MySQLiteHelper.COLUMN_ID, id);
        values.put(MySQLiteHelper.COLUMN_VENDOR, vendor);
        values.put(MySQLiteHelper.COLUMN_PRICE, price);
        values.put(MySQLiteHelper.COLUMN_NAME_BRAND, name);
        values.put(MySQLiteHelper.COLUMN_IMAGE_NAME, url);

        database.insert(MySQLiteHelper.TABLE_WATER_PRICE_BY_RANKING_PARSING, null,
                values);
    }

    public void deleteAllPrice() {
        database.delete(MySQLiteHelper.TABLE_WATER_PRICES, null, null);
        database.delete(MySQLiteHelper.TABLE_WATER_PRICE_BY_RANKING_PARSING, null, null);
    }

//    public List<WaterPrice> getAllPrices() {
//        List<WaterPrice> waterPrices = new ArrayList<>();
//
//        Cursor cursor = database.query(MySQLiteHelper.TABLE_WATER_PRICES,
//                allColumns, null, null, null, null, null);
//
//        cursor.moveToFirst();
//        while (!cursor.isAfterLast()) {
//          WaterPrice waterPrice = cursorToItem(cursor);
//            waterPrices.add(waterPrice);
//            cursor.moveToNext();
//        }
//        // make sure to close the cursor
//        cursor.close();
//        return waterPrices;
//    }

    public WaterPrice getWaterPrice(int id) {
        Cursor cursor = database.query(MySQLiteHelper.TABLE_WATER_PRICES,
                allColumns, " " + MySQLiteHelper.COLUMN_ID + " = ? ", new String[]{String.valueOf(id)}, null, null, null);

        WaterPrice waterPrice = null;
        if (cursor.getCount() > 0) {
            cursor.moveToFirst();
            waterPrice = cursorToItem(cursor);
            cursor.close();
        }
        return waterPrice;
    }

    //Septian: get brand name from all vendor available
    public String[] getBrandNameArr() {

        Cursor cursor = database.query(MySQLiteHelper.TABLE_WATER_PRICES,
                allColumns, null, null, null, null, null);

        String[] waterName = new String[cursor.getCount() + 1];
        int i = 1;
        if (cursor.getCount() > 0) {
            cursor.moveToFirst();
            if (cursor.moveToFirst()) {
                do {
                    waterName[i] = cursor.getString(cursor.getColumnIndex(MySQLiteHelper.COLUMN_NAME_BRAND));
                    i++;
                } while (cursor.moveToNext());
            }
            cursor.close();
        }

        return waterName;
    }

    public String[] getBrandNameArrOnVendorRanking(String[] vendorInRanking) {
        List vendorInRankingStr = Arrays.asList(vendorInRanking);
        String ven = vendorInRankingStr.toString();
        String[] venStr;

        //replace bracket with paranthesses
        ven = ven.replace("[", "");
        ven = ven.replace("]", "");

        venStr = ven.split(",");
        for (int i = 0; i < venStr.length; i++) {
            venStr[i] = "'"+venStr[i]+"'";
        }

        List vendorInRankingStr2 = Arrays.asList(venStr);
        String ven2 = vendorInRankingStr2.toString();

        ven2 = ven2.replace("[","(");
        ven2 = ven2.replace("]", ")");

        String query = "select distinct(" + MySQLiteHelper.COLUMN_NAME_BRAND + ")"
                + " from " + MySQLiteHelper.TABLE_WATER_PRICE_BY_RANKING_PARSING
                + " where " + MySQLiteHelper.COLUMN_VENDOR + " in " + ven2;

        Cursor cursor = database.rawQuery(query, null);

        String[] brandName = new String[cursor.getCount() + 1];

        int i = 1;
        if (cursor.getCount() > 0) {
            cursor.moveToFirst();
            if (cursor.moveToFirst()) {
                do {
                    brandName[i] = cursor.getString(cursor.getColumnIndex(MySQLiteHelper.COLUMN_NAME_BRAND));
                    i++;
                } while (cursor.moveToNext());
            }
            cursor.close();
        }
        return brandName;
    }

    public String[] getVendorNameArrOnVendorRanking(String brandName) {

        Cursor cursor = database.query(true
                , MySQLiteHelper.TABLE_WATER_PRICE_BY_RANKING_PARSING
                , allColumnsByRankingParsing
                , MySQLiteHelper.COLUMN_NAME_BRAND + " = ?"
                , new String[]{brandName}
                , null, null, null, null);

        String[] vendorName = new String[cursor.getCount()];
        int i = 0;
        if (cursor.getCount() > 0) {
            cursor.moveToFirst();
            if (cursor.moveToFirst()) {
                do {
                    vendorName[i] = cursor.getString(cursor.getColumnIndex(MySQLiteHelper.COLUMN_VENDOR));
                    i++;
                } while (cursor.moveToNext());
            }
            cursor.close();
        }
        return vendorName;
    }

    public int getPriceCount() {
        String countQuery = "SELECT  " + MySQLiteHelper.COLUMN_ID + " FROM " + MySQLiteHelper.TABLE_WATER_PRICES;
        //      SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = database.rawQuery(countQuery, null);
        int count = cursor.getCount();
        cursor.close();

        return count;
    }

    //get item based in row in db sqlite
    private WaterPrice cursorToItem(Cursor cursor) {
        WaterPrice waterPrice = new WaterPrice();
        waterPrice.setId(cursor.getInt(0));
        waterPrice.setPrice(cursor.getLong(1));
        waterPrice.setName(cursor.getString(2));
        return waterPrice;
    }

    //this gonna be help to query data sqlite based on name of product
    //this is representing of 'product Id' not 'brand Id'
    public int getBrandIdOnName(String brandName) {
        String query = "SELECT "
                + MySQLiteHelper.COLUMN_ID + " FROM "
                + MySQLiteHelper.TABLE_WATER_PRICES + " WHERE "
                + MySQLiteHelper.COLUMN_NAME_BRAND + " = '" + brandName + "'";

        Cursor cursor = database.rawQuery(query, null);
        int idProduct = -2;

        cursor.moveToFirst();
        if (cursor.moveToFirst()) {
            idProduct = cursor.getInt(cursor.getColumnIndex(MySQLiteHelper.COLUMN_ID));
        }
        cursor.close();

        return idProduct;
    }

    public String getNameBrandOnName(String brandName) {
        String query = "SELECT "
                + MySQLiteHelper.COLUMN_NAME_BRAND + " FROM "
                + MySQLiteHelper.TABLE_WATER_PRICES + " WHERE "
                + MySQLiteHelper.COLUMN_NAME_BRAND + " = '" + brandName + "'";

        Cursor cursor = database.rawQuery(query, null);
        String nameBrand = "";

        cursor.moveToFirst();
        if (cursor.moveToFirst()) {
            nameBrand = cursor.getString(cursor.getColumnIndex(MySQLiteHelper.COLUMN_NAME_BRAND));
        }
        cursor.close();

        return nameBrand;
    }

    public long getPriceBrandOnName(String brandName) {
        String query = "SELECT "
                + MySQLiteHelper.COLUMN_PRICE + " FROM "
                + MySQLiteHelper.TABLE_WATER_PRICES + " WHERE "
                + MySQLiteHelper.COLUMN_NAME_BRAND + " = '" + brandName + "'";

        Cursor cursor = database.rawQuery(query, null);
        long priceBrand = -2;

        cursor.moveToFirst();
        if (cursor.moveToFirst()) {
            priceBrand = cursor.getInt(cursor.getColumnIndex(MySQLiteHelper.COLUMN_PRICE));
        }
        cursor.close();

        return priceBrand;
    }

    public String getImageBrandOnName(String brandName) {
        String query = "SELECT "
                + MySQLiteHelper.COLUMN_IMAGE_NAME + " FROM "
                + MySQLiteHelper.TABLE_WATER_PRICES + " WHERE "
                + MySQLiteHelper.COLUMN_NAME_BRAND + " = '" + brandName + "'";

        Cursor cursor = database.rawQuery(query, null);
        String urlImage = "invalid_url";

        cursor.moveToFirst();
        if (cursor.moveToFirst()) {
            urlImage = cursor.getString(cursor.getColumnIndex(MySQLiteHelper.COLUMN_IMAGE_NAME));
        }
        cursor.close();

        return urlImage;
    }

    //Septian: get all value vendor product by ranking
    public Map getAllProductVendorByRanking() {
        Cursor cursor = database.query(MySQLiteHelper.TABLE_WATER_PRICE_BY_RANKING,
                allColumnsByRanking, null, null, null, null, null);

        Map<String, List<String>> mapJson = new HashMap<>();
        List<String> listVendorProduct = new ArrayList<>();

        /*String[] vendor = new String[cursor.getCount() + 1];
        String[] jsonProduct = new String[cursor.getCount() + 1];*/

        int i = 0;
        if (cursor.getCount() > 0) {
            cursor.moveToFirst();
            if (cursor.moveToFirst()) {
                do {
                    /*vendor[i] = cursor.getString(cursor.getColumnIndex(MySQLiteHelper.COLUMN_VENDOR));
                    jsonProduct[i] = cursor.getString(cursor.getColumnIndex(MySQLiteHelper.COLUMN_JSON_PRODUCT_VENDOR));*/

                    listVendorProduct.add(cursor.getString(cursor.getColumnIndex(MySQLiteHelper.COLUMN_VENDOR)));
                    listVendorProduct.add(cursor.getString(cursor.getColumnIndex(MySQLiteHelper.COLUMN_JSON_PRODUCT_VENDOR)));
                    mapJson.put(String.valueOf(i), listVendorProduct);

                    i++;
                } while (cursor.moveToNext());
            }
            cursor.close();
        }
        return mapJson;
    }
}