public static ArrayList<Customer> getAllCustomers() {
ArrayList<Customer> list = new ArrayList<Customer>();
DBHelper db = new DBHelper();
try {
String queryString = "SELECT * FROM customer";
ResultSet rs = db.executeQuery(queryString);
while (rs.next()) {
Customer customer = new Customer();
customer.setId(rs.getInt("id"));
customer.setFirstName(rs.getString("first_name"));
customer.setLastName(rs.getString("last_name"));
customer.setPhone(rs.getString("phone"));
customer.setAddress(rs.getString("address"));
customer.setZipCode(rs.getInt("zipcode"));
customer.setCity(rs.getString("city"));
customer.setEmail(rs.getString("email"));
customer.setPassword(rs.getString("password"));
list.add(customer);
}
} catch (Exception e) {
e.printStackTrace();
}
db.close();
return list;
}
@Override
public void onReceive(Context context, Intent intent) {
// TODO Auto-generated method stub
db = new DBHelper(context);
mContext = context;
Calendar c = Calendar.getInstance();
int yy = c.get(Calendar.YEAR);
int mm = c.get(Calendar.MONTH) + 1;
int dd = c.get(Calendar.DATE);
int hh = c.get(Calendar.HOUR_OF_DAY);
int mn = c.get(Calendar.MINUTE);
int nw = (hh * 60) + mn;
Cursor cur = db.getAllTaskByDate(yy, mm, dd);
if (cur.moveToFirst()) {
do {
int tstime = cur.getInt(cur.getColumnIndex("stime"));
int tstimem = cur.getInt(cur.getColumnIndex("stimem"));
int tt = (tstime * 60) + tstimem;
if ((tt - nw) <= 5 && (tt - nw) > 0) {
String title = cur.getString(cur.getColumnIndex("tname"));
showNotification(context, 1, title, "Starts at " + tstime + ":" + tstimem);
}
} while (cur.moveToNext());
}
cur.close();
if (mm == 0) {
Cursor lcur = db.getLogin();
int id = 0;
if (lcur.moveToFirst()) {
id = lcur.getInt(lcur.getColumnIndex("uid"));
}
lcur.close();
Cursor mcur = db.getTaskByPendingSync();
if (mcur.moveToFirst()) {
do {
int tid = mcur.getInt(mcur.getColumnIndex("tid"));
int rating = mcur.getInt(mcur.getColumnIndex("rating"));
String feedback = mcur.getString(mcur.getColumnIndex("feedback"));
if (isNetworkAvailable() && id != 0) {
new RequestTask()
.execute(
context.getResources().getString(R.string.server)
+ "services/feedbackcollector.php?"
+ "id="
+ id
+ "&tid="
+ tid
+ "&rating="
+ rating
+ "&feedback="
+ feedback);
}
} while (mcur.moveToNext());
}
mcur.close();
}
}
@Override
public void run() {
RestAdapter restAdapter =
new RestAdapter.Builder().setEndpoint("https://shedule.herokuapp.com").build();
ScheduleInterface scheduleInterface = restAdapter.create(ScheduleInterface.class);
Schedule schedule = scheduleInterface.getSchedule(parameters);
ContentValues cv = new ContentValues();
DBHelper dbHelper = new DBHelper(getApplicationContext());
SQLiteDatabase db = dbHelper.getWritableDatabase();
db.delete("schedule", null, null);
for (Schedule.Objects elem : schedule.objects) {
cv.put("room", elem.room);
cv.put("day", elem.day);
cv.put("groupNumber", elem.groupNumber);
cv.put("hours", elem.hours);
cv.put("lecture", elem.lecture);
cv.put("teacher", elem.teacher);
db.insert("schedule", null, cv);
}
dbHelper.close();
Intent intent = new Intent("EndOperation");
sendBroadcast(intent);
}
@Override
protected void onResume() {
super.onResume();
etLectNum = (EditText) findViewById(R.id.etLectNum);
etLectTitle = (EditText) findViewById(R.id.etLectTitle);
etLectDescription = (EditText) findViewById(R.id.etLectDescription);
tvLastLect = (TextView) findViewById(R.id.tvLastLect);
bSubmit = (Button) findViewById(R.id.bLectSubmit);
spf = PreferenceManager.getDefaultSharedPreferences(this);
ed = spf.edit();
semSelected = spf.getInt("SemesterSelected", 0);
courseSelected = spf.getString("CourseSelected", null);
dbh = new DBHelper(this);
if (getIntent().getExtras().getBoolean("Update")) {
Cursor rs = dbh.getLectureData(semSelected, courseSelected, spf.getInt("LectureSelected", 0));
rs.moveToFirst();
etLectNum.setText(String.valueOf(rs.getInt(rs.getColumnIndex(DBHelper.LECTURES_NUMBER))));
etLectNum.setFocusable(false);
etLectNum.setClickable(false);
etLectTitle.setText(rs.getString(rs.getColumnIndex(DBHelper.LECTURES_TITLE)));
etLectDescription.setText(rs.getString(rs.getColumnIndex(DBHelper.LECTURES_DESCRIPTION)));
rs.close();
dbh.close();
} else {
tvLastLect.setText(
"Last Lecture Number added: " + dbh.getLastAddedLecture(semSelected, courseSelected));
}
bSubmit.setOnClickListener(this);
}
/**
* Создает таблицы и заполняет их начальными данными
*
* @throws SQLException, IOException
*/
public static void createTableAndInserData() throws SQLException, IOException {
DBHelper DBHelper = new DBHelper();
Connection con = DBHelper.getConnection();
if (con == null) {
return;
}
Statement statement = con.createStatement();
statement.executeUpdate(DROP_AND_CREAT_ALL);
for (int i = 1; i <= 10; i++) {
String name = "Prov_" + i;
statement.executeUpdate("INSERT INTO provider (id,name) VALUES (" + i + ",'" + name + "');");
for (int n = 0; n < 3; n++) {
String country =
Countries.values()[new Random().nextInt(Countries.values().length)].toString();
statement.executeUpdate(
"INSERT INTO provider_country (provider_id,country) VALUES ("
+ i
+ ",'"
+ country
+ "');");
}
for (int n = 0; n < 3; n++) {
String curr =
Currencies.values()[new Random().nextInt(Currencies.values().length)].toString();
statement.executeUpdate(
"INSERT INTO provider_curr (provider_id,currency) VALUES (" + i + ",'" + curr + "');");
}
}
DBHelper.closeConnection(con);
}
/**
* Прочитать из таблиц и вывести всех провайдеров
*
* @throws IOException
* @throws SQLException
*/
public static void task1() throws IOException, SQLException {
DBHelper DBHelper = new DBHelper();
Connection con = DBHelper.getConnection();
Statement statement = con.createStatement();
ResultSet rs =
statement.executeQuery(
"SELECT pv.id prvid, pv.name prvname,"
+ " STRING_AGG(DISTINCT pc.currency, ',' ORDER BY pc.currency) currencies,"
+ " STRING_AGG(DISTINCT pcn.country, ',' ORDER BY pcn.country) countries"
+ " FROM provider pv"
+ " JOIN provider_curr pc"
+ " ON pv.id = pc.provider_id"
+ " JOIN provider_country pcn"
+ " ON pv.id = pcn.provider_id"
+ " GROUP BY pv.id, pv.name;");
while (rs.next()) {
int prId = rs.getInt("prvid");
String prName = rs.getString("prvname");
String prCurrencies = rs.getString("currencies");
String prCountries = rs.getString("countries");
System.out.printf("%s (id %d) - [%s], [%s]\n", prName, prId, prCurrencies, prCountries);
}
DBHelper.closeConnection(con);
}
/** Returns an ArrayList of Annotations with a TicketID. */
public static ArrayList<Annotation> getAnnotations(int ticketNum) {
DBHelper db = DBHelperFactory.createDBHelper();
ArrayList<Annotation> result = db.createAnnotationArrayListByTicket(ticketNum);
db.close();
return result;
}
/** Gets a specified ticket. */
public static Ticket getTicket(int ticketNum) {
DBHelper db = DBHelperFactory.createDBHelper();
Ticket result = db.retrieveTicket(ticketNum);
db.close();
return result;
}
public void saveEvent(View view) throws IOException {
String eventName = ((EditText) findViewById(R.id.eventName)).getText().toString();
TextView tv = (TextView) findViewById(R.id.errorField);
if (eventName.equals("")) {
tv.setText("You must specify an Event name.");
} else if (!startTimeSet) {
tv.setText("You must specify a start time.");
} else if (!endTimeSet) {
tv.setText("You must specify an end time.");
} else if (!startDateSet) {
tv.setText("You must specify a start date.");
} else if (!endDateSet) {
tv.setText("You must specify an end date.");
} else if (newEvent.getEndTime() - newEvent.getStartTime() < 0) {
tv.setText("End time must be after start time.");
} else {
newEvent.setName(eventName);
DBHelper db = new DBHelper(this);
if (!db.createEvent(
newEvent.getName(), newEvent.getStartTimeInMillis(), newEvent.getEndTimeInMillis())) {
tv.setText("An Event with that name already exists.");
return;
}
Toast.makeText(this, "Event Created", Toast.LENGTH_LONG).show();
Alarm a = new Alarm();
a.setAlarm(this, newEvent.getName(), newEvent.getStartTimeInMillis());
Intent intent = new Intent(this, MainActivity.class);
startActivity(intent);
}
}
/**
* Всем провайдерам, которые содержат страну Россия, добавить валюту RUB
*
* @throws IOException
* @throws SQLException
*/
public static void task4() throws IOException, SQLException {
DBHelper DBHelper = new DBHelper();
Connection con = DBHelper.getConnection();
Statement statement = con.createStatement();
List<Integer> prvRussian = new ArrayList<>();
ResultSet rs =
statement.executeQuery("SELECT * FROM provider_country WHERE country = 'Russian'");
while (rs.next()) {
prvRussian.add(rs.getInt("provider_id"));
}
prvRussian.forEach(
(prvId) -> {
try {
PreparedStatement st =
con.prepareStatement(
"INSERT INTO provider_curr (provider_id,currency) VALUES (?,?);");
st.setInt(1, prvId);
st.setString(2, "RUB");
st.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
});
DBHelper.closeConnection(con);
}
/** Returns an ArrayList of all the registered accounts */
public static ArrayList<String> getWorkerNames() {
DBHelper db = DBHelperFactory.createDBHelper();
ArrayList<String> result = db.createUsernameArrayList();
db.close();
return result;
}
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
db = new DBHelper(this);
Cursor cursor = getContacts();
if (flag == 0) {
flag = 1;
while (cursor.moveToNext()) {
String displayName =
cursor.getString(cursor.getColumnIndex(ContactsContract.Data.DISPLAY_NAME));
db.addContact(new Details(displayName, "00000"));
}
}
List<Details> contacts = db.getAllContacts();
List<String> names = new ArrayList<String>();
for (int i = 0; i < contacts.size(); i++) {
names.add(contacts.get(i).getName());
}
String[] objs = names.toArray(new String[] {});
adapter = new ListAdapter(this, contacts, objs);
setListAdapter(adapter);
}
/** Assigns a worker by name to a ticket by ID. */
public static void assignWorker(int ticketNum, String workername) {
DBHelper db = DBHelperFactory.createDBHelper();
Account temp = db.retrieveAccount(workername);
db.updateTicketWorker(ticketNum, temp.get_id());
db.updateTicketQueue(ticketNum, 1);
db.close();
}
/**
* Вывести провайдеров с определенной валютой и страной
*
* @param currency
* @param country
* @throws SQLException
* @throws IOException
*/
public static void task2(String currency, String country) throws SQLException, IOException {
DBHelper DBHelper = new DBHelper();
Connection con = DBHelper.getConnection();
if (con == null) {
return;
}
PreparedStatement st =
con.prepareStatement(
"SELECT *"
+ "FROM provider pv "
+ "JOIN provider_curr pc"
+ " ON pv.id = pc.provider_id"
+ " AND pc.currency = ?"
+ " JOIN provider_country ptr"
+ " ON pv.id = ptr.provider_id"
+ " AND ptr.country = ?;");
st.setString(1, currency);
st.setString(2, country);
ResultSet result = st.executeQuery();
while (result.next()) {
System.out.println(result.getString("name"));
}
DBHelper.closeConnection(con);
}
public User getUserForToken(String token) {
DBHelper dbHelper = new DBHelper(context);
SQLiteDatabase db = dbHelper.getReadableDatabase();
Cursor cursor =
db.query(
DBConfig.TABLE_USER,
null,
DBConfig.KEY_TOKEN + "=?",
new String[] {String.valueOf(token)},
null,
null,
null,
null);
if (cursor != null) {
cursor.moveToFirst();
}
User item = new User();
if (cursor.getCount() == 0) {
db.close();
cursor.close();
return item;
}
item.setIdMaster(cursor.getString(0));
item.setLogin(cursor.getString(1));
item.setToken(cursor.getString(3));
item.setName(cursor.getString(4));
item.setBalance(cursor.getString(5));
item.setOnLine(cursor.getInt(6));
item.setTextInfo(cursor.getString(8));
cursor.close();
db.close();
return item;
}
private void addFavor(int id) {
Traveler p = Travelers.get(id);
Cursor cursor =
DH.matchData(
p.getName(),
p.getCategory(),
p.getAddress(),
p.getTelephone(),
p.getLongitude(),
p.getLatitude(),
p.getContent());
int rows_num = cursor.getCount();
if (rows_num == 1) {
Toast.makeText(this, "您已經新增過了", Toast.LENGTH_SHORT).show();
} else {
DH.insert(
p.getName(),
p.getCategory(),
p.getAddress(),
p.getTelephone(),
p.getLongitude(),
p.getLatitude(),
p.getContent());
Toast.makeText(this, "新增至我的最愛", Toast.LENGTH_SHORT).show();
}
}
@Override
public Cursor query(
Uri uri, String[] projection, String selection, String[] selectionArgs, String sortOrder) {
SQLiteDatabase db;
try {
db = dbOpenHelper.getWritableDatabase();
} catch (SQLiteException e) {
db = dbOpenHelper.getReadableDatabase();
}
String groupBy = null;
String having = null;
// creating a database query
SQLiteQueryBuilder queryBuilder = new SQLiteQueryBuilder();
switch (uriMatcher.match(uri)) {
case SINGLE_ROW:
String rowID = uri.getPathSegments().get(1);
queryBuilder.appendWhere(DBHelper.NOTE_ID + "=" + rowID);
default:
break;
}
queryBuilder.setTables(DBHelper.TABLE_NAME);
Cursor cursor =
queryBuilder.query(db, projection, selection, selectionArgs, groupBy, having, sortOrder);
return cursor;
}
public void deleteFav(MainPageItem qy) {
Cursor cursor = null;
String where =
FavTable.USER_ID
+ " = '"
+ MyApplication.getInstance().getCurrentUser().getObjectId()
+ "' AND "
+ FavTable.OBJECT_ID
+ " = '"
+ qy.getObjectId()
+ "'";
cursor = dbHelper.query(DBHelper.TABLE_NAME, null, where, null, null, null, null);
if (cursor != null && cursor.getCount() > 0) {
cursor.moveToFirst();
int isLove = cursor.getInt(cursor.getColumnIndex(FavTable.IS_LOVE));
if (isLove == 0) {
dbHelper.delete(DBHelper.TABLE_NAME, where, null);
} else {
ContentValues cv = new ContentValues();
cv.put(FavTable.IS_FAV, 0);
dbHelper.update(DBHelper.TABLE_NAME, cv, where, null);
}
}
if (cursor != null) {
cursor.close();
dbHelper.close();
}
}
/**
* 设置内容的收藏状态
*
* @param context
* @param lists
*/
public List<MainPageItem> setFavInFav(List<MainPageItem> lists) {
Cursor cursor = null;
if (lists != null && lists.size() > 0) {
for (Iterator iterator = lists.iterator(); iterator.hasNext(); ) {
MainPageItem content = (MainPageItem) iterator.next();
content.setMyFav(true);
String where =
FavTable.USER_ID
+ " = '"
+ MyApplication.getInstance().getCurrentUser().getObjectId()
+ "' AND "
+ FavTable.OBJECT_ID
+ " = '"
+ content.getObjectId()
+ "'";
cursor = dbHelper.query(DBHelper.TABLE_NAME, null, where, null, null, null, null);
if (cursor != null && cursor.getCount() > 0) {
cursor.moveToFirst();
if (cursor.getInt(cursor.getColumnIndex(FavTable.IS_LOVE)) == 1) {
content.setMyLove(true);
} else {
content.setMyLove(false);
}
}
LogUtils.i(TAG, content.getMyFav() + ".." + content.getMyLove());
}
}
if (cursor != null) {
cursor.close();
dbHelper.close();
}
return lists;
}
public long insertFav(MainPageItem qy) {
long uri = 0;
Cursor cursor = null;
String where =
FavTable.USER_ID
+ " = '"
+ MyApplication.getInstance().getCurrentUser().getObjectId()
+ "' AND "
+ FavTable.OBJECT_ID
+ " = '"
+ qy.getObjectId()
+ "'";
cursor = dbHelper.query(DBHelper.TABLE_NAME, null, where, null, null, null, null);
if (cursor != null && cursor.getCount() > 0) {
cursor.moveToFirst();
ContentValues conv = new ContentValues();
conv.put(FavTable.IS_FAV, 1);
conv.put(FavTable.IS_LOVE, 1);
dbHelper.update(DBHelper.TABLE_NAME, conv, where, null);
} else {
ContentValues cv = new ContentValues();
cv.put(FavTable.USER_ID, MyApplication.getInstance().getCurrentUser().getObjectId());
cv.put(FavTable.OBJECT_ID, qy.getObjectId());
cv.put(FavTable.IS_LOVE, qy.getMyLove() == true ? 1 : 0);
cv.put(FavTable.IS_FAV, qy.getMyFav() == true ? 1 : 0);
uri = dbHelper.insert(DBHelper.TABLE_NAME, null, cv);
}
if (cursor != null) {
cursor.close();
dbHelper.close();
}
return uri;
}
public void run(View view) {
Bundle extras = getIntent().getExtras();
if (extras != null) {
int Value = extras.getInt("id");
if (Value > 0) {
if (mydb.updateContact(
id_To_Update,
name.getText().toString(),
phone.getText().toString(),
email.getText().toString(),
street.getText().toString(),
place.getText().toString())) {
Toast.makeText(getApplicationContext(), "Updated", Toast.LENGTH_SHORT).show();
Intent intent = new Intent(getApplicationContext(), com.example.myapp.MainActivity.class);
startActivity(intent);
} else {
Toast.makeText(getApplicationContext(), "not Updated", Toast.LENGTH_SHORT).show();
}
} else {
if (mydb.insertContact(
name.getText().toString(),
phone.getText().toString(),
email.getText().toString(),
street.getText().toString(),
place.getText().toString())) {
Toast.makeText(getApplicationContext(), "done", Toast.LENGTH_SHORT).show();
} else {
Toast.makeText(getApplicationContext(), "not done", Toast.LENGTH_SHORT).show();
}
Intent intent = new Intent(getApplicationContext(), com.example.myapp.MainActivity.class);
startActivity(intent);
}
}
}
private ArrayList<Long> findSegmetsEdgeIsPartOf(Node node, boolean isOrigin) {
ResultSet rs = null;
PreparedStatement stmt = null;
ArrayList<Long> ids = new ArrayList<>();
try {
stmt =
db.prepareStatement(
"SELECT SEGMENTS.ID FROM SEGMENTS, EDGES WHERE EDGES."
+ (isOrigin ? "ORIGIN" : "DESTINATION")
+ "= "
+ node.id
+ " AND SEGMENTS.ID = EDGES.SEGMENT");
rs = db.executeQuery(stmt);
while (rs.next()) {
long segmentId = rs.getLong("id");
ids.add(segmentId);
}
return ids;
} catch (SQLException e) {
System.err.println("Error getting segmentid from method modelNodeToSegment");
e.printStackTrace();
} finally {
try {
rs.close();
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return null;
}
public void checkResult() {
int maxScore;
if (nummovecount == GetOptimalValue()) {
maxScore = numscore + colWidth * 100;
showDialog(
"Congo You Do It !\n Total Move " + nummovecount + "\nYour Score : " + maxScore, true);
} else {
maxScore = numscore;
showDialog(
"Ohh You miss it.\n Total Move by you "
+ nummovecount
+ "\n Required : "
+ GetOptimalValue()
+ "\nYour Score : "
+ numscore,
false);
}
Cursor rs = mydb.getMyData(1);
rs.moveToFirst();
if (maxScore > rs.getInt(rs.getColumnIndex(DBHelper.MSCORE))) {
mydb.updateScore(1, maxScore, "");
}
if (!rs.isClosed()) rs.close();
}
public static void insertCollect(Context ctx, MSArticle article, MSPlayer player, Message msg) {
MSResponse response = new MSResponse(msg);
String collectId = (String) response.getResponseField("favoriteId");
DBHelper db = DBHelper.getInstance(ctx);
int time = XTimeUtils.str2time(XTimeUtils.getTime());
db.insertCollect(article, player.id, time, collectId);
}
@Override
public void onClick(View arg0) {
// TODO Auto-generated method stub
if (etLectNum.getText().toString().length() == 0) {
Dialog d = new Dialog(this);
d.setTitle("Lecture Number cannot be empty!");
d.show();
} else {
lecture_number = Integer.parseInt(etLectNum.getText().toString());
lecture_title = etLectTitle.getText().toString();
lecture_description = etLectDescription.getText().toString();
dbh = new DBHelper(this);
if (getIntent().getExtras().getBoolean("Update")) {
dbh.updateLecture(
semSelected, courseSelected, lecture_number, lecture_title, lecture_description, "");
Toast.makeText(
getApplicationContext(),
"Lecture " + lecture_number + " updated successfully!",
Toast.LENGTH_SHORT)
.show();
Intent intent =
new Intent(getApplicationContext(), com.aakash.studentmanager.LectureList.class);
intent.setFlags(Intent.FLAG_ACTIVITY_CLEAR_TOP);
startActivity(intent);
} else {
Cursor rs = dbh.getLectureData(semSelected, courseSelected, lecture_number);
rs.moveToFirst();
if (rs.isAfterLast() == true) {
dbh.insertLecture(
semSelected, courseSelected, lecture_number, lecture_title, lecture_description, "");
Toast.makeText(
getApplicationContext(),
"Lecture " + lecture_number + " added successfully!",
Toast.LENGTH_SHORT)
.show();
Intent intent =
new Intent(getApplicationContext(), com.aakash.studentmanager.LectureList.class);
intent.setFlags(Intent.FLAG_ACTIVITY_CLEAR_TOP);
startActivity(intent);
} else {
Dialog d = new Dialog(this);
d.setTitle("Duplicate Entry:");
TextView tv = new TextView(this);
tv.setText("Lecture Number " + lecture_number + " already added!");
d.setContentView(tv);
d.show();
}
}
dbh.close();
}
}
/**
* create gtd Table
*
* @param context
*/
public GtdTable(Context context) {
helper = new DBHelper(context);
//
if (!helper.isTableExits(TABLE_NAME)) {
//
helper.createTable(CREATE_SQL);
}
}
public static void main(String[] args)
throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException {
Class.forName("com.mysql.jdbc.Driver").newInstance();
DBHelper helper = new DBHelper();
helper.start();
// helper.executeUpdate("DROP TABLE IF EXISTS GHOSTS;");
// helper.executeUpdate("DROP TABLE IF EXISTS HAUNTS;");
}
public NoticeDaoDBManager(Context context) {
try {
this.context = context;
dbHelper = DBHelper.getInstance(context);
dataBase = dbHelper.getWritableDatabase();
} catch (Exception e) {
}
}
public static void syncCollectionSuc(Context ctx, MSResponse msResponse) {
JSONArray articlesArray = (JSONArray) msResponse.getResponseField("articlesList");
MSPlayer msPlayer = MSPlayer.currentMSPlayer(ctx);
DBHelper dbHelper = DBHelper.getInstance(ctx);
for (int i = 0; i < articlesArray.length(); i++) {
JSONObject articleObj = articlesArray.optJSONObject(i);
dbHelper.checkCollection(articleObj, msPlayer.id);
}
}
/** Returns the last Annotation by date attached to a specified TicketID. */
public static Annotation getLastAnnotation(int ticketID) {
DBHelper db = DBHelperFactory.createDBHelper();
ArrayList<Annotation> temp = db.createAnnotationArrayListByTicket(ticketID);
Annotation result = temp.get(temp.size() - 1);
db.close();
return result;
}
