001    /*
002     * Copyright (c) 2009 The openGion Project.
003     *
004     * Licensed under the Apache License, Version 2.0 (the "License");
005     * you may not use this file except in compliance with the License.
006     * You may obtain a copy of the License at
007     *
008     *     http://www.apache.org/licenses/LICENSE-2.0
009     *
010     * Unless required by applicable law or agreed to in writing, software
011     * distributed under the License is distributed on an "AS IS" BASIS,
012     * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND,
013     * either express or implied. See the License for the specific language
014     * governing permissions and limitations under the License.
015     */
016    package org.opengion.hayabusa.mail;
017    
018    import java.util.ArrayList;
019    import java.util.HashMap;
020    import java.util.List;
021    import java.util.Map;
022    import java.util.Arrays;
023    
024    import org.opengion.fukurou.db.DBUtil;
025    import org.opengion.fukurou.mail.MailTX;
026    import org.opengion.fukurou.util.LogWriter;
027    import org.opengion.fukurou.util.StringUtil;
028    import org.opengion.hayabusa.common.HybsSystem;
029    
030    /**
031     * パッチによるメール送信の実?ラスです?
032     * 送信??モンはパラメータ??ブル(GE30)を監視して、新規???タが登録されたら?
033     * そ???タをパラメータとしてメール合?処?ソ?に渡して合?を行って送信します?
034     * ?に、??果を受取って、パラメータ??ブルの状況フラグを?信?送信エラーに更新します?
035     * エラーが発生した?合?エラー??ブルにエラーメ?ージを書き込みます?
036     *
037     * @og.group メールモジュール
038     *
039     * @version  4.0
040     * @author   Sen.Li
041     * @since    JDK1.6
042     */
043    public class MailManager_DB extends AbstractMailManager {
044            // 5.2.0.0 (2010/09/01) Ver4互換モード対?
045            private static final String H_TXT = HybsSystem.sysBool( "VER4_COMPATIBLE_MODE" ) ? "HEADER" : "H_TXT";
046            private static final String F_TXT = HybsSystem.sysBool( "VER4_COMPATIBLE_MODE" ) ? "FOOTER" : "F_TXT";
047    
048            // 5.2.0.0 (2010/09/01) Ver4互換モード対?
049            // 5.6.8.0 (2013/09/06) 送信日時対?
050    //      private static final String     selGE30 = "SELECT  UNIQ,PTN_ID,FROM_ID,TO_ID,CC_ID,BCC_ID,H_TXT,F_TXT"          // 5.0.3.0 (2009/11/04)
051            private static final String     selGE30 = "SELECT  UNIQ,PTN_ID,FROM_ID,TO_ID,CC_ID,BCC_ID,"+H_TXT+","+F_TXT
052                                                                                    +",PARAM0,PARAM1,PARAM2,PARAM3,PARAM4,PARAM5,PARAM6,PARAM7,PARAM8,PARAM9"
053                                                                                    +",ATTACH1,ATTACH2,ATTACH3,ATTACH4,ATTACH5"
054                                                                                    + " FROM GE30"
055                                                                                    + " WHERE SYSTEM_ID =? AND FGJ='1'"
056                                                                                    + " AND (SNDTIME IS NULL OR SNDTIME <= ? )";
057            private static final String     insGE36 = "INSERT INTO GE36(PARA_KEY,ERRMSG,DYSET,USRSET,PGUPD,SYSTEM_ID,FGJ)"
058                                                                                    + " VALUES(?,?,?,?,?,?,'1')";
059            private static final String     updGE30 = "UPDATE GE30 SET FGJ= ? WHERE UNIQ = ? ";
060            private static final String     SNED_OK = "2";
061            private static final String     SNED_NG = "8";
062            private static final int GE30_UNIQ              = 0 ;
063            private static final int GE30_PTN_ID    = 1 ;
064            private static final int GE30_FROM_ID   = 2 ;
065            private static final int GE30_TO_ID             = 3 ;
066            private static final int GE30_CC_ID             = 4 ;
067            private static final int GE30_BCC_ID    = 5 ;
068            private static final int GE30_H_TXT             = 6 ;           // 5.0.3.0 (2009/11/04) HEADER ?H_TXT
069            private static final int GE30_F_TXT             = 7 ;           // 5.0.3.0 (2009/11/04) FOOTER ?F_TXT
070            private static final int GE30_PARAM0    = 8 ;
071            private static final int GE30_PARAM1    = 9 ;
072            private static final int GE30_PARAM2    = 10 ;
073            private static final int GE30_PARAM3    = 11 ;
074            private static final int GE30_PARAM4    = 12 ;
075            private static final int GE30_PARAM5    = 13 ;
076            private static final int GE30_PARAM6    = 14 ;
077            private static final int GE30_PARAM7    = 15 ;
078            private static final int GE30_PARAM8    = 16 ;
079            private static final int GE30_PARAM9    = 17 ;
080            private static final int GE30_ATTACH1   = 18 ;
081            private static final int GE30_ATTACH2   = 19 ;
082            private static final int GE30_ATTACH3   = 20 ;
083            private static final int GE30_ATTACH4   = 21 ;
084            private static final int GE30_ATTACH5   = 22 ;
085            private static final int GE36_PARA_KEY  = 0 ;
086            private static final int GE36_ERRMSG    = 1 ;
087            private static final int GE36_DYSET             = 2 ;
088            private static final int GE36_USRSET    = 3 ;
089            private static final int GE36_PGUPD     = 4 ;
090            private static final int GE36_SYSTEM_ID = 5 ;
091            private final List<String>         errMsgList     = new ArrayList<String>();
092    
093            /**
094             * バッチより呼出のメインメソ?です?
095             * パラメータ??ブル(GE30)を監視します?
096             * 新規???タが登録されたら、メール?合?して送信を行います?
097             * エラーが発生した?合?エラー??ブルにエラーメ?ージを書き込みます?
098             *
099             * @og.rev 5.5.5.1 (2012/08/07) リソース系DBID 付け忘れ対?
100             * @og.rev 5.6.8.0 (2013/09/06) 送信日時対?
101             *
102             * @param systemId シス?ID
103             */
104            public void sendDBMail( final String systemId ){
105                    // パラメータ??ブルよりバッチでセ?した??タを取得します?
106    //              String[][] ge30datas = DBUtil.dbExecute( selGE30, new String[]{ systemId }, appInfo );
107    //              String[][] ge30datas = DBUtil.dbExecute( selGE30, new String[]{ systemId }, appInfo, DBID );            // 5.5.5.1 (2012/08/07)
108                    String[][] ge30datas = DBUtil.dbExecute( selGE30, new String[]{ systemId, HybsSystem.getDate( "yyyyMMddHHmmss" ) }, appInfo, DBID );            // 5.6.8.0 (2013/09/06) 
109                    int ge30Len = ge30datas.length;
110                    for( int i=0; i < ge30Len; i++ ) {
111                            String fgj = SNED_OK;
112                            try {
113                                    Map<String, String> initParam = makeParamMap( systemId, ge30datas[i] );
114                                    create( initParam );
115                                    send();                                                         // 合?されたメール?、宛?で送信処?行います?
116                                    errMsgList.addAll( getErrList() );
117                            }
118                            catch( RuntimeException rex ) {
119                                    fgj = SNED_NG;
120                                    errMsgList.add( "メール送信失敗しました。パラメータキー?? + ge30datas[i][GE30_UNIQ] + " " + rex.getMessage() );
121                            }
122                            finally {
123                                    commitParamTable( ge30datas[i][GE30_UNIQ], fgj );
124    //                              if ( errMsgList.size() > 0 ) {
125                                    if ( ! errMsgList.isEmpty() ) {
126                                            writeErrorTable( ge30datas[i][GE30_UNIQ], systemId, errMsgList );
127                                            errMsgList.clear();
128                                    }
129                            }
130                    }
131            }
132    
133            /**
134             * パラメータ??ブルに登録した??タをパラメータマップにセ?します?
135             *
136             * @param       systemId        シス?ID
137             * @param       ge30Data        パラメータ??ブルの??タ配?
138             *
139             * @return      ??タをセ?したマッ?
140             */
141            private Map<String, String> makeParamMap( final String systemId, final String[] ge30Data ){
142                    Map<String,String>   paramMap = null;
143                    if( ( ge30Data != null ) && ( ge30Data.length > 0 ) ) {
144                            paramMap = new HashMap<String,String>();
145                            paramMap.put( "SYSTEM_ID", systemId    );
146                            paramMap.put( "PARAKEY", ge30Data[GE30_UNIQ]    );
147                            paramMap.put( "PTN_ID" , ge30Data[GE30_PTN_ID]  );
148                            paramMap.put( "FROM"   , ge30Data[GE30_FROM_ID] );
149                            paramMap.put( "TO"     , ge30Data[GE30_TO_ID]   );
150                            paramMap.put( "CC"     , ge30Data[GE30_CC_ID]   );
151                            paramMap.put( "BCC"    , ge30Data[GE30_BCC_ID]  );
152                            paramMap.put( "H_TXT"  , ge30Data[GE30_H_TXT]   );                      // 5.0.3.0 (2009/11/04) HEADER ?H_TXT
153                            paramMap.put( "F_TXT"  , ge30Data[GE30_F_TXT]   );                      // 5.0.3.0 (2009/11/04) FOOTER ?F_TXT
154                            paramMap.put( "PARAM0" , ge30Data[GE30_PARAM0]  );
155                            paramMap.put( "PARAM1" , ge30Data[GE30_PARAM1]  );
156                            paramMap.put( "PARAM2" , ge30Data[GE30_PARAM2]  );
157                            paramMap.put( "PARAM3" , ge30Data[GE30_PARAM3]  );
158                            paramMap.put( "PARAM4" , ge30Data[GE30_PARAM4]  );
159                            paramMap.put( "PARAM5" , ge30Data[GE30_PARAM5]  );
160                            paramMap.put( "PARAM6" , ge30Data[GE30_PARAM6]  );
161                            paramMap.put( "PARAM7" , ge30Data[GE30_PARAM7]  );
162                            paramMap.put( "PARAM8" , ge30Data[GE30_PARAM8]  );
163                            paramMap.put( "PARAM9" , ge30Data[GE30_PARAM9]  );
164                            paramMap.put( "ATTACH1", ge30Data[GE30_ATTACH1] );
165                            paramMap.put( "ATTACH2", ge30Data[GE30_ATTACH2] );
166                            paramMap.put( "ATTACH3", ge30Data[GE30_ATTACH3] );
167                            paramMap.put( "ATTACH4", ge30Data[GE30_ATTACH4] );
168                            paramMap.put( "ATTACH5", ge30Data[GE30_ATTACH5] );
169                            paramMap.put( "DATE", HybsSystem.getDate("yyyy/MM/dd") );
170                            paramMap.put( "TIME", HybsSystem.getDate("HH:mm:ss") );
171                            paramMap.put( "LOGIN_USERID", "DAEMON" );
172                            paramMap.put( "LOGIN_USERNAME", "DAEMON" );
173                            paramMap.put( "PGID", "DAEMON" );
174                    }
175                    return paramMap;
176            }
177    
178            /**
179             * 送信後?パラメータ??ブルの状況フラグを更新します?
180             * 送信エラーなし?場合?フラグを?送信?2)’?エラーの場合?送信エラー(8)’に更新します?
181             *
182             * @og.rev 5.5.5.1 (2012/08/07) リソース系DBID 付け忘れ対?
183             *
184             * @param       uniq    ユニ?クキー
185             * @param       fgj             状況フラグ[2:送信?8:エラー]
186             */
187            private void commitParamTable( final String uniq, final String fgj ){
188                    String[] updGE30Args = { fgj, uniq };
189    //              DBUtil.dbExecute( updGE30, updGE30Args, appInfo );
190                    DBUtil.dbExecute( updGE30, updGE30Args, appInfo, DBID );                // 5.5.5.1 (2012/08/07)
191            }
192    
193            /**
194             * エラー??ブルにエラーメ?ージを登録します?
195             *
196             * @og.rev 4.4.0.1 (2009/08/08) メール送信追?
197             * @og.rev 5.5.5.1 (2012/08/07) リソース系DBID 付け忘れ対?
198             *
199             * @param       paraKey         パラメータキー(GE36.PARA_KEY)
200             * @param       systemId        シス?ID
201             * @param       emList          エラーメ?ージリス?
202             *
203             */
204    //      private void writeErrorTable( final String paraKey, final String systemId, final List<String> errMsgList ){
205            private void writeErrorTable( final String paraKey, final String systemId, final List<String> emList ){
206                    String[] insGE36Args = new String[6];
207                    insGE36Args[GE36_PARA_KEY]      = paraKey;
208                    insGE36Args[GE36_DYSET]         = HybsSystem.getDate( "yyyyMMddHHmmss" );
209                    insGE36Args[GE36_USRSET]        = "DAEMON";
210                    insGE36Args[GE36_PGUPD]         = "DAEMON";
211                    insGE36Args[GE36_SYSTEM_ID] = systemId;
212                    for( int i=0; i< emList.size(); i++ ){
213                            insGE36Args[GE36_ERRMSG] = trim( emList.get( i ), 4000);
214    //                      DBUtil.dbExecute( insGE36, insGE36Args,appInfo );
215                            DBUtil.dbExecute( insGE36, insGE36Args, appInfo, DBID );                // 5.5.5.1 (2012/08/07)
216                    }
217    
218                    sendMail( paraKey, systemId, emList ); // 4.4.0.1 (2009/08/08)
219            }
220    
221            /**
222             * エラー??のメール送信を行います?
223             * エラーメールは、シス?パラメータ の COMMON_MAIL_SERVER(メールサーバ?)と
224             * ERROR_MAIL_FROM_USER(エラーメール発信?と、ERROR_MAIL_TO_USERS(エラーメール受信?
225             * がすべて設定されて?場合に??信されます?
226             *
227             * @og.rev 4.4.0.1 (2009/08/08) 追?
228             * @og.rev 5.4.3.2 (2012/01/06) 認証対?
229             *
230             * @param       paraKey         メールキー
231             * @param       systemId        シス?ID
232             * @param       emList          エラーメ?ージリス?
233             */
234    //      private void sendMail( final String paraKey, final String systemId, final List<String> errMsgList ) {
235            private void sendMail( final String paraKey, final String systemId, final List<String> emList ) {
236    
237                    String   host = HybsSystem.sys( "COMMON_MAIL_SERVER" );
238                    String   from = HybsSystem.sys( "ERROR_MAIL_FROM_USER" );
239                    String   charset                = HybsSystem.sys( "MAIL_DEFAULT_CHARSET" );
240                    String   smtpPort               = HybsSystem.sys( "SMTP_PORT" );                                // 5.4.3.2 (2012/01/06)
241                    String   auth                   = HybsSystem.sys( "MAIL_SEND_AUTH" );                   // 5.4.3.2 (2012/01/06)
242                    String   authUser               = HybsSystem.sys( "MAIL_SEND_AUTH_USER" );              // 5.4.3.2 (2012/01/06)
243                    String   authPass               = HybsSystem.sys( "MAIL_SEND_AUTH_PASSWORD" );  // 5.4.3.2 (2012/01/06)
244    
245                    String[] to = StringUtil.csv2Array( HybsSystem.sys( "ERROR_MAIL_TO_USERS" ) );
246                    if( host != null && from != null && to.length > 0 ) {
247                            String subject = "SYSTEM_ID=[" + systemId + "] , PARA_KEY=[" + paraKey + "] , "
248                                                       + "DMN_HOST=[" + HybsSystem.HOST_NAME + "]" ;
249                            StringBuilder inErrMsg = new StringBuilder();
250                            inErrMsg.append( emList.size() + "件のエラーがありました? );
251                            inErrMsg.append( HybsSystem.CR );
252                            for( int i=0; i< emList.size(); i++ ){
253                                    inErrMsg.append( i+1 );
254                                    inErrMsg.append( "-----" );
255                                    inErrMsg.append( HybsSystem.CR );
256                                    inErrMsg.append( emList.get( i ) );
257                                    inErrMsg.append( HybsSystem.CR );
258                            }
259                            try {
260                                    //MailTX tx = new MailTX( host );
261                                    MailTX tx = new MailTX( host, charset, smtpPort, auth, authUser, authPass ); // 5.4.3.2
262                                    tx.setFrom( from );
263                                    tx.setTo( to );
264                                    tx.setSubject( "メールモジュール送信エラー?? + subject );
265                                    tx.setMessage( inErrMsg.toString() );
266                                    tx.sendmail();
267                            }
268                            catch( Throwable ex ) {
269                                    String errMsg = "エラー時メール送信に失敗しました? + HybsSystem.CR
270                                                            + " SUBJECT:" + subject                                 + HybsSystem.CR
271                                                            + " HOST:" + host                                               + HybsSystem.CR
272                                                            + " FROM:" + from                                               + HybsSystem.CR
273                                                            + " TO:"   + Arrays.toString( to )              + HybsSystem.CR
274                                                            + ex.getMessage();              // 5.1.8.0 (2010/07/01) errMsg 修正
275                                    LogWriter.log( errMsg );
276                                    LogWriter.log( ex );
277                            }
278                    }
279            }
280    }