· 8 years ago · Jun 26, 2018, 08:12 PM
1package com.example.marek.mojalistaproduktow;
2
3import android.annotation.SuppressLint;
4import android.content.ContentValues;
5import android.content.Context;
6import android.database.Cursor;
7import android.database.sqlite.SQLiteDatabase;
8import android.database.sqlite.SQLiteOpenHelper;
9
10import java.text.Format;
11import java.text.ParseException;
12import java.text.SimpleDateFormat;
13import java.util.ArrayList;
14import java.util.Date;
15
16/**
17 * Created by Marek on 05.04.2018.
18 */
19
20public class DatabaseHelper extends SQLiteOpenHelper {
21 private static final int DATABASE_VERSION = 1;
22 private static final String DATABASE_NAME = "productmanager.db";
23
24 private static final String TABLE_PRODUCT = "product";
25 private static final String COLUMN_PRODUCT_NAME = "product_name";
26 private static final String COLUMN_PRODUCT_DESCRIPTION = "product_description";
27 private static final String COLUMN_PRODUCT_EANCODE = "product_eancode";
28
29 private static final String TABLE_PRICES = "prices";
30 private static final String COLUMN_PRICE = "price";
31 private static final String COLUMN_DATE = "date";
32
33
34 private static final String CREATE_TABLE_PRODUCT =
35 "CREATE TABLE " + TABLE_PRODUCT + "("
36 + COLUMN_PRODUCT_EANCODE + " TEXT PRIMARY KEY NOT NULL UNIQUE, "
37 + COLUMN_PRODUCT_NAME + " TEXT, "
38 + COLUMN_PRODUCT_DESCRIPTION + " TEXT)";
39
40 private static final String CREATE_TABLE_PRICES =
41 "CREATE TABLE " + TABLE_PRICES + "("
42 + COLUMN_PRODUCT_EANCODE + " TEXT, "
43 + COLUMN_PRICE + " DOUBLE, "
44 + COLUMN_DATE + " DATETIME)";
45
46
47 private static final String DROP_TABLE_PRODUCT =
48 "DROP TABLE IF EXISTS " + TABLE_PRODUCT;
49
50 private static final String DROP_TABLE_PRICES =
51 "DROP TABLE IF EXISTS " + TABLE_PRICES;
52
53
54 public DatabaseHelper(Context context) {
55 super(context, DATABASE_NAME, null, DATABASE_VERSION);
56 }
57
58 @Override
59 public void onCreate(SQLiteDatabase db) {
60 db.execSQL(CREATE_TABLE_PRODUCT);
61 db.execSQL(CREATE_TABLE_PRICES);
62 }
63
64 @Override
65 public void onUpgrade(SQLiteDatabase db, int i, int i1) {
66 db.execSQL(DROP_TABLE_PRODUCT);
67 db.execSQL(DROP_TABLE_PRICES);
68 onCreate(db);
69 }
70
71 public boolean addProduct(Product product) {
72 SQLiteDatabase db = this.getWritableDatabase();
73 ContentValues contentValues = new ContentValues();
74
75 contentValues.put(COLUMN_PRODUCT_EANCODE, product.getEanCode());
76 contentValues.put(COLUMN_PRODUCT_NAME, product.getName());
77 contentValues.put(COLUMN_PRODUCT_DESCRIPTION, product.getDescription());
78
79 long result = db.insert(TABLE_PRODUCT, null, contentValues);
80
81 if(result == -1)
82 return false;
83 else {
84 ContentValues values = new ContentValues();
85
86 for(int i=0; i<product.getPricesCount(); ++i) {
87 values.put(COLUMN_PRODUCT_EANCODE, product.getEanCode());
88 values.put(COLUMN_PRICE, product.getPrice(i).getPrice());
89
90
91 @SuppressLint("SimpleDateFormat")
92 SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
93 String date = formatter.format(product.getPrice(0).getDate());
94
95 values.put(COLUMN_DATE, date);
96
97 }
98 result = db.insert(TABLE_PRICES, null, values);
99
100 if(result == -1)
101 return false;
102 }
103
104 return true;
105 }
106
107 public boolean addPrice(String eanCode, Price price) {
108 SQLiteDatabase db = this.getWritableDatabase();
109 ContentValues contentValues = new ContentValues();
110
111 contentValues.put(COLUMN_PRODUCT_EANCODE, eanCode);
112 contentValues.put(COLUMN_PRICE, price.getPrice());
113
114 @SuppressLint("SimpleDateFormat")
115 SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
116 contentValues.put(COLUMN_DATE, formatter.format(price.getDate()));
117
118 long result = db.insert(TABLE_PRICES, null, contentValues);
119
120 if(result == -1)
121 return false;
122
123 return true;
124 }
125
126 public boolean isExist(String eanCode) {
127 String[] selectionArgs = { eanCode };
128 String selection = COLUMN_PRODUCT_EANCODE + " = ?";
129 SQLiteDatabase db = this.getWritableDatabase();
130
131 Cursor cursor = db.query(TABLE_PRODUCT,
132 null,
133 selection,
134 selectionArgs,
135 null,
136 null,
137 null);
138
139 int cursorCount = cursor.getCount();
140
141 if(cursorCount > 0)
142 return true;
143
144 return false;
145
146 }
147
148 public Product getData(String eanCode) {
149 SQLiteDatabase db = this.getReadableDatabase();
150 Cursor result = db.rawQuery("SELECT * FROM " + TABLE_PRODUCT +
151 " WHERE " + COLUMN_PRODUCT_EANCODE + " = " + eanCode,
152 null);
153
154 if(result != null) {
155 result.moveToFirst();
156
157 Product product = new Product(result.getString(result.getColumnIndex(COLUMN_PRODUCT_NAME)),
158 result.getString(result.getColumnIndex(COLUMN_PRODUCT_EANCODE)),
159 result.getString(result.getColumnIndex(COLUMN_PRODUCT_DESCRIPTION)),
160 null);
161
162 return product;
163 }
164 else
165 return null;
166 }
167
168 public ArrayList<Price> getPrices(String eancode) {
169 SQLiteDatabase db = this.getReadableDatabase();
170 Cursor result = db.rawQuery("SELECT * FROM " + TABLE_PRICES + " WHERE " + COLUMN_PRODUCT_EANCODE + " LIKE " + eancode, null);
171
172
173 result.moveToFirst();
174 ArrayList<Price> prices = new ArrayList<>();
175
176 do {
177 String dateString = result.getString(result.getColumnIndex(COLUMN_DATE));
178 double price = result.getDouble(result.getColumnIndex(COLUMN_PRICE));
179
180 Date date = null;
181 try {
182 date = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(dateString);
183 } catch (ParseException e) {
184 e.printStackTrace();
185 }
186
187
188 prices.add(new Price(price, date));
189 } while(result.moveToNext());
190
191 return prices;
192 }
193
194 public Cursor getProducts() {
195 SQLiteDatabase db = this.getReadableDatabase();
196 Cursor result = db.rawQuery("SELECT * FROM " + TABLE_PRODUCT,null);
197
198 return result;
199 }
200
201 public void editProduct(Product product) {
202 SQLiteDatabase db = this.getWritableDatabase();
203 String query = "UPDATE " + TABLE_PRODUCT + " SET " + COLUMN_PRODUCT_NAME + " = '" + product.getName() + "', "
204 + COLUMN_PRODUCT_DESCRIPTION + " = '" + product.getDescription() + "'"
205 + " WHERE " + COLUMN_PRODUCT_EANCODE + " = '" + product.getEanCode() + "';";
206
207 db.execSQL(query);
208 }
209
210 public void deleteAllDate() {
211 SQLiteDatabase db = this.getReadableDatabase();
212 db.execSQL("DELETE FROM " + TABLE_PRODUCT);
213 db.execSQL("DELETE FROM " + TABLE_PRICES);
214 }
215
216}