RequestProcessor


1   /******************************************************************************
2    * The contents of this file are subject to the   Compiere License  Version 1.1
3    * ("License"); You may not use this file except in compliance with the License
4    * You may obtain a copy of the License at http://www.compiere.org/license.html
5    * Software distributed under the License is distributed on an  "AS IS"  basis,
6    * WITHOUT WARRANTY OF ANY KIND, either express or implied. See the License for
7    * the specific language governing rights and limitations under the License.
8    * The Original Code is                  Compiere  ERP & CRM  Business Solution
9    * The Initial Developer of the Original Code is Jorg Janke  and ComPiere, Inc.
10   * Portions created by Jorg Janke are Copyright (C) 1999-2001 Jorg Janke, parts
11   * created by ComPiere are Copyright (C) ComPiere, Inc.;   All Rights Reserved.
12   * Contributor(s): ______________________________________.
13   *****************************************************************************/
14  package org.compiere.request;
15  
16  import java.sql.*;
17  import java.util.*;
18  
19  import org.apache.log4j.Logger;
20  
21  import org.compiere.util.DB;
22  import org.compiere.util.EMail;
23  import org.compiere.util.EMailUtil;
24  import org.compiere.util.Msg;
25  import org.compiere.model.MRequest;
26  
27  
28  /**
29   *  Request Processor.
30   *
31   *  @author     Jorg Janke
32   *  @version    $Id: RequestProcessor.java,v 1.12 2003/10/11 05:21:33 jjanke Exp $
33   */
34  public class RequestProcessor
35  {
36      /**
37       *  Constructor
38       *  @param vo value object
39       */
40      public RequestProcessor (RequestProcessorVO vo)
41      {
42          long startTime = System.currentTimeMillis();
43          runProcess (vo);
44          //  Allocate Requests
45          runAllocate (vo);
46          //  read new emails
47          runEMail (vo);
48          //
49          long msec = System.currentTimeMillis() - startTime;
50          double sec = (double)msec / 1000;
51          log.info("RequestProcessor " + vo.Name + " - " + sec + " sec");
52      }   //  RequestProcessor
53  
54      /** Logger                  */
55      private static Logger   log = Logger.getLogger(RequestProcessor.class);
56  
57      /*************************************************************************/
58  
59      /**
60       *  Process requests.
61       *  - update requests
62       *  - send alerts
63       *  - escalate alerts
64       *  - Set time next run
65       *
66       *  @param vo Value Object
67       */
68      private void runProcess (RequestProcessorVO vo)
69      {
70          //  Run only when scheduled
71          if (vo.DateNextRun != null && vo.DateNextRun.getTime() > System.currentTimeMillis())
72              return;
73  
74          /**
75           *  update DueType of all requests
76           */
77          String   sql = "UPDATE R_Request "
78              + "SET DueType=R_Request_DueType(DateNextAction) "
79              + "WHERE Processed='N' AND IsActive='Y'";
80          int no = DB.executeUpdate(sql);
81          log.debug("runProcess - Active Requests updated=" + no);
82  
83          /**
84           *  send alerts
85           */
86          if (vo.OverdueAlertDays != 0)
87          {
88              sql = "SELECT DocumentNo, Summary, SalesRep_ID "
89                  + "FROM R_Request "
90                  + "WHERE AD_Client_ID=?"
91                  + " AND TRUNC(DateNextAction)+" + vo.OverdueAlertDays + " > TRUNC(SysDate)";
92              try
93              {
94                  PreparedStatement pstmt = DB.prepareStatement(sql);
95                  pstmt.setInt(1, vo.AD_Client_ID);
96                  ResultSet rs = pstmt.executeQuery();
97                  while (rs.next())
98                  {
99                      String   DocumentNo = rs.getString(1);
100                     String   Summary = rs.getString(2);
101                     int SalesRep_ID = rs.getInt(3);
102                     sendAlert (vo, DocumentNo, Summary, SalesRep_ID);
103                 }
104                 rs.close();
105                 pstmt.close();
106             }
107             catch (SQLException e)
108             {
109                 log.error("runProcess (Alert): " + vo.Name, e);
110             }
111         }
112 
113         /**
114          *  Escalate
115          */
116         if (vo.OverdueAssignDays != 0)
117         {
118             sql = "SELECT DocumentNo, Summary, SalesRep_ID, R_Request_ID "
119                 + "FROM R_Request "
120                 + "WHERE AD_Client_ID=?"
121                 + " AND IsEscalated='N'"    //  just once
122                 + " AND TRUNC(DateNextAction)+" + vo.OverdueAssignDays + " > TRUNC(SysDate)";
123             try
124             {
125                 PreparedStatement pstmt = DB.prepareStatement(sql);
126                 pstmt.setInt(1, vo.AD_Client_ID);
127                 ResultSet rs = pstmt.executeQuery();
128                 while (rs.next())
129                 {
130 
131                     String   DocumentNo = rs.getString(1);
132                     String   Summary = rs.getString(2);
133                     int SalesRep_ID = rs.getInt(3);
134                     int R_Request_ID = rs.getInt(4);
135                     escalate (vo, DocumentNo, Summary, SalesRep_ID, R_Request_ID);
136                 }
137                 rs.close();
138                 pstmt.close();
139             }
140             catch (SQLException e)
141             {
142                 log.error("runProcess (Escalate): " + vo.Name, e);
143             }
144         }
145 
146 
147         /**
148          *  New NextRun
149          */
150         String   nextRun = "SysDate+" + vo.Frequency;             //  default (D)ays
151         if (vo.FrequencyType.equals(RequestProcessorVO.FREQUENCY_HOUR))         //  (H)our
152             nextRun += "/24";
153         else if (vo.FrequencyType.equals(RequestProcessorVO.FREQUENCY_MINUTE))  //  (M)inute
154             nextRun += "/1440";     //  60/24
155         //
156         String   update = "UPDATE R_RequestProcessor "
157             + "SET DateLastRun=SysDate, DateNextRun=" + nextRun
158             + " WHERE R_RequestProcessor_ID=" + vo.R_RequestProcessor_ID;
159         DB.executeUpdate(update);
160         //  Read
161         sql = "SELECT DateNextRun "
162             + "FROM R_RequestProcessor "
163             + "WHERE R_RequestProcessor_ID=?";
164         try
165         {
166             PreparedStatement pstmt = DB.prepareStatement(sql);
167             pstmt.setInt(1, vo.R_RequestProcessor_ID);
168             ResultSet rs = pstmt.executeQuery();
169             if (rs.next())
170                 vo.DateNextRun = rs.getTimestamp(1);
171             rs.close();
172             pstmt.close();
173         }
174         catch (SQLException e)
175         {
176             log.error("runProcess (NextRun): " + vo.Name, e);
177         }
178         log.debug("runProcess complete:" + vo.Name);
179     }   //  runProcess
180 
181     /**
182      *  Send Alert EMail
183      *
184      *  @param vo Value Object
185      *  @param DocumentNo document no
186      *  @param Summary document summary
187      *  @param SalesRep_ID user
188      */
189     private void sendAlert (RequestProcessorVO vo,
190         String   DocumentNo, String   Summary, int SalesRep_ID)
191     {
192         String   to = EMailUtil.getEMail_User(SalesRep_ID);
193         //  Alert: Request {0} overdue
194         String   subject = Msg.getMsg(vo.AD_Language, "RequestAlert", new Object  [] {DocumentNo});
195         EMail email = new EMail (vo.SMTPHost, vo.RequestEMail, to, subject, Summary);
196         email.send();
197     }   //  sendAlert
198 
199     /**
200      *  Escalate
201      *
202      *  @param vo Value Object
203      *  @param DocumentNo document no
204      *  @param Summary document summary
205      *  @param SalesRep_ID user
206      *  @param R_Request_ID request
207      */
208     private void escalate (RequestProcessorVO vo,
209         String   DocumentNo, String   Summary, int SalesRep_ID, int R_Request_ID)
210     {
211         //  Get Supervisor
212         int Supervisor_ID = -1;
213         String   sql = "SELECT Supervisor_ID from AD_User WHERE AD_User_ID=?";
214         try
215         {
216             PreparedStatement pstmt = DB.prepareStatement(sql);
217             pstmt.setInt(1, SalesRep_ID);
218             ResultSet rs = pstmt.executeQuery();
219             if (rs.next())
220             {
221                 Supervisor_ID = rs.getInt(1);
222                 if (rs.wasNull())
223                     Supervisor_ID = -1;
224             }
225             rs.close();
226             pstmt.close();
227         }
228         catch (SQLException e)
229         {
230             log.error("escalate", e);
231         }
232         //  No one to escalate to
233         if (Supervisor_ID == -1 || Supervisor_ID == vo.Supervisor_ID)
234         {
235             sql = "UPDATE R_Request SET IsEscalated='Y' WHERE R_Request_ID=" + R_Request_ID;
236             DB.executeUpdate(sql);
237             return;
238         }
239 
240         //  ----------------
241 
242         //  Escalate: Request {0}
243         String   subject = Msg.getMsg(vo.AD_Language, "RequestEscalate", new Object  [] {DocumentNo});
244         sql = "UPDATE R_Request "
245             + "SET IsEscalated='Y', ActionType='T', AD_User_ID=" + Supervisor_ID
246             + ", Result='" + subject + "', Updated=SysDate, UpdatedBy=0 "
247             + "WHERE R_Request_ID=" + R_Request_ID;
248         int no = DB.executeUpdate(sql);
249         //  We updated Request, now process it.
250         if (no == 1)
251         {
252             try
253             {
254                 CallableStatement cstmt = DB.prepareCall("{CALL R_Request_Process(NULL,?)}");
255                 cstmt.setInt(1, R_Request_ID);
256                 cstmt.execute();
257                 cstmt.close();
258             }
259             catch (SQLException e)
260             {
261                 log.error("escalate (process)", e);
262             }
263         }
264         else
265             log.error("escalate - Request not updated #=" + no);
266 
267         //  Send Email - current user
268         String   to = EMailUtil.getEMail_User(SalesRep_ID);
269         EMail email = new EMail (vo.SMTPHost, vo.RequestEMail, to, subject, Summary);
270         email.send();
271         //  Send Email - new user
272         to = EMailUtil.getEMail_User(Supervisor_ID);
273         email = new EMail (vo.SMTPHost, vo.RequestEMail, to, subject, Summary);
274         email.send();
275     }   //  escalate
276 
277 
278     /*************************************************************************/
279 
280     /**
281      *  Allocate Sales Rep
282      *  @param vo request processor
283      */
284     private void runAllocate (RequestProcessorVO vo)
285     {
286         int changed = 0;
287         int same = 0;
288         Properties ctx = new Properties();
289         //
290         String   sql = "SELECT * FROM R_Request WHERE AD_Client_ID=? AND SalesRep_ID=0 AND Processed='N'";
291         PreparedStatement pstmt = null;
292         try
293         {
294             pstmt = DB.prepareStatement(sql);
295             pstmt.setInt(1, vo.AD_Client_ID);
296             ResultSet rs = pstmt.executeQuery();
297             while (rs.next())
298             {
299                 MRequest req = new MRequest (ctx, rs);
300                 int SalesRep_ID = findSalesRep(vo, req.getR_RequestType_ID(), req.getSummary());
301                 if (SalesRep_ID != 0)
302                 {
303                     req.setSalesRep_ID(SalesRep_ID);
304                     req.save();
305                     changed++;
306                 }
307                 else
308                     same++;
309             }
310             rs.close();
311             pstmt.close();
312             pstmt = null;
313         }
314         catch (SQLException ex)
315         {
316             log.error("runAllocate", ex);
317         }
318         try
319         {
320             if (pstmt != null)
321                 pstmt.close();
322         }
323         catch (SQLException ex1)
324         {
325         }
326         pstmt = null;
327         //
328         log.info("runAllocate - Same=" + same + ", Changed=" + changed);
329     }   //  runAllocate
330 
331     /**
332      *  Find BPartner from WebRequest.
333      *  @param rs ResultSet
334      *  @return C_BPartner_ID/AD_User_ID
335      *  @throws SQLException
336      */
337     private int[] getBPartner (ResultSet rs) throws SQLException
338     {
339         int[] retValue = new int[] {0,0};
340 
341         //  Try to find it by email address
342         int AD_Client_ID = rs.getInt("AD_Client_ID");
343         String   EMail = rs.getString("EMail");
344         if (EMail != null)
345         {
346             String   sql = "SELECT C_BPartner_ID, AD_User_ID "
347                 + "FROM AD_User "
348                 + "WHERE AD_Client_ID=? AND EMail=?";
349             PreparedStatement pstmt = null;
350             try
351             {
352                 pstmt = DB.prepareStatement(sql);
353                 pstmt.setInt(1, AD_Client_ID);
354                 pstmt.setString(2, EMail);
355                 ResultSet rsx = pstmt.executeQuery();
356                 if (rsx.next())
357                 {
358                     retValue[0] = rsx.getInt(1);
359                     retValue[1] = rsx.getInt(2);
360                 }
361                 rsx.close();
362                 pstmt.close();
363                 pstmt = null;
364             }
365             catch (Exception   e)
366             {
367                 log.error("getBPartner", e);
368             }
369             finally
370             {
371                 try
372                 {
373                     if (pstmt != null)
374                         pstmt.close ();
375                 }
376                 catch (Exception   e)
377                 {}
378                 pstmt = null;
379             }
380         }
381         if (retValue[0] != 0 && retValue[1] != 0)
382         {
383             log.debug("getBPartner - found C_BPartner_ID=" + retValue[0] + ", AD_User_ID=" + retValue[1]);
384             return retValue;
385         }
386 
387         return retValue;
388     }   //  createBPartner
389 
390 
391     /**
392      *  Find SalesRep/User based on Request Type and Question.
393      *
394      *  @param vo value object
395      *  @param R_RequestType_ID request type
396      *  @param Question info
397      *  @return SalesRep_ID user
398      */
399     private int findSalesRep (RequestProcessorVO vo, int R_RequestType_ID, String   Question)
400     {
401         String   sql = "SELECT  R_RequestType_ID, Keyword, AD_User_ID "
402             + "FROM R_RequestProcessor_Route "
403             + "WHERE R_RequestProcessor_ID=?"
404             + " AND IsActive='Y' "
405             + "ORDER BY SeqNo";
406 
407         int AD_User_ID = 0;
408         String   Q = Question.toUpperCase();
409         PreparedStatement pstmt = null;
410         try
411         {
412             pstmt = DB.prepareStatement(sql);
413             pstmt.setInt(1, vo.R_RequestProcessor_ID);
414             ResultSet rs = pstmt.executeQuery();
415             while (rs.next() && AD_User_ID == 0)
416             {
417                 if (R_RequestType_ID != 0 && rs.getInt(1) == R_RequestType_ID)
418                     AD_User_ID = rs.getInt(3);
419                 else
420                 {
421                     String   keyword = rs.getString(2);
422                     if (keyword != null && Q.indexOf(keyword.toUpperCase()) != -1)
423                         AD_User_ID = rs.getInt(3);
424                 }
425             }
426             rs.close();
427             pstmt.close();
428             pstmt = null;
429         }
430         catch (Exception   e)
431         {
432             log.error("findSalesRep", e);
433         }
434         finally
435         {
436             try
437             {
438                 if (pstmt != null)
439                     pstmt.close ();
440             }
441             catch (Exception   e)
442             {}
443             pstmt = null;
444         }
445         //  return user
446         if (AD_User_ID != 0)
447             return AD_User_ID;
448         return vo.Supervisor_ID;
449     }   //  findSalesRep
450 
451     /**
452      *  Get (default) RequestType.
453      *
454      *  @param vo value object
455      *  @param R_RequestType_ID initial value
456      *  @return R_RequestType_ID or default
457      */
458     private int getRequestTypeID (RequestProcessorVO vo, int R_RequestType_ID)
459     {
460         if (R_RequestType_ID > 0)
461             return R_RequestType_ID;
462 
463         //  Get Default
464         String   sql = "SELECT R_RequestType_ID "
465             + "FROM R_RequestType "
466             + "WHERE AD_Client_ID=? AND IsDefault='Y'";
467         int retValue = 0;
468         try
469         {
470             PreparedStatement pstmt = DB.prepareStatement(sql);
471             pstmt.setInt(1, vo.AD_Client_ID);
472             ResultSet rs = pstmt.executeQuery();
473             if (rs.next())
474                 retValue = rs.getInt(1);
475             rs.close();
476             pstmt.close();
477         }
478         catch (SQLException e)
479         {
480             log.error("getRequestTypeID", e);
481         }
482         return retValue;
483     }   //  getRequestTypeID
484 
485     /*************************************************************************/
486 
487     /**
488      *  Create from EMail
489      *  @param vo Value Object
490      */
491     private void runEMail (RequestProcessorVO vo)
492     {
493 
494     }   //  runEMail
495 
496     /*************************************************************************/
497 
498     /**
499      *  Test Run
500      *  @param args ignored
501      */
502     public static void main(String  [] args)
503     {
504         org.compiere.Compiere.startupClient ();
505         RequestProcessorVO[] vo = RequestProcessorVO.get();
506         if (vo != null && vo.length > 0)
507             new RequestProcessor (vo[0]);
508     }   //  main
509 
510 }   //  RequestProcessor
511
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags