Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- package dataBaseHandlers;
- import android.content.ContentValues;
- import android.content.Context;
- import android.content.SharedPreferences;
- import android.database.Cursor;
- import android.database.sqlite.SQLiteDatabase;
- import android.database.sqlite.SQLiteOpenHelper;
- import android.util.Log;
- import com.github.mikephil.charting.data.Entry;
- import com.google.gson.Gson;
- import com.google.gson.GsonBuilder;
- import java.text.DateFormat;
- import java.text.DecimalFormat;
- import java.text.ParseException;
- import java.text.SimpleDateFormat;
- import java.util.ArrayList;
- import java.util.Date;
- import java.util.HashMap;
- import java.util.LinkedHashSet;
- import java.util.Set;
- import java.util.concurrent.locks.ReadWriteLock;
- import java.util.concurrent.locks.ReentrantReadWriteLock;
- import model.SaleModel;
- /**
- * Created by steve on 7/3/16.
- */
- public class SalesHandler extends SQLiteOpenHelper {
- private static final String TAG = SalesHandler.class.getSimpleName();
- // Database Version
- private static final int DATABASE_VERSION = 31;
- // Database Name
- private static final String DATABASE_NAME = "Sales";
- // Table names
- private static final String TABLE_SALES = "loadSales";
- private static final String TABLE_MANY_SALES = "manySales";
- private static final String TABLE_TOTAL_SALES = "total_sales";
- private static final String TABLE_DELETE_SALES = "delete_sales";
- private static final String TABLE_SALES_KEYS = "sales_keys";
- private static final String TABLE_SALES_NOTIFICATIONS = "sales_notifications";
- // cCheckouts Table Columns names
- private static final String KEY_ID = "id";
- private static final String KEY_SID = "s_id";
- private static final String KEY_TIME_CODE = "time_code";
- private static final String KEY_ATTENDANT_ID = "attendant_id";
- private static final String KEY_ATTENDANT_NAME = "attendant_name";
- private static final String KEY_PRODUCT_ID = "product_id";
- private static final String KEY_CUSTOMER_ID = "customer_id";
- private static final String KEY_CUSTOMER_FIRST_NAME = "customer_first_name";
- private static final String KEY_CUSTOMER_LAST_NAME = "customer_last_name";
- private static final String KEY_CUSTOMER_IMAGE = "customer_image";
- private static final String KEY_UID = "uid";
- private static final String KEY_STATUS = "status";
- private static final String KEY_PRICE = "price";
- private static final String KEY_QUANTITY = "quantity";
- private static final String KEY_PRODUCT_NAME = "p_name";
- private static final String KEY_PRODUCT_IMAGE = "p_image";
- private static final String KEY_CREATED_AT = "created_at";
- private static final String KEY_CREATED_AT_AM_PM = "created_at_am_pm";
- private static final String KEY_CREATED_AT_DAY = "created_at_day";
- private static final String KEY_CREATED_AT_MONTH = "created_at_month";
- private static final String KEY_CREATED_AT_YEAR = "created_at_year";
- private static final String KEY_COUNT = "count";
- private static final String KEY_DELETED_AT = "deleted_at";
- private static final String KEY_OFFLINETAG = "offline_tag";
- private static final String KEY_PRODUCT_JSON = "jsonProducts";
- private static final String KEY_SVG = "svg";
- private static final String KEY_CREATED_AT_DATE = "created_at_date";
- private static final String KEY_UNIQUE = "unique_key";
- private static final String KEY_POSITION = "position";
- private static final String KEY_MESSAGE = "message";
- private static final ReadWriteLock rwLock = new ReentrantReadWriteLock(true);
- private static SalesHandler sInstance;
- int pos = 0;
- HashMap<String, String> map = new HashMap<String, String>();
- ArrayList<HashMap<String, String>> arraylist = new ArrayList<HashMap<String, String>>();
- Context context;
- double sum = 0;
- int count = 0;
- String set_variant;
- HashMap<String, String> amountMap = new HashMap<String, String>();
- ArrayList<String> amountArraylist = new ArrayList<String>();
- ArrayList<String> hour_labels = new ArrayList<>();
- ArrayList<String> product_labels = new ArrayList<>();
- ArrayList<String> customer_labels = new ArrayList<>();
- ArrayList<String> staff_labels = new ArrayList<>();
- ArrayList<String> notificationsList = new ArrayList<String>();
- ArrayList<String> sumQuantityList = new ArrayList<>();
- ArrayList<SaleModel> sales = new ArrayList<>();
- String period_label;
- DecimalFormat mFormat = new DecimalFormat("###,###,###");
- private HashMap hp;
- public SalesHandler(Context context) {
- super(context, DATABASE_NAME, null, DATABASE_VERSION);
- this.context = context;
- }
- public static synchronized SalesHandler getInstance(Context context) {
- // Use the application context, which will ensure that you
- // don't accidentally leak an Activity's context.
- // See this article for more information: http://bit.ly/6LRzfx
- if (sInstance == null) {
- sInstance = new SalesHandler(context.getApplicationContext());
- }
- return sInstance;
- }
- private static void beginReadLock() {
- rwLock.readLock().lock();
- }
- private static void endReadLock() {
- rwLock.readLock().unlock();
- }
- private static void beginWriteLock() {
- rwLock.writeLock().lock();
- }
- private static void endWriteLock() {
- rwLock.writeLock().unlock();
- }
- @Override
- public void onCreate(SQLiteDatabase db) {
- String CREATE_SALES_TABLE = "CREATE TABLE " + TABLE_SALES + "("
- + KEY_ID + " INTEGER PRIMARY KEY," + KEY_TIME_CODE + " TEXT," + KEY_ATTENDANT_ID + " TEXT," + KEY_SID + " TEXT," + KEY_ATTENDANT_NAME + " TEXT,"
- + KEY_PRODUCT_ID + " TEXT," + KEY_CUSTOMER_ID + " TEXT," + KEY_QUANTITY + " TEXT," + KEY_PRODUCT_NAME + " TEXT,"
- + KEY_PRODUCT_IMAGE + " TEXT," + KEY_CREATED_AT + " TEXT," + KEY_DELETED_AT + " TEXT," + KEY_PRICE + " TEXT," + KEY_UNIQUE + " TEXT unique,"
- + KEY_CUSTOMER_FIRST_NAME + " TEXT," + KEY_CUSTOMER_LAST_NAME + " TEXT," + KEY_CUSTOMER_IMAGE + " TEXT," + KEY_UID + " TEXT," + KEY_CREATED_AT_AM_PM + " TEXT,"
- + KEY_SVG + " TEXT," + KEY_CREATED_AT_DATE + " TEXT," + KEY_COUNT + " TEXT,"
- + KEY_OFFLINETAG + " TEXT" + ")";
- String CREATE_MANY_SALES_TABLE = "CREATE TABLE " + TABLE_MANY_SALES + "("
- + KEY_ID + " INTEGER PRIMARY KEY," + KEY_ATTENDANT_ID + " TEXT," + KEY_ATTENDANT_NAME + " TEXT,"
- + KEY_CUSTOMER_ID + " TEXT," + KEY_PRODUCT_JSON + " TEXT," + KEY_CREATED_AT + " TEXT,"
- + KEY_PRICE + " TEXT," + KEY_OFFLINETAG + " TEXT," + KEY_STATUS + " TEXT," + KEY_TIME_CODE + " TEXT" + ")";
- String CREATE_TOTAL_SALES_AMOUNT = "CREATE TABLE " + TABLE_TOTAL_SALES + "("
- + KEY_ID + " INTEGER PRIMARY KEY," + KEY_QUANTITY + " TEXT," + KEY_CREATED_AT + " TEXT,"
- + KEY_CREATED_AT_DAY + " TEXT," + KEY_CREATED_AT_MONTH + " TEXT,"
- + KEY_CREATED_AT_YEAR + " TEXT," + KEY_COUNT + " TEXT," + KEY_ATTENDANT_ID + " TEXT,"
- + KEY_ATTENDANT_NAME + " TEXT," + KEY_PRICE + " TEXT,"
- + KEY_PRODUCT_ID + " TEXT," + KEY_PRODUCT_NAME + " TEXT," + KEY_CUSTOMER_ID + " TEXT,"
- + KEY_CUSTOMER_FIRST_NAME + " TEXT," + KEY_CUSTOMER_LAST_NAME + " TEXT,"
- + KEY_CREATED_AT_AM_PM + " TEXT" + ")";
- String CREATE_DELETED_SALES = "CREATE TABLE " + TABLE_DELETE_SALES + "("
- + KEY_ID + " INTEGER PRIMARY KEY," + KEY_POSITION + " INTEGER,"
- + KEY_SID + " TEXT" + ")";
- String CREATE_SALES_KEYS = "CREATE TABLE " + TABLE_SALES_KEYS + "("
- + KEY_ID + " INTEGER PRIMARY KEY," + KEY_TIME_CODE + " TEXT" + ")";
- String CREATE_SALES_NOTIFICATIONS = "CREATE TABLE " + TABLE_SALES_NOTIFICATIONS + "("
- + KEY_ID + " INTEGER PRIMARY KEY," + KEY_QUANTITY + " TEXT," + KEY_MESSAGE + " TEXT" + ")";
- db.execSQL(CREATE_SALES_TABLE);
- db.execSQL(CREATE_MANY_SALES_TABLE);
- db.execSQL(CREATE_TOTAL_SALES_AMOUNT);
- db.execSQL(CREATE_DELETED_SALES);
- db.execSQL(CREATE_SALES_KEYS);
- db.execSQL(CREATE_SALES_NOTIFICATIONS);
- }
- @Override
- public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
- db.execSQL("DROP TABLE IF EXISTS " + TABLE_SALES);
- db.execSQL("DROP TABLE IF EXISTS " + TABLE_MANY_SALES);
- db.execSQL("DROP TABLE IF EXISTS " + TABLE_TOTAL_SALES);
- db.execSQL("DROP TABLE IF EXISTS " + TABLE_DELETE_SALES);
- db.execSQL("DROP TABLE IF EXISTS " + TABLE_SALES_KEYS);
- db.execSQL("DROP TABLE IF EXISTS " + TABLE_SALES_NOTIFICATIONS);
- // Create tables again
- onCreate(db);
- }
- /**
- * Storing user details in database
- */
- public void addToDisplaySalesTable(SaleModel sale) {
- {
- SQLiteDatabase db = null;
- try {
- beginWriteLock();
- db = this.getWritableDatabase();
- /*this.getWritableDatabase();*/
- ContentValues values = new ContentValues();
- values.put(KEY_SID, sale.getId());
- values.put(KEY_TIME_CODE, sale.getTime_code());
- values.put(KEY_ATTENDANT_ID, sale.getAttendant_id());
- values.put(KEY_ATTENDANT_NAME, sale.getAttendant_name());
- values.put(KEY_PRODUCT_ID, sale.getProduct_id());
- values.put(KEY_PRICE, sale.getPrice()); //price
- values.put(KEY_CUSTOMER_ID, sale.getCustomer_id());
- values.put(KEY_CUSTOMER_FIRST_NAME, sale.getCustomer_first_name());
- values.put(KEY_CUSTOMER_LAST_NAME, sale.getCustomer_last_name());
- values.put(KEY_CUSTOMER_IMAGE, sale.getCustomer_image());
- values.put(KEY_UID, sale.getUid());
- values.put(KEY_QUANTITY, sale.getQuantity());
- values.put(KEY_PRODUCT_NAME, sale.getName());
- values.put(KEY_PRODUCT_IMAGE, sale.getImage());
- values.put(KEY_CREATED_AT, sale.getCreated_at());
- values.put(KEY_DELETED_AT, sale.getDeleted_at());
- values.put(KEY_CREATED_AT_DATE, createdAtDate(sale.getCreated_at()));
- values.put(KEY_SVG, sale.getSvg());
- values.put(KEY_COUNT, "1");
- values.put(KEY_OFFLINETAG, sale.getOfflineTag());
- values.put(KEY_UNIQUE, sale.getUnique_key());
- values.put(KEY_CREATED_AT_AM_PM, getTimeFormattedInAMPM(sale.getCreated_at()).replace("0", ""));
- // db.insertWithOnConflict(tableName, BaseColumns._ID, v, SQLiteDatabase.CONFLICT_REPLACE);
- long id = db.insertWithOnConflict(TABLE_SALES, KEY_UNIQUE, values, SQLiteDatabase.CONFLICT_REPLACE);
- Log.d("addsale_ ", "addToDisplaySalesTable code" + sale.getTime_code());
- Log.d(TAG, "New sale inserted into sqlite: " + id);
- // clear Synced Sales of a previous date
- deletePreviousDaySales();
- // Closing database connection
- // db.close();
- } catch (Exception e) {
- Log.d("addsale_exceptions1 ", e.toString());
- } finally {
- if (db != null) {
- try {
- // db.close();
- } catch (Exception e) {
- Log.d("addsale_exceptions2", e.toString());
- }
- }
- endWriteLock();
- }
- }
- }
- public void deletePreviousDaySales() {
- SQLiteDatabase db = this.getWritableDatabase();
- db.execSQL("DELETE FROM " + TABLE_SALES + " WHERE `" + KEY_OFFLINETAG + "` = 'false' AND `"
- + KEY_CREATED_AT_DATE + "` != '" + getCurrentDate() + "'");
- }
- String getCurrentDate() {
- Date now = new Date();
- SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
- return sdf.format(now);
- }
- String createdAtDate(String created_at) {
- Date createdAtDate = new Date();
- try {
- createdAtDate = new SimpleDateFormat("yyyy-MM-dd").parse(created_at);
- } catch (ParseException e) {
- e.printStackTrace();
- }
- return new SimpleDateFormat("yyyy-MM-dd").format(createdAtDate);
- }
- public ArrayList<SaleModel> displaySales() {
- sales.clear();
- hp = new HashMap();
- ArrayList<SaleModel> sales = new ArrayList<>();
- {
- SQLiteDatabase db = null;
- try {
- beginReadLock();
- db = this.getWritableDatabase();
- Cursor res = db.rawQuery("SELECT * FROM " + TABLE_SALES + " ORDER BY datetime(created_at) DESC", null);
- //Cursor res = db.rawQuery("SELECT * FROM " + TABLE_SALES + " ORDER BY id ASC", null);
- //Cursor res = db.rawQuery("SELECT * FROM " + TABLE_SALES, null);
- res.moveToFirst();
- while (!res.isAfterLast()) {
- SaleModel l_sales = new SaleModel();
- l_sales.setId(res.getInt(res
- .getColumnIndex(KEY_SID)));
- l_sales.setTime_code(res.getString(res
- .getColumnIndex(KEY_TIME_CODE)));
- l_sales.setAttendant_id(res.getString(res
- .getColumnIndex(KEY_ATTENDANT_ID)));
- l_sales.setAttendant_name(res.getString(res
- .getColumnIndex(KEY_ATTENDANT_NAME)));
- l_sales.setProduct_id(res.getString(res
- .getColumnIndex(KEY_PRODUCT_ID)));
- l_sales.setPrice(res.getString(res
- .getColumnIndex(KEY_PRICE)));
- l_sales.setCustomer_id(res.getString(res
- .getColumnIndex(KEY_CUSTOMER_ID)));
- l_sales.setCustomer_first_name(res.getString(res
- .getColumnIndex(KEY_CUSTOMER_FIRST_NAME)));
- l_sales.setCustomer_last_name(res.getString(res
- .getColumnIndex(KEY_CUSTOMER_LAST_NAME)));
- l_sales.setCustomer_image(res.getString(res
- .getColumnIndex(KEY_CUSTOMER_IMAGE)));
- l_sales.setUid(res.getString(res
- .getColumnIndex(KEY_UID)));
- l_sales.setQuantity(res.getString(res
- .getColumnIndex(KEY_QUANTITY)));
- l_sales.setName(res.getString(res
- .getColumnIndex(KEY_PRODUCT_NAME)));
- l_sales.setImage(res.getString(res
- .getColumnIndex(KEY_PRODUCT_IMAGE)));
- l_sales.setCreated_at(res.getString(res
- .getColumnIndex(KEY_CREATED_AT)));
- l_sales.setSvg(res.getString(res
- .getColumnIndex(KEY_SVG)));
- l_sales.setOfflineTag(res.getString(res
- .getColumnIndex(KEY_OFFLINETAG)));
- l_sales.setQuantity_name(res.getString(res
- .getColumnIndex(KEY_QUANTITY)) + " " + res.getString(res
- .getColumnIndex(KEY_PRODUCT_NAME)));
- Log.d("my_products", res.getString(res
- .getColumnIndex(KEY_QUANTITY)) + " " + res.getString(res
- .getColumnIndex(KEY_PRODUCT_NAME)));
- if (res.getString(res.getColumnIndex(KEY_DELETED_AT)).length() < 5) {
- sales.add(l_sales);
- }
- res.moveToNext();
- }
- res.close();
- Set<SaleModel> final_sales = new LinkedHashSet<SaleModel>(sales);
- final_sales.addAll(sales);
- sales.clear();
- sales.addAll(final_sales);
- Log.d(TAG, "Fetching sales from Sqlite: " + sales.toString());
- } catch (Exception e) {
- Log.d("addsale_exceptions1 ", e.toString());
- } finally {
- if (db != null) {
- try {
- // db.close();
- } catch (Exception e) {
- Log.d("addsale_exceptions2", e.toString());
- }
- }
- endReadLock();
- }
- }
- return sales;
- }
- public ArrayList<SaleModel> displayFilteredSales() {
- arraylist.clear();
- ArrayList<SaleModel> sales = new ArrayList<>();
- hp = new HashMap();
- {
- SQLiteDatabase db = null;
- try {
- beginReadLock();
- db = this.getWritableDatabase();
- Cursor res = db.rawQuery("SELECT * FROM " + TABLE_SALES + " ORDER BY datetime(created_at) DESC", null);
- //Cursor res = db.rawQuery("SELECT * FROM " + TABLE_SALES + " ORDER BY id ASC", null);
- //Cursor res = db.rawQuery("SELECT * FROM " + TABLE_SALES, null);
- res.moveToFirst();
- while (!res.isAfterLast()) {
- SaleModel l_sales = new SaleModel();
- l_sales.setId(res.getInt(res
- .getColumnIndex(KEY_SID)));
- l_sales.setTime_code(res.getString(res
- .getColumnIndex(KEY_TIME_CODE)));
- l_sales.setAttendant_id(res.getString(res
- .getColumnIndex(KEY_ATTENDANT_ID)));
- l_sales.setAttendant_name(res.getString(res
- .getColumnIndex(KEY_ATTENDANT_NAME)));
- l_sales.setProduct_id(res.getString(res
- .getColumnIndex(KEY_PRODUCT_ID)));
- l_sales.setPrice(res.getString(res
- .getColumnIndex(KEY_PRICE)));
- l_sales.setCustomer_id(res.getString(res
- .getColumnIndex(KEY_CUSTOMER_ID)));
- l_sales.setCustomer_first_name(res.getString(res
- .getColumnIndex(KEY_CUSTOMER_FIRST_NAME)));
- l_sales.setCustomer_last_name(res.getString(res
- .getColumnIndex(KEY_CUSTOMER_LAST_NAME)));
- l_sales.setCustomer_image(res.getString(res
- .getColumnIndex(KEY_CUSTOMER_IMAGE)));
- l_sales.setUid(res.getString(res
- .getColumnIndex(KEY_UID)));
- l_sales.setQuantity(res.getString(res
- .getColumnIndex(KEY_QUANTITY)));
- l_sales.setName(res.getString(res
- .getColumnIndex(KEY_PRODUCT_NAME)));
- l_sales.setImage(res.getString(res
- .getColumnIndex(KEY_PRODUCT_IMAGE)));
- l_sales.setCreated_at(res.getString(res
- .getColumnIndex(KEY_CREATED_AT)));
- l_sales.setSvg(res.getString(res
- .getColumnIndex(KEY_SVG)));
- l_sales.setOfflineTag(res.getString(res
- .getColumnIndex(KEY_OFFLINETAG)));
- l_sales.setQuantity_name(res.getString(res
- .getColumnIndex(KEY_QUANTITY)) + " " + res.getString(res
- .getColumnIndex(KEY_PRODUCT_NAME)));
- Log.d("my_products", res.getString(res
- .getColumnIndex(KEY_QUANTITY)) + " " + res.getString(res
- .getColumnIndex(KEY_PRODUCT_NAME)));
- if (res.getString(res.getColumnIndex(KEY_DELETED_AT)).length() < 5) {
- if (getattid().equals(res.getString(res
- .getColumnIndex(KEY_ATTENDANT_ID)))) {
- sales.add(l_sales);
- }
- }
- res.moveToNext();
- }
- Log.d(TAG, "Fetching sales from Sqlite: ");
- res.close();
- Set<SaleModel> final_sales = new LinkedHashSet<SaleModel>(sales);
- final_sales.addAll(sales);
- sales.clear();
- sales.addAll(final_sales);
- } catch (Exception e) {
- Log.d("addsale_exceptions1 ", e.toString());
- } finally {
- if (db != null) {
- try {
- // db.close();
- } catch (Exception e) {
- Log.d("addsale_exceptions2", e.toString());
- }
- }
- endReadLock();
- }
- }
- return sales;
- }
- public ArrayList<Entry> sales_values() {
- hour_labels.clear();
- Cursor c = null;
- ArrayList<Entry> values = new ArrayList<>();
- SQLiteDatabase db = this.getReadableDatabase();
- if (set_variant.equals("price")) {
- c = db.rawQuery("select created_at_am_pm ,sum(price) as sum \n" +
- "from loadSales\n" +
- "group by created_at_am_pm ", null);
- } else {
- c = db.rawQuery("select created_at_am_pm ,sum(quantity) as sum \n" +
- "from loadSales\n" +
- "group by created_at_am_pm ", null);
- }
- int i = 0;
- try {
- while (c.moveToNext()) {
- Double sum = c.getDouble(1);
- // int count = c.getCount();
- // Log.d("Sum is: " + sum);
- hour_labels.add(c.getString(c.getColumnIndex("created_at_am_pm")).replace("0", ""));
- values.add(new Entry(Float.valueOf(String.valueOf(sum)), i));
- Log.d("sum_values_", String.valueOf(sum) + "/" + c.getString(c.getColumnIndex("created_at_am_pm")));
- i++;
- }
- } finally {
- c.close();
- }
- Log.d("sum_values_values", values.size() + "/" + hour_labels.size());
- return values;
- }
- public ArrayList<Entry> products_values() {
- Cursor c = null;
- product_labels.clear();
- ArrayList<Entry> values = new ArrayList<>();
- SQLiteDatabase db = this.getReadableDatabase();
- if (set_variant.equals("price")) {
- c = db.rawQuery("select p_name ,sum(price) as sum \n" +
- "from loadSales\n" +
- "group by product_id ORDER BY sum(price) DESC LIMIT 20", null);
- } else {
- c = db.rawQuery("select p_name ,sum(quantity) as sum \n" +
- "from loadSales\n" +
- "group by product_id ORDER BY sum(quantity) DESC LIMIT 20", null);
- }
- int i = 0;
- if (c != null) {
- try {
- while (c.moveToNext()) {
- Double sum = c.getDouble(1);
- product_labels.add(mFormat.format(sum).replace(".0", "") + " " + c.getString(c.getColumnIndex("p_name")));
- values.add(new Entry(Float.valueOf(String.valueOf(sum)), i));
- Log.d("sum_product_values_", String.valueOf(sum) + "/" + c.getString(c.getColumnIndex("p_name")));
- i++;
- }
- } finally {
- c.close();
- }
- }
- return values;
- }
- public ArrayList<Entry> customer_values() {
- customer_labels.clear();
- ArrayList<Entry> values = new ArrayList<>();
- Cursor c = null;
- SQLiteDatabase db = this.getReadableDatabase();
- /* Cursor c = db.rawQuery("select * ,sum(quantity) as sum \n" +
- "from loadSales\n" +
- "group by customer_id ORDER BY sum(quantity) DESC LIMIT 20", null);*/
- if (set_variant.equals("price")) {
- c = db.rawQuery("select customer_first_name,sum(price) as sum \n" +
- "from loadSales\n" +
- "group by customer_id ORDER BY sum DESC LIMIT 20", null);
- } else {
- c = db.rawQuery("select customer_first_name,sum(quantity) as sum \n" +
- "from loadSales\n" +
- "group by customer_id ORDER BY sum DESC LIMIT 20", null);
- }
- int i = 0;
- if (c != null) {
- try {
- while (c.moveToNext()) {
- Double sum = c.getDouble(1);
- customer_labels.add(mFormat.format(sum).replace(".0", "") + " " + c.getString(c.getColumnIndex("customer_first_name"))
- /* + " " + c.getString(c.getColumnIndex("customer_last_name"))*/);
- values.add(new Entry(Float.valueOf(String.valueOf(sum)), i));
- Log.d("sum_customer_values_", String.valueOf(sum) + "/" + c.getString(c.getColumnIndex("customer_first_name")));
- i++;
- }
- } finally {
- c.close();
- }
- }
- // Log.d("sum_product_values_", values.size() + "/" + hour_labels.size());
- return values;
- }
- public ArrayList<Entry> staff_values() {
- staff_labels.clear();
- ArrayList<Entry> values = new ArrayList<>();
- Cursor c = null;
- SQLiteDatabase db = this.getReadableDatabase();
- if (set_variant.equals("price")) {
- c = db.rawQuery("select attendant_name,sum(price) as sum \n" +
- "from loadSales\n" +
- "group by attendant_id ORDER BY sum DESC", null);
- } else {
- c = db.rawQuery("select attendant_name,sum(quantity) as sum \n" +
- "from loadSales\n" +
- "group by attendant_id ORDER BY sum DESC", null);
- }
- int i = 0;
- if (c != null) {
- try {
- while (c.moveToNext()) {
- Double sum = c.getDouble(1);
- staff_labels.add(mFormat.format(sum) + " " + c.getString(c.getColumnIndex("attendant_name"))
- /* + " " + c.getString(c.getColumnIndex("customer_last_name"))*/);
- values.add(new Entry(Float.valueOf(String.valueOf(sum)), i));
- Log.d("sum_staff_values_", String.valueOf(sum) + "/" + c.getString(c.getColumnIndex("attendant_name")));
- i++;
- }
- } finally {
- c.close();
- }
- }
- // Log.d("sum_product_values_", values.size() + "/" + hour_labels.size());
- return values;
- }
- public String setVariant(String variant) {
- set_variant = variant;
- return variant;
- }
- private String getTimeFormattedInAMPM(String dateStr) {
- DateFormat readFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
- DateFormat writeFormat = new SimpleDateFormat("ha");
- Date date = null;
- try {
- date = readFormat.parse(dateStr);
- } catch (ParseException e) {
- e.printStackTrace();
- }
- String formattedDate = writeFormat.format(date);
- return formattedDate;
- }
- public ArrayList<String> hour_labels() {
- return hour_labels;
- }
- public ArrayList<String> product_labels() {
- return product_labels;
- }
- public ArrayList<String> customer_labels() {
- return customer_labels;
- }
- public ArrayList<String> staff_labels() {
- return staff_labels;
- }
- public double getFilteredTotalSales() {
- SQLiteDatabase db = this.getReadableDatabase();
- Cursor c = db.rawQuery("SELECT SUM(price) FROM " + TABLE_SALES + " WHERE "
- + KEY_ATTENDANT_ID + "= '" + getattid() + "' AND " + KEY_DELETED_AT + " IS ''", null);
- c.moveToFirst();
- double i = c.getDouble(0);
- Log.d("double value database", String.valueOf(i));
- c.close();
- return i;
- }
- String getattid() {
- SharedPreferences spref2 = context.getSharedPreferences("ACCOUNT", context.MODE_PRIVATE);
- String attid = spref2.getString("attid", "");
- return attid;
- }
- String getAdmin() {
- SharedPreferences spref2 = context.getSharedPreferences("ACCOUNT", context.MODE_PRIVATE);
- String admin = spref2.getString("admin", "");
- return admin;
- }
- public void deleteSingleSale(String sale_id) {
- //Open the database
- SQLiteDatabase database = context.openOrCreateDatabase(DATABASE_NAME, Context.MODE_PRIVATE, null);
- //Execute sql query to remove from database
- //NOTE: When removing by String in SQL, value must be enclosed with ''
- database.execSQL("DELETE FROM " + TABLE_SALES + " WHERE " + KEY_SID + "= '" + sale_id + "'");
- Log.d("deleted_sale", sale_id + " from sqlite");
- //Close the database
- //causes SQLDatabaseLocked Exception
- //database.close();
- }
- public void deleteSingleSaleOnChildChanged(String time_code) {
- //Open the database
- SQLiteDatabase database = context.openOrCreateDatabase(DATABASE_NAME, Context.MODE_PRIVATE, null);
- //Execute sql query to remove from database
- //NOTE: When removing by String in SQL, value must be enclosed with ''
- database.execSQL("DELETE FROM " + TABLE_SALES + " WHERE " + KEY_TIME_CODE + "= '" + time_code + "'");
- Log.d("deleted_sale", time_code + " from sqlite");
- //Close the database
- //causes SQLDatabaseLocked Exception
- //database.close();
- }
- public void deleteSingleSale(int sale_id) {
- //Open the database
- SQLiteDatabase database = context.openOrCreateDatabase(DATABASE_NAME, Context.MODE_PRIVATE, null);
- //Execute sql query to remove from database
- //NOTE: When removing by String in SQL, value must be enclosed with ''
- database.execSQL("DELETE FROM " + TABLE_SALES + " WHERE " + KEY_SID + "= '" + sale_id + "'");
- Log.d("deleted_sale", sale_id + " from sqlite");
- //Close the database
- //causes SQLDatabaseLocked Exception
- //database.close();
- }
- public void deleteSales() {
- SQLiteDatabase db = context.openOrCreateDatabase(DATABASE_NAME, Context.MODE_PRIVATE, null);
- // Delete All Rows
- db.delete(TABLE_SALES, null, null);
- // db.close();
- Log.d(TAG, "Deleted all user info from sqlite");
- }
- public boolean checkForTables() {
- boolean hasRows = false;
- SQLiteDatabase db = getReadableDatabase();
- Cursor cursor = db.rawQuery("SELECT COUNT(*) FROM " + TABLE_SALES, null);
- cursor.moveToFirst();
- int count = cursor.getInt(0);
- if (count > 0)
- hasRows = true;
- cursor.close();
- return hasRows;
- }
- public int getRowCount() {
- String countQuery = "SELECT * FROM " + TABLE_SALES;
- SQLiteDatabase db = this.getReadableDatabase();
- Cursor cursor = db.rawQuery(countQuery, null);
- int cnt = cursor.getCount();
- // return row count
- cursor.close();
- return cnt;
- }
- public double getTotalSales() {
- SQLiteDatabase db = this.getReadableDatabase();
- Cursor c = db.rawQuery("SELECT SUM(price) FROM " + TABLE_SALES + " WHERE " + KEY_DELETED_AT + " IS ''", null);
- c.moveToFirst();
- double i = c.getDouble(0);
- Log.d("double value database", String.valueOf(i));
- c.close();
- return i;
- }
- public void updateSingleSale(String s_id, String attendant_id, String
- attendant_name, String product_id,
- String price, String customer_id, String customer_first_name, String
- customer_last_name,
- String customer_image, String uid, String quantity, String p_name, String
- p_image, String created_at,
- String deleted_at, String svg, String offline_tag, String time_code) {
- SQLiteDatabase db = context.openOrCreateDatabase(DATABASE_NAME, Context.MODE_PRIVATE, null);
- Log.d("addsale_edited4", time_code);
- ContentValues values = new ContentValues();
- values.put(KEY_SID, s_id);
- values.put(KEY_TIME_CODE, time_code);
- values.put(KEY_ATTENDANT_ID, attendant_id);
- values.put(KEY_ATTENDANT_NAME, attendant_name);
- values.put(KEY_PRODUCT_ID, product_id);
- values.put(KEY_PRICE, price); //price
- values.put(KEY_CUSTOMER_ID, customer_id);
- values.put(KEY_CUSTOMER_FIRST_NAME, customer_first_name);
- values.put(KEY_CUSTOMER_LAST_NAME, customer_last_name);
- values.put(KEY_CUSTOMER_IMAGE, customer_image);
- values.put(KEY_UID, uid);
- values.put(KEY_QUANTITY, quantity);
- values.put(KEY_PRODUCT_NAME, p_name);
- values.put(KEY_PRODUCT_IMAGE, p_image);
- values.put(KEY_CREATED_AT, created_at);
- values.put(KEY_DELETED_AT, deleted_at);
- values.put(KEY_SVG, svg);
- values.put(KEY_OFFLINETAG, offline_tag);
- // Inserting Row
- long id = db.update(TABLE_SALES, values, KEY_SID + "=" + s_id, null);
- //myDataBase.update(TABLE, con, KEY_ID + "=" + id,null);
- // mDb.update(DATABASE_TABLE, args, KEY_ROWID + "=" + rowId, null)>0;
- // Closing database connection
- Log.d("sale_id_edit", s_id);
- }
- public String getLastSaleCreatedAtDate() {
- String selectQuery = "SELECT * FROM " + TABLE_SALES + " ORDER BY id ASC LIMIT 1";
- SQLiteDatabase db = context.openOrCreateDatabase(DATABASE_NAME, Context.MODE_PRIVATE, null);
- Cursor cursor = db.rawQuery(selectQuery, null);
- String str = "0";
- if (cursor.moveToFirst()) {
- str = cursor.getString(cursor.getColumnIndex(KEY_CREATED_AT));
- }
- cursor.close();
- return str;
- }
- /**
- * MANY SALES+==================================================>
- */
- public void addToPendingSalesTable(String jsonProducts, String attendant_name, String
- phone, String price, String customer_id, String attendant_id, String datetime, String timeCode, String status) {
- SQLiteDatabase database = context.openOrCreateDatabase(DATABASE_NAME, Context.MODE_PRIVATE, null);
- ContentValues values = new ContentValues();
- values.put(KEY_ATTENDANT_ID, attendant_id);
- values.put(KEY_ATTENDANT_NAME, attendant_name);
- values.put(KEY_CUSTOMER_ID, customer_id);
- values.put(KEY_PRICE, price);
- values.put(KEY_CREATED_AT, datetime);
- values.put(KEY_TIME_CODE, timeCode);
- values.put(KEY_PRODUCT_JSON, jsonProducts);
- values.put(KEY_STATUS, status);
- // values.put("updateStatus", "no");
- long id = database.insert(TABLE_MANY_SALES, null, values);
- // Closing database connection
- // database.endTransaction();
- Log.d(TAG, "Offline checkout inserted into sqlite: " + "");
- }
- public String composeSalesJSONfromSQLite() {
- //convert sqlite rows into Json fields
- Gson gson = new GsonBuilder().create();
- ArrayList<HashMap<String, Object>> offlineList = new ArrayList<>();
- // here is what i'm talking about
- // String product = "[{\"id\":\"2\",\"title\":\"bag\"}]";
- String selectQuery = "SELECT * FROM manySales ";
- SQLiteDatabase database = this.getWritableDatabase();
- Cursor cursor = database.rawQuery(selectQuery, null);
- if (cursor.moveToFirst()) {
- do {
- Object[] productObj = gson.fromJson(cursor.getString(4), Object[].class);
- HashMap<String, Object> map = new HashMap<>();
- map.put("products", productObj);
- map.put("attendant_id", cursor.getString(1));
- map.put("attendant_name", cursor.getString(2));
- map.put("customer_id", cursor.getString(3));
- map.put("created_at", cursor.getString(5));
- map.put("price", cursor.getString(6));
- map.put("time_code", cursor.getString(9));
- //get sales that have pending status, to avoid duplication
- if (cursor.getString(8).equals("pending")) {
- offlineList.add(map);
- }
- } while (cursor.moveToNext());
- }
- cursor.close();
- // database.endTransaction();
- System.out.println(gson.toJson(offlineList));
- return gson.toJson(offlineList);
- }
- public void updatePendingSalesToProcessing(String status) {
- {
- SQLiteDatabase db = null;
- try {
- beginWriteLock();
- db = this.getWritableDatabase();
- db.execSQL("UPDATE " + TABLE_MANY_SALES + " SET " + KEY_STATUS + " = '" + status + "'");
- Log.d("update_status", "updated pending sales to " + status);
- } catch (Exception e) {
- Log.d("update_pending_e", e.toString());
- } finally {
- if (db != null) {
- try {
- // db.close();
- } catch (Exception e) {
- Log.d("updatepending_e", e.toString());
- }
- }
- endWriteLock();
- }
- }
- }
- public void deletePendingSales() {
- {
- SQLiteDatabase db = null;
- try {
- beginWriteLock();
- db = this.getWritableDatabase();
- // Delete All Rows that have been processed
- // db.delete(TABLE_MANY_SALES, null, null);
- db.execSQL("DELETE FROM " + TABLE_MANY_SALES + " WHERE " + KEY_STATUS + " = 'processing'");
- // db.close();
- //db.endTransaction();
- Log.d(TAG, "Deleted all user info from sqlite");
- } catch (Exception e) {
- Log.d("addsale_exceptions1 ", e.toString());
- } finally {
- if (db != null) {
- try {
- // db.close();
- } catch (Exception e) {
- Log.d("addsale_exceptions2", e.toString());
- }
- }
- endWriteLock();
- }
- }
- }
- public void deletePendingSingleSale(String time_code) {
- //Open the database
- SQLiteDatabase database = context.openOrCreateDatabase(DATABASE_NAME, Context.MODE_PRIVATE, null);
- //Execute sql query to remove from database
- //NOTE: When removing by String in SQL, value must be enclosed with ''
- database.execSQL("DELETE FROM " + TABLE_MANY_SALES + " WHERE " + KEY_TIME_CODE + "= '" + time_code + "'");
- Log.d("deleted_sale", time_code + " from sqlite");
- //Close the database
- //causes SQLDatabaseLocked Exception
- //database.close();
- }
- public boolean checkForPendingSalesTables() {
- boolean hasRows = false;
- SQLiteDatabase db = getReadableDatabase();
- Cursor cursor = db.rawQuery("SELECT COUNT(*) FROM " + TABLE_MANY_SALES, null);
- cursor.moveToFirst();
- int count = cursor.getInt(0);
- if (count > 0)
- hasRows = true;
- cursor.close();
- // db.endTransaction();
- return hasRows;
- }
- public int getFilteredTotalCountOfSales() {
- {
- SQLiteDatabase db = null;
- try {
- beginWriteLock();
- db = this.getWritableDatabase();
- db = this.getReadableDatabase();
- Cursor c = db.rawQuery("SELECT SUM(quantity) FROM " + TABLE_SALES + " WHERE "
- + KEY_ATTENDANT_ID + "= '" + getattid() + "'", null);
- c.moveToFirst();
- count = c.getInt(0);
- c.close();
- } catch (Exception e) {
- Log.d("addsale_exceptions1 ", e.toString());
- } finally {
- if (db != null) {
- try {
- // db.close();
- } catch (Exception e) {
- Log.d("addsale_exceptions2", e.toString());
- }
- }
- endWriteLock();
- }
- }
- return count;
- }
- public int getTotalQuantityOfProducts() {
- SQLiteDatabase db = this.getReadableDatabase();
- Cursor c = db.rawQuery("SELECT SUM(quantity) FROM " + TABLE_SALES, null);
- c.moveToFirst();
- int i = c.getInt(0);
- c.close();
- return i;
- }
- public void setLabelType(String label) {
- period_label = label;
- }
- /**
- * TOTAL AMOUNT SALES+==================================================>
- */
- public void insertDateSelected(String created_at_am, String quantity,
- String product_id, String name,
- String customer_first_name,
- String customer_last_name, String customer_id,
- String attendant_name, String price, String attendant_id) {
- SQLiteDatabase database = context.openOrCreateDatabase(DATABASE_NAME, Context.MODE_PRIVATE, null);
- ContentValues values = new ContentValues();
- values.put(KEY_CREATED_AT_AM_PM, getTimeFormattedInAMPM(created_at_am));
- values.put(KEY_CREATED_AT_DAY, getDayFormatted(created_at_am));
- values.put(KEY_CREATED_AT_MONTH, getMonthFormatted(created_at_am));
- values.put(KEY_CREATED_AT_YEAR, getYearFormatted(created_at_am));
- values.put(KEY_CREATED_AT, created_at_am);
- values.put(KEY_QUANTITY, quantity);
- values.put(KEY_COUNT, "1");
- values.put(KEY_PRODUCT_ID, product_id);
- values.put(KEY_PRODUCT_NAME, name);
- values.put(KEY_CUSTOMER_FIRST_NAME, customer_first_name);
- values.put(KEY_CUSTOMER_LAST_NAME, customer_last_name);
- values.put(KEY_CUSTOMER_ID, customer_id);
- values.put(KEY_ATTENDANT_ID, attendant_id);
- values.put(KEY_PRICE, price);
- values.put(KEY_ATTENDANT_NAME, attendant_name);
- long id = database.insert(TABLE_TOTAL_SALES, null, values);
- // Closing database connection
- Log.d(TAG, "total_checkout amount inserted into sqlite: " + id);
- }
- private String getDayFormatted(String dateStr) {
- DateFormat readFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
- DateFormat writeFormat = new SimpleDateFormat("dd");
- Date date = null;
- try {
- date = readFormat.parse(dateStr);
- } catch (ParseException e) {
- e.printStackTrace();
- }
- String formattedDate = writeFormat.format(date);
- return formattedDate;
- }
- private String getMonthFormatted(String dateStr) {
- DateFormat readFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
- DateFormat writeFormat = new SimpleDateFormat("MMM ");
- Date date = null;
- try {
- date = readFormat.parse(dateStr);
- } catch (ParseException e) {
- e.printStackTrace();
- }
- String formattedDate = writeFormat.format(date);
- return formattedDate;
- }
- private String getYearFormatted(String dateStr) {
- DateFormat readFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
- DateFormat writeFormat = new SimpleDateFormat("yyyy");
- Date date = null;
- try {
- date = readFormat.parse(dateStr);
- } catch (ParseException e) {
- e.printStackTrace();
- }
- String formattedDate = writeFormat.format(date);
- return formattedDate;
- }
- public double getTotalSalesAmount() {
- SQLiteDatabase db = this.getReadableDatabase();
- Cursor c = db.rawQuery("SELECT SUM(price) FROM " + TABLE_TOTAL_SALES, null);
- c.moveToFirst();
- double i = c.getDouble(0);
- Log.d("double value database", String.valueOf(i));
- c.close();
- return i;
- }
- public void deleteTotalSalesAmount() {
- SQLiteDatabase db = getReadableDatabase();
- // Delete All Rows
- db.delete(TABLE_TOTAL_SALES, null, null);
- // db.close();
- Log.d(TAG, "Deleted all TotalCheckousAmount");
- }
- public ArrayList<Entry> dateValues() {
- hour_labels.clear();
- ArrayList<Entry> values = new ArrayList<>();
- hour_labels().clear();
- SQLiteDatabase db = this.getReadableDatabase();
- Cursor c = null;
- if (period_label.equals("hours")) {
- c = db.rawQuery("select created_at_am_pm ,sum(count) as sum \n" +
- "from total_sales\n" +
- "group by created_at_am_pm ORDER BY datetime(created_at_day) ASC ", null);
- }
- if (period_label.equals("days")) {
- c = db.rawQuery("select created_at_day ,sum(count) as sum \n" +
- "from total_sales\n" +
- "group by created_at_day ORDER BY datetime(created_at) ASC ", null);
- }
- if (period_label.equals("months")) {
- c = db.rawQuery("select created_at_month ,sum(count) as sum \n" +
- "from total_sales\n" +
- "group by created_at_month ORDER BY datetime(created_at) ASC ", null);
- }
- int i = 0;
- try {
- while (c.moveToNext()) {
- Double sum = c.getDouble(1);
- // int count = c.getCount();
- // Log.d("Sum is: " + sum);
- values.add(new Entry(Float.valueOf(String.valueOf(sum)), i));
- Log.d("sum_values_entry", String.valueOf(sum));
- if (period_label.equals("hours")) {
- hour_labels.add(c.getString(c.getColumnIndex("created_at_am_pm")));
- }
- if (period_label.equals("days")) {
- hour_labels.add(c.getString(c.getColumnIndex("created_at_day")));
- }
- if (period_label.equals("months")) {
- hour_labels.add(c.getString(c.getColumnIndex("created_at_month")));
- }
- // Log.d("sum_values_", String.valueOf(sum) + "/" + c.getString(c.getColumnIndex("created_at_am_pm")));
- i++;
- }
- } finally {
- c.close();
- }
- Log.d("sum_sales_values", values.toString());
- Log.d("sum_sales_values", hour_labels.toString());
- Log.d("sum_sales_values", values.size() + "/" + hour_labels.size() + period_label);
- return values;
- }
- public ArrayList<Entry> date_products_values(String variant) {
- Cursor c = null;
- product_labels.clear();
- ArrayList<Entry> values = new ArrayList<>();
- SQLiteDatabase db = this.getReadableDatabase();
- c = db.rawQuery("select p_name ,sum('" + variant + "') as sum \n" +
- "from total_sales\n" +
- "group by product_id ORDER BY sum('" + variant + "') DESC", null);
- int i = 0;
- if (c != null) {
- try {
- while (c.moveToNext()) {
- Double sum = c.getDouble(1);
- if (product_labels.size() < 10) {
- product_labels.add(mFormat.format(sum).replace(".0", "") + " " + c.getString(c.getColumnIndex("p_name")));
- values.add(new Entry(Float.valueOf(String.valueOf(sum)), i));
- } else {
- if (product_labels.contains("Others")) {
- product_labels.add(mFormat.format(sum).replace(".0", "") + " Others");
- values.add(new Entry(Float.valueOf(String.valueOf(sum)), i));
- } else {
- product_labels.add(mFormat.format(sum).replace(".0", "") + " Others");
- values.add(new Entry(Float.valueOf(String.valueOf(sum)), i));
- }
- }
- Log.d("sum_product_values_", String.valueOf(sum) + "/" + c.getString(c.getColumnIndex("p_name")));
- i++;
- }
- } finally {
- c.close();
- }
- }
- return values;
- }
- public ArrayList<Entry> date_customer_values() {
- customer_labels.clear();
- ArrayList<Entry> values = new ArrayList<>();
- SQLiteDatabase db = this.getReadableDatabase();
- /* Cursor c = db.rawQuery("select * ,sum(quantity) as sum \n" +
- "from total_sales\n" +
- "group by customer_id ORDER BY sum(quantity) DESC LIMIT 20", null);*/
- Cursor c = db.rawQuery("select customer_first_name,sum(quantity) as sum \n" +
- "from total_sales\n" +
- "group by customer_id ORDER BY sum DESC LIMIT 20", null);
- int i = 0;
- if (c != null) {
- try {
- while (c.moveToNext()) {
- Double sum = c.getDouble(1);
- customer_labels.add(String.valueOf(sum).replace(".0", "") + " " + c.getString(c.getColumnIndex("customer_first_name"))
- /* + " " + c.getString(c.getColumnIndex("customer_last_name"))*/);
- values.add(new Entry(Float.valueOf(String.valueOf(sum)), i));
- Log.d("sum_customer_values_", c.getCount() + "/" +
- String.valueOf(sum) + "/" + c.getString(c.getColumnIndex("customer_first_name")));
- i++;
- }
- } finally {
- c.close();
- }
- }
- // Log.d("sum_product_values_", values.size() + "/" + hour_labels.size());
- return values;
- }
- public ArrayList<Entry> date_staff_values() {
- staff_labels.clear();
- ArrayList<Entry> values = new ArrayList<>();
- SQLiteDatabase db = this.getReadableDatabase();
- Cursor c = db.rawQuery("select attendant_name,sum(price) as sum \n" +
- "from total_sales\n" +
- "group by attendant_id ORDER BY sum DESC", null);
- int i = 0;
- if (c != null) {
- try {
- while (c.moveToNext()) {
- Double sum = c.getDouble(1);
- staff_labels.add(String.valueOf(sum).replace(".0", "") + " " + c.getString(c.getColumnIndex("attendant_name"))
- /* + " " + c.getString(c.getColumnIndex("customer_last_name"))*/);
- values.add(new Entry(Float.valueOf(String.valueOf(sum)), i));
- Log.d("sum_staff_values_", String.valueOf(sum) + "/" + c.getString(c.getColumnIndex("attendant_name")));
- i++;
- }
- } finally {
- c.close();
- }
- }
- // Log.d("sum_product_values_", values.size() + "/" + hour_labels.size());
- return values;
- }
- /**
- * SALES TO DELETE+==================================================>
- */
- public void AddToDeleteSalesTable(String sale_id, int position) {
- SQLiteDatabase database = context.openOrCreateDatabase(DATABASE_NAME, Context.MODE_PRIVATE, null);
- ContentValues values = new ContentValues();
- values.put(KEY_SID, sale_id);
- values.put(KEY_POSITION, position);
- long id = database.insert(TABLE_DELETE_SALES, null, values);
- // Closing database connection
- Log.d(TAG, "sales to delete inserted into sqlite: " + id);
- }
- public void RemoveSaleFromDeleteTable(String sale_id) {
- //Open the database
- SQLiteDatabase database = context.openOrCreateDatabase(DATABASE_NAME, Context.MODE_PRIVATE, null);
- //Execute sql query to remove from database
- //NOTE: When removing by String in SQL, value must be enclosed with ''
- database.execSQL("DELETE FROM " + TABLE_DELETE_SALES + " WHERE " + KEY_SID + "= '" + sale_id + "'");
- Log.d("deleted_sale", sale_id + " from sqlite");
- //Close the database
- //causes SQLDatabaseLocked Exception
- //database.close();
- }
- public String getSaleIdToBeDeleted() {
- String selectQuery = "SELECT * FROM " + TABLE_DELETE_SALES + " ORDER BY s_id DESC LIMIT 1";
- SQLiteDatabase db = context.openOrCreateDatabase(DATABASE_NAME, Context.MODE_PRIVATE, null);
- Cursor cursor = db.rawQuery(selectQuery, null);
- String str = "0";
- if (cursor.moveToFirst()) {
- str = cursor.getString(cursor.getColumnIndex(KEY_SID));
- pos = cursor.getInt(cursor.getColumnIndex(KEY_POSITION));
- }
- cursor.close();
- return str;
- }
- public int getPositionToBeDeleted() {
- return pos;
- }
- public void RemoveAllSalesToBeDeleted() {
- {
- SQLiteDatabase db = null;
- try {
- beginWriteLock();
- db = this.getWritableDatabase();
- // Delete All Rows
- db.delete(TABLE_DELETE_SALES, null, null);
- // db.close();
- //db.endTransaction();
- Log.d(TAG, "Deleted all user info from sqlite");
- } catch (Exception e) {
- Log.d("addsale_exceptions1 ", e.toString());
- } finally {
- if (db != null) {
- try {
- // db.close();
- } catch (Exception e) {
- Log.d("addsale_exceptions2", e.toString());
- }
- }
- endWriteLock();
- }
- }
- }
- public boolean checkForDeleteSalesQueueTables() {
- boolean hasRows = false;
- SQLiteDatabase db = getReadableDatabase();
- Cursor cursor = db.rawQuery("SELECT COUNT(*) FROM " + TABLE_DELETE_SALES, null);
- cursor.moveToFirst();
- int count = cursor.getInt(0);
- if (count > 0)
- hasRows = true;
- cursor.close();
- // db.endTransaction();
- return hasRows;
- }
- public String getAllSalesToBeDeleted() {
- ArrayList<Integer> saleIds = new ArrayList<Integer>();
- String selectQuery = "SELECT * FROM " + TABLE_DELETE_SALES;
- SQLiteDatabase db = context.openOrCreateDatabase(DATABASE_NAME, Context.MODE_PRIVATE, null);
- Cursor cursor = db.rawQuery(selectQuery, null);
- int id = 0;
- if (cursor.moveToFirst()) {
- do {
- id = cursor.getInt(cursor.getColumnIndex(KEY_SID));
- saleIds.add(id);
- } while (cursor.moveToNext());
- }
- cursor.close();
- Gson gson = new Gson();
- return gson.toJson(saleIds);
- }
- /**
- * SALES KEYS TABLE+==================================================>
- */
- public void addSaleKey(String timeCode) {
- {
- SQLiteDatabase db = null;
- try {
- beginWriteLock();
- db = this.getWritableDatabase();
- ContentValues values = new ContentValues();
- values.put(KEY_TIME_CODE, timeCode);
- // values.put("updateStatus", "no");
- long id = db.insert(TABLE_SALES_KEYS, null, values);
- // Closing database connection
- // database.endTransaction()
- } catch (Exception e) {
- Log.d("addsale_key ", e.toString());
- } finally {
- if (db != null) {
- try {
- //db.close();
- } catch (Exception e) {
- Log.d("addsale_key", e.toString());
- }
- }
- endWriteLock();
- }
- }
- }
- public Boolean isSaleKeyExisting(String sale_key) {
- SQLiteDatabase db = context.openOrCreateDatabase(DATABASE_NAME, Context.MODE_PRIVATE, null);
- String selectString = "SELECT * FROM " + TABLE_SALES_KEYS + " WHERE " + KEY_TIME_CODE + " =?";
- // Add the String you are searching by here.
- // Put it in an array to avoid an unrecognized token error
- Cursor cursor = db.rawQuery(selectString, new String[]{sale_key});
- boolean hasObject = false;
- if (cursor.moveToFirst()) {
- hasObject = true;
- //region if you had multiple records to check for, use this region.
- int count = 0;
- while (cursor.moveToNext()) {
- count++;
- }
- //here, count is records found
- Log.d(TAG, String.format("%d records found", count));
- //endregion
- }
- cursor.close(); // Dont forget to close your cursor
- // db.close(); //AND your Database!
- return hasObject;
- }
- public void RemoveAllSalesKeys() {
- {
- SQLiteDatabase db = null;
- try {
- beginWriteLock();
- db = this.getWritableDatabase();
- // Delete All Rows
- db.delete(TABLE_SALES_KEYS, null, null);
- // db.close();
- //db.endTransaction();
- Log.d(TAG, "Deleted all user info from sqlite");
- } catch (Exception e) {
- Log.d("addsale_exceptions1 ", e.toString());
- } finally {
- if (db != null) {
- try {
- // db.close();
- } catch (Exception e) {
- Log.d("addsale_exceptions2", e.toString());
- }
- }
- endWriteLock();
- }
- }
- }
- public int getSaleKeysCount() {
- String countQuery = "SELECT * FROM " + TABLE_SALES_KEYS;
- SQLiteDatabase db = this.getReadableDatabase();
- Cursor cursor = db.rawQuery(countQuery, null);
- int cnt = cursor.getCount();
- // return row count
- cursor.close();
- return cnt;
- }
- /**
- * NOTIFICATION SALES MESSAGES TABLE+==================================================>
- */
- public void addSalesNotifications(String message, String quantity) {
- {
- SQLiteDatabase db = null;
- try {
- beginWriteLock();
- db = this.getWritableDatabase();
- ContentValues values = new ContentValues();
- values.put(KEY_MESSAGE, message);
- values.put(KEY_QUANTITY, quantity);
- // values.put("updateStatus", "no");
- long id = db.insert(TABLE_SALES_NOTIFICATIONS, null, values);
- // Closing database connection
- // database.endTransaction()
- } catch (Exception e) {
- Log.d("addsale_key ", e.toString());
- } finally {
- if (db != null) {
- try {
- // db.close();
- } catch (Exception e) {
- Log.d("addsale_key", e.toString());
- }
- }
- endWriteLock();
- }
- }
- }
- public ArrayList<String> readLastNotifications() {
- {
- SQLiteDatabase db = null;
- try {
- beginReadLock();
- db = this.getWritableDatabase();
- Cursor res = db.rawQuery("SELECT * FROM " + TABLE_SALES_NOTIFICATIONS + " ORDER BY id ASC", null);
- res.moveToFirst();
- while (!res.isAfterLast()) {
- String message = res.getString(res
- .getColumnIndex(KEY_MESSAGE));
- notificationsList.add(message);
- res.moveToNext();
- }
- Log.d("notificat_sh", "sale-" + notificationsList.size());
- Log.d(TAG, "Fetching promos from Sqlite: ");
- res.close();
- } catch (Exception e) {
- Log.d("readLastNotifications ", e.toString());
- } finally {
- if (db != null) {
- try {
- // db.close();
- } catch (Exception e) {
- Log.d("readLastNotifications", e.toString());
- }
- }
- endReadLock();
- }
- }
- return notificationsList;
- }
- public void RemoveAllNotificationMessages() {
- {
- SQLiteDatabase db = null;
- try {
- beginWriteLock();
- db = this.getWritableDatabase();
- // Delete All Rows
- db.delete(TABLE_SALES_NOTIFICATIONS, null, null);
- // db.close();
- //db.endTransaction();
- Log.d(TAG, "Deleted all user info from sqlite");
- } catch (Exception e) {
- Log.d("addsale_exceptions1 ", e.toString());
- } finally {
- if (db != null) {
- try {
- // db.close();
- } catch (Exception e) {
- Log.d("addsale_exceptions2", e.toString());
- }
- }
- endWriteLock();
- }
- }
- }
- public double getNotificationsCount() {
- SQLiteDatabase db = this.getReadableDatabase();
- Cursor c = db.rawQuery("SELECT SUM(quantity) FROM " + TABLE_SALES_NOTIFICATIONS, null);
- c.moveToFirst();
- double i = c.getDouble(0);
- Log.d("double value database", String.valueOf(i));
- c.close();
- return i;
- }
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement