Examples with Select db.Select used on opensource projects

Example 61 with Select

use of db.Select in project common by zenlunatics.

the class EventProvider method sendReminder.

// --------------------------------------------------------------------------
private void sendReminder(Mail mail, String recipient, Event event, String note, LocalDate date, int num_days, boolean last_event, Site site, DBConnection db) {
    if (recipient == null || recipient.length() == 0)
        return;
    String name = getDisplayName();
    if (!name.endsWith(" Calendar"))
        name += " Calendar";
    StringBuilder message = new StringBuilder(name).append(" Reminder - ");
    StringBuilder subject = new StringBuilder();
    String event_text = event.getEventText();
    if (event_text != null && m_event_text_in_reminder_subject)
        subject.append(event_text).append(' ');
    appendReminderDate(event, date, num_days, last_event, message, subject, site);
    if (event_text != null) {
        message.append("\n\nEvent: ");
        message.append(event_text);
    }
    String notes = event.getNotes();
    if (notes != null && notes.length() > 0) {
        message.append("\n\nNotes: ");
        message.append(notes);
    }
    if (note != null && note.length() > 0) {
        message.append("\n\nReminder notes: ");
        message.append(note);
    }
    String owner = null;
    if (m_events_are_owned) {
        String[] row = db.readRow(new Select("first,last,email").from("people").whereIdEquals(event.getOwner()));
        owner = row[0] + " " + row[1] + "<" + row[2] + ">";
    }
    if (s_reminder_handler == null || !s_reminder_handler.handleReminder(mail, this, event, owner, recipient, subject.toString(), message.toString(), site, db)) {
        if (owner != null)
            mail.setReplyTo(owner);
        if ("poster".equals(recipient))
            recipient = db.lookupString(new Select("email").from("people").whereIdEquals(event.getOwner()));
        mail.send(recipient, getDisplayName() + " Reminder - " + subject.toString(), message.toString(), false);
    }
}

Example 62 with Select

use of db.Select in project common by zenlunatics.

the class EventProvider method selectRepeatingEvents.

// --------------------------------------------------------------------------
private ArrayList<Event> selectRepeatingEvents(Calendar from, Calendar to, String category, String location, Request request) {
    if (category != null && !m_events_have_category)
        return null;
    if (location != null && m_locations == null)
        return null;
    try {
        StringBuilder where = new StringBuilder();
        if (to != null) {
            where.append("date<'");
            where.append(DBConnection.dateString(to));
            where.append("' AND ");
        }
        where.append("(end_date ISNULL OR end_date>='");
        where.append(DBConnection.dateString(from));
        where.append("')");
        Select q = new Select("*").from(m_events_table).where(where.toString());
        andFilters(q);
        if (category != null && !category.equals("All"))
            q.andWhere(m_events_table + "_categories_id=" + request.db.lookupInt("id", m_events_table + "_categories", "text='" + DBConnection.escape(category) + "'", -1));
        if (location != null && !location.equals("All")) {
            String locations_table = getLocationsTable();
            q.andWhere(locations_table + "_id=" + request.db.lookupInt("id", locations_table, "text='" + DBConnection.escape(location) + "'", -1));
        }
        q.andWhere("repeat<>'never'");
        ResultSet rs = request.db.select(q.toString());
        if (rs.isBeforeFirst()) {
            ArrayList<Event> repeating_events = new ArrayList<Event>();
            while (rs.next()) repeating_events.add(readEvent(rs, request));
            rs.getStatement().close();
            return repeating_events;
        }
        rs.getStatement().close();
        return null;
    } catch (SQLException e) {
        throw new RuntimeException(e);
    }
}

Example 63 with Select

use of db.Select in project common by zenlunatics.

the class EventProvider method adjustTables.

// --------------------------------------------------------------------------
protected void adjustTables(DBConnection db) {
    if (m_events_table != null) {
        JDBCTable table_def = new JDBCTable().add(new JDBCColumn("date", Types.DATE)).add(new JDBCColumn("notes", Types.VARCHAR)).add(new JDBCColumn("uuid", Types.VARCHAR));
        if (m_events_are_owned)
            table_def.add(new JDBCColumn("_owner_", "people").setOnDeleteSetNull(true));
        if (m_events_can_repeat) {
            table_def.add(new JDBCColumn("repeat", Types.VARCHAR));
            table_def.add(new JDBCColumn("end_date", Types.DATE));
        }
        if (m_events_have_category) {
            String categories_table = m_events_table + "_categories";
            table_def.add(new JDBCColumn(categories_table).setOnDeleteSetNull(true));
            JDBCTable table_def2 = new JDBCTable().add(new JDBCColumn("text", Types.VARCHAR)).add(new JDBCColumn("color", Types.VARCHAR));
            if (db.tableExists(categories_table))
                db.getTable(categories_table).createMissingColumns(table_def2, db).dropExtraColumns(table_def2, db);
            else
                db.createTable(categories_table, "text VARCHAR,color VARCHAR");
            m_categories = (Options) m_site.getObjects(categories_table);
            if (m_categories == null) {
                m_categories = new Options(new Select("*").from(categories_table).orderBy("lower(text)"), true, m_site).setAllowEditing(true);
                m_categories.setObjectClass(Category.class);
                m_site.addObjects(m_categories);
            }
        }
        if (m_events_have_color)
            table_def.add(new JDBCColumn("color", Types.VARCHAR));
        if (m_events_have_event)
            table_def.add(new JDBCColumn("event", Types.VARCHAR));
        if (m_events_have_location) {
            String locations_table = getLocationsTable();
            table_def.add(new JDBCColumn(locations_table).setOnDeleteSetNull(true));
            db.createTable(locations_table, "text VARCHAR");
            m_locations = (Options) m_site.getObjects(locations_table);
            if (m_locations == null) {
                m_locations = new Options(new Select("*").from(locations_table).orderBy("lower(text)"), true, m_site).setAllowEditing(true);
                m_site.addObjects(m_locations);
            }
        }
        if (m_events_have_start_time) {
            table_def.add(new JDBCColumn("start_time", Types.TIME));
            table_def.add(new JDBCColumn("end_time", Types.TIME));
        }
        if (m_one_table != null)
            table_def.add(new JDBCColumn(m_one_table));
        if (m_support_registrations)
            table_def.add(new JDBCColumn("register_people", Types.BOOLEAN));
        table_def.add(new JDBCColumn("_timestamp_", Types.TIMESTAMP));
        for (JDBCColumn column : m_additional_columns) table_def.add(column);
        db.getTable(m_events_table, true).matchColumns(table_def, db);
        db.createIndex(m_events_table, "date");
        if (m_support_registrations) {
            table_def = new JDBCTable().add(new JDBCColumn(m_events_table)).add(new JDBCColumn("person", Types.INTEGER)).add(new JDBCColumn("note", Types.VARCHAR));
            db.getTable(m_events_table + "_registrations", true).createMissingColumns(table_def, db).dropExtraColumns(table_def, db);
        }
        adjustRemindersTables(db);
    }
    if (m_role != null) {
        Roles.add(m_role, m_site, db);
        setAccessPolicy(new RoleAccessPolicy(m_role).add().delete().edit());
    } else
        setAccessPolicy(null);
}

Example 64 with Select

use of db.Select in project common by zenlunatics.

the class EventProvider method maxEvents.

// --------------------------------------------------------------------------
protected int maxEvents(int target_max, Calendar c, Request request) {
    if (m_events_table == null) {
        request.site.log(m_name + " maxEvents with null events table");
        return 0;
    }
    Select query = new Select(null).from(m_events_table).where("date>='" + DBConnection.dateString(c) + "'");
    andFilters(query);
    if (m_events_can_repeat)
        query.andWhere("repeat='never'");
    int max_events = request.db.countRows(m_events_table, query.getWhere());
    if (max_events < target_max && m_events_can_repeat) {
        Select q2 = new Select(null).from(m_events_table).where("(end_date ISNULL OR end_date>='" + DBConnection.dateString(c) + "')");
        andFilters(q2);
        q2.andWhere("repeat<>'never'");
        max_events += request.db.countRows(m_events_table, q2.getWhere());
    }
    return max_events;
}

Example 65 with Select

use of db.Select in project common by zenlunatics.

the class EventProvider method sendReminders.

// --------------------------------------------------------------------------
@AdminTask({ "nightly" })
public void sendReminders(LocalDateTime now, boolean nightly, Site site, DBConnection db) {
    Mail mail = new Mail(site);
    ArrayList<Integer> delete_ids = new ArrayList<Integer>();
    String reminders_table = m_events_table + "_reminders";
    try {
        StringBuilder columns = new StringBuilder();
        columns.append(m_events_table).append(".id,").append(reminders_table).append(".id AS reminder_id,").append(m_events_table).append("_id,last_sent,note,email,num,unit,before,date,notes");
        if (m_events_have_event)
            columns.append(",event");
        if (m_events_can_repeat)
            columns.append(",repeat,end_date,repeat_days");
        if (m_events_have_start_time)
            columns.append(",start_time,end_time");
        if (m_events_are_owned)
            columns.append(",").append(m_events_table).append("._owner_");
        Select query = new Select(columns.toString()).from(m_events_table).joinOn(reminders_table, "(" + m_events_table + ".id=" + reminders_table + "." + m_events_table + "_id OR " + reminders_table + "." + m_events_table + "_id IS NULL)").where(nightly ? "(unit='day' OR unit='week')" : "(unit='hour' OR unit='minute')");
        andFilters(query);
        ResultSet rs = db.select(query);
        Calendar calendar = site.newCalendar();
        while (rs.next()) {
            int num = rs.getInt("num");
            String unit = rs.getString("unit");
            boolean before = rs.getBoolean("before");
            int num_days = numDays(num, unit, before);
            LocalDate reminder_date = site.newLocalDate().minusDays(num_days);
            Event event = new Event(this, calendar, null);
            event.load(rs, site);
            if (event.hasEndedBy(reminder_date)) {
                int id = rs.getInt(3);
                if (!rs.wasNull())
                    delete_ids.add(id);
            } else {
                boolean occurs_on = false;
                boolean last_event = false;
                if (m_events_can_repeat) {
                    String repeat_days = rs.getString("repeat_days");
                    if ("first event".equals(repeat_days))
                        occurs_on = reminder_date.isEqual(rs.getDate("date").toLocalDate());
                    else if ("last event".equals(repeat_days)) {
                        occurs_on = reminder_date.isEqual(rs.getDate("end_date").toLocalDate());
                        last_event = true;
                    } else
                        occurs_on = event.occursOn(reminder_date);
                } else
                    occurs_on = event.occursOn(reminder_date);
                if (occurs_on) {
                    Date last_sent = rs.getDate("last_sent");
                    if (last_sent == null || !last_sent.toLocalDate().equals(now.toLocalDate())) {
                        if (m_events_have_start_time && (unit.equals("hour") || unit.equals("minute"))) {
                            LocalTime time = before ? rs.getTime("start_time").toLocalTime() : rs.getTime("end_time").toLocalTime();
                            if (unit.equals("hour"))
                                if (before)
                                    time = time.minusHours(num);
                                else
                                    time = time.plusHours(num);
                            else if (before)
                                time = time.minusMinutes(num);
                            else
                                time = time.plusMinutes(num);
                            if (now.toLocalTime().isBefore(time))
                                continue;
                        }
                        String recipient = rs.getString("email");
                        sendReminder(mail, recipient, event, rs.getString("note"), reminder_date, num_days, last_event, site, db);
                        if (!event.isRepeating()) {
                            int id = rs.getInt(3);
                            if (!rs.wasNull()) {
                                delete_ids.add(id);
                                continue;
                            }
                        }
                        db.update(reminders_table, new NameValuePairs().setDate("last_sent", now.toLocalDate()), rs.getInt("reminder_id"));
                    }
                }
            }
        }
        rs.getStatement().close();
    } catch (Exception e) {
        System.out.println("EventProvider.sendReminders");
        e.printStackTrace(System.out);
    }
    for (Integer id : delete_ids) db.deleteById(reminders_table, id);
}