/** * Created on 2003/10/30 * * 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.text.DecimalFormat; import java.text.NumberFormat; import java.util.Date; import java.util.Enumeration; import java.util.Hashtable; import java.util.Vector; import javax.servlet.http.HttpServletRequest; import jp.co.sint.basic.SIBasic; import jp.co.sint.basic.SILogin; 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.database.SITableCondition; import jp.co.sint.database.SITableConditionManager; 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.SIHTMLUtil; 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 UIMerchantListCond extends SIBasic { // ログ用のインスタンスの生成 private static Category log = Category.getInstance(SIConfig.SILOG4J_WEBSHOP_CATEGORY_NAME); // 会員レベル private String memberLevelCodeTxt = ""; // ショップコード private String shopCodeTxt = ""; // 商品コード private String cmdtyCodeFromTxt = "";// 7.2.0 ST0547 修正 private String cmdtyCodeToTxt = "";// 7.2.0 ST0547 追加 // 商品名 private String cmdtyNameTxt = ""; // 条件フラグ private String conditionFlg = "0"; // 条件SQL private String conditionSQL = ""; private String conditionMember = ""; public UIMerchantListCond() {} /** * @return */ // 7.2.0 ST0547 修正 ここから public String getCmdtyCodeFromTxt() { return cmdtyCodeFromTxt; } // 7.2.0 ST0547 修正 ここまで // 7.2.0 ST0547 追加 ここから /** * @return */ public String getCmdtyCodeToTxt() { return cmdtyCodeToTxt; } // 7.2.0 ST0547 追加 ここまで /** * @return */ public String getCmdtyNameTxt() { return cmdtyNameTxt; } /** * @return */ public String getShopCodeTxt() { return shopCodeTxt; } /** * @return */ public String getMemberLevelCodeTxt() { return memberLevelCodeTxt; } /** * @return */ public String getConditionFlg() { return conditionFlg; } /** * @return */ public String getConditionSQL() { return conditionSQL; } /** * @return */ public String getConditionMember() { return conditionMember; } /** * @param string */ public void setMemberLevelCodeTxt(String lMemberLevelCodeTxt) { if (SIUtil.isNull(lMemberLevelCodeTxt)) lMemberLevelCodeTxt = ""; this.memberLevelCodeTxt = SIUtil.changeTo(lMemberLevelCodeTxt.trim(), this.encode); } /** * @param string */ // 7.2.0 ST0547 修正 ここから public void setCmdtyCodeFromTxt(String lCmdtyCodeFromTxt) { if (SIUtil.isNull(lCmdtyCodeFromTxt)) lCmdtyCodeFromTxt = ""; this.cmdtyCodeFromTxt = SIUtil.changeTo(lCmdtyCodeFromTxt.trim(), this.encode); } // 7.2.0 ST0547 修正 ここまで // 7.2.0 ST0547 追加 ここから /** * @param string */ public void setCmdtyCodeToTxt(String lCmdtyCodeToTxt) { if (SIUtil.isNull(lCmdtyCodeToTxt)) lCmdtyCodeToTxt = ""; this.cmdtyCodeToTxt = SIUtil.changeTo(lCmdtyCodeToTxt.trim(), this.encode); } // 7.2.0 ST0547 追加 ここから /** * @param string */ public void setCmdtyNameTxt(String lCmdtyNameTxt) { if (SIUtil.isNull(lCmdtyNameTxt)) lCmdtyNameTxt = ""; this.cmdtyNameTxt = SIUtil.changeTo(lCmdtyNameTxt.trim(), this.encode); } /** * @param string */ public void setShopCodeTxt(String lShopCodeTxt) { if (SIUtil.isNull(lShopCodeTxt)) lShopCodeTxt = ""; this.shopCodeTxt = SIUtil.changeTo(lShopCodeTxt.trim(), this.encode); } /** * @param string */ public void setConditionFlg(String lConditionFlg) { if (lConditionFlg == null) lConditionFlg = ""; this.conditionFlg = lConditionFlg; } /** * @param string */ public void setConditionSQL(String lConditionSQL) { if (lConditionSQL == null) lConditionSQL = ""; this.conditionSQL = lConditionSQL; } /** * @param string */ public void setConditionMember(String lConditionMember) { if (lConditionMember == null) conditionMember = ""; this.conditionMember = lConditionMember; } /** * init 入力したデータから、このbeansを設定します。 * * @param lUrlParam * @return なし * @throws なし */ // 7.1.1 ST0236 修正 ここから public void init(SIURLParameter lUrlParam) { this.setEncode(SIConfig.SIENCODE_SHIFT_JIS); this.setMemberLevelCodeTxt((String) lUrlParam.getParam("memberLevelCodeTxt")); this.setShopCodeTxt((String) lUrlParam.getParam("shopCodeTxt")); this.setCmdtyCodeFromTxt((String) lUrlParam.getParam("cmdtyCodeFromTxt"));// 7.2.0 ST0547 修正 this.setCmdtyCodeToTxt((String) lUrlParam.getParam("cmdtyCodeToTxt"));// 7.2.0 ST0547 追加 this.setCmdtyNameTxt((String) lUrlParam.getParam("cmdtyNameTxt")); this.setActionNameTxt((String) lUrlParam.getParam("actionNameTxt")); this.setEditModeTxt((String) lUrlParam.getParam("editModeTxt")); this.setConditionFlg((String) lUrlParam.getParam("conditionFlg")); } // 7.1.1 ST0236 修正 ここまで /** * validate 入力したデータをチェックします。 不正なデータがある場合、エラーをオブジェクトに格納します。 そのオブジェクトは、エラーメッセージとして画面に表示されます。 * * @param HttpServletRequest ，Connection * @return true:エラーがない false:エラーが１つ以上ある * @throws なし */ public boolean validate(HttpServletRequest lRequest, Connection lConnection) { SILogin lLogin = SIHTMLUtil.getLogin(lRequest); lRequest.removeAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY); SICustomErrors errors = new SICustomErrors(); SITableConditionManager lConditionMan = new SITableConditionManager(); SITableConditionManager lConditionMember = new SITableConditionManager(); if (SIUtil.isNotNull(this.getMemberLevelCodeTxt()) && SICheckValid.checkValid(errors, "会員種別", this.getMemberLevelCodeTxt(), SICheckDataConf.SICHECK_DATA_DIGIT_TYPE) && SICheckValid.checkValid(errors, "会員種別", this.getMemberLevelCodeTxt(), SICheckDataConf.SICHECK_DATA_BYTE_LEN_WITHIN_TYPE, 6)) { lConditionMember.add(new SITableCondition("c", "MemberLevelCode", this.memberLevelCodeTxt, SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND)); } // ショップコード if (lLogin.isMall() && SIUtil.isNotNull(this.shopCodeTxt) && SICheckValid.checkValid(errors, "ショップコード", this.shopCodeTxt, SICheckDataConf.SICHECK_DATA_ALPHA_DIGIT_TYPE)) { lConditionMan.add(new SITableCondition("bbb", "ShopCode", this.shopCodeTxt, SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND)); } // 7.2.0 ST0547 修正 ここから // 商品コード if ((SIUtil.isNotNull(this.cmdtyCodeFromTxt) && SICheckValid.checkValid(errors, "親コードFrom", this.cmdtyCodeFromTxt, SICheckDataConf.SICHECK_DATA_ALPHA_DIGIT_TYPE))) { lConditionMan.add(new SITableCondition("bbb", "CmdtyCode", this.cmdtyCodeFromTxt, SIConfig.SICONDITION_TYPE_GREATER_EQUAL, SIConfig.SICONDITION_TYPE_AND)); } // 7.2.0 ST0547 修正 ここまで // 7.2.0 ST0547 追加 ここから if ((SIUtil.isNotNull(this.cmdtyCodeToTxt) && SICheckValid.checkValid(errors, "親コードTo", this.cmdtyCodeToTxt, SICheckDataConf.SICHECK_DATA_ALPHA_DIGIT_TYPE))) { lConditionMan.add(new SITableCondition("bbb", "CmdtyCode", this.cmdtyCodeToTxt, SIConfig.SICONDITION_TYPE_LESS_EQUAL, SIConfig.SICONDITION_TYPE_AND)); } // 7.2.0 ST0547 追加 ここまで // 商品名 if (SIUtil.isNotNull(this.cmdtyNameTxt) && SICheckValid.checkValid(errors, "商品名", this.cmdtyNameTxt, SICheckDataConf.SICHECK_DATA_ZENKAKU_TYPE)) { lConditionMan.add(new SITableCondition("bbb", "CmdtyName", this.cmdtyNameTxt, SIConfig.SICONDITION_TYPE_FRONTLIKE, SIConfig.SICONDITION_TYPE_AND));// 7.2.0 ST0547 修正 } // 条件文の設定 this.setConditionSQL(lConditionMan.getCondtionSQL()); this.setConditionMember(lConditionMember.getCondtionSQL()); if (!errors.isEmpty()) lRequest.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY, errors); return errors.isEmpty(); } /** * getLank * * @return Vector * @throws SIDBAccessException * @param */ public Vector getLank(Connection lConnection) throws SIDBAccessException { PreparedStatement preparedStatement = null; ResultSet lResultSet = null; StringBuffer lSqlBuf = new StringBuffer(); String[] aryLank = new String[4]; Vector vecLank = new Vector(); Vector vecLank2 = new Vector(); int arySum = 0; lSqlBuf.append(" SELECT A.MemberLevelName , COUNT(B.MemberLevelCode) as cntMemberLevelName "); lSqlBuf.append("FROM MemberLevelMtbl").append(SIDBMultiConf.SIALIAS_CURR_NAME).append("A LEFT OUTER JOIN"); lSqlBuf.append(" (SELECT c.memberlevelcode "); lSqlBuf.append(" FROM custtbl").append(SIDBMultiConf.SIALIAS_CURR_NAME).append("c "); lSqlBuf.append(" WHERE 1=1 "); if (this.conditionSQL.length() != 0) { // 検索条件にショップを入れる時 lSqlBuf.append(" AND c.custcode IN("); lSqlBuf.append(" SELECT DISTINCT aa.custcode"); // 7.2.0 ST1030 修正 ここから lSqlBuf.append(" FROM ").append(SIConfig.SIVIEW_ORDER_LATEST_NAME).append(SIDBMultiConf.SIALIAS_CURR_NAME).append("aa, "); // lSqlBuf.append(" FROM ordertbl").append(SIDBMultiConf.SIALIAS_CURR_NAME).append("aa, "); // 7.2.0 ST1030 修正 ここまで lSqlBuf.append(" (SELECT Distinct aaa.ordercode, aaa.deliverycode"); // 7.2.0 ST1030 修正ここから lSqlBuf.append(" FROM ").append(SIConfig.SIVIEW_ORDER_DELIVERY_LATEST_NAME).append(SIDBMultiConf.SIALIAS_CURR_NAME).append("aaa, "); lSqlBuf.append(SIConfig.SIVIEW_ORDER_DETAIL_LATEST_NAME).append(SIDBMultiConf.SIALIAS_CURR_NAME).append("bbb "); // lSqlBuf.append(" FROM orderdeliveryTbl").append(SIDBMultiConf.SIALIAS_CURR_NAME).append("aaa,"); // lSqlBuf.append(" orderdetailtbl").append(SIDBMultiConf.SIALIAS_CURR_NAME).append("bbb "); // 7.2.0 ST1030 修正 ここまで lSqlBuf.append(" WHERE aaa.ordercode=bbb.ordercode and aaa.deliverycode=bbb.deliverycode "); lSqlBuf.append(this.conditionSQL);// ショップ検索条件 lSqlBuf.append(" AND bbb.SHIPPMENTDATE IS NOT NULL )").append(SIDBMultiConf.SIALIAS_CURR_NAME).append("bb "); lSqlBuf.append(" WHERE aa.ordercode = bb.ordercode AND aa.status = 1 AND aa.RECEIPTDATE IS NOT NULL) "); } lSqlBuf.append(this.conditionMember);// メンバー区分 lSqlBuf.append(" )").append(SIDBMultiConf.SIALIAS_CURR_NAME).append("B "); lSqlBuf.append(" ON A.memberlevelcode=B.memberlevelcode "); lSqlBuf.append(" WHERE A.MemberLevelName IS NOT NULL GROUP BY A.MemberLevelName"); log.debug("getLank:lSqlBuf=" + lSqlBuf.toString()); try { preparedStatement = lConnection.prepareStatement(lSqlBuf.toString()); lResultSet = preparedStatement.executeQuery(); while (lResultSet.next()) { aryLank = new String[4]; aryLank[0] = SIUtil.CP932ToJIS(lResultSet.getString("MemberLevelName")); aryLank[1] = SIUtil.CP932ToJIS(lResultSet.getString("cntMemberLevelName")); arySum = arySum + lResultSet.getInt("cntMemberLevelName"); vecLank.addElement(aryLank); } if (arySum == 0) { arySum = 1; } Enumeration e = vecLank.elements(); // 小数点以下1桁のフォーマットを指定 NumberFormat nf = new DecimalFormat("0.0%"); // 小数点を表示しない NumberFormat nf2 = new DecimalFormat("0"); while (e.hasMoreElements()) { aryLank = new String[4]; aryLank = (String[]) e.nextElement(); aryLank[2] = String.valueOf(nf.format((float) Integer.parseInt(aryLank[1]) / arySum)); aryLank[3] = String.valueOf(nf2.format((float) Integer.parseInt(aryLank[1]) * 100 / 2 / arySum)); // 画像の個数 vecLank2.addElement(aryLank); } } catch (Exception ex) { throw new SIDBAccessException(ex); } finally { SIDBUtil.close(lResultSet, preparedStatement); } return vecLank2; } /** * getSite * * @return Vector * @throws SIDBAccessException * @param */ public Vector getSite(Connection lConnection) throws SIDBAccessException { PreparedStatement preparedStatement = null; ResultSet lResultSet = null; StringBuffer lSqlBuf = new StringBuffer(); String[] arySite = new String[4]; Vector vecSite = new Vector(); Vector vecSite2 = new Vector(); int arySum = 0; lSqlBuf.append(" SELECT A.HowToKnow , Count(B.HowToKnow) as cntHowToKnow FROM custtbl").append(SIDBMultiConf.SIALIAS_CURR_NAME).append(" A LEFT OUTER JOIN"); lSqlBuf.append(" (SELECT c.custcode, c.HowToKnow "); lSqlBuf.append(" FROM custtbl").append(SIDBMultiConf.SIALIAS_CURR_NAME).append("c "); lSqlBuf.append(" WHERE 1=1 "); if (this.conditionSQL.length() != 0) { // 検索条件にショップを入れる時 lSqlBuf.append(" AND c.custcode IN("); lSqlBuf.append(" SELECT DISTINCT aa.custcode"); // 7.2.0 ST1030 修正 ここから lSqlBuf.append(" FROM ").append(SIConfig.SIVIEW_ORDER_LATEST_NAME).append(SIDBMultiConf.SIALIAS_CURR_NAME).append("aa, "); // lSqlBuf.append(" FROM ordertbl").append(SIDBMultiConf.SIALIAS_CURR_NAME).append("aa, "); // 7.2.0 ST1030 修正 ここまで lSqlBuf.append(" (SELECT Distinct aaa.ordercode, aaa.deliverycode"); // 7.2.0 ST1030 修正ここから lSqlBuf.append(" FROM ").append(SIConfig.SIVIEW_ORDER_DELIVERY_LATEST_NAME).append(SIDBMultiConf.SIALIAS_CURR_NAME).append("aaa, "); lSqlBuf.append(SIConfig.SIVIEW_ORDER_DETAIL_LATEST_NAME).append(SIDBMultiConf.SIALIAS_CURR_NAME).append("bbb "); // lSqlBuf.append(" FROM orderdeliveryTbl").append(SIDBMultiConf.SIALIAS_CURR_NAME).append("aaa,"); // lSqlBuf.append(" orderdetailtbl").append(SIDBMultiConf.SIALIAS_CURR_NAME).append("bbb "); // 7.2.0 ST1030 修正 ここまで lSqlBuf.append(" WHERE aaa.ordercode=bbb.ordercode and aaa.deliverycode=bbb.deliverycode "); lSqlBuf.append(this.conditionSQL);// ショップ検索条件 lSqlBuf.append(" AND bbb.SHIPPMENTDATE IS NOT NULL )").append(SIDBMultiConf.SIALIAS_CURR_NAME).append("bb "); lSqlBuf.append(" WHERE aa.ordercode = bb.ordercode AND aa.status = 1 AND aa.RECEIPTDATE IS NOT NULL) "); } lSqlBuf.append(this.conditionMember);// メンバー区分 lSqlBuf.append(" AND c.HowToKnow IS NOT NULL )").append(SIDBMultiConf.SIALIAS_CURR_NAME).append("B "); lSqlBuf.append(" ON A.custcode=B.custcode "); lSqlBuf.append(" WHERE A.HowToKnow IS NOT NULL GROUP BY A.HowToKnow"); log.debug("getSite:lSqlBuf=" + lSqlBuf); try { preparedStatement = lConnection.prepareStatement(lSqlBuf.toString()); lResultSet = preparedStatement.executeQuery(); while (lResultSet.next()) { arySite = new String[4]; arySite[0] = SIUtil.CP932ToJIS(lResultSet.getString("HowToKnow")); arySite[1] = SIUtil.CP932ToJIS(lResultSet.getString("cntHowToKnow")); arySum = arySum + lResultSet.getInt("cntHowToKnow"); vecSite.addElement(arySite); } if (arySum == 0) { arySum = 1; } Enumeration e = vecSite.elements(); // 小数点以下1桁のフォーマットを指定 NumberFormat nf = new DecimalFormat("0.0%"); // 小数点を表示しない NumberFormat nf2 = new DecimalFormat("0"); while (e.hasMoreElements()) { arySite = new String[4]; arySite = (String[]) e.nextElement(); arySite[2] = String.valueOf(nf.format((float) Integer.parseInt(arySite[1]) / arySum)); arySite[3] = String.valueOf(nf2.format((float) Integer.parseInt(arySite[1]) * 100 / 2 / arySum)); // 画像の個数 vecSite2.addElement(arySite); } } catch (Exception ex) { throw new SIDBAccessException(ex); } finally { SIDBUtil.close(lResultSet, preparedStatement); } return vecSite2; } /** * getHobby * * @return Vector * @throws SIDBAccessException * @param */ public Vector getHobby(Connection lConnection) throws SIDBAccessException { PreparedStatement preparedStatement = null; ResultSet lResultSet = null; StringBuffer lSqlBuf = new StringBuffer(); StringBuffer countSql = new StringBuffer(); String[] aryHobby = new String[4]; Vector vecHobby = new Vector(); Vector vecHobby2 = new Vector(); int arySum = 0; lSqlBuf.append("SELECT A.HobbyName , COUNT(B.custcode) as cntHobbyName FROM custhobbytbl").append(SIDBMultiConf.SIALIAS_CURR_NAME).append("A LEFT OUTER JOIN "); lSqlBuf.append("(SELECT c.custcode FROM custtbl c WHERE 1=1 "); if (this.conditionSQL.length() != 0) { // 検索条件にショップを入れる時 lSqlBuf.append("AND c.custcode IN( "); lSqlBuf.append(" SELECT aa.custcode FROM ORDERLATESTVW aa, "); lSqlBuf.append(" (SELECT DISTINCT aaa.ordercode, aaa.deliverycode"); lSqlBuf.append(" FROM ORDERDELIVERYLATESTVW aaa, ORDERDETAILLATESTVW bbb "); lSqlBuf.append(" WHERE aaa.ordercode=bbb.ordercode and aaa.deliverycode=bbb.deliverycode "); lSqlBuf.append(this.conditionSQL);// ショップ検索条件 lSqlBuf.append(" AND bbb.SHIPPMENTDATE IS NOT NULL) bb "); lSqlBuf.append(" WHERE aa.ordercode = bb.ordercode AND aa.status = 1 AND aa.RECEIPTDATE IS NOT NULL) "); } lSqlBuf.append(this.conditionMember);// メンバー区分 lSqlBuf.append(" ) B "); lSqlBuf.append(" ON A.custcode=B.custcode "); lSqlBuf.append(" WHERE A.HobbyName IS NOT NULL GROUP BY A.HobbyName"); log.debug("getHobby:lSqlBuf=" + lSqlBuf); countSql.append("SELECT COUNT(*) FROM custtbl c WHERE c.custcode IN (SELECT custcode FROM custhobbytbl) "); if (this.conditionSQL.length() != 0) { // 検索条件にショップを入れる時 countSql.append("AND c.custcode IN( "); countSql.append(" SELECT aa.custcode FROM ORDERLATESTVW aa, "); countSql.append(" (SELECT DISTINCT aaa.ordercode, aaa.deliverycode"); countSql.append(" FROM ORDERDELIVERYLATESTVW aaa, ORDERDETAILLATESTVW bbb "); countSql.append(" WHERE aaa.ordercode=bbb.ordercode and aaa.deliverycode=bbb.deliverycode "); countSql.append(this.conditionSQL);// ショップ検索条件 countSql.append(" AND bbb.SHIPPMENTDATE IS NOT NULL) bb "); countSql.append(" WHERE aa.ordercode = bb.ordercode AND aa.status = 1 AND aa.RECEIPTDATE IS NOT NULL) "); } countSql.append(this.conditionMember);// メンバー区分 try { preparedStatement = lConnection.prepareStatement(lSqlBuf.toString()); lResultSet = preparedStatement.executeQuery(); while (lResultSet.next()) { aryHobby = new String[4]; aryHobby[0] = SIUtil.CP932ToJIS(lResultSet.getString("HobbyName")); aryHobby[1] = SIUtil.CP932ToJIS(lResultSet.getString("cntHobbyName")); //arySum = arySum + lResultSet.getInt("cntHobbyName"); vecHobby.addElement(aryHobby); } arySum = Integer.parseInt(SIDBUtil.getFirstData(lConnection, countSql.toString())); if (arySum == 0) { arySum = 1; } Enumeration e = vecHobby.elements(); // 小数点以下1桁のフォーマットを指定 NumberFormat nf = new DecimalFormat("0.0%"); // 小数点を表示しない NumberFormat nf2 = new DecimalFormat("0"); while (e.hasMoreElements()) { aryHobby = new String[4]; aryHobby = (String[]) e.nextElement(); aryHobby[2] = String.valueOf(nf.format((float) Integer.parseInt(aryHobby[1]) / arySum)); aryHobby[3] = String.valueOf(nf2.format((float) Integer.parseInt(aryHobby[1]) * 100 / 2 / arySum)); // 画像の個数 vecHobby2.addElement(aryHobby); } } catch (Exception ex) { throw new SIDBAccessException(ex); } finally { SIDBUtil.close(lResultSet, preparedStatement); } return vecHobby2; } /** * getJob * * @return Vector * @throws SIDBAccessException * @param */ public Vector getJob(Connection lConnection) throws SIDBAccessException { PreparedStatement preparedStatement = null; ResultSet lResultSet = null; StringBuffer lSqlBuf = new StringBuffer(); String[] aryJob = new String[4]; Vector vecJob = new Vector(); Vector vecJob2 = new Vector(); int arySum = 0; lSqlBuf.append(" SELECT A.job , Count(B.Job) as cntJob FROM custtbl").append(SIDBMultiConf.SIALIAS_CURR_NAME).append(" A LEFT OUTER JOIN"); lSqlBuf.append(" (SELECT c.custcode, c.Job "); lSqlBuf.append(" FROM custtbl").append(SIDBMultiConf.SIALIAS_CURR_NAME).append("c "); lSqlBuf.append(" WHERE 1=1 "); if (this.conditionSQL.length() != 0) { // 検索条件にショップを入れる時 lSqlBuf.append(" AND c.custcode IN("); lSqlBuf.append(" SELECT DISTINCT aa.custcode"); // 7.2.0 ST1030 修正 ここから lSqlBuf.append(" FROM ").append(SIConfig.SIVIEW_ORDER_LATEST_NAME).append(SIDBMultiConf.SIALIAS_CURR_NAME).append("aa, "); // lSqlBuf.append(" FROM ordertbl").append(SIDBMultiConf.SIALIAS_CURR_NAME).append("aa, "); // 7.2.0 ST1030 修正 ここまで lSqlBuf.append(" (SELECT Distinct aaa.ordercode, aaa.deliverycode"); // 7.2.0 ST1030 修正ここから lSqlBuf.append(" FROM ").append(SIConfig.SIVIEW_ORDER_DELIVERY_LATEST_NAME).append(SIDBMultiConf.SIALIAS_CURR_NAME).append("aaa, ").append( SIConfig.SIVIEW_ORDER_DETAIL_LATEST_NAME).append(SIDBMultiConf.SIALIAS_CURR_NAME).append("bbb "); // lSqlBuf.append(" FROM orderdeliveryTbl").append(SIDBMultiConf.SIALIAS_CURR_NAME).append("aaa, orderdetailtbl").append(SIDBMultiConf.SIALIAS_CURR_NAME).append("bbb "); // 7.2.0 ST1030 修正 ここまで lSqlBuf.append(" WHERE aaa.ordercode=bbb.ordercode and aaa.deliverycode=bbb.deliverycode "); lSqlBuf.append(this.conditionSQL);// ショップ検索条件 lSqlBuf.append(" AND bbb.SHIPPMENTDATE IS NOT NULL )").append(SIDBMultiConf.SIALIAS_CURR_NAME).append("bb "); lSqlBuf.append(" WHERE aa.ordercode = bb.ordercode AND aa.status = 1 AND aa.RECEIPTDATE IS NOT NULL) "); } lSqlBuf.append(this.conditionMember);// メンバー区分 lSqlBuf.append(" AND c.job IS NOT NULL )").append(SIDBMultiConf.SIALIAS_CURR_NAME).append("B "); lSqlBuf.append(" ON A.custcode=B.custcode "); lSqlBuf.append(" WHERE A.job IS NOT NULL GROUP BY A.job"); log.debug("getJob:lSqlBuf=" + lSqlBuf); try { preparedStatement = lConnection.prepareStatement(lSqlBuf.toString()); lResultSet = preparedStatement.executeQuery(); while (lResultSet.next()) { aryJob = new String[4]; aryJob[0] = SIUtil.CP932ToJIS(lResultSet.getString("Job")); aryJob[1] = SIUtil.CP932ToJIS(lResultSet.getString("cntJob")); arySum = arySum + lResultSet.getInt("cntJob"); vecJob.addElement(aryJob); } if (arySum == 0) { arySum = 1; } Enumeration e = vecJob.elements(); // 小数点以下1桁のフォーマットを指定 NumberFormat nf = new DecimalFormat("0.0%"); // 小数点を表示しない NumberFormat nf2 = new DecimalFormat("0"); while (e.hasMoreElements()) { aryJob = new String[4]; aryJob = (String[]) e.nextElement(); aryJob[2] = String.valueOf(nf.format((float) Integer.parseInt(aryJob[1]) / arySum)); aryJob[3] = String.valueOf(nf2.format((float) Integer.parseInt(aryJob[1]) * 100 / 2 / arySum)); // 画像の個数 vecJob2.addElement(aryJob); } } catch (Exception ex) { throw new SIDBAccessException(ex); } finally { SIDBUtil.close(lResultSet, preparedStatement); } return vecJob2; } /** * getGender * * @return Hashtable * @throws SIDBAccessException * @param Connection */ public Hashtable getGender(Connection lConnection) throws SIDBAccessException { PreparedStatement preparedStatement = null; ResultSet lResultSet = null; StringBuffer lSqlBuf = new StringBuffer(); Hashtable hashGender = new Hashtable(); lSqlBuf.append(" SELECT c.custcode, c.sex "); lSqlBuf.append(" FROM custtbl").append(SIDBMultiConf.SIALIAS_CURR_NAME).append("c "); lSqlBuf.append(" WHERE 1=1 "); if (this.conditionSQL.length() != 0) { // 検索条件にショップを入れる時 lSqlBuf.append(" AND c.custcode IN("); lSqlBuf.append(" SELECT DISTINCT aa.custcode"); // 7.2.0 ST1030 修正 ここから lSqlBuf.append(" FROM ").append(SIConfig.SIVIEW_ORDER_LATEST_NAME).append(SIDBMultiConf.SIALIAS_CURR_NAME).append("aa, "); // lSqlBuf.append(" FROM ordertbl").append(SIDBMultiConf.SIALIAS_CURR_NAME).append("aa, "); // 7.2.0 ST1030 修正 ここまで lSqlBuf.append(" (SELECT Distinct aaa.ordercode, aaa.deliverycode"); // 7.2.0 ST1030 修正ここから lSqlBuf.append(" FROM ").append(SIConfig.SIVIEW_ORDER_DELIVERY_LATEST_NAME).append(SIDBMultiConf.SIALIAS_CURR_NAME).append("aaa, ").append( SIConfig.SIVIEW_ORDER_DETAIL_LATEST_NAME).append(SIDBMultiConf.SIALIAS_CURR_NAME).append("bbb "); // lSqlBuf.append(" FROM orderdeliveryTbl").append(SIDBMultiConf.SIALIAS_CURR_NAME).append("aaa, orderdetailtbl").append(SIDBMultiConf.SIALIAS_CURR_NAME).append("bbb "); // 7.2.0 ST1030 修正 ここまで lSqlBuf.append(" WHERE aaa.ordercode=bbb.ordercode and aaa.deliverycode=bbb.deliverycode "); lSqlBuf.append(this.conditionSQL);// ショップ検索条件 lSqlBuf.append(" AND bbb.SHIPPMENTDATE IS NOT NULL )").append(SIDBMultiConf.SIALIAS_CURR_NAME).append("bb "); lSqlBuf.append(" WHERE aa.ordercode = bb.ordercode AND aa.status = 1 AND aa.RECEIPTDATE IS NOT NULL) "); } lSqlBuf.append(this.conditionMember);// メンバー区分 lSqlBuf.append(" AND c.sex IS NOT NULL "); log.debug("getGender:lSqlBuf=" + lSqlBuf); try { preparedStatement = lConnection.prepareStatement(lSqlBuf.toString()); lResultSet = preparedStatement.executeQuery(); int man = 0; int numOfMan = 0; int woman = 0; int numOfWoman = 0; int genderSum = 0; while (lResultSet.next()) { if (lResultSet.getString("sex").trim().equals("1")) { hashGender.put("man", new Integer(man++)); } else if (lResultSet.getString("sex").trim().equals("2")) { hashGender.put("woman", new Integer(woman++)); } } hashGender.put("numOfMan", new Integer(man)); hashGender.put("numOfWoman", new Integer(woman)); genderSum = man + woman; if (genderSum == 0) { genderSum = 1; } // 小数点以下1桁のフォーマットを指定 NumberFormat nf = new DecimalFormat("0.0%"); // 小数点を表示しない NumberFormat nf2 = new DecimalFormat("0"); hashGender.put("genderManPercent", String.valueOf(nf.format((float) man / genderSum))); hashGender.put("genderWomanPercent", String.valueOf(nf.format((float) woman / genderSum))); hashGender.put("man", new Integer(nf2.format((float) man * 100 / 2 / genderSum))); // 画像の個数 hashGender.put("woman", new Integer(nf2.format((float) woman * 100 / 2 / genderSum))); // 画像の個数 hashGender.put("genderSum", new Integer(genderSum)); } catch (Exception ex) { throw new SIDBAccessException(ex); } finally { SIDBUtil.close(lResultSet, preparedStatement); } return hashGender; } /** * getCustYearsOld * * @return Hashtable * @throws SIDBAccessException * @param Connection */ public Hashtable getCustYearsOld(Connection lConnection) throws SIDBAccessException { PreparedStatement preparedStatement = null; ResultSet lResultSet = null; StringBuffer lSqlBuf = new StringBuffer(); Hashtable hashMerchantYear = new Hashtable(); lSqlBuf.append(" SELECT c.custcode, c.birthdate "); lSqlBuf.append(" FROM custtbl").append(SIDBMultiConf.SIALIAS_CURR_NAME).append("c "); lSqlBuf.append(" WHERE 1=1 "); if (this.conditionSQL.length() != 0) { // 検索条件にショップを入れる時 lSqlBuf.append(" AND c.custcode IN("); lSqlBuf.append(" SELECT DISTINCT aa.custcode"); // 7.2.0 ST1030 修正 ここから lSqlBuf.append(" FROM ").append(SIConfig.SIVIEW_ORDER_LATEST_NAME).append(SIDBMultiConf.SIALIAS_CURR_NAME).append("aa, "); // lSqlBuf.append(" FROM ordertbl").append(SIDBMultiConf.SIALIAS_CURR_NAME).append("aa, "); // 7.2.0 ST1030 修正 ここまで lSqlBuf.append(" (SELECT Distinct aaa.ordercode, aaa.deliverycode"); // 7.2.0 ST1030 修正ここから lSqlBuf.append(" FROM ").append(SIConfig.SIVIEW_ORDER_DELIVERY_LATEST_NAME).append(SIDBMultiConf.SIALIAS_CURR_NAME).append("aaa, ").append( SIConfig.SIVIEW_ORDER_DETAIL_LATEST_NAME).append(SIDBMultiConf.SIALIAS_CURR_NAME).append("bbb "); // lSqlBuf.append(" FROM orderdeliveryTbl").append(SIDBMultiConf.SIALIAS_CURR_NAME).append("aaa, orderdetailtbl").append(SIDBMultiConf.SIALIAS_CURR_NAME).append("bbb "); // 7.2.0 ST1030 修正 ここまで lSqlBuf.append(" WHERE aaa.ordercode=bbb.ordercode and aaa.deliverycode=bbb.deliverycode "); lSqlBuf.append(this.conditionSQL);// ショップ検索条件 lSqlBuf.append(" AND bbb.SHIPPMENTDATE IS NOT NULL )").append(SIDBMultiConf.SIALIAS_CURR_NAME).append("bb "); lSqlBuf.append(" WHERE aa.ordercode = bb.ordercode AND aa.status = 1 AND aa.RECEIPTDATE IS NOT NULL) "); } lSqlBuf.append(this.conditionMember);// メンバー区分 lSqlBuf.append(" AND c.birthdate IS NOT NULL "); log.debug("getCustYearsOld:lSqlBuf=" + lSqlBuf.toString()); try { preparedStatement = lConnection.prepareStatement(lSqlBuf.toString()); lResultSet = preparedStatement.executeQuery(); SIDateTime dateTime = new SIDateTime(); int year01 = 0; int year02 = 0; int year03 = 0; int year04 = 0; int year05 = 0; int wk_year = 0; while (lResultSet.next()) { wk_year = this.getToYear(lResultSet.getDate("birthDate")); // 0~12 if (0 <= wk_year && wk_year <= 12) { year01++; } // 13~19 else if (12 < wk_year && wk_year <= 19) { year02++; } // 20~29 else if (19 < wk_year && wk_year <= 29) { year03++; } // 30~49 else if (29 < wk_year && wk_year <= 49) { year04++; } // 50~ else if (wk_year > 49) { year05++; } else { // birthDate Null値の場合 } } int yearSum = year01 + year02 + year03 + year04 + year05; if (yearSum == 0) { yearSum = 1; } // 小数点以下1桁のフォーマットを指定 NumberFormat nf = new DecimalFormat("0.0%"); // 小数点を表示しない NumberFormat nf2 = new DecimalFormat("0"); hashMerchantYear.put("numOfYear01", new Integer(year01)); hashMerchantYear.put("numOfYear02", new Integer(year02)); hashMerchantYear.put("numOfYear03", new Integer(year03)); hashMerchantYear.put("numOfYear04", new Integer(year04)); hashMerchantYear.put("numOfYear05", new Integer(year05)); hashMerchantYear.put("year01Percent", String.valueOf(nf.format((float) year01 / yearSum))); hashMerchantYear.put("year02Percent", String.valueOf(nf.format((float) year02 / yearSum))); hashMerchantYear.put("year03Percent", String.valueOf(nf.format((float) year03 / yearSum))); hashMerchantYear.put("year04Percent", String.valueOf(nf.format((float) year04 / yearSum))); hashMerchantYear.put("year05Percent", String.valueOf(nf.format((float) year05 / yearSum))); hashMerchantYear.put("year01", new Integer(nf2.format((float) year01 * 100 / 2 / yearSum))); // 画像の個数 hashMerchantYear.put("year02", new Integer(nf2.format((float) year02 * 100 / 2 / yearSum))); // 画像の個数 hashMerchantYear.put("year03", new Integer(nf2.format((float) year03 * 100 / 2 / yearSum))); // 画像の個数 hashMerchantYear.put("year04", new Integer(nf2.format((float) year04 * 100 / 2 / yearSum))); // 画像の個数 hashMerchantYear.put("year05", new Integer(nf2.format((float) year05 * 100 / 2 / yearSum))); // 画像の個数 } catch (Exception ex) { throw new SIDBAccessException(ex); } finally { SIDBUtil.close(lResultSet, preparedStatement); } return hashMerchantYear; } /** * getToYear * * @return int * @throws * @param Date */ public int getToYear(Date pDateTime) { if (pDateTime == null) return -1; SIDateTime currDate = new SIDateTime(); SIDateTime birthDate = new SIDateTime(pDateTime); int yearsOld = 0; yearsOld = currDate.getYear() - birthDate.getYear(); if (currDate.getMonth() < birthDate.getMonth()) { yearsOld = yearsOld - 1; } else if (currDate.getMonth() == birthDate.getMonth()) { if (currDate.getDay() < birthDate.getDay()) { yearsOld = yearsOld - 1; } } return yearsOld; } public String[] getAryNameYearsOld() { String[] aryYearsOld = { "12歳以下", "13 - 19歳", "20 - 29歳", "30 - 49歳", "50歳以上" }; return aryYearsOld; } public String[] getAryNameGender() { String[] aryGender = { "男性", "女性" }; return aryGender; } }