Это старая версия документа.

aaa

import java.nio.charset.Charset;
 
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.time.format.DateTimeFormatter;
 
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.PreparedStatement;
import java.sql.Timestamp;
 
public class DbEmail extends ScriptJava {
 
    Ref refLastDate;
    Ref refMsgText;
    Ref refMsgError;
    Ref refMsgSend;
 
    int time;
    String text;
 
    final static String dbname = "db";
    Database db;
 
    boolean connected;
 
 
    @Override
    public boolean load() {
        time = getArg("time", 800);
        refLastDate = createRef("lastdate");
        refMsgText  = createRef("msg", "text");
        refMsgError = createRef("msg", "error");
        refMsgSend  = createRef("msg", "send");
 
        return true;
    }
 
 
    @Override
    public boolean prepare() {
        db = getDatabase(dbname);
        if (db == null) {
            printError("Database module is not found: " + dbname);
            return false;
        }
        connected = false;
        return true;
    }
 
 
    @Override
    public void execute() {
        if (db == null || !isValid())
            return;
 
        if (!connected && db.isConnected()) {
            connected = true;
            init();
        }
 
        if (connected && !db.isConnected()) {
            connected = false;
        }
 
        if (connected)
            update();
    }
 
 
    /// init ///
    private void init() {
    }
 
 
    private final static Charset chUTF = Charset.forName("UTF-8");
    private final static Charset chWindows1251 = Charset.forName("windows-1251");
    private String w(String s) {
        return new String(s.getBytes(chWindows1251), chUTF);
    }
 
 
    /// update ///
    private void update() {
 
        LocalDateTime curldt = LocalDateTime.now();
        long dt1 = 0;
        long dt2 = 0;
 
        try (Statement st = db.getConnection().createStatement()) {
 
            String sql = "select current_timestamp from rdb$database";
            try (ResultSet rs = st.executeQuery(sql)) {
                if (rs.next())
                    curldt = rs.getTimestamp(1).toLocalDateTime();
            }
 
            if( refLastDate.getInt() == 0 ) {
                LocalDateTime lastldt = curldt.minusDays(1);
                refLastDate.setInt(
                        (lastldt.getYear() % 100) * 10000 + lastldt.getMonthValue() * 100 + lastldt.getDayOfMonth() );
            }
 
 
            dt1 = refLastDate.getInt() * 10000  + time;
            dt2 = (curldt.getYear() % 100) * 100000000 + curldt.getMonthValue() * 1000000 + curldt.getDayOfMonth() * 10000 +
                        curldt.getHour() * 100 + curldt.getMinute();
 
            db.getConnection().commit();
        } catch (Exception e) {
            printError(e, "Request error");
        }
 
        if (dt2 - dt1 < 10000) {
            if( refMsgError.getBool() ) {
                refMsgSend.setInt(refMsgSend.getInt() + 1);
            }
            return;
        }
 
        LocalDateTime ldt2 = LocalDateTime.of(
                curldt.toLocalDate(),
                LocalTime.of(time / 100, time % 100));
 
        LocalDateTime ldt1 = ldt2.minusDays(1);
 
        String sql =
                "select t.id, t.dt_begin, r.name rname, t.descr, t.status, t.status, " +
                        "t.dt_create, t.dt_end, t.dt_expire, p.name pname, t.dt_reg, " +
                        "datediff(minute, t.dt_end, t.dt_reg) delay, t.reader_id, t.dt_begin, list(pl.name, ', ') persons " +
                        "from msc_person p " +
                        "right join msc_task t on p.id=t.person_id " +
                        "join msc_reader r on r.id=t.reader_id " +
                        "left join msc_scheduleperson sp on sp.schedule_id=t.schedule_id " +
                        "left join msc_person pl on pl.id=sp.person_id " +
                        "where t.dt_begin>=? and t.dt_begin<? and status=? " +
                        "group by " +
                        "  t.id, t.dt_begin, rname, t.descr, t.status, t.status, t.dt_create, " +
                        "  t.dt_end, t.dt_expire, pname, t.dt_reg, delay, t.reader_id " +
                        "order by t.dt_begin ";
 
        int n = 0;
        text = w("Отчет по невыполненным задачам\n") +
                "==============================\n\n";
 
        try (PreparedStatement st = db.getConnection().prepareStatement(sql)) {
 
            st.setTimestamp(1, Timestamp.valueOf(ldt1));
            st.setTimestamp(2, Timestamp.valueOf(ldt2));
            st.setInt(3, 3);
 
            DateTimeFormatter dtf = DateTimeFormatter.ofPattern("dd.MM.yyyy HH:mm");
 
            try (ResultSet rs = st.executeQuery()) {
                while (rs.next()) {
 
                    String persons = rs.getString("persons");
                    if( rs.wasNull() )
                        persons = "";
 
                    text +=
                            w("Дата начала:  ") + rs.getTimestamp("dt_begin").toLocalDateTime().format(dtf) + "\n" +
                                    w("Закончить до: ") + rs.getTimestamp("dt_end").toLocalDateTime().format(dtf) + "\n" +
                                    w("Считыватель:  ") + rs.getString("rname") + "\n" +
                                    w("Описание:     ") + rs.getString("descr") + "\n" +
                                    w("Назначение:   ") + persons + "\n" +
                                    "---" + "\n";
 
                    ++n;
                }
            }
 
            text += w("Всего задач: ") + n + "\n";
 
            refMsgText.setString(text);
            refMsgSend.setInt(1);
 
            refLastDate.setInt( (curldt.getYear() % 100) * 10000 + curldt.getMonthValue() * 100 + curldt.getDayOfMonth() );
 
 
        } catch (Exception e) {
            printError(e, "Request error");
        }
 
    }
}