Вьюха такая:
Поля:
Folder ASC| End Date ASC| Flag (@If(Action="Этап завершен";"1";"0"))
Selection Formula = "SELECT FOLDERIDOS_COPY!="" & (Action="Этап завершен" | Action="Доставлено") & ActTime>=@Adjust(@Now;0;-1;0;0;0;0)"
Агент такой:
import lotus.domino.*;
import java.sql.*;
import java.lang.*;
import java.text.*;
//***** ЗАКАЧКА ИЗ ЖУРНАЛА ПРЕДКОНТРАКТНОЙ РАБОТЫ *****
//сначала запускается RefreshStatViews для обновления вьюшек
public class JavaAgent extends AgentBase {
Document dc=null;
public void NotesMain() {
Session s = getSession();
Connection cnn=null;
Statement stmt=null;
ResultSet rs=null;
int dcs=0;
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
String strAny="SELECT username,instanceos,subject,activityname,folderidos_copy,contractdepexecutorpers
on,"+
"contractdepexecutor,contractexecutor,instanceunid,contractsignernamelist,co
ntractcontragent,"+
"duedateos,start_time,end_time,form,unid,alt_start_time,ins_time,decision "+
"FROM xxlotus_journal_contracts WHERE unid=''";
try {
//создать соединение к Oracle
Class.forName("oracle.jdbc.driver.OracleDriver");
cnn = DriverManager.getConnection("jdbc
racle:thin
0.0.0.0:1521
rod","xx","xx");
stmt = cnn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
rs = stmt.executeQuery(strAny);
String fld="";
int x=0;
Database db=s.getDatabase("UprFlow01/Upr/Uralkaly","PreContract\\Journal.nsf");
View vw=db.getView("vSortedFolders");
Timestamp[] dt=new Timestamp[2];
DateTime dd=null;
dc=vw.getFirstDocument();
while (dc!=null){
if (!dc.getItemValueString("FOLDERIDOS_COPY").equals(fld)) {dt[0]=null;dt[1]=null;x=0;}
if (dc.getItemValueString("Action").equals("Доставлено")) {
if (x==0) {dd=(DateTime)dc.getItemValue("ActTime").elementAt(0);
dt[0]=new Timestamp(dd.toJavaDate().getTime());}
x++;
dd=(DateTime)dc.getItemValue("ActTime").elementAt(0);
dt[1]=new Timestamp(dd.toJavaDate().getTime());}
else {
if (dt[1]!=null) {dcs+=(byte)addRow(rs, dc, dt[0], dt[1]); x=0;}
}
fld=dc.getItemValueString("FOLDERIDOS_COPY");
dc=vw.getNextDocument(dc);
} //wend
s.recycle();rs.close();stmt.close();cnn.close();
} //end try
catch (SQLException sqle) {System.err.println("SQL error: "+sqle.getErrorCode()+": "+sqle.getMessage());}
catch (Exception e) {e.printStackTrace();}
finally {System.out.println("'DropEventsToOraB' Inserted " + dcs + " documents");}
} //end void
private byte addRow(ResultSet rs,Document dc,Timestamp t0,Timestamp t1) {
java.util.Date dtc=new java.util.Date();
try {
rs.moveToInsertRow();
rs.updateString("UNID", dc.getUniversalID());
rs.updateString("FORM",dc.getItemValueString("FORMNAME"));
rs.updateString("USERNAME",dc.getItemValueString("ActivityOwnerOS_copy"));
rs.updateString("INSTANCEOS",dc.getItemValueString("INSTANCEOS"));
rs.updateString("SUBJECT",dc.getItemValueString("SUBJECT"));
rs.updateString("ACTIVITYNAME",dc.getItemValueString("ACTIVITYNAME"));
rs.updateString("DECISION",dc.getItemValueString("APPROVALCHOICEOS"));
rs.updateString("FOLDERIDOS_COPY",dc.getItemValueString("FOLDERIDOS_COPY"));
rs.updateString("CONTRACTDEPEXECUTORPERSON",dc.getItemValueString("CONTRACTDEPEXECUTORPERSON"));
rs.updateString("CONTRACTEXECUTOR",dc.getItemValueString("EXECUTOR"));
rs.updateString("CONTRACTDEPEXECUTOR",dc.getItemValueString("CURRENTDEPARTMENT"));
rs.updateString("CONTRACTCONTRAGENT",dc.getItemValueString("CONTRAGENT"));
rs.updateString("INSTANCEUNID",dc.getItemValueString("INSTANCEUNID"));
rs.updateString("CONTRACTSIGNERNAMELIST",dc.getItemValueString("CONTRACTSIGNERNAMELIST"));
DateTime dd=null;
if (canBeUpdated("DUEDATEOS")) {
dd=(DateTime)dc.getItemValue("DUEDATEOS").elementAt(0);
rs.updateTimestamp("DUEDATEOS",new Timestamp(dd.toJavaDate().getTime()));}
if (canBeUpdated("ACTTIME")) {
dd=(DateTime)dc.getItemValue("ACTTIME").elementAt(0);
rs.updateTimestamp("END_TIME",new Timestamp(dd.toJavaDate().getTime()));}
rs.updateTimestamp("ALT_START_TIME",t0);
rs.updateTimestamp("START_TIME",t1);
rs.updateTimestamp("INS_TIME",new Timestamp(dtc.getTime()));
try {rs.insertRow();return 1;}
catch (SQLException sqle) {rs.cancelRowUpdates();}
}
catch (Exception e) {}
return 0;
}
private boolean canBeUpdated(String nm) {
try {
String itm=(String)dc.getFirstItem(nm).getText();
if (itm.length()>0) return true;
}
catch (Exception e) {}
return false;
}
} //end class