/** * Created on 2003/10/27 * * To change the template for this generated file go to * Window>Preferences>Java>Code Generation>Code and Comments */ package jp.co.sint.beans.mallmgr; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.text.DecimalFormat; import java.text.NumberFormat; import java.text.ParseException; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Calendar; import java.util.Collection; import java.util.Date; import java.util.Hashtable; import javax.servlet.http.HttpServletRequest; import jp.co.sint.basic.SIBasic; import jp.co.sint.basic.SIRfmAnalyse; import jp.co.sint.config.SIConfig; import jp.co.sint.config.SIDBMultiConf; import jp.co.sint.database.SIDBAccessException; import jp.co.sint.database.SIDBUtil; import jp.co.sint.tools.SICheckDataConf; import jp.co.sint.tools.SICheckValid; import jp.co.sint.tools.SICustomError; import jp.co.sint.tools.SICustomErrors; import jp.co.sint.tools.SIDateTime; import jp.co.sint.tools.SIUtil; import jp.co.sint.tools.SIURLParameter;//7.1.1 ST0236 追加 import org.apache.log4j.Category; /** * @author arai * * To change the template for this generated type comment go to * Window>Preferences>Java>Code Generation>Code and Comments */ public class UIRfmOrderLogListCond extends SIBasic { //ログ用のインスタンスの生成 private static Category log = Category.getInstance(SIConfig.SILOG4J_WEBSHOP_CATEGORY_NAME); private String r1 = "60"; private String r2 = "180"; private String f1 = "1"; private String f2 = "10"; private String f3 = "5"; private String m1 = "1"; private String m2 = "10000"; private String m3 = "5000"; private String analyseMode = ""; private Hashtable hashParameters = new Hashtable(); private int countCustomerAll = 0; private long sumOfPrice = 0; private long sumCmdtyCount = 0;//全購入商品数 private long sumPurchaseCount = 0;//全購入回数 private String dispMode="0";//表示モード 0:データ 1:グラフ private static String RAFA = "rafa"; private static String RAFB = "rafb"; private static String RAFC = "rafc"; private static String RBFA = "rbfa"; private static String RBFB = "rbfb"; private static String RBFC = "rbfc"; private static String RCFA = "rcfa"; private static String RCFB = "rcfb"; private static String RCFC = "rcfc"; private static String[] RF = { RAFA, RAFB, RAFC, RBFA, RBFB, RBFC, RCFA, RCFB, RCFC }; private static String RAMA = "rama"; private static String RAMB = "ramb"; private static String RAMC = "ramc"; private static String RBMA = "rbma"; private static String RBMB = "rbmb"; private static String RBMC = "rbmc"; private static String RCMA = "rcma"; private static String RCMB = "rcmb"; private static String RCMC = "rcmc"; private static String[] RM = { RAMA, RAMB, RAMC, RBMA, RBMB, RBMC, RCMA, RCMB, RCMC }; private static String FAMA = "fama"; private static String FAMB = "famb"; private static String FAMC = "famc"; private static String FBMA = "fbma"; private static String FBMB = "fbmb"; private static String FBMC = "fbmc"; private static String FCMA = "fcma"; private static String FCMB = "fcmb"; private static String FCMC = "fcmc"; private static String[] FM = { FAMA, FAMB, FAMC, FBMA, FBMB, FBMC, FCMA, FCMB, FCMC }; public static String Mode_RF = "RF"; public static String Mode_RM = "RM"; public static String Mode_FM = "FM"; /** * 初期化処理 */ //7.1.1 ST0236 修正 ここから public void init(SIURLParameter lUrlParam){ this.setEncode(SIConfig.SIENCODE_SHIFT_JIS); this.setR1((String)lUrlParam.getParam("r1")); this.setR2((String)lUrlParam.getParam("r2")); this.setF1((String)lUrlParam.getParam("f1")); this.setF2((String)lUrlParam.getParam("f2")); this.setF3((String)lUrlParam.getParam("f3")); this.setM1((String)lUrlParam.getParam("m1")); this.setM2((String)lUrlParam.getParam("m2")); this.setM3((String)lUrlParam.getParam("m3")); this.setAnalyseMode((String)lUrlParam.getParam("analyseMode")); this.setDispMode((String)lUrlParam.getParam("dispModeTxt")); this.setActionNameTxt((String)lUrlParam.getParam("actionNameTxt")); this.setEditModeTxt((String)lUrlParam.getParam("editModeTxt")); } //7.1.1 ST0236 修正 ここまで /** * RF分析 入力値チェック * @param lRequest * @param lConnection * @return */ public boolean RF_validate(HttpServletRequest lRequest, Connection lConnection) { lRequest.removeAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY); SICustomErrors errors = new SICustomErrors(); //7.1.1 ST0162 修正 ここから SICheckValid.checkValid(errors, "RAの日数", this.getR1(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE + SICheckDataConf.SICHECK_DATA_DIGIT_TYPE); SICheckValid.checkValid(errors, "RAの日数", this.getR1(), SICheckDataConf.SICHECK_DATA_BYTE_LEN_WITHIN_TYPE, 4); SICheckValid.checkValid(errors, "RBの日数", this.getR2(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE + SICheckDataConf.SICHECK_DATA_DIGIT_TYPE); SICheckValid.checkValid(errors, "RBの日数", this.getR2(), SICheckDataConf.SICHECK_DATA_BYTE_LEN_WITHIN_TYPE, 4); //R-Check if (errors.isEmpty()) { if (Integer.parseInt(this.getR1()) >= Integer.parseInt(this.getR2())) { errors.addError(new SICustomError("input.data.rfm.r")); } } SICheckValid.checkValid(errors, "FAの期間", this.getF1(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE + SICheckDataConf.SICHECK_DATA_DIGIT_TYPE); SICheckValid.checkValid(errors, "FAの期間", this.getF1(), SICheckDataConf.SICHECK_DATA_BYTE_LEN_WITHIN_TYPE, 3); SICheckValid.checkValid(errors, "FAの回数", this.getF2(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE + SICheckDataConf.SICHECK_DATA_DIGIT_TYPE); SICheckValid.checkValid(errors, "FAの回数", this.getF2(), SICheckDataConf.SICHECK_DATA_BYTE_LEN_WITHIN_TYPE, 4); SICheckValid.checkValid(errors, "FBの回数", this.getF3(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE + SICheckDataConf.SICHECK_DATA_DIGIT_TYPE); SICheckValid.checkValid(errors, "FBの回数", this.getF3(), SICheckDataConf.SICHECK_DATA_BYTE_LEN_WITHIN_TYPE, 4); //7.1.1 ST0162 修正 ここまで //F-Check if (errors.isEmpty()) { if (Integer.parseInt(this.getF2()) <= Integer.parseInt(this.getF3())) { errors.addError(new SICustomError("input.data.rfm.f")); } } if (!errors.isEmpty()) { lRequest.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY, errors); } else { hashParameters.clear(); hashParameters.put("R1", this.getR1()); hashParameters.put("R2", this.getR2()); hashParameters.put("F1", this.getF1()); hashParameters.put("F2", this.getF2()); hashParameters.put("F3", this.getF3()); } return errors.isEmpty(); } /** * RM分析 入力値チェック * @param lRequest * @param lConnection * @return */ public boolean RM_validate(HttpServletRequest lRequest, Connection lConnection) { lRequest.removeAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY); SICustomErrors errors = new SICustomErrors(); //7.1.1 ST0162 修正 ここから SICheckValid.checkValid(errors, "RAの日数", this.getR1(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE + SICheckDataConf.SICHECK_DATA_DIGIT_TYPE); SICheckValid.checkValid(errors, "RAの日数", this.getR1(), SICheckDataConf.SICHECK_DATA_BYTE_LEN_WITHIN_TYPE, 4); SICheckValid.checkValid(errors, "RBの日数", this.getR2(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE + SICheckDataConf.SICHECK_DATA_DIGIT_TYPE); SICheckValid.checkValid(errors, "RBの日数", this.getR2(), SICheckDataConf.SICHECK_DATA_BYTE_LEN_WITHIN_TYPE, 4); //R-Check if (errors.isEmpty()) { if (Integer.parseInt(this.getR1()) >= Integer.parseInt(this.getR2())) { errors.addError(new SICustomError("input.data.rfm.r")); } } SICheckValid.checkValid(errors, "MAの期間", this.getM1(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE + SICheckDataConf.SICHECK_DATA_DIGIT_TYPE); SICheckValid.checkValid(errors, "MAの期間", this.getM1(), SICheckDataConf.SICHECK_DATA_BYTE_LEN_WITHIN_TYPE, 3); SICheckValid.checkValid(errors, "MAの金額", this.getM2(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE + SICheckDataConf.SICHECK_DATA_DIGIT_TYPE); SICheckValid.checkValid(errors, "MAの金額", this.getM2(), SICheckDataConf.SICHECK_DATA_BYTE_LEN_WITHIN_TYPE, 8); SICheckValid.checkValid(errors, "MBの金額", this.getM3(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE + SICheckDataConf.SICHECK_DATA_DIGIT_TYPE); SICheckValid.checkValid(errors, "MBの金額", this.getM3(), SICheckDataConf.SICHECK_DATA_BYTE_LEN_WITHIN_TYPE, 8); //7.1.1 ST0162 修正 ここまで //M-Check if (errors.isEmpty()) { if (Integer.parseInt(this.getM2()) <= Integer.parseInt(this.getM3())) { errors.addError(new SICustomError("input.data.rfm.m")); } } if (!errors.isEmpty()) { lRequest.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY, errors); } else { hashParameters.clear(); hashParameters.put("R1", this.getR1()); hashParameters.put("R2", this.getR2()); hashParameters.put("M1", this.getM1()); hashParameters.put("M2", this.getM2()); hashParameters.put("M3", this.getM3()); } return errors.isEmpty(); } /** * FM分析 入力値チェック * @param lRequest * @param lConnection * @return */ public boolean FM_validate(HttpServletRequest lRequest, Connection lConnection) { lRequest.removeAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY); SICustomErrors errors = new SICustomErrors(); //7.1.1 ST0162 修正 ここから SICheckValid.checkValid(errors, "FAの期間", this.getF1(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE + SICheckDataConf.SICHECK_DATA_DIGIT_TYPE); SICheckValid.checkValid(errors, "FAの期間", this.getF1(), SICheckDataConf.SICHECK_DATA_BYTE_LEN_WITHIN_TYPE, 3); SICheckValid.checkValid(errors, "FAの回数", this.getF2(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE + SICheckDataConf.SICHECK_DATA_DIGIT_TYPE); SICheckValid.checkValid(errors, "FAの回数", this.getF2(), SICheckDataConf.SICHECK_DATA_BYTE_LEN_WITHIN_TYPE, 4); SICheckValid.checkValid(errors, "FBの回数", this.getF3(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE + SICheckDataConf.SICHECK_DATA_DIGIT_TYPE); SICheckValid.checkValid(errors, "FBの回数", this.getF3(), SICheckDataConf.SICHECK_DATA_BYTE_LEN_WITHIN_TYPE, 4); //F-Check if (errors.isEmpty()) { if (Integer.parseInt(this.getF2()) <= Integer.parseInt(this.getF3())) { errors.addError(new SICustomError("input.data.rfm.f")); } } SICheckValid.checkValid(errors, "MAの期間", this.getM1(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE + SICheckDataConf.SICHECK_DATA_DIGIT_TYPE); SICheckValid.checkValid(errors, "MAの期間", this.getM1(), SICheckDataConf.SICHECK_DATA_BYTE_LEN_WITHIN_TYPE, 3); SICheckValid.checkValid(errors, "MAの金額", this.getM2(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE + SICheckDataConf.SICHECK_DATA_DIGIT_TYPE); SICheckValid.checkValid(errors, "MAの金額", this.getM2(), SICheckDataConf.SICHECK_DATA_BYTE_LEN_WITHIN_TYPE, 8); SICheckValid.checkValid(errors, "MBの金額", this.getM3(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE + SICheckDataConf.SICHECK_DATA_DIGIT_TYPE); SICheckValid.checkValid(errors, "MBの金額", this.getM3(), SICheckDataConf.SICHECK_DATA_BYTE_LEN_WITHIN_TYPE, 8); //7.1.1 ST0162 修正 ここまで //M-Check if (errors.isEmpty()) { if (Integer.parseInt(this.getM2()) <= Integer.parseInt(this.getM3())) { errors.addError(new SICustomError("input.data.rfm.m")); } } if (!errors.isEmpty()) { lRequest.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY, errors); } else { hashParameters.clear(); hashParameters.put("F1", this.getF1()); hashParameters.put("F2", this.getF2()); hashParameters.put("F3", this.getF3()); hashParameters.put("M1", this.getM1()); hashParameters.put("M2", this.getM2()); hashParameters.put("M3", this.getM3()); } return errors.isEmpty(); } /** * RF分析 SQL作成 * @param lItemName * @return */ public String RF_CaluculateCondSQL(String lItemName){ StringBuffer lSqlBuf=new StringBuffer(); lSqlBuf.append(" FROM ("); lSqlBuf.append(" SELECT a.custcode,b.countBuy,b.amount,b.sumofprice "); lSqlBuf.append(" FROM ( "); lSqlBuf.append(" SELECT custCode FROM rfmanalysevw GROUP BY custCode "); if (RAFA.equals(lItemName) || RAFB.equals(lItemName) || RAFC.equals(lItemName)) { lSqlBuf.append(" HAVING max(initdatetime)::text >= ?"); } else if (RBFA.equals(lItemName) || RBFB.equals(lItemName) || RBFC.equals(lItemName)) { lSqlBuf.append(" HAVING max(initdatetime)::text < ? and max(initdatetime)::text >= ?"); } else if (RCFA.equals(lItemName) || RCFB.equals(lItemName) || RCFC.equals(lItemName)) { lSqlBuf.append(" HAVING max(initdatetime)::text < ?"); } lSqlBuf.append(") a"); lSqlBuf.append(",(SELECT custcode,count(custcode) AS countBuy,sum(sumofprice) AS sumofprice,sum(amount) AS amount FROM rfmanalysevw WHERE 1=1 "); if (RAFA.equals(lItemName) || RBFA.equals(lItemName) || RCFA.equals(lItemName)) { //RA-FA lSqlBuf.append(" AND initdatetime::text >= ?"); lSqlBuf.append(" GROUP BY custcode "); lSqlBuf.append(" HAVING count(custcode) >= ?"); } else if (RAFB.equals(lItemName) || RBFB.equals(lItemName) || RCFB.equals(lItemName)) { //RA-FB lSqlBuf.append(" AND initdatetime::text >= ?"); lSqlBuf.append(" GROUP BY custcode "); lSqlBuf.append(" HAVING count(custcode) < ? and count(custcode) >= ?"); } else if (RAFC.equals(lItemName) || RBFC.equals(lItemName) || RCFC.equals(lItemName)) { //RA-FC lSqlBuf.append(" AND custCode NOT IN (SELECT custCode FROM rfmanalysevw WHERE initdatetime::text >= ? "); lSqlBuf.append(" GROUP BY custcode HAVING count(custcode) >= ?) GROUP BY custCode "); } lSqlBuf.append(") b "); lSqlBuf.append("WHERE a.custCode = b.custCode) x "); return lSqlBuf.toString(); } /** * RF分析 SQL作成 * @param lItemName * @return */ public String RF_CaluculateSQL(String lItemName){ StringBuffer lSqlBuf=new StringBuffer(); lSqlBuf.append(" SELECT count(custcode) AS CountOfCustomer"); lSqlBuf.append(",avg(countBuy) AS avgPurchaseCount"); lSqlBuf.append(",sum(countBuy) AS sumPurchaseCount"); lSqlBuf.append(",avg(amount) AS avgCmdtyCount"); lSqlBuf.append(",sum(amount) AS sumCmdtyCount "); lSqlBuf.append(RF_CaluculateCondSQL(lItemName)); return lSqlBuf.toString(); } /** * RF分析 結果取得 * @param lConnection * @return * @throws SIDBAccessException */ public Collection RF_Caluculate(Connection lConnection) throws SIDBAccessException { PreparedStatement preparedStatement = null; ResultSet lResultSet = null; SIRfmAnalyse lrfmanalyse = new SIRfmAnalyse(); Collection lrfColl = new ArrayList(); java.sql.Date lDate; String R1 = ""; String R2 = ""; String F1 = ""; int F2 = 0; int F3 = 0; NumberFormat nf = new DecimalFormat("0.0"); for (int i = 0; i < RF.length; i++) { String arg1 = RF[i]; try { log.debug("RF_CaluculateSQL("+arg1+")="+RF_CaluculateSQL(arg1)); preparedStatement = lConnection.prepareStatement(RF_CaluculateSQL(arg1)); R1 = getDayMinus((String) hashParameters.get("R1")); R2 = getDayMinus((String) hashParameters.get("R2")); F1 = getMonthsMinus((String) hashParameters.get("F1")); F2 = Integer.parseInt((String) hashParameters.get("F2")); F3 = Integer.parseInt((String) hashParameters.get("F3")); if (RAFA.equals(arg1)) { //RA-FA if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){ preparedStatement.setString(1, R1); preparedStatement.setString(2, F1); }else{ lDate=new java.sql.Date(new SIDateTime(R1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(1,lDate); lDate=new java.sql.Date(new SIDateTime(F1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(2,lDate); } preparedStatement.setInt(3, F2); } else if (RAFB.equals(arg1)) { //RA-FB if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){ preparedStatement.setString(1, R1); preparedStatement.setString(2, F1); }else{ lDate=new java.sql.Date(new SIDateTime(R1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(1,lDate); lDate=new java.sql.Date(new SIDateTime(F1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(2,lDate); } preparedStatement.setInt(3, F2); preparedStatement.setInt(4, F3); } else if (RAFC.equals(arg1)) { //RA-FC if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){ preparedStatement.setString(1, R1); preparedStatement.setString(2, F1); }else{ lDate=new java.sql.Date(new SIDateTime(R1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(1,lDate); lDate=new java.sql.Date(new SIDateTime(F1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(2,lDate); } preparedStatement.setInt(3, F3); } else if (RBFA.equals(arg1)) { if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){ preparedStatement.setString(1, R1); preparedStatement.setString(2, R2); preparedStatement.setString(3, F1); }else{ lDate=new java.sql.Date(new SIDateTime(R1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(1,lDate); lDate=new java.sql.Date(new SIDateTime(R2,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(2,lDate); lDate=new java.sql.Date(new SIDateTime(F1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(3,lDate); } preparedStatement.setInt(4, F2); } else if (RBFB.equals(arg1)) { //RB-FB if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){ preparedStatement.setString(1, R1); preparedStatement.setString(2, R2); preparedStatement.setString(3, F1); }else{ lDate=new java.sql.Date(new SIDateTime(R1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(1,lDate); lDate=new java.sql.Date(new SIDateTime(R2,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(2,lDate); lDate=new java.sql.Date(new SIDateTime(F1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(3,lDate); } preparedStatement.setInt(4, F2); preparedStatement.setInt(5, F3); } else if (RBFC.equals(arg1)) { //RB-FC if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){ preparedStatement.setString(1, R1); preparedStatement.setString(2, R2); preparedStatement.setString(3, F1); }else{ lDate=new java.sql.Date(new SIDateTime(R1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(1,lDate); lDate=new java.sql.Date(new SIDateTime(R2,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(2,lDate); lDate=new java.sql.Date(new SIDateTime(F1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(3,lDate); } preparedStatement.setInt(4, F3); } else if (RCFA.equals(arg1)) { //RC-FA if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){ preparedStatement.setString(1, R2); preparedStatement.setString(2, F1); }else{ lDate=new java.sql.Date(new SIDateTime(R2,SIConfig.SIDATE_FORMAT).getTime());//7.1.1 ST0241 修正 preparedStatement.setDate(1,lDate); lDate=new java.sql.Date(new SIDateTime(F1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(2,lDate); } preparedStatement.setInt(3, F2); } else if (RCFB.equals(arg1)) { //RC-FB if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){ preparedStatement.setString(1, R2); preparedStatement.setString(2, F1); }else{ lDate=new java.sql.Date(new SIDateTime(R2,SIConfig.SIDATE_FORMAT).getTime());//7.1.1 ST0241 修正 preparedStatement.setDate(1,lDate); lDate=new java.sql.Date(new SIDateTime(F1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(2,lDate); } preparedStatement.setInt(3, F2); preparedStatement.setInt(4, F3); } else if (RCFC.equals(arg1)) { //RC-FC if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){ preparedStatement.setString(1, R2); preparedStatement.setString(2, F1); }else{ lDate=new java.sql.Date(new SIDateTime(R2,SIConfig.SIDATE_FORMAT).getTime());//7.1.1 ST0241 修正 preparedStatement.setDate(1,lDate); lDate=new java.sql.Date(new SIDateTime(F1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(2,lDate); } preparedStatement.setInt(3, F3); } lResultSet = preparedStatement.executeQuery(); if (lResultSet.next()) { lrfmanalyse = new SIRfmAnalyse(); lrfmanalyse.setEncode(SIConfig.SIENCODE_NONE); lrfmanalyse.setCountOfCustomer(lResultSet.getString("countOfCustomer")); lrfmanalyse.setAvgCmdtyCount(lResultSet.getString("avgCmdtyCount")); lrfmanalyse.setAvgCmdtyCount(nf.format(Float.valueOf(lrfmanalyse.getAvgCmdtyCount()))); lrfmanalyse.setSumCmdtyCount(lResultSet.getString("sumCmdtyCount")); lrfmanalyse.setAvgPurchaseCount(lResultSet.getString("avgPurchaseCount")); lrfmanalyse.setAvgPurchaseCount(nf.format(Float.valueOf(lrfmanalyse.getAvgPurchaseCount()))); lrfmanalyse.setSumPurchaseCount(lResultSet.getString("sumPurchaseCount")); lrfColl.add(lrfmanalyse); } } catch (Exception ex) { ex.printStackTrace(); throw new SIDBAccessException(ex); } finally { SIDBUtil.close(lResultSet, preparedStatement); } } return lrfColl; } /** * RM分析 SQL作成 * @param lItemName * @return */ public String RM_CaluculateCondSQL(String lItemName){ StringBuffer lSqlBuf=new StringBuffer(); lSqlBuf.append(" FROM "); lSqlBuf.append(" (SELECT a.custcode,b.countBuy,b.amount,b.sumofprice "); lSqlBuf.append(" FROM "); lSqlBuf.append(" (SELECT custCode FROM rfmanalysevw GROUP BY custCode "); if (RAMA.equals(lItemName) || RAMB.equals(lItemName) || RAMC.equals(lItemName)) { lSqlBuf.append(" HAVING max(initdatetime)::text >= ?"); } else if (RBMA.equals(lItemName) || RBMB.equals(lItemName) || RBMC.equals(lItemName)) { lSqlBuf.append(" HAVING max(initdatetime)::text < ? AND max(initdatetime)::text >= ?"); } else if (RCMA.equals(lItemName) || RCMB.equals(lItemName) || RCMC.equals(lItemName)) { lSqlBuf.append(" HAVING max(initdatetime)::text < ?"); } lSqlBuf.append(") a,( "); //RM Sum lSqlBuf.append(" SELECT custcode"); lSqlBuf.append(",count(custcode) AS countBuy"); lSqlBuf.append(",sum(sumofprice) AS sumofprice "); lSqlBuf.append(",sum(amount) AS amount "); lSqlBuf.append("FROM rfmanalysevw WHERE 1=1 "); if (RAMA.equals(lItemName) || RBMA.equals(lItemName) || RCMA.equals(lItemName)) { //RA-MA lSqlBuf.append(" AND initdatetime::text >= ?"); lSqlBuf.append(" GROUP BY custcode "); lSqlBuf.append(" HAVING sum(sumofprice) >= ?"); } else if (RAMB.equals(lItemName) || RBMB.equals(lItemName) || RCMB.equals(lItemName)) { //RA-MB lSqlBuf.append(" AND initdatetime::text >= ?"); lSqlBuf.append(" GROUP BY custcode "); lSqlBuf.append(" HAVING sum(sumofprice) < ? AND sum(sumofprice) >= ?"); } else if (RAMC.equals(lItemName) || RBMC.equals(lItemName) || RCMC.equals(lItemName)) { //RA-MC lSqlBuf.append(" AND custCode NOT IN (SELECT custCode FROM rfmanalysevw WHERE initdatetime::text >= ? "); lSqlBuf.append(" GROUP BY custcode HAVING sum(sumofprice) >= ?) GROUP BY custCode "); } lSqlBuf.append(" ) b "); lSqlBuf.append(" WHERE a.custCode = b.custCode) x "); return lSqlBuf.toString(); } /** * RM分析 SQL作成 * @param lConnection * @param lItemName * @return * @throws SIDBAccessException */ public String RM_CaluculateSQL(Connection lConnection,String lItemName)throws SIDBAccessException{ StringBuffer lSqlBuf=new StringBuffer(); lSqlBuf.append(" SELECT count(custcode) AS CountOfCustomer "); if (RAMC.equals(lItemName) || RBMC.equals(lItemName) || RCMC.equals(lItemName)) { lSqlBuf.append("," + this.getMinusSumLeft_RM(lConnection, lItemName) + " - " + this.getMinusSumRight_RM(lConnection, lItemName) + " as sumPurchaseMoney"); } else { lSqlBuf.append(",sum(sumofprice) AS sumPurchaseMoney "); } lSqlBuf.append(RM_CaluculateCondSQL(lItemName)); return lSqlBuf.toString(); } /** * RM分析 結果取得 * @param lConnection * @return * @throws SIDBAccessException */ public Collection RM_Caluculate(Connection lConnection) throws SIDBAccessException { PreparedStatement preparedStatement = null; ResultSet lResultSet = null; SIRfmAnalyse lrfmanalyse = new SIRfmAnalyse(); Collection lrmColl = new ArrayList(); java.sql.Date lDate; String R1 = ""; String R2 = ""; String M1 = ""; int M2 = 0; int M3 = 0; for (int i = 0; i < RM.length; i++) { String arg1 = RM[i]; try { log.debug("RM_Caluculate:RM_CaluculateSQL(lConnection,"+arg1+")="+RM_CaluculateSQL(lConnection,arg1)); preparedStatement = lConnection.prepareStatement(RM_CaluculateSQL(lConnection,arg1)); R1 = getDayMinus((String) hashParameters.get("R1")); R2 = getDayMinus((String) hashParameters.get("R2")); M1 = getMonthsMinus((String) hashParameters.get("M1")); M2 = Integer.parseInt((String) hashParameters.get("M2")); M3 = Integer.parseInt((String) hashParameters.get("M3")); if (RAMA.equals(arg1)) { //RA-MA if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){ preparedStatement.setString(1, R1); preparedStatement.setString(2, M1); }else{ lDate=new java.sql.Date(new SIDateTime(R1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(1, lDate); lDate=new java.sql.Date(new SIDateTime(M1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(2, lDate); } preparedStatement.setInt(3, M2); } else if (RAMB.equals(arg1)) { //RA-MB if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){ preparedStatement.setString(1, R1); preparedStatement.setString(2, M1); }else{ lDate=new java.sql.Date(new SIDateTime(R1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(1, lDate); lDate=new java.sql.Date(new SIDateTime(M1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(2, lDate); } preparedStatement.setInt(3, M2); preparedStatement.setInt(4, M3); } else if (RAMC.equals(arg1)) { //RA-MC if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){ preparedStatement.setString(1, R1); preparedStatement.setString(2, M1); }else{ lDate=new java.sql.Date(new SIDateTime(R1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(1, lDate); lDate=new java.sql.Date(new SIDateTime(M1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(2, lDate); } preparedStatement.setInt(3, M3); } else if (RBMA.equals(arg1)) { //RB-MA if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){ preparedStatement.setString(1, R1); preparedStatement.setString(2, R2); preparedStatement.setString(3, M1); }else{ lDate=new java.sql.Date(new SIDateTime(R1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(1, lDate); lDate=new java.sql.Date(new SIDateTime(R2,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(2, lDate); lDate=new java.sql.Date(new SIDateTime(M1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(3, lDate); } preparedStatement.setInt(4, M2); } else if (RBMB.equals(arg1)) { //RB-MB if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){ preparedStatement.setString(1, R1); preparedStatement.setString(2, R2); preparedStatement.setString(3, M1); }else{ lDate=new java.sql.Date(new SIDateTime(R1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(1, lDate); lDate=new java.sql.Date(new SIDateTime(R2,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(2, lDate); lDate=new java.sql.Date(new SIDateTime(M1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(3, lDate); } preparedStatement.setInt(4, M2); preparedStatement.setInt(5, M3); } else if (RBMC.equals(arg1)) { //RB-MC if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){ preparedStatement.setString(1, R1); preparedStatement.setString(2, R2); preparedStatement.setString(3, M1); }else{ lDate=new java.sql.Date(new SIDateTime(R1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(1, lDate); lDate=new java.sql.Date(new SIDateTime(R2,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(2, lDate); lDate=new java.sql.Date(new SIDateTime(M1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(3, lDate); } preparedStatement.setInt(4, M3); } else if (RCMA.equals(arg1)) { //RC-MA if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){ preparedStatement.setString(1, R2); preparedStatement.setString(2, M1); }else{ lDate=new java.sql.Date(new SIDateTime(R2,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(1, lDate); lDate=new java.sql.Date(new SIDateTime(M1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(2, lDate); } preparedStatement.setInt(3, M2); } else if (RCMB.equals(arg1)) { //RC-MB if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){ preparedStatement.setString(1, R2); preparedStatement.setString(2, M1); }else{ lDate=new java.sql.Date(new SIDateTime(R2,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(1, lDate); lDate=new java.sql.Date(new SIDateTime(M1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(2, lDate); } preparedStatement.setInt(3, M2); preparedStatement.setInt(4, M3); } else if (RCMC.equals(arg1)) { //RC-MC if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){ preparedStatement.setString(1, R2); preparedStatement.setString(2, M1); }else{ lDate=new java.sql.Date(new SIDateTime(R2,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(1, lDate); lDate=new java.sql.Date(new SIDateTime(M1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(2, lDate); } preparedStatement.setInt(3, M3); } lResultSet = preparedStatement.executeQuery(); if (lResultSet.next()) { lrfmanalyse = new SIRfmAnalyse(); lrfmanalyse.setEncode(SIConfig.SIENCODE_NONE); lrfmanalyse.setCountOfCustomer(lResultSet.getString("countOfCustomer")); lrfmanalyse.setSumPurchaseMoney(lResultSet.getString("sumPurchaseMoney")); lrmColl.add(lrfmanalyse); } } catch (Exception ex) { ex.printStackTrace(); throw new SIDBAccessException(ex); } finally { SIDBUtil.close(lResultSet, preparedStatement); } } return lrmColl; } /** * RM分析 * @param lConnection * @param arg1 * @return * @throws SIDBAccessException */ public String getMinusSumLeft_RM(Connection lConnection, String arg1) throws SIDBAccessException { PreparedStatement preparedStatement = null; ResultSet lResultSet = null; StringBuffer lSqlBuf = new StringBuffer(); java.sql.Date lDate; String R1 = ""; String R2 = ""; String M1 = ""; int M2 = 0; int M3 = 0; String strSumOfPrice = ""; R1 = getDayMinus((String) hashParameters.get("R1")); R2 = getDayMinus((String) hashParameters.get("R2")); M1 = getMonthsMinus((String) hashParameters.get("M1")); M2 = Integer.parseInt((String) hashParameters.get("M2")); M3 = Integer.parseInt((String) hashParameters.get("M3")); lSqlBuf.append(" SELECT sum(sumofprice) as sumofprice "); lSqlBuf.append(" FROM (SELECT custCode FROM rfmanalysevw GROUP BY custCode "); if (RAMC.equals(arg1)) { lSqlBuf.append(" HAVING max(initdatetime)::text >= ?"); } else if (RBMC.equals(arg1)) { lSqlBuf.append(" HAVING max(initdatetime)::text < ? AND max(initdatetime)::text >= ?"); } else if (RCMC.equals(arg1)) { lSqlBuf.append(" HAVING max(initdatetime)::text < ?"); } lSqlBuf.append(" ) a, "); lSqlBuf.append("(SELECT custcode,sum(sumofprice) AS sumofprice "); lSqlBuf.append(" FROM rfmanalysevw "); lSqlBuf.append(" WHERE custCode NOT IN (SELECT custCode FROM rfmanalysevw WHERE initdatetime::text >= ? "); lSqlBuf.append(" GROUP BY custcode HAVING sum(sumofprice) >= ?) GROUP BY custCode "); lSqlBuf.append(" ) b "); lSqlBuf.append(" WHERE a.custcode = b.custcode "); try { log.debug("getMinusSumLeft_RM:arg1="+arg1+",lSqlBuf="+lSqlBuf.toString()); preparedStatement = lConnection.prepareStatement(lSqlBuf.toString()); if (RAMC.equals(arg1)) { if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){ preparedStatement.setString(1, R1); preparedStatement.setString(2, M1); }else{ lDate=new java.sql.Date(new SIDateTime(R1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(1, lDate); lDate=new java.sql.Date(new SIDateTime(M1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(2, lDate); } preparedStatement.setInt(3, M3); } else if (RBMC.equals(arg1)) { if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){ preparedStatement.setString(1, R1); preparedStatement.setString(2, R2); preparedStatement.setString(3, M1); }else{ lDate=new java.sql.Date(new SIDateTime(R1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(1, lDate); lDate=new java.sql.Date(new SIDateTime(R2,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(2, lDate); lDate=new java.sql.Date(new SIDateTime(M1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(3, lDate); } preparedStatement.setInt(4, M3); } else if (RCMC.equals(arg1)) { if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){ preparedStatement.setString(1, R2); preparedStatement.setString(2, M1); }else{ lDate=new java.sql.Date(new SIDateTime(R2,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(1, lDate); lDate=new java.sql.Date(new SIDateTime(M1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(2, lDate); } preparedStatement.setInt(3, M3); } lResultSet = preparedStatement.executeQuery(); if (lResultSet.next()) { if (SIUtil.isNotNull(lResultSet.getString("sumofprice"))) { strSumOfPrice = lResultSet.getString("sumofprice"); } else { strSumOfPrice = "0"; } } else { strSumOfPrice = "0"; } } catch (Exception ex) { ex.printStackTrace(); throw new SIDBAccessException(ex); } finally { SIDBUtil.close(lResultSet, preparedStatement); } return strSumOfPrice; } /** * RM分析 * @param lConnection * @param arg1 * @return * @throws SIDBAccessException */ public String getMinusSumRight_RM(Connection lConnection, String arg1) throws SIDBAccessException { PreparedStatement preparedStatement = null; ResultSet lResultSet = null; StringBuffer lSqlBuf = new StringBuffer(); java.sql.Date lDate; String R1 = ""; String R2 = ""; String M1 = ""; int M2 = 0; int M3 = 0; String strSumOfPrice = ""; R1 = getDayMinus((String) hashParameters.get("R1")); R2 = getDayMinus((String) hashParameters.get("R2")); M1 = getMonthsMinus((String) hashParameters.get("M1")); M2 = Integer.parseInt((String) hashParameters.get("M2")); M3 = Integer.parseInt((String) hashParameters.get("M3")); log.debug("R1="+R1+",R2="+R2+",M1="+M1+",M2="+M2+",M3="+M3); lSqlBuf.append(" SELECT sum(sumofprice) AS sumofprice "); lSqlBuf.append(" FROM (SELECT custCode FROM rfmanalysevw GROUP BY custCode "); if (RAMC.equals(arg1)) { lSqlBuf.append(" HAVING max(initdatetime)::text >= ?"); } else if (RBMC.equals(arg1)) { lSqlBuf.append(" HAVING max(initdatetime)::text < ? AND max(initdatetime)::text >= ?"); } else if (RCMC.equals(arg1)) { lSqlBuf.append(" HAVING max(initdatetime)::text < ?"); } lSqlBuf.append(" ) a, "); lSqlBuf.append("(SELECT custcode,sum(sumofprice) AS sumofprice "); lSqlBuf.append(" FROM rfmanalysevw "); lSqlBuf.append(" WHERE initdatetime::text < ?"); lSqlBuf.append(" AND custCode NOT IN (SELECT custCode FROM rfmanalysevw WHERE initdatetime::text >= ? "); lSqlBuf.append(" GROUP BY custcode HAVING sum(sumofprice) >= ?) GROUP BY custCode "); lSqlBuf.append(" ) b "); lSqlBuf.append(" WHERE a.custcode = b.custcode "); try { log.debug("getMinusSumRight_RM:arg1="+arg1+",lSqlBuf="+lSqlBuf.toString()); preparedStatement = lConnection.prepareStatement(lSqlBuf.toString()); if (RAMC.equals(arg1)) { if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){ preparedStatement.setString(1, R1); preparedStatement.setString(2, M1); preparedStatement.setString(3, M1); }else{ lDate=new java.sql.Date(new SIDateTime(R1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(1, lDate); lDate=new java.sql.Date(new SIDateTime(M1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(2, lDate); lDate=new java.sql.Date(new SIDateTime(M1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(3, lDate); } preparedStatement.setInt(4, M3); } else if (RBMC.equals(arg1)) { if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){ preparedStatement.setString(1, R1); preparedStatement.setString(2, R2); preparedStatement.setString(3, M1); preparedStatement.setString(4, M1); }else{ lDate=new java.sql.Date(new SIDateTime(R1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(1, lDate); lDate=new java.sql.Date(new SIDateTime(R2,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(2, lDate); lDate=new java.sql.Date(new SIDateTime(M1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(3, lDate); lDate=new java.sql.Date(new SIDateTime(M1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(4, lDate); } preparedStatement.setInt(5, M3); } else if (RCMC.equals(arg1)) { if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){ preparedStatement.setString(1, R2); preparedStatement.setString(2, M1); preparedStatement.setString(3, M1); }else{ lDate=new java.sql.Date(new SIDateTime(R2,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(1, lDate); lDate=new java.sql.Date(new SIDateTime(M1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(2, lDate); lDate=new java.sql.Date(new SIDateTime(M1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(3, lDate); } preparedStatement.setInt(4, M3); } lResultSet = preparedStatement.executeQuery(); if (lResultSet.next()) { if (SIUtil.isNotNull(lResultSet.getString("sumofprice"))) { strSumOfPrice = lResultSet.getString("sumofprice"); } else { strSumOfPrice = "0"; } } else { strSumOfPrice = "0"; } } catch (Exception ex) { ex.printStackTrace(); throw new SIDBAccessException(ex); } finally { SIDBUtil.close(lResultSet, preparedStatement); } return strSumOfPrice; } /** * FM分析 SQL作成 * @param lItemName * @return */ public String FM_CaluculateCondSQL(String lItemName){ StringBuffer lSqlBuf=new StringBuffer(); lSqlBuf.append(" FROM "); lSqlBuf.append(" (SELECT a.custcode,b.countBuy,b.amount,b.sumofprice "); lSqlBuf.append(" FROM "); lSqlBuf.append(" (SELECT custcode,count(custcode) AS countBuy,sum(sumofprice) AS sumofprice,sum(amount) AS amount "); lSqlBuf.append(" FROM rfmanalysevw WHERE 1=1 "); if (FAMA.equals(lItemName) || FAMB.equals(lItemName) || FAMC.equals(lItemName)) { lSqlBuf.append(" AND initdatetime::text >= ?"); lSqlBuf.append(" GROUP BY custcode "); lSqlBuf.append(" HAVING count(custcode) >= ?"); } else if (FBMA.equals(lItemName) || FBMB.equals(lItemName) || FBMC.equals(lItemName)) { lSqlBuf.append(" AND initdatetime::text >= ?"); lSqlBuf.append(" GROUP BY custcode "); lSqlBuf.append(" HAVING count(custcode) < ? AND count(custcode) >= ?"); } else if (FCMA.equals(lItemName) || FCMB.equals(lItemName) || FCMC.equals(lItemName)) { lSqlBuf.append(" AND custCode NOT IN (SELECT custCode FROM rfmanalysevw WHERE initdatetime::text >= ? "); lSqlBuf.append(" GROUP BY custcode HAVING count(custcode) >= ?) GROUP BY custCode "); } //FM Sum lSqlBuf.append(" ) a, "); lSqlBuf.append("(SELECT custcode,count(custcode) AS countBuy "); lSqlBuf.append(",sum(sumofprice) AS sumofprice"); lSqlBuf.append(",sum(amount) AS amount "); lSqlBuf.append("FROM rfmanalysevw "); lSqlBuf.append("WHERE 1=1 "); if (FAMA.equals(lItemName) || FBMA.equals(lItemName) || FCMA.equals(lItemName)) { lSqlBuf.append(" AND initdatetime::text >= ?"); lSqlBuf.append(" GROUP BY custcode "); lSqlBuf.append(" HAVING sum(sumofprice) >= ?"); } else if (FAMB.equals(lItemName) || FBMB.equals(lItemName) || FCMB.equals(lItemName)) { lSqlBuf.append(" AND initdatetime::text >= ?"); lSqlBuf.append(" GROUP BY custcode "); lSqlBuf.append(" HAVING sum(sumofprice) < ? AND sum(sumofprice) >= ?"); } else if (FAMC.equals(lItemName) || FBMC.equals(lItemName) || FCMC.equals(lItemName)) { lSqlBuf.append(" AND custCode NOT IN (SELECT custCode FROM rfmanalysevw WHERE initdatetime::text >= ? "); lSqlBuf.append(" GROUP BY custcode HAVING sum(sumofprice) >= ?) GROUP BY custCode "); } lSqlBuf.append(" ) b "); lSqlBuf.append(" WHERE a.custCode = b.custCode "); lSqlBuf.append(" ) x "); return lSqlBuf.toString(); } /** * FM分析 SQL作成 * @param lConnection * @param lItemName * @return * @throws SIDBAccessException */ public String FM_CaluculateSQL(Connection lConnection,String lItemName)throws SIDBAccessException{ StringBuffer lSqlBuf=new StringBuffer(); lSqlBuf.append(" SELECT count(custcode) AS CountOfCustomer,"); if (FAMC.equals(lItemName) || FBMC.equals(lItemName) || FCMC.equals(lItemName)) { lSqlBuf.append(this.getMinusSumLeft_FM(lConnection, lItemName) + " - " + this.getMinusSumRight_FM(lConnection, lItemName) + " AS sumPurchaseMoney,"); } else { lSqlBuf.append(" sum(sumofprice) AS sumPurchaseMoney,"); } lSqlBuf.append(" avg(countBuy) AS avgPurchaseCount,sum(countBuy) AS sumPurchaseCount,avg(amount) AS avgCmdtyCount,sum(amount) AS sumCmdtyCount "); lSqlBuf.append(FM_CaluculateCondSQL(lItemName)); return lSqlBuf.toString(); } /** * FM分析 結果取得 * @param lConnection * @return * @throws SIDBAccessException */ public Collection FM_Caluculate(Connection lConnection) throws SIDBAccessException { PreparedStatement preparedStatement = null; ResultSet lResultSet = null; SIRfmAnalyse lrfmanalyse = new SIRfmAnalyse(); Collection lrmColl = new ArrayList(); java.sql.Date lDate; String F1 = ""; int F2 = 0; int F3 = 0; String M1 = ""; int M2 = 0; int M3 = 0; NumberFormat nf = new DecimalFormat("0.0"); for (int i = 0; i < FM.length; i++) { String arg1 = FM[i]; try { log.debug("FM_Caluculate:FM_CaluculateSQL(lConnection,"+arg1+")="+FM_CaluculateSQL(lConnection,arg1)); preparedStatement = lConnection.prepareStatement(FM_CaluculateSQL(lConnection,arg1)); F1 = getMonthsMinus((String) hashParameters.get("F1")); F2 = Integer.parseInt((String) hashParameters.get("F2")); F3 = Integer.parseInt((String) hashParameters.get("F3")); M1 = getMonthsMinus((String) hashParameters.get("M1")); M2 = Integer.parseInt((String) hashParameters.get("M2")); M3 = Integer.parseInt((String) hashParameters.get("M3")); if (FAMA.equals(arg1)) { //FA-MA if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){ preparedStatement.setString(1, F1); preparedStatement.setString(3, M1); }else{ lDate=new java.sql.Date(new SIDateTime(F1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(1, lDate); lDate=new java.sql.Date(new SIDateTime(M1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(3, lDate); } preparedStatement.setInt(2, F2); preparedStatement.setInt(4, M2); } else if (FAMB.equals(arg1)) { //FA-MB if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){ preparedStatement.setString(1, F1); preparedStatement.setString(3, M1); }else{ lDate=new java.sql.Date(new SIDateTime(F1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(1, lDate); lDate=new java.sql.Date(new SIDateTime(M1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(3, lDate); } preparedStatement.setInt(2, F2); preparedStatement.setInt(4, M2); preparedStatement.setInt(5, M3); } else if (FAMC.equals(arg1)) { //FA-MC if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){ preparedStatement.setString(1, F1); preparedStatement.setString(3, M1); }else{ lDate=new java.sql.Date(new SIDateTime(F1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(1, lDate); lDate=new java.sql.Date(new SIDateTime(M1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(3, lDate); } preparedStatement.setInt(2, F2); preparedStatement.setInt(4, M3); } else if (FBMA.equals(arg1)) { //FB-MA if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){ preparedStatement.setString(1, F1); preparedStatement.setString(4, M1); }else{ lDate=new java.sql.Date(new SIDateTime(F1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(1, lDate); lDate=new java.sql.Date(new SIDateTime(M1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(4, lDate); } preparedStatement.setInt(2, F2); preparedStatement.setInt(3, F3); preparedStatement.setInt(5, M2); } else if (FBMB.equals(arg1)) { //FB-MB if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){ preparedStatement.setString(1, F1); preparedStatement.setString(4, M1); }else{ lDate=new java.sql.Date(new SIDateTime(F1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(1, lDate); lDate=new java.sql.Date(new SIDateTime(M1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(4, lDate); } preparedStatement.setInt(2, F2); preparedStatement.setInt(3, F3); preparedStatement.setInt(5, M2); preparedStatement.setInt(6, M3); } else if (FBMC.equals(arg1)) { //FB-MC if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){ preparedStatement.setString(1, F1); preparedStatement.setString(4, M1); }else{ lDate=new java.sql.Date(new SIDateTime(F1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(1, lDate); lDate=new java.sql.Date(new SIDateTime(M1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(4, lDate); } preparedStatement.setInt(2, F2); preparedStatement.setInt(3, F3); preparedStatement.setInt(5, M3); } else if (FCMA.equals(arg1)) { //FC-MA if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){ preparedStatement.setString(1, F1); preparedStatement.setString(3, M1); }else{ lDate=new java.sql.Date(new SIDateTime(F1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(1, lDate); lDate=new java.sql.Date(new SIDateTime(M1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(3, lDate); } preparedStatement.setInt(2, F3); preparedStatement.setInt(4, M2); } else if (FCMB.equals(arg1)) { //FC-MB if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){ preparedStatement.setString(1, F1); preparedStatement.setString(3, M1); }else{ lDate=new java.sql.Date(new SIDateTime(F1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(1, lDate); lDate=new java.sql.Date(new SIDateTime(M1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(3, lDate); } preparedStatement.setInt(2, F3); preparedStatement.setInt(4, M2); preparedStatement.setInt(5, M3); } else if (FCMC.equals(arg1)) { //FC-MC if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){ preparedStatement.setString(1, F1); preparedStatement.setString(3, M1); }else{ lDate=new java.sql.Date(new SIDateTime(F1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(1, lDate); lDate=new java.sql.Date(new SIDateTime(M1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(3, lDate); } preparedStatement.setInt(2, F3); preparedStatement.setInt(4, M3); } lResultSet = preparedStatement.executeQuery(); if (lResultSet.next()) { lrfmanalyse = new SIRfmAnalyse(); lrfmanalyse.setEncode(SIConfig.SIENCODE_NONE); lrfmanalyse.setCountOfCustomer(lResultSet.getString("CountOfCustomer")); lrfmanalyse.setAvgCmdtyCount(lResultSet.getString("avgCmdtyCount")); lrfmanalyse.setAvgCmdtyCount(nf.format(Float.valueOf(lrfmanalyse.getAvgCmdtyCount()))); lrfmanalyse.setSumCmdtyCount(lResultSet.getString("sumCmdtyCount")); lrfmanalyse.setAvgPurchaseCount(lResultSet.getString("avgPurchaseCount")); lrfmanalyse.setAvgPurchaseCount(nf.format(Float.valueOf(lrfmanalyse.getAvgPurchaseCount()))); lrfmanalyse.setSumPurchaseCount(lResultSet.getString("sumPurchaseCount")); lrfmanalyse.setSumPurchaseMoney(lResultSet.getString("sumPurchaseMoney")); lrmColl.add(lrfmanalyse); } } catch (Exception ex) { ex.printStackTrace(); throw new SIDBAccessException(ex); } finally { SIDBUtil.close(lResultSet, preparedStatement); } } return lrmColl; } /** * FM分析 * @param lConnection * @param arg1 * @return * @throws SIDBAccessException */ public String getMinusSumLeft_FM(Connection lConnection, String arg1) throws SIDBAccessException { PreparedStatement preparedStatement = null; ResultSet lResultSet = null; StringBuffer lSqlBuf = new StringBuffer(); java.sql.Date lDate; String F1 = ""; int F2 = 0; int F3 = 0; String M1 = ""; int M2 = 0; int M3 = 0; String strSumOfPrice = ""; F1 = getMonthsMinus((String) hashParameters.get("F1")); F2 = Integer.parseInt((String) hashParameters.get("F2")); F3 = Integer.parseInt((String) hashParameters.get("F3")); M1 = getMonthsMinus((String) hashParameters.get("M1")); M2 = Integer.parseInt((String) hashParameters.get("M2")); M3 = Integer.parseInt((String) hashParameters.get("M3")); lSqlBuf.append(" select sum(sumofprice) as sumofprice"); lSqlBuf.append(" from "); lSqlBuf.append(" (select custcode"); lSqlBuf.append(" from rfmanalysevw where 1=1 "); if (FAMC.equals(arg1)) { lSqlBuf.append(" and initdatetime::text >= ?"); lSqlBuf.append(" group by custcode "); lSqlBuf.append(" having count(custcode) >= ?"); } else if (FBMC.equals(arg1)) { lSqlBuf.append(" and initdatetime::text >= ?"); lSqlBuf.append(" group by custcode "); lSqlBuf.append(" having count(custcode) < ? and count(custcode) >= ?"); } else if (FCMC.equals(arg1)) { lSqlBuf.append(" and custCode not in (select custCode from rfmanalysevw where initdatetime::text >= ? "); lSqlBuf.append(" group by custcode having count(custcode) >= ?) group by custCode "); } lSqlBuf.append(" ) a ,( "); lSqlBuf.append(" select custcode,sum(sumofprice) as sumofprice"); lSqlBuf.append(" from rfmanalysevw where 1=1 "); lSqlBuf.append(" and custCode not in (select custCode from rfmanalysevw where initdatetime::text >= ? "); lSqlBuf.append(" group by custcode having sum(sumofprice) >= ?) group by custCode"); lSqlBuf.append(" ) b "); lSqlBuf.append(" where a.custcode = b.custcode "); try { preparedStatement = lConnection.prepareStatement(lSqlBuf.toString()); if (FAMC.equals(arg1)) { if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){ preparedStatement.setString(1, F1); preparedStatement.setString(3, M1); }else{ lDate=new java.sql.Date(new SIDateTime(F1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(1, lDate); lDate=new java.sql.Date(new SIDateTime(M1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(3, lDate); } preparedStatement.setInt(2, F2); preparedStatement.setInt(4, M3); } else if (FBMC.equals(arg1)) { if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){ preparedStatement.setString(1, F1); preparedStatement.setString(4, M1); }else{ lDate=new java.sql.Date(new SIDateTime(F1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(1, lDate); lDate=new java.sql.Date(new SIDateTime(M1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(4, lDate); } preparedStatement.setInt(2, F2); preparedStatement.setInt(3, F3); preparedStatement.setInt(5, M3); } else if (FCMC.equals(arg1)) { if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){ preparedStatement.setString(1, F1); preparedStatement.setString(3, M1); }else{ lDate=new java.sql.Date(new SIDateTime(F1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(1, lDate); lDate=new java.sql.Date(new SIDateTime(M1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(3, lDate); } preparedStatement.setInt(2, F3); preparedStatement.setInt(4, M3); } lResultSet = preparedStatement.executeQuery(); if (lResultSet.next()) { if (SIUtil.isNotNull(lResultSet.getString("sumofprice"))) { strSumOfPrice = lResultSet.getString("sumofprice"); } else { strSumOfPrice = "0"; } } else { strSumOfPrice = "0"; } } catch (Exception ex) { ex.printStackTrace(); throw new SIDBAccessException(ex); } finally { SIDBUtil.close(lResultSet, preparedStatement); } return strSumOfPrice; } /** * FM分析 * @param lConnection * @param arg1 * @return * @throws SIDBAccessException */ public String getMinusSumRight_FM(Connection lConnection, String arg1) throws SIDBAccessException { PreparedStatement preparedStatement = null; ResultSet lResultSet = null; StringBuffer lSqlBuf = new StringBuffer(); java.sql.Date lDate; String F1 = ""; int F2 = 0; int F3 = 0; String M1 = ""; int M2 = 0; int M3 = 0; String strSumOfPrice = ""; F1 = getMonthsMinus((String) hashParameters.get("F1")); F2 = Integer.parseInt((String) hashParameters.get("F2")); F3 = Integer.parseInt((String) hashParameters.get("F3")); M1 = getMonthsMinus((String) hashParameters.get("M1")); M2 = Integer.parseInt((String) hashParameters.get("M2")); M3 = Integer.parseInt((String) hashParameters.get("M3")); lSqlBuf.append(" select sum(sumofprice) as sumofprice"); lSqlBuf.append(" from "); lSqlBuf.append(" (select custcode"); lSqlBuf.append(" from rfmanalysevw where 1=1 "); if (FAMC.equals(arg1)) { lSqlBuf.append(" and initdatetime::text >= ?"); lSqlBuf.append(" group by custcode "); lSqlBuf.append(" having count(custcode) >= ?"); } else if (FBMC.equals(arg1)) { lSqlBuf.append(" and initdatetime::text >= ?"); lSqlBuf.append(" group by custcode "); lSqlBuf.append(" having count(custcode) < ? and count(custcode) >= ?"); } else if (FCMC.equals(arg1)) { lSqlBuf.append(" and custCode not in (select custCode from rfmanalysevw where initdatetime::text >= ? "); lSqlBuf.append(" group by custcode having count(custcode) >= ?) group by custCode "); } lSqlBuf.append(" ) a ,( "); lSqlBuf.append(" select custcode,sum(sumofprice) as sumofprice"); lSqlBuf.append(" from rfmanalysevw where 1=1 "); lSqlBuf.append(" and initdatetime::text < ?"); lSqlBuf.append(" and custCode not in (select custCode from rfmanalysevw where initdatetime::text >= ? "); lSqlBuf.append(" group by custcode having sum(sumofprice) >= ?) group by custCode"); lSqlBuf.append(" ) b "); lSqlBuf.append(" where a.custcode = b.custcode "); try { preparedStatement = lConnection.prepareStatement(lSqlBuf.toString()); if (FAMC.equals(arg1)) { if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){ preparedStatement.setString(1, F1); preparedStatement.setString(3, M1); preparedStatement.setString(4, M1); }else{ lDate=new java.sql.Date(new SIDateTime(F1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(1, lDate); lDate=new java.sql.Date(new SIDateTime(M1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(3, lDate); preparedStatement.setDate(4, lDate); } preparedStatement.setInt(2, F2); preparedStatement.setInt(5, M3); } else if (FBMC.equals(arg1)) { if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){ preparedStatement.setString(1, F1); preparedStatement.setString(4, M1); preparedStatement.setString(5, M1); }else{ lDate=new java.sql.Date(new SIDateTime(F1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(1, lDate); lDate=new java.sql.Date(new SIDateTime(M1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(4, lDate); preparedStatement.setDate(5, lDate); } preparedStatement.setInt(2, F2); preparedStatement.setInt(3, F3); preparedStatement.setInt(6, M3); } else if (FCMC.equals(arg1)) { if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){ preparedStatement.setString(1, F1); preparedStatement.setString(3, M1); preparedStatement.setString(4, M1); }else{ lDate=new java.sql.Date(new SIDateTime(F1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(1, lDate); lDate=new java.sql.Date(new SIDateTime(M1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(3, lDate); preparedStatement.setDate(4, lDate); } preparedStatement.setInt(2, F3); preparedStatement.setInt(5, M3); } lResultSet = preparedStatement.executeQuery(); if (lResultSet.next()) { if (SIUtil.isNotNull(lResultSet.getString("sumofprice"))) { strSumOfPrice = lResultSet.getString("sumofprice"); } else { strSumOfPrice = "0"; } } else { strSumOfPrice = "0"; } } catch (Exception ex) { ex.printStackTrace(); throw new SIDBAccessException(ex); } finally { SIDBUtil.close(lResultSet, preparedStatement); } return strSumOfPrice; } /** * 顧客数の割合 * @param pCustomerCount * @return */ public String getPercentCustomer(String pCustomerCount) { if (SIUtil.isNull(pCustomerCount)) return "0"; if (this.countCustomerAll == 0) return "0"; NumberFormat nf = new DecimalFormat("0.0%"); float aF = Float.parseFloat(pCustomerCount) / (float) countCustomerAll; return String.valueOf(nf.format(aF)); } /** * 購入金額の割合 * @param pSumOfPrice * @return */ public String getPercentSumOfPrice(String pSumOfPrice) { if (SIUtil.isNull(pSumOfPrice)) return "0"; if (this.sumOfPrice == 0) return "0"; NumberFormat nf = new DecimalFormat("0.0%"); float aF = Float.parseFloat(pSumOfPrice) / (float) sumOfPrice; return String.valueOf(nf.format(aF)); } /** * 購入商品数の割合 * @param pSumCmdtyCount * @return */ public String getPercentSumCmdty(String pSumCmdtyCount) { if (SIUtil.isNull(pSumCmdtyCount)) return "0"; if (this.sumCmdtyCount == 0) return "0"; NumberFormat nf = new DecimalFormat("0.0%"); float aF = Float.parseFloat(pSumCmdtyCount) / (float) sumCmdtyCount; return String.valueOf(nf.format(aF)); } /** * 購入回数の割合 * @param pSumOfPrice * @return */ public String getPercentSumPurchase(String pSumPurchaseCount) { if (SIUtil.isNull(pSumPurchaseCount)) return "0"; if (this.sumPurchaseCount == 0) return "0"; NumberFormat nf = new DecimalFormat("0.0%"); float aF = Float.parseFloat(pSumPurchaseCount) / (float) sumPurchaseCount; return String.valueOf(nf.format(aF)); } /** * 全顧客数取得 * @param lConnection * @throws SIDBAccessException */ public void getCountCustomerAll(Connection lConnection) throws SIDBAccessException { PreparedStatement preparedStatement = null; ResultSet lResultSet = null; StringBuffer lSqlBuf = new StringBuffer(); lSqlBuf.append(" select count(distinct custCode) as countCustomerAll from rfmanalysevw"); try { preparedStatement = lConnection.prepareStatement(lSqlBuf.toString()); lResultSet = preparedStatement.executeQuery(); if (lResultSet.next()) { this.countCustomerAll = lResultSet.getInt("countCustomerAll"); } } catch (Exception ex) { ex.printStackTrace(); throw new SIDBAccessException(ex); } finally { SIDBUtil.close(lResultSet, preparedStatement); } } /** * 全合計金額取得 * @param lConnection * @throws SIDBAccessException */ public void getSumOfPrice(Connection lConnection) throws SIDBAccessException { PreparedStatement preparedStatement = null; ResultSet lResultSet = null; StringBuffer lSqlBuf = new StringBuffer(); java.sql.Date lDate; lSqlBuf.append(" select sum(sumOfPrice) as sumOfPrice from rfmanalysevw where sumOfPrice is not Null"); lSqlBuf.append(" and initdatetime::text >= ?"); try { preparedStatement = lConnection.prepareStatement(lSqlBuf.toString()); if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){ preparedStatement.setString(1, getMonthsMinus((String) hashParameters.get("M1"))); }else{ lDate=new java.sql.Date(new SIDateTime(getMonthsMinus((String) hashParameters.get("M1")),SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(1, lDate); } lResultSet = preparedStatement.executeQuery(); if (lResultSet.next()) { this.sumOfPrice = lResultSet.getLong("sumOfPrice"); } else { this.sumOfPrice = 0; } } catch (Exception ex) { ex.printStackTrace(); throw new SIDBAccessException(ex); } finally { SIDBUtil.close(lResultSet, preparedStatement); } } /** * 全購入商品数、購入回数を取得 * @param lConnection * @throws SIDBAccessException */ public void getSumCmdtyCountAndSumPurchaseCount(Connection lConnection) throws SIDBAccessException { PreparedStatement preparedStatement = null; ResultSet lResultSet = null; StringBuffer lSqlBuf = new StringBuffer(); if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){ lSqlBuf.append(" select sum(amount) as sumCmdtyCount,count(rfmanalysevw) as sumPurchaseCount from rfmanalysevw where sumOfPrice is not Null"); }else{ lSqlBuf.append(" select sum(amount) as sumCmdtyCount,count(RFMANALYSECODE) as sumPurchaseCount from rfmanalysevw where sumOfPrice is not Null"); } log.debug("getSumCmdtyCountAndSumPurchaseCount:lSqlBuf="+lSqlBuf.toString()); try { preparedStatement = lConnection.prepareStatement(lSqlBuf.toString()); lResultSet = preparedStatement.executeQuery();//修正 postgres8.x対応 if (lResultSet.next()) { this.sumCmdtyCount = lResultSet.getLong("sumCmdtyCount"); this.sumPurchaseCount = lResultSet.getLong("sumPurchaseCount"); } else { this.sumOfPrice = 0; } } catch (Exception ex) { ex.printStackTrace(); throw new SIDBAccessException(ex); } finally { SIDBUtil.close(lResultSet, preparedStatement); } } /** * 日を引く * @param arg1 * @return */ public String getDayMinus(String arg1) { Calendar cal = Calendar.getInstance(); Date date = cal.getTime(); int day = Integer.parseInt(arg1); cal.setTime(date); cal.add(Calendar.DAY_OF_MONTH, -day); //指定した日付を暦から差し引く。 SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd"); return sdf.format(cal.getTime()).toString(); } /** * 月を引く * @return */ public String getMonthsMinus(String arg1) { if (SIUtil.isNull(arg1)) return ""; Calendar cal = Calendar.getInstance(); Date date = cal.getTime(); int month = Integer.parseInt(arg1); cal.setTime(date); cal.add(Calendar.MONTH, -month); //指定した日付を暦から差し引く。 SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd"); return sdf.format(cal.getTime()).toString(); } /** * メールアドレスのリストを取得 * @param lUrlParam * @param lConnection * @return */ public String[] getMailAddressList(SIURLParameter lUrlParam,Connection lConnection){//7.1.1 ST0236 修正 Collection lCollection=new ArrayList(); PreparedStatement preparedStatement = null; ResultSet lResultSet = null; java.sql.Date lDate; log.debug("mailInxTxt="+(String)lUrlParam.getParam("mailInxTxt")+",getAnalyseMode()="+getAnalyseMode());//7.1.1 ST0236 修正 int lMailInx=Integer.parseInt((String)lUrlParam.getParam("mailInxTxt"));//7.1.1 ST0236 修正 String lItemName=""; StringBuffer lSqlBuf=new StringBuffer(); String R1 = ""; String R2 = ""; String F1 = ""; int F2 = 0; int F3 = 0; String M1 = ""; int M2 = 0; int M3 = 0; lSqlBuf.append("SELECT email "); try { if (getAnalyseMode().equals(Mode_RF)){ R1 = getDayMinus((String) hashParameters.get("R1")); R2 = getDayMinus((String) hashParameters.get("R2")); F1 = getMonthsMinus((String) hashParameters.get("F1")); F2 = Integer.parseInt((String) hashParameters.get("F2")); F3 = Integer.parseInt((String) hashParameters.get("F3")); lItemName=RF[lMailInx]; lSqlBuf.append(RF_CaluculateCondSQL(lItemName)); lSqlBuf.append(",CustTbl").append(SIDBMultiConf.SIALIAS_CURR_NAME).append("bb WHERE x.CustCode=bb.CustCode AND bb.newmailflg = 1"); lSqlBuf.append(" AND bb.DelFlg != ").append(SIConfig.SIDEL_FLG_DELETE); //7.3.0 ST2027 追加 log.debug("lSqlBuf[RF]="+lSqlBuf+",lItemName="+lItemName); preparedStatement = lConnection.prepareStatement(lSqlBuf.toString()); if (RAFA.equals(lItemName)) { //RA-FA if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){ preparedStatement.setString(1, R1); preparedStatement.setString(2, F1); }else{ lDate=new java.sql.Date(new SIDateTime(R1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(1, lDate); lDate=new java.sql.Date(new SIDateTime(F1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(2, lDate); } preparedStatement.setInt(3, F2); } else if (RAFB.equals(lItemName)) { //RA-FB if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){ preparedStatement.setString(1, R1); preparedStatement.setString(2, F1); }else{ lDate=new java.sql.Date(new SIDateTime(R1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(1, lDate); lDate=new java.sql.Date(new SIDateTime(F1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(2, lDate); } preparedStatement.setInt(3, F2); preparedStatement.setInt(4, F3); } else if (RAFC.equals(lItemName)) { //RA-FC if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){ preparedStatement.setString(1, R1); preparedStatement.setString(2, F1); }else{ lDate=new java.sql.Date(new SIDateTime(R1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(1, lDate); lDate=new java.sql.Date(new SIDateTime(F1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(2, lDate); } preparedStatement.setInt(3, F3); } else if (RBFA.equals(lItemName)) { //RB-FA if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){ preparedStatement.setString(1, R1); preparedStatement.setString(2, R2); preparedStatement.setString(3, F1); }else{ lDate=new java.sql.Date(new SIDateTime(R1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(1, lDate); lDate=new java.sql.Date(new SIDateTime(R2,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(2, lDate); lDate=new java.sql.Date(new SIDateTime(F1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(3, lDate); } preparedStatement.setInt(4, F2); } else if (RBFB.equals(lItemName)) { //RB-FB if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){ preparedStatement.setString(1, R1); preparedStatement.setString(2, R2); preparedStatement.setString(3, F1); }else{ lDate=new java.sql.Date(new SIDateTime(R1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(1, lDate); lDate=new java.sql.Date(new SIDateTime(R2,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(2, lDate); lDate=new java.sql.Date(new SIDateTime(F1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(3, lDate); } preparedStatement.setInt(4, F2); preparedStatement.setInt(5, F3); } else if (RBFC.equals(lItemName)) { //RB-FC if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){ preparedStatement.setString(1, R1); preparedStatement.setString(2, R2); preparedStatement.setString(3, F1); }else{ lDate=new java.sql.Date(new SIDateTime(R1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(1, lDate); lDate=new java.sql.Date(new SIDateTime(R2,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(2, lDate); lDate=new java.sql.Date(new SIDateTime(F1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(3, lDate); } preparedStatement.setInt(4, F3); } else if (RCFA.equals(lItemName)) { //RC-FA if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){ preparedStatement.setString(1, R2); preparedStatement.setString(2, F1); }else{ lDate=new java.sql.Date(new SIDateTime(R2,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(1, lDate); lDate=new java.sql.Date(new SIDateTime(F1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(2, lDate); } preparedStatement.setInt(3, F2); } else if (RCFB.equals(lItemName)) { //RC-FB if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){ preparedStatement.setString(1, R2); preparedStatement.setString(2, F1); }else{ lDate=new java.sql.Date(new SIDateTime(R2,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(1, lDate); lDate=new java.sql.Date(new SIDateTime(F1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(2, lDate); } preparedStatement.setInt(3, F2); preparedStatement.setInt(4, F3); } else if (RCFC.equals(lItemName)) { //RC-FC if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){ preparedStatement.setString(1, R2); preparedStatement.setString(2, F1); }else{ lDate=new java.sql.Date(new SIDateTime(R2,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(1, lDate); lDate=new java.sql.Date(new SIDateTime(F1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(2, lDate); } preparedStatement.setInt(3, F3); } lResultSet = preparedStatement.executeQuery(); if (lResultSet.next()) { } }else if (getAnalyseMode().equals(Mode_RM)){ R1 = getDayMinus((String) hashParameters.get("R1")); R2 = getDayMinus((String) hashParameters.get("R2")); M1 = getMonthsMinus((String) hashParameters.get("M1")); M2 = Integer.parseInt((String) hashParameters.get("M2")); M3 = Integer.parseInt((String) hashParameters.get("M3")); lItemName=RM[lMailInx]; lSqlBuf.append(RM_CaluculateCondSQL(lItemName)); lSqlBuf.append(",CustTbl").append(SIDBMultiConf.SIALIAS_CURR_NAME).append("bb WHERE x.CustCode=bb.CustCode AND bb.newmailflg = 1"); lSqlBuf.append(" AND bb.DelFlg != ").append(SIConfig.SIDEL_FLG_DELETE); //7.3.0 ST2027 追加 log.debug("lSqlBuf[RM]="+lSqlBuf+",lItemName="+lItemName); preparedStatement = lConnection.prepareStatement(lSqlBuf.toString()); if (RAMA.equals(lItemName)) { //RA-MA if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){ preparedStatement.setString(1, R1); preparedStatement.setString(2, M1); }else{ lDate=new java.sql.Date(new SIDateTime(R1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(1, lDate); lDate=new java.sql.Date(new SIDateTime(M1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(2, lDate); } preparedStatement.setInt(3, M2); } else if (RAMB.equals(lItemName)) { //RA-MB if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){ preparedStatement.setString(1, R1); preparedStatement.setString(2, M1); }else{ lDate=new java.sql.Date(new SIDateTime(R1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(1, lDate); lDate=new java.sql.Date(new SIDateTime(M1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(2, lDate); } preparedStatement.setInt(3, M2); preparedStatement.setInt(4, M3); } else if (RAMC.equals(lItemName)) { //RA-MC if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){ preparedStatement.setString(1, R1); preparedStatement.setString(2, M1); }else{ lDate=new java.sql.Date(new SIDateTime(R1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(1, lDate); lDate=new java.sql.Date(new SIDateTime(M1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(2, lDate); } preparedStatement.setInt(3, M3); } else if (RBMA.equals(lItemName)) { //RB-MA if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){ preparedStatement.setString(1, R1); preparedStatement.setString(2, R2); preparedStatement.setString(3, M1); }else{ lDate=new java.sql.Date(new SIDateTime(R1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(1, lDate); lDate=new java.sql.Date(new SIDateTime(R2,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(2, lDate); lDate=new java.sql.Date(new SIDateTime(M1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(3, lDate); } preparedStatement.setInt(4, M2); } else if (RBMB.equals(lItemName)) { //RB-MB if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){ preparedStatement.setString(1, R1); preparedStatement.setString(2, R2); preparedStatement.setString(3, M1); }else{ lDate=new java.sql.Date(new SIDateTime(R1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(1, lDate); lDate=new java.sql.Date(new SIDateTime(R2,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(2, lDate); lDate=new java.sql.Date(new SIDateTime(M1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(3, lDate); } preparedStatement.setInt(4, M2); preparedStatement.setInt(5, M3); } else if (RBMC.equals(lItemName)) { //RB-MC if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){ preparedStatement.setString(1, R1); preparedStatement.setString(2, R2); preparedStatement.setString(3, M1); }else{ lDate=new java.sql.Date(new SIDateTime(R1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(1, lDate); lDate=new java.sql.Date(new SIDateTime(R2,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(2, lDate); lDate=new java.sql.Date(new SIDateTime(M1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(3, lDate); } preparedStatement.setInt(4, M3); } else if (RCMA.equals(lItemName)) { //RC-MA if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){ preparedStatement.setString(1, R2); preparedStatement.setString(2, M1); }else{ lDate=new java.sql.Date(new SIDateTime(R2,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(1, lDate); lDate=new java.sql.Date(new SIDateTime(M1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(2, lDate); } preparedStatement.setInt(3, M2); } else if (RCMB.equals(lItemName)) { //RC-MB if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){ preparedStatement.setString(1, R2); preparedStatement.setString(2, M1); }else{ lDate=new java.sql.Date(new SIDateTime(R2,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(1, lDate); lDate=new java.sql.Date(new SIDateTime(M1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(2, lDate); } preparedStatement.setInt(3, M2); preparedStatement.setInt(4, M3); } else if (RCMC.equals(lItemName)) { //RC-MC if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){ preparedStatement.setString(1, R2); preparedStatement.setString(2, M1); }else{ lDate=new java.sql.Date(new SIDateTime(R2,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(1, lDate); lDate=new java.sql.Date(new SIDateTime(M1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(2, lDate); } preparedStatement.setInt(3, M3); } lResultSet = preparedStatement.executeQuery(); }else if (getAnalyseMode().equals(Mode_FM)){ F1 = getMonthsMinus((String) hashParameters.get("F1")); F2 = Integer.parseInt((String) hashParameters.get("F2")); F3 = Integer.parseInt((String) hashParameters.get("F3")); M1 = getMonthsMinus((String) hashParameters.get("M1")); M2 = Integer.parseInt((String) hashParameters.get("M2")); M3 = Integer.parseInt((String) hashParameters.get("M3")); lItemName=FM[lMailInx]; lSqlBuf.append(FM_CaluculateCondSQL(lItemName)); lSqlBuf.append(",CustTbl").append(SIDBMultiConf.SIALIAS_CURR_NAME).append("bb WHERE x.CustCode=bb.CustCode AND bb.newmailflg = 1"); lSqlBuf.append(" AND bb.DelFlg != ").append(SIConfig.SIDEL_FLG_DELETE); //7.3.0 ST2027 追加 log.debug("lSqlBuf[FM]="+lSqlBuf+",lItemName="+lItemName); preparedStatement = lConnection.prepareStatement(lSqlBuf.toString()); if (FAMA.equals(lItemName)) { //FA-MA if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){ preparedStatement.setString(1, F1); preparedStatement.setString(3, M1); }else{ lDate=new java.sql.Date(new SIDateTime(F1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(1, lDate); lDate=new java.sql.Date(new SIDateTime(M1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(3, lDate); } preparedStatement.setInt(2, F2); preparedStatement.setInt(4, M2); } else if (FAMB.equals(lItemName)) { //FA-MB //FA-MA if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){ preparedStatement.setString(1, F1); preparedStatement.setString(3, M1); }else{ lDate=new java.sql.Date(new SIDateTime(F1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(1, lDate); lDate=new java.sql.Date(new SIDateTime(M1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(3, lDate); } preparedStatement.setInt(2, F2); preparedStatement.setInt(4, M2); preparedStatement.setInt(5, M3); } else if (FAMC.equals(lItemName)) { //FA-MC //FA-MA if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){ preparedStatement.setString(1, F1); preparedStatement.setString(3, M1); }else{ lDate=new java.sql.Date(new SIDateTime(F1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(1, lDate); lDate=new java.sql.Date(new SIDateTime(M1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(3, lDate); } preparedStatement.setInt(2, F2); preparedStatement.setInt(4, M3); } else if (FBMA.equals(lItemName)) { //FB-MA //FA-MA if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){ preparedStatement.setString(1, F1); preparedStatement.setString(4, M1); }else{ lDate=new java.sql.Date(new SIDateTime(F1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(1, lDate); lDate=new java.sql.Date(new SIDateTime(M1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(4, lDate); } preparedStatement.setInt(2, F2); preparedStatement.setInt(3, F3); preparedStatement.setInt(5, M2); } else if (FBMB.equals(lItemName)) { //FB-MB if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){ preparedStatement.setString(1, F1); preparedStatement.setString(4, M1); }else{ lDate=new java.sql.Date(new SIDateTime(F1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(1, lDate); lDate=new java.sql.Date(new SIDateTime(M1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(4, lDate); } preparedStatement.setInt(2, F2); preparedStatement.setInt(3, F3); preparedStatement.setInt(5, M2); preparedStatement.setInt(6, M3); } else if (FBMC.equals(lItemName)) { //FB-MC if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){ preparedStatement.setString(1, F1); preparedStatement.setString(4, M1); }else{ lDate=new java.sql.Date(new SIDateTime(F1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(1, lDate); lDate=new java.sql.Date(new SIDateTime(M1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(4, lDate); } preparedStatement.setInt(2, F2); preparedStatement.setInt(3, F3); preparedStatement.setInt(5, M3); } else if (FCMA.equals(lItemName)) { //FC-MA if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){ preparedStatement.setString(1, F1); preparedStatement.setString(3, M1); }else{ lDate=new java.sql.Date(new SIDateTime(F1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(1, lDate); lDate=new java.sql.Date(new SIDateTime(M1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(3, lDate); } preparedStatement.setInt(2, F3); preparedStatement.setInt(4, M2); } else if (FCMB.equals(lItemName)) { //FC-MB if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){ preparedStatement.setString(1, F1); preparedStatement.setString(3, M1); }else{ lDate=new java.sql.Date(new SIDateTime(F1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(1, lDate); lDate=new java.sql.Date(new SIDateTime(M1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(3, lDate); } preparedStatement.setInt(2, F3); preparedStatement.setInt(4, M2); preparedStatement.setInt(5, M3); } else if (FCMC.equals(lItemName)) { //FC-MC if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){ preparedStatement.setString(1, F1); preparedStatement.setString(3, M1); }else{ lDate=new java.sql.Date(new SIDateTime(F1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(1, lDate); lDate=new java.sql.Date(new SIDateTime(M1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(3, lDate); } preparedStatement.setInt(2, F3); preparedStatement.setInt(4, M3); } } lResultSet = preparedStatement.executeQuery(); String lMailAddress=""; while (lResultSet.next()){ lCollection.add(lResultSet.getString(1)); } } catch (NumberFormatException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); }catch(ParseException e){ e.printStackTrace(); }finally{ SIDBUtil.close(lResultSet, preparedStatement); } return (String[])lCollection.toArray(new String[0]); } /** * CSV出力用のSQLを取得 * @param lUrlParam * @param lConnection * @return */ public Collection getCSVSQL(SIURLParameter lUrlParam,Connection lConnection){//7.1.1 ST0236 修正 Collection lCollection=new ArrayList(); PreparedStatement preparedStatement = null; ResultSet lResultSet = null; String sql = ""; java.sql.Date lDate; log.debug("csvInxTxt="+(String)lUrlParam.getParam("csvvInxTxt")+",getAnalyseMode()="+getAnalyseMode());//7.1.1 ST0236 修正 int lCsvInx=Integer.parseInt((String)lUrlParam.getParam("csvInxTxt"));//7.1.1 ST0236 修正 String lItemName=""; StringBuffer lSqlBuf=new StringBuffer(); String R1 = ""; String R2 = ""; String F1 = ""; int F2 = 0; int F3 = 0; String M1 = ""; int M2 = 0; int M3 = 0; lSqlBuf.append("SELECT bbb.Rfmanalysecode, bbb.Custcode,aaa.CustName,bbb.OrderCode, bbb.Sumofprice, bbb.Amount, bbb.Initdatetime "); try { if (getAnalyseMode().equals(Mode_RF)){ R1 = getDayMinus((String) hashParameters.get("R1")); R2 = getDayMinus((String) hashParameters.get("R2")); F1 = getMonthsMinus((String) hashParameters.get("F1")); F2 = Integer.parseInt((String) hashParameters.get("F2")); F3 = Integer.parseInt((String) hashParameters.get("F3")); lItemName=RF[lCsvInx]; lSqlBuf.append(RF_CaluculateCondSQL(lItemName)); lSqlBuf.append(" LEFT OUTER JOIN CustTbl").append(SIDBMultiConf.SIALIAS_CURR_NAME).append("aaa ON x.CustCode=aaa.CustCode "); lSqlBuf.append(",rfmanalysevw").append(SIDBMultiConf.SIALIAS_CURR_NAME).append("bbb "); lSqlBuf.append(" WHERE x.CustCode=bbb.CustCode "); lSqlBuf.append("ORDER BY CustCode ASC,Initdatetime ASC"); log.debug("lSqlBuf[RF]="+lSqlBuf+",lItemName="+lItemName); preparedStatement = lConnection.prepareStatement(lSqlBuf.toString()); if (RAFA.equals(lItemName)) { //RA-FA if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){ preparedStatement.setString(1, R1); preparedStatement.setString(2, F1); }else{ lDate=new java.sql.Date(new SIDateTime(R1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(1, lDate); lDate=new java.sql.Date(new SIDateTime(F1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(2, lDate); } preparedStatement.setInt(3, F2); } else if (RAFB.equals(lItemName)) { //RA-FB if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){ preparedStatement.setString(1, R1); preparedStatement.setString(2, F1); }else{ lDate=new java.sql.Date(new SIDateTime(R1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(1, lDate); lDate=new java.sql.Date(new SIDateTime(F1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(2, lDate); } preparedStatement.setInt(3, F2); preparedStatement.setInt(4, F3); } else if (RAFC.equals(lItemName)) { //RA-FC if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){ preparedStatement.setString(1, R1); preparedStatement.setString(2, F1); }else{ lDate=new java.sql.Date(new SIDateTime(R1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(1, lDate); lDate=new java.sql.Date(new SIDateTime(F1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(2, lDate); } preparedStatement.setInt(3, F3); } else if (RBFA.equals(lItemName)) { //RB-FA if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){ preparedStatement.setString(1, R1); preparedStatement.setString(2, R2); preparedStatement.setString(3, F1); }else{ lDate=new java.sql.Date(new SIDateTime(R1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(1, lDate); lDate=new java.sql.Date(new SIDateTime(R2,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(2, lDate); lDate=new java.sql.Date(new SIDateTime(F1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(3, lDate); } preparedStatement.setInt(4, F2); } else if (RBFB.equals(lItemName)) { //RB-FB if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){ preparedStatement.setString(1, R1); preparedStatement.setString(2, R2); preparedStatement.setString(3, F1); }else{ lDate=new java.sql.Date(new SIDateTime(R1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(1, lDate); lDate=new java.sql.Date(new SIDateTime(R2,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(2, lDate); lDate=new java.sql.Date(new SIDateTime(F1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(3, lDate); } preparedStatement.setInt(4, F2); preparedStatement.setInt(5, F3); } else if (RBFC.equals(lItemName)) { //RB-FC if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){ preparedStatement.setString(1, R1); preparedStatement.setString(2, R2); preparedStatement.setString(3, F1); }else{ lDate=new java.sql.Date(new SIDateTime(R1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(1, lDate); lDate=new java.sql.Date(new SIDateTime(R2,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(2, lDate); lDate=new java.sql.Date(new SIDateTime(F1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(3, lDate); } preparedStatement.setInt(4, F3); } else if (RCFA.equals(lItemName)) { //RC-FA if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){ preparedStatement.setString(1, R2); preparedStatement.setString(2, F1); }else{ lDate=new java.sql.Date(new SIDateTime(R2,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(1, lDate); lDate=new java.sql.Date(new SIDateTime(F1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(2, lDate); } preparedStatement.setInt(3, F2); } else if (RCFB.equals(lItemName)) { //RC-FB if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){ preparedStatement.setString(1, R2); preparedStatement.setString(2, F1); }else{ lDate=new java.sql.Date(new SIDateTime(R2,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(1, lDate); lDate=new java.sql.Date(new SIDateTime(F1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(2, lDate); } preparedStatement.setInt(3, F2); preparedStatement.setInt(4, F3); } else if (RCFC.equals(lItemName)) { //RC-FC if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){ preparedStatement.setString(1, R2); preparedStatement.setString(2, F1); }else{ lDate=new java.sql.Date(new SIDateTime(R2,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(1, lDate); lDate=new java.sql.Date(new SIDateTime(F1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(2, lDate); } preparedStatement.setInt(3, F3); } }else if (getAnalyseMode().equals(Mode_RM)){ R1 = getDayMinus((String) hashParameters.get("R1")); R2 = getDayMinus((String) hashParameters.get("R2")); M1 = getMonthsMinus((String) hashParameters.get("M1")); M2 = Integer.parseInt((String) hashParameters.get("M2")); M3 = Integer.parseInt((String) hashParameters.get("M3")); lItemName=RM[lCsvInx]; lSqlBuf.append(RM_CaluculateCondSQL(lItemName)); lSqlBuf.append(" LEFT OUTER JOIN CustTbl").append(SIDBMultiConf.SIALIAS_CURR_NAME).append("aaa ON x.CustCode=aaa.CustCode "); lSqlBuf.append(",rfmanalysevw").append(SIDBMultiConf.SIALIAS_CURR_NAME).append("bbb "); lSqlBuf.append(" WHERE x.CustCode=bbb.CustCode "); lSqlBuf.append("ORDER BY CustCode ASC,Initdatetime ASC"); log.debug("lSqlBuf[RM]="+lSqlBuf+",lItemName="+lItemName); preparedStatement = lConnection.prepareStatement(lSqlBuf.toString()); if (RAMA.equals(lItemName)) { //RA-MA if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){ preparedStatement.setString(1, R1); preparedStatement.setString(2, M1); }else{ lDate=new java.sql.Date(new SIDateTime(R1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(1, lDate); lDate=new java.sql.Date(new SIDateTime(M1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(2, lDate); } preparedStatement.setInt(3, M2); } else if (RAMB.equals(lItemName)) { //RA-MB if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){ preparedStatement.setString(1, R1); preparedStatement.setString(2, M1); }else{ lDate=new java.sql.Date(new SIDateTime(R1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(1, lDate); lDate=new java.sql.Date(new SIDateTime(M1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(2, lDate); } preparedStatement.setInt(3, M2); preparedStatement.setInt(4, M3); } else if (RAMC.equals(lItemName)) { //RA-MC if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){ preparedStatement.setString(1, R1); preparedStatement.setString(2, M1); }else{ lDate=new java.sql.Date(new SIDateTime(R1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(1, lDate); lDate=new java.sql.Date(new SIDateTime(M1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(2, lDate); } preparedStatement.setInt(3, M3); } else if (RBMA.equals(lItemName)) { //RB-MA if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){ preparedStatement.setString(1, R1); preparedStatement.setString(2, R2); preparedStatement.setString(3, M1); }else{ lDate=new java.sql.Date(new SIDateTime(R1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(1, lDate); lDate=new java.sql.Date(new SIDateTime(R2,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(2, lDate); lDate=new java.sql.Date(new SIDateTime(M1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(3, lDate); } preparedStatement.setInt(4, M2); } else if (RBMB.equals(lItemName)) { //RB-MB if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){ preparedStatement.setString(1, R1); preparedStatement.setString(2, R2); preparedStatement.setString(3, M1); }else{ lDate=new java.sql.Date(new SIDateTime(R1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(1, lDate); lDate=new java.sql.Date(new SIDateTime(R2,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(2, lDate); lDate=new java.sql.Date(new SIDateTime(M1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(3, lDate); } preparedStatement.setInt(4, M2); preparedStatement.setInt(5, M3); } else if (RBMC.equals(lItemName)) { //RB-MC if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){ preparedStatement.setString(1, R1); preparedStatement.setString(2, R2); preparedStatement.setString(3, M1); }else{ lDate=new java.sql.Date(new SIDateTime(R1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(1, lDate); lDate=new java.sql.Date(new SIDateTime(R2,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(2, lDate); lDate=new java.sql.Date(new SIDateTime(M1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(3, lDate); } preparedStatement.setInt(4, M3); } else if (RCMA.equals(lItemName)) { //RC-MA if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){ preparedStatement.setString(1, R2); preparedStatement.setString(2, M1); }else{ lDate=new java.sql.Date(new SIDateTime(R2,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(1, lDate); lDate=new java.sql.Date(new SIDateTime(M1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(2, lDate); } preparedStatement.setInt(3, M2); } else if (RCMB.equals(lItemName)) { //RC-MB if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){ preparedStatement.setString(1, R2); preparedStatement.setString(2, M1); }else{ lDate=new java.sql.Date(new SIDateTime(R2,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(1, lDate); lDate=new java.sql.Date(new SIDateTime(M1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(2, lDate); } preparedStatement.setInt(3, M2); preparedStatement.setInt(4, M3); } else if (RCMC.equals(lItemName)) { //RC-MC if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){ preparedStatement.setString(1, R2); preparedStatement.setString(2, M1); }else{ lDate=new java.sql.Date(new SIDateTime(R2,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(1, lDate); lDate=new java.sql.Date(new SIDateTime(M1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(2, lDate); } preparedStatement.setInt(3, M3); } }else if (getAnalyseMode().equals(Mode_FM)){ F1 = getMonthsMinus((String) hashParameters.get("F1")); F2 = Integer.parseInt((String) hashParameters.get("F2")); F3 = Integer.parseInt((String) hashParameters.get("F3")); M1 = getMonthsMinus((String) hashParameters.get("M1")); M2 = Integer.parseInt((String) hashParameters.get("M2")); M3 = Integer.parseInt((String) hashParameters.get("M3")); lItemName=FM[lCsvInx]; lSqlBuf.append(FM_CaluculateCondSQL(lItemName)); lSqlBuf.append(" LEFT OUTER JOIN CustTbl").append(SIDBMultiConf.SIALIAS_CURR_NAME).append("aaa ON x.CustCode=aaa.CustCode "); lSqlBuf.append(",rfmanalysevw").append(SIDBMultiConf.SIALIAS_CURR_NAME).append("bbb "); lSqlBuf.append(" WHERE x.CustCode=bbb.CustCode "); lSqlBuf.append("ORDER BY CustCode ASC,Initdatetime ASC"); log.debug("lSqlBuf[FM]="+lSqlBuf+",lItemName="+lItemName); preparedStatement = lConnection.prepareStatement(lSqlBuf.toString()); if (FAMA.equals(lItemName)) { //FA-MA if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){ preparedStatement.setString(1, F1); preparedStatement.setString(3, M1); }else{ lDate=new java.sql.Date(new SIDateTime(F1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(1, lDate); lDate=new java.sql.Date(new SIDateTime(M1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(3, lDate); } preparedStatement.setInt(2, F2); preparedStatement.setInt(4, M2); } else if (FAMB.equals(lItemName)) { //FA-MB if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){ preparedStatement.setString(1, F1); preparedStatement.setString(3, M1); }else{ lDate=new java.sql.Date(new SIDateTime(F1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(1, lDate); lDate=new java.sql.Date(new SIDateTime(M1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(3, lDate); } preparedStatement.setInt(2, F2); preparedStatement.setInt(4, M2); preparedStatement.setInt(5, M3); } else if (FAMC.equals(lItemName)) { //FA-MC if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){ preparedStatement.setString(1, F1); preparedStatement.setString(3, M1); }else{ lDate=new java.sql.Date(new SIDateTime(F1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(1, lDate); lDate=new java.sql.Date(new SIDateTime(M1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(3, lDate); } preparedStatement.setInt(2, F2); preparedStatement.setInt(4, M3); } else if (FBMA.equals(lItemName)) { //FB-MA if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){ preparedStatement.setString(1, F1); preparedStatement.setString(4, M1); }else{ lDate=new java.sql.Date(new SIDateTime(F1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(1, lDate); lDate=new java.sql.Date(new SIDateTime(M1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(4, lDate); } preparedStatement.setInt(2, F2); preparedStatement.setInt(3, F3); preparedStatement.setInt(5, M2); } else if (FBMB.equals(lItemName)) { //FB-MB if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){ preparedStatement.setString(1, F1); preparedStatement.setString(4, M1); }else{ lDate=new java.sql.Date(new SIDateTime(F1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(1, lDate); lDate=new java.sql.Date(new SIDateTime(M1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(4, lDate); } preparedStatement.setInt(2, F2); preparedStatement.setInt(3, F3); preparedStatement.setInt(5, M2); preparedStatement.setInt(6, M3); } else if (FBMC.equals(lItemName)) { //FB-MC if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){ preparedStatement.setString(1, F1); preparedStatement.setString(4, M1); }else{ lDate=new java.sql.Date(new SIDateTime(F1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(1, lDate); lDate=new java.sql.Date(new SIDateTime(M1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(4, lDate); } preparedStatement.setInt(2, F2); preparedStatement.setInt(3, F3); preparedStatement.setInt(5, M3); } else if (FCMA.equals(lItemName)) { //FC-MA if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){ preparedStatement.setString(1, F1); preparedStatement.setString(3, M1); }else{ lDate=new java.sql.Date(new SIDateTime(F1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(1, lDate); lDate=new java.sql.Date(new SIDateTime(M1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(3, lDate); } preparedStatement.setInt(2, F3); preparedStatement.setInt(4, M2); } else if (FCMB.equals(lItemName)) { //FC-MB if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){ preparedStatement.setString(1, F1); preparedStatement.setString(3, M1); }else{ lDate=new java.sql.Date(new SIDateTime(F1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(1, lDate); lDate=new java.sql.Date(new SIDateTime(M1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(3, lDate); } //7.1.2 ST0241 削除 preparedStatement.setString(1, F1); preparedStatement.setInt(2, F3); //7.1.2 ST0241 削除 preparedStatement.setString(3, M1); preparedStatement.setInt(4, M2); preparedStatement.setInt(5, M3); } else if (FCMC.equals(lItemName)) { //FC-MC if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){ preparedStatement.setString(1, F1); preparedStatement.setString(3, M1); }else{ lDate=new java.sql.Date(new SIDateTime(F1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(1, lDate); lDate=new java.sql.Date(new SIDateTime(M1,SIConfig.SIDATE_FORMAT).getTime()); preparedStatement.setDate(3, lDate); } preparedStatement.setInt(2, F3); preparedStatement.setInt(4, M3); } } lResultSet = preparedStatement.executeQuery(); String lMailAddress=""; while (lResultSet.next()){ String[] lLineData=new String[7]; lLineData[0]=lResultSet.getString("Rfmanalysecode"); lLineData[1]=lResultSet.getString("Custcode"); lLineData[2]=SIUtil.CP932ToJIS(lResultSet.getString("CustName")); // 7.4.0 ST2060 修正 lLineData[3]=lResultSet.getString("OrderCode"); lLineData[4]=lResultSet.getString("Sumofprice"); lLineData[5]=lResultSet.getString("Amount"); lLineData[6]=SIDBUtil.getDateTime(lResultSet.getTimestamp("InitDateTime")); lCollection.add(lLineData); } } catch (NumberFormatException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); }catch(ParseException e){ e.printStackTrace(); }finally{ SIDBUtil.close(lResultSet, preparedStatement); } return lCollection; } /** * パーセントをグラフ表示する。 * 5パーセントで画像１つ * @param percent * @return */ public int getPerToGraf(String percent){ String str = percent.replace('%','0').replace('％','0'); int count = (int)Math.round(Float.parseFloat(str)/5); return count; } /** * R1 getter * @return */ public String getR1() { return this.r1; } /** * R2 getter * @return */ public String getR2() { return this.r2; } /** * R1 setter * @param string */ public void setR1(String string) { if (SIUtil.isNull(string)) string=""; this.r1=SIUtil.changeTo(string.trim(),this.encode); } /** * R2 setter * @param string */ public void setR2(String string) { if (SIUtil.isNull(string)) string=""; this.r2=SIUtil.changeTo(string.trim(),this.encode); } /** * F1 getter * @return */ public String getF1() { return this.f1; } /** * F2 getter * @return */ public String getF2() { return this.f2; } /** * F3 getter * @return */ public String getF3() { return this.f3; } /** * F1 setter * @param string */ public void setF1(String string) { if (SIUtil.isNull(string)) string=""; this.f1=SIUtil.changeTo(string.trim(),this.encode); } /** * F2 setter * @param string */ public void setF2(String string) { if (SIUtil.isNull(string)) string=""; this.f2=SIUtil.changeTo(string.trim(),this.encode); } /** * F3 setter * @param string */ public void setF3(String string) { if (SIUtil.isNull(string)) string=""; this.f3=SIUtil.changeTo(string.trim(),this.encode); } /** * M1 getter * @return */ public String getM1() { return this.m1; } /** * M2 getter * @return */ public String getM2() { return this.m2; } /** * M3 getter * @return */ public String getM3() { return this.m3; } /** * M1 setter * @param string */ public void setM1(String string) { if (SIUtil.isNull(string)) string=""; this.m1=SIUtil.changeTo(string.trim(),this.encode); } /** * M2 setter * @param string */ public void setM2(String string) { if (SIUtil.isNull(string)) string=""; this.m2=SIUtil.changeTo(string.trim(),this.encode); } /** * M3 setter * @param string */ public void setM3(String string) { if (SIUtil.isNull(string)) string=""; this.m3=SIUtil.changeTo(string.trim(),this.encode); } /** * 分析モード setter * @return */ public String getAnalyseMode() { return this.analyseMode; } /** * 分析モード getter * @param string */ public void setAnalyseMode(String string) { if (SIUtil.isNull(string)) string=""; this.analyseMode=SIUtil.changeTo(string.trim(),this.encode); } /** * 表示モード setter * @return */ public String getDispMode() { return this.dispMode; } /** * 表示モード getter * @param string */ public void setDispMode(String string) { if (SIUtil.isNull(string)) string="0"; this.dispMode=SIUtil.changeTo(string.trim(),this.encode); } }