/**
* Copyright (c) 2003-2004 System Integrator Corporation.
* All Rights Reserved.
*/
package jp.co.sint.tools;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.net.URLDecoder;
import java.net.URLEncoder;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Iterator;
import java.util.LinkedHashMap;
import javax.servlet.http.Cookie;
import javax.servlet.http.HttpServletRequest;
import jp.co.sint.basic.SIKeepDetail;
import jp.co.sint.basic.SINameValue;
import jp.co.sint.basic.SIOrder;
import jp.co.sint.basic.SIStoreStock;
import jp.co.sint.beans.front.UIUserLogin;
import jp.co.sint.beans.mallmgr.UIRegOrder;
import jp.co.sint.config.SIConfig;
//import jp.co.sint.config.SIDBMultiConf;
import jp.co.sint.config.SIFlagConf;
import jp.co.sint.database.SIDBAccessException;
import jp.co.sint.database.SIDBUtil;
import jp.co.sint.database.SIDeleteRec;
import jp.co.sint.database.SIDuplicateKeyException;
import jp.co.sint.database.SIInsertRec;
import jp.co.sint.database.SIModifyRec;
import jp.co.sint.database.SISpcType;
import jp.co.sint.mail.SISendMail;
import org.apache.log4j.Category;
/**
* @version $Id : UIRegNewOrderCmdty.java,v 1.0 Exp $
* @author : Tsuyoshi Yagi
* Description :BG関連のライブラリ
*
* History
*
*
* Author Date Reason
*
* ============ ========== ===========================
* Tsuyoshi Yagi 2005/08/12 Original
*/
public class SIBGUtil {
// ログ用のインスタンスの生成
private static Category log = Category.getInstance(SIConfig.SILOG4J_WEBSHOP_CATEGORY_NAME);
/**
* getBranch 親コード、在庫コードより在庫を保有する支店名を取得
*
* @param lConnection
* @param cmdtyCode
* @param individualCode
* @return
* @throws SIDBAccessException
*/
public static String getBranch(Connection lConnection, String cmdtyCode, String individualCode) throws SIDBAccessException {
Statement lStatement = null;
ResultSet lResultSet = null;
String res = "";
StringBuffer str = new StringBuffer();
str.append("SELECT b.branchname FROM storestocktbl a, branchtbl b ");
str.append("WHERE a.branchcode=b.branchcode ");
str.append("AND a.cmdtycode=").append(SIDBUtil.SQL2Str(cmdtyCode," "));
str.append("AND a.individualcode=").append(SIDBUtil.SQL2Str(individualCode," "));
str.append("AND a.amount::numeric>0 AND b.branchflg='0' ");
str.append("ORDER BY a.salespriorityflg,b.prioritybase,a.branchcode");
// 実行
try {
lStatement = lConnection.createStatement();
lResultSet = lStatement.executeQuery(str.toString());
StringBuffer buff = new StringBuffer();
int cnt = 0;
// 商品レコードのセットの作成
while (lResultSet.next()) {
if (cnt > 0) {
buff.append(", ");
}
buff.append(lResultSet.getString("branchName"));
cnt++;
}
res = buff.toString();
} catch (Exception ex) {
throw new SIDBAccessException(ex);
} finally {
SIDBUtil.close(lResultSet, lStatement);
}
return res;
}
// EDBTG003-00 nagayoshi add start
public static String getCmdtyFullName(Connection lConnection, String lCmdtyCode, String lIndividualCode) throws SIDBAccessException {
return getCmdtyFullName(lConnection, lCmdtyCode, lIndividualCode, "");
}
// EDBTG003-00 nagayoshi add end
/**
* getCmdtyFullName 親コード、在庫コードより色と中古・新品区分を含む商品名を取得
*
* @param lConnection コネクション
* @param lCmdtyCode 親コード
* @param lIndividualCode 在庫コード
* @param lSetDetailFlg 明細区分
* @return cmdtyFullName 色と中古・新品区分を含む商品名
* @throws SIDBAccessException
*/
// EDBTG003-00 nagayoshi mod start
// public static String getCmdtyFullName(Connection lConnection, String lCmdtyCode, String lIndividualCode) throws SIDBAccessException {
public static String getCmdtyFullName(Connection lConnection, String lCmdtyCode, String lIndividualCode, String lSetDetailFlg) throws SIDBAccessException {
// EDBTG003-00 nagayoshi mod start
Statement lStatement = null;
ResultSet lResultSet = null;
String lCmdtyName = "";
String lColorName = "";
int lconsignGoodsFlg = 0;
int lUsedNewFlg;
StringBuffer lSqlBuf = new StringBuffer();
lSqlBuf.append("SELECT c.cmdtyname, a.colorname, b.usednewflg,b.consignGoodsFlg ");
lSqlBuf.append("FROM colortbl a, ");
lSqlBuf.append("individualtbl b ");
lSqlBuf.append("LEFT OUTER JOIN cmdtymtbl c ");
lSqlBuf.append("ON b.cmdtycode=c.cmdtycode ");
lSqlBuf.append("WHERE a.colorcode=c.colorcode ");
lSqlBuf.append("AND b.individualcode=").append(SIDBUtil.SQL2Str(lIndividualCode," "));
lSqlBuf.append("AND b.cmdtycode=").append(SIDBUtil.SQL2Str(lCmdtyCode));
// 実行
try {
lStatement = lConnection.createStatement();
lResultSet = lStatement.executeQuery(lSqlBuf.toString());
String lCmdtyFullName = "";
String lUsedNewName = "";
while (lResultSet.next()) {
lCmdtyName = lResultSet.getString("cmdtyname");
lColorName = lResultSet.getString("colorname");
lUsedNewFlg = lResultSet.getInt("usednewflg");
lconsignGoodsFlg = lResultSet.getInt("consignGoodsFlg");
if (lUsedNewFlg == 1 || lUsedNewFlg == 6 || lUsedNewFlg == 7) {// 新品
lUsedNewName = "新品";
} else if (lUsedNewFlg == 2 || lUsedNewFlg == 4) {// 新古OUTLET
lUsedNewName = "新古OUTLET";
} else if (lUsedNewFlg == 3) {// BG認定中古
lUsedNewName = "BG認定中古";
} else {// 中古
lUsedNewName = "中古";
}
if (lconsignGoodsFlg == 1) {
lUsedNewName = lUsedNewName + "[委託]";
}
// EDBTG003-00 nagayoshi add start
if (SIUtil.isNotNull(lSetDetailFlg) && lSetDetailFlg.equals(SIConfig.SET_DETAIL_FLG_APPENDED)) {
if (SIUtil.isNotNull(lUsedNewName)) {
lUsedNewName = "・";
}
lUsedNewName = "添付品";
}
// EDBTG003-00 nagayoshi add end
if (SIUtil.isNotNull(lCmdtyName)) {
lCmdtyFullName = lCmdtyFullName + lCmdtyName;
}
String str = "";
if (SIUtil.isNotNull(lColorName) && SIUtil.isNotNull(lUsedNewName)) {
if (SIUtil.isNull(str)) {
str = str + "(";
}
str = str + lColorName + "・" + lUsedNewName;
} else if (SIUtil.isNotNull(lColorName) || SIUtil.isNotNull(lUsedNewName)) {
if (SIUtil.isNull(str)) {
str = str + "(";
}
str = str + lColorName + lUsedNewName;
}
if (SIUtil.isNotNull(str)) {
str = str + ")";
}
lCmdtyFullName = lCmdtyFullName + str;
}
return lCmdtyFullName;
} catch (Exception ex) {
throw new SIDBAccessException(ex);
} finally {
SIDBUtil.close(lResultSet, lStatement);
}
}
/**
* getUsednewFlg 親コード、在庫コードより新品区分を取得
*
* @param lConnection コネクション
* @param lCmdtyCode 親コード
* @param lIndividualCode 在庫コード
* @return cmdtyFullName 色と中古・新品区分を含む商品名
* @throws SIDBAccessException
* @throws SIDBAccessException
*/
public static String getUsednewFlg(Connection lConnection, String lCmdtyCode, String lIndividualCode) {
Statement lStatement = null;
ResultSet lResultSet = null;
int lUsedNewFlg = 0;
int lconsignGoodsFlg = 0;
String lUsedNewName = "";
StringBuffer lSqlBuf = new StringBuffer();
lSqlBuf.append("SELECT a.usednewflg,a.consignGoodsFlg ");
lSqlBuf.append("FROM individualtbl a ");
lSqlBuf.append("WHERE a.individualcode=").append(SIDBUtil.SQL2Str(lIndividualCode," "));
lSqlBuf.append("AND a.cmdtycode =").append(SIDBUtil.SQL2Str(lCmdtyCode));
try {
lStatement = lConnection.createStatement();
lResultSet = lStatement.executeQuery(lSqlBuf.toString());
while (lResultSet.next()) {
lconsignGoodsFlg = lResultSet.getInt("consignGoodsFlg");
lUsedNewFlg = lResultSet.getInt("usednewflg");
if (lUsedNewFlg == 6) lUsedNewFlg = 1;
lUsedNewName = SIFlagConf.SIFLAG_USED_NEW_FLG_LIST_NAME[lUsedNewFlg][0];
if (lconsignGoodsFlg == 1) {
lUsedNewName = lUsedNewName + "[委託]";
}
}
} catch (Exception ex) {
ex.getStackTrace();
} finally {
SIDBUtil.close(lResultSet, lStatement);
}
return lUsedNewName;
}
/**
* getDBParameter 指定したテーブルの指定した列の値を取得
*
* @param lConnection コネクション
* @param tableName テーブル名
* @param conditionColName 条件用列名
* @param targetColName 取得用列名
* @param condition 条件
* @return dbParam 取得DB値
* @throws SIDBAccessException
*/
public static String getDBParameter(Connection lConnection, String tableName, String targetColName, String conditionColName, String condition) throws SIDBAccessException {
Statement lStatement = null;
ResultSet lResultSet = null;
StringBuffer lSqlBuf = new StringBuffer();
String dbParam = "";
try {
lSqlBuf.append("SELECT ").append(targetColName).append(" FROM ").append(tableName);
lSqlBuf.append(" WHERE ").append(conditionColName).append(" = ").append(SIDBUtil.SQL2Str(condition));
log.debug("getDBParameter:lSqlBuf=" + lSqlBuf.toString());
lStatement = lConnection.createStatement();
lResultSet = lStatement.executeQuery(lSqlBuf.toString());
if (lResultSet.next()) dbParam = lResultSet.getString(targetColName);
} catch (Exception ex) {
throw new SIDBAccessException(ex);
} finally {
SIDBUtil.close(lResultSet, lStatement);
}
return dbParam;
}
/**
* getBranchStockTxt 親コード、在庫コードより支店別在庫状況を取得
*
* @param lConnection コネクション
* @param lCmdtyCode 親コード
* @param lIndividualCode 在庫コード
* @param lUnitName 商品の単位
* @param lSplitter 分割用文字列
* @param lListFlg 支店リスト作成用フラグ
* @return 支店別在庫状況
* @throws SIDBAccessException
*/
public static String getBranchStockTxt(Connection lConnection, String lCmdtyCode, String lIndividualCode, String lUnitName, String lSplitter) throws SIDBAccessException {
return getBranchStockTxt(lConnection, lCmdtyCode, lIndividualCode, lUnitName, lSplitter, false);
}
public static String getBranchStockTxt(Connection lConnection, String lCmdtyCode, String lIndividualCode, String lUnitName, String lSplitter, boolean lListFlg)
throws SIDBAccessException {
Statement lStatement = null;
ResultSet lResultSet = null;
StringBuffer lSqlBuf = new StringBuffer();
StringBuffer lStockBuf = new StringBuffer();
try {
lSqlBuf.append("SELECT b.BranchName,a.Amount ");
lSqlBuf.append("FROM StoreStocktbl a,Branchtbl b,CmdtyUnitTbl c ");
lSqlBuf.append("WHERE a.CmdtyCode=").append(SIDBUtil.SQL2Str(lCmdtyCode," "));
lSqlBuf.append("AND a.IndividualCode=").append(SIDBUtil.SQL2Str(lIndividualCode," "));
lSqlBuf.append("AND a.BranchCode=b.BranchCode ");
lSqlBuf.append("AND a.IndividualCode=c.IndividualCode ");
lSqlBuf.append("AND CASE WHEN a.BranchCode IN ('16','19') THEN false ");
lSqlBuf.append(" WHEN c.DeliveryTypeCode='1' AND c.UsedNewFlg IN ('1','6','7') AND b.BranchCode IN ('1','2','3','4','5','6','7','8','9','10','11','12') THEN false ");
lSqlBuf.append(" ELSE true END ");
lSqlBuf.append("AND a.Amount!='0' ");
lSqlBuf.append("ORDER BY a.SalesPriorityFlg,b.PriorityBase,a.BranchCode");
log.debug("getBranchStockTxt:lSqlBuf=" + lSqlBuf.toString());
lStatement = lConnection.createStatement();
lResultSet = lStatement.executeQuery(lSqlBuf.toString());
while (lResultSet.next()) {
if (lListFlg) {
lStockBuf.append(lResultSet.getString("BranchName"));
if (!lResultSet.isLast()) lStockBuf.append(lSplitter);
} else {
lStockBuf.append(lResultSet.getString("BranchName")).append(" ");
lStockBuf.append(lResultSet.getString("Amount")).append(lUnitName);
if (!lResultSet.isLast()) lStockBuf.append(lSplitter);
}
}
} catch (Exception ex) {
throw new SIDBAccessException(ex);
} finally {
SIDBUtil.close(lResultSet, lStatement);
}
return lStockBuf.toString();
}
/**
* getBGPriceDispFlg 親コード、在庫コードよりBG特価表示の是非を取得
*
* @param lConnection コネクション
* @param lCmdtyCode 親コード
* @param lIndividualCode 在庫コード
* @return bgPriceDispFlg BG特価表示の是非
* @throws SIDBAccessException
*/
public static boolean getBGPriceDispFlg(Connection lConnection, String lCmdtyCode, String lIndividualCode) throws SIDBAccessException {
Statement lStatement = null;
ResultSet lResultSet = null;
StringBuffer lSqlBuf = new StringBuffer();
boolean bgPriceDispFlg = true;
try {
lSqlBuf.append("SELECT UsedNewFlg,BGPriceDispFlg ");
lSqlBuf.append("FROM cmdtyvw ");
lSqlBuf.append("WHERE CmdtyCode=").append(SIDBUtil.SQL2Str(lCmdtyCode," "));
lSqlBuf.append("AND IndividualCode=").append(SIDBUtil.SQL2Str(lIndividualCode));
log.debug("getBGPriceDispFlg:lSqlBuf=" + lSqlBuf.toString());
lStatement = lConnection.createStatement();
lResultSet = lStatement.executeQuery(lSqlBuf.toString());
if (lResultSet.next()) {
if (lResultSet.getString("BGPriceDispFlg").equals("1")) bgPriceDispFlg = false;
}
} catch (Exception ex) {
throw new SIDBAccessException(ex);
} finally {
SIDBUtil.close(lResultSet, lStatement);
}
return bgPriceDispFlg;
}
/**
* getOrderLocaton 受注コード、親コード、在庫コードより出荷状況を取得
*
* @param lConnection コネクション
* @param lOrderCode 受注コード
* @param lCmdtyCode 親コード
* @param lIndividualCode 在庫コード
* @return 出荷状況
* @throws SIDBAccessException
*/
//EDBTG003-00 elecs-matsushima mod start
// public static String getOrderLocation(Connection lConnection, String lOrderCode, String lCmdtyCode, String lIndividualCode) throws SIDBAccessException {
public static String getOrderLocation(Connection lConnection, String lOrderCode, String lCmdtyCode, String lIndividualCode, String orderDetailCode) throws SIDBAccessException {
//EDBTG003-00 elecs-matsushima mod end
Statement lStatement = null;
ResultSet lResultSet = null;
StringBuffer lSqlBuf = new StringBuffer();
StringBuffer lOrderLocationBuf = new StringBuffer();
try {
lSqlBuf.append("SELECT b.BranchName,a.OrderAlLocationNumber ");
lSqlBuf.append("FROM OrderAlLocationInfotbl a, ");
lSqlBuf.append("Branchtbl b ");
lSqlBuf.append("WHERE a.OrderCode=").append(SIDBUtil.SQL2Str(lOrderCode," "));
lSqlBuf.append("AND a.CmdtyCode=").append(SIDBUtil.SQL2Str(lCmdtyCode," "));
lSqlBuf.append("AND a.IndividualCode=").append(SIDBUtil.SQL2Str(lIndividualCode," "));
lSqlBuf.append("AND a.detailcode=").append(SIDBUtil.SQL2Str(orderDetailCode," ")); //EDBTG003-00 elecs-matsushima add
lSqlBuf.append("AND a.BranchCode=b.BranchCode ");
lSqlBuf.append("ORDER BY a.BranchCode ASC");
log.debug("getOrderLocation:lSqlBuf=" + lSqlBuf.toString());
lStatement = lConnection.createStatement();
lResultSet = lStatement.executeQuery(lSqlBuf.toString());
while (lResultSet.next()) {
lOrderLocationBuf.append(lResultSet.getString("BranchName"));
lOrderLocationBuf.append(lResultSet.getString("OrderAlLocationNumber"));
if (!lResultSet.isLast()) lOrderLocationBuf.append(",");
}
} catch (Exception ex) {
throw new SIDBAccessException(ex);
} finally {
SIDBUtil.close(lResultSet, lStatement);
}
return lOrderLocationBuf.toString();
}
/**
* getCmdtyCodeList 親コードリストを戻す
*
* @param connection DBへのコネクション
* @param sql 結果に親コードを持つクエリ文
* @return レコード数
* @throws SIDBAccessException
*/
public static String getCmdtyCodeList(Connection lConnection, String lSql) throws SIDBAccessException {
Statement lStatement = null;
ResultSet lResultSet = null;
String cmdtyCodeList = "";
try {
log.debug("getCmdtyCodeList:lSql=" + lSql);
lStatement = lConnection.createStatement();
lResultSet = lStatement.executeQuery(lSql);
while (lResultSet.next()) {
cmdtyCodeList += "'" + lResultSet.getString(1) + "'";
if (!lResultSet.isLast()) cmdtyCodeList += ",";
}
if (SIUtil.isNull(cmdtyCodeList)) cmdtyCodeList = "''";
} catch (SQLException ex) {
ex.printStackTrace();
throw new SIDBAccessException(ex);
} finally {
SIDBUtil.close(lStatement, lResultSet);
}
return (cmdtyCodeList);
}
/**
* getRequestList 出荷指示メールの送信先リストを戻す
*
* @param connection DBへのコネクション
* @param lOrderCode 受注コード
* @return 送信先リスト
* @throws SIDBAccessException
*/
public static String[][] getRequestList(Connection lConnection, String lOrderCode) throws SIDBAccessException {
String[][] list;
Statement lStatement = null;
ResultSet lResultSet = null;
StringBuffer lSqlBuf = new StringBuffer();
lSqlBuf.append("SELECT DISTINCT b.branchname,b.email,b.branchcode ");
lSqlBuf.append("FROM orderallocationinfotbl a,branchtbl b ");
lSqlBuf.append("WHERE a.ordercode=").append(SIDBUtil.SQL2Str(lOrderCode," "));
lSqlBuf.append("AND b.branchcode=a.branchcode ");
lSqlBuf.append("ORDER BY b.branchcode");
try {
lStatement = lConnection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
lResultSet = lStatement.executeQuery(lSqlBuf.toString());
lResultSet.last();
list = new String[lResultSet.getRow()][3];
lResultSet.beforeFirst();
int i = 0;
while (lResultSet.next()) {
list[i][0] = lResultSet.getString("branchcode");
list[i][1] = lResultSet.getString("branchname");
list[i][2] = lResultSet.getString("email");
i++;
}
} catch (SQLException ex) {
ex.printStackTrace();
throw new SIDBAccessException(ex);
} finally {
SIDBUtil.close(lStatement, lResultSet);
}
return list;
}
public static String[][] getRequestList2(Connection lConnection, String lOrderCode) throws SIDBAccessException {
String[][] list;
Statement lStatement = null;
ResultSet lResultSet = null;
StringBuffer lSqlBuf = new StringBuffer();
lSqlBuf.append("SELECT DISTINCT b.branchcode,b.branchname ");
lSqlBuf.append(",CASE WHEN b.branchcode='999' AND s.email IS NOT NULL THEN s.storagecode ELSE NULL END AS storagecode ");
lSqlBuf.append(",CASE WHEN b.branchcode='999' AND s.email IS NOT NULL THEN s.storagename ELSE NULL END AS storagename ");
lSqlBuf.append(",CASE WHEN b.branchcode='999' AND s.email IS NOT NULL THEN s.email ELSE b.email END AS email ");
lSqlBuf.append("FROM orderallocationinfotbl a,branchtbl b,cmdtymtbl c ");
lSqlBuf.append("LEFT OUTER JOIN storagetbl s ON c.storagecode=s.storagecode ");
lSqlBuf.append("WHERE a.ordercode=").append(SIDBUtil.SQL2Str(lOrderCode," "));
lSqlBuf.append("AND c.cmdtycode=a.cmdtycode ");
lSqlBuf.append("AND b.branchcode=a.branchcode ");
lSqlBuf.append("ORDER BY b.branchcode");
try {
lStatement = lConnection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
lResultSet = lStatement.executeQuery(lSqlBuf.toString());
lResultSet.last();
list = new String[lResultSet.getRow()][5];
lResultSet.beforeFirst();
int i = 0;
while (lResultSet.next()) {
list[i][0] = lResultSet.getString("branchcode");
list[i][1] = lResultSet.getString("branchname");
list[i][2] = lResultSet.getString("storagecode");
list[i][3] = lResultSet.getString("storagename");
list[i][4] = lResultSet.getString("email");
i++;
}
} catch (SQLException ex) {
ex.printStackTrace();
throw new SIDBAccessException(ex);
} finally {
SIDBUtil.close(lStatement, lResultSet);
}
return list;
}
public static String[] getRequestCCList(Connection lConnection, String lOrderCode) throws SIDBAccessException {
String[] list;
Statement lStatement = null;
ResultSet lResultSet = null;
StringBuffer lSqlBuf = new StringBuffer();
lSqlBuf.append("SELECT c.email FROM chargetbl c,orderlatestvw o WHERE o.chargecode=c.chargecode ");
lSqlBuf.append("AND o.ordercode=").append(SIDBUtil.SQL2Str(lOrderCode," "));
lSqlBuf.append("UNION SELECT b.email FROM branchtbl b,orderlatestvw o WHERE o.branchcode=b.branchcode ");
lSqlBuf.append("AND o.ordercode=").append(SIDBUtil.SQL2Str(lOrderCode));
try {
lStatement = lConnection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
lResultSet = lStatement.executeQuery(lSqlBuf.toString());
lResultSet.last();
list = new String[lResultSet.getRow()];
lResultSet.beforeFirst();
int i = 0;
while (lResultSet.next()) {
list[i] = lResultSet.getString("email");
i++;
}
} catch (SQLException ex) {
ex.printStackTrace();
throw new SIDBAccessException(ex);
} finally {
SIDBUtil.close(lStatement, lResultSet);
}
return list;
}
public static String convert1toA(String str) {
String res = "";
if (SIUtil.isNull(str) || str.equals("")) { return res; }
int flg = Integer.parseInt(str);
switch (flg) {
case 1:
res = "A";
break;
case 2:
res = "B";
break;
case 3:
res = "C";
break;
case 4:
res = "D";
break;
case 5:
res = "E";
break;
case 6:
res = "F";
break;
case 7:
res = "G";
break;
case 8:
res = "H";
break;
case 9:
res = "I";
break;
case 10:
res = "J";
break;
case 11:
res = "K";
break;
case 12:
res = "L";
break;
case 13:
res = "M";
break;
case 14:
res = "N";
break;
case 15:
res = "O";
break;
case 16:
res = "P";
break;
case 17:
res = "Q";
break;
case 18:
res = "R";
break;
case 19:
res = "S";
break;
case 20:
res = "T";
break;
case 21:
res = "U";
break;
case 22:
res = "V";
break;
case 23:
res = "W";
break;
case 24:
res = "X";
break;
case 25:
res = "Y";
break;
case 26:
res = "Z";
break;
default:
res = "";
}
return res;
}
public static String convertAto1(String str) {
String res = "";
if (SIUtil.isNull(str) || str.equals("")) { return res; }
switch (str.charAt(0)) {
case 'A':
res = "1";
break;
case 'B':
res = "2";
break;
case 'C':
res = "3";
break;
case 'D':
res = "4";
break;
case 'E':
res = "5";
break;
case 'F':
res = "6";
break;
case 'G':
res = "7";
break;
case 'H':
res = "8";
break;
case 'I':
res = "9";
break;
case 'J':
res = "10";
break;
case 'K':
res = "11";
break;
case 'L':
res = "12";
break;
case 'M':
res = "13";
break;
case 'N':
res = "14";
break;
case 'O':
res = "15";
break;
case 'P':
res = "16";
break;
case 'Q':
res = "17";
break;
case 'R':
res = "18";
break;
case 'S':
res = "19";
break;
case 'T':
res = "20";
break;
case 'U':
res = "21";
break;
case 'V':
res = "22";
break;
case 'W':
res = "23";
break;
case 'X':
res = "24";
break;
case 'Y':
res = "25";
break;
case 'Z':
res = "26";
break;
default:
res = "";
}
return res;
}
/**
* deliveryDate 指定した区間の日付データのコレクションを返す
*
* @param conn コネクション
* @param start 開始日
* @param end 終了日
* @return
*/
public static Collection deliveryDate(Connection conn, int start, int end) {
return deliveryDate(conn, start, end,"");
}
public static Collection deliveryDate(Connection conn, int start, int end,String nodelivery) {
Collection coll = new ArrayList();
Statement statement = null;
ResultSet resultSet = null;
SINameValue lNameValue = new SINameValue();
String[] nodeliveryList = null;
if(SIUtil.isNotNull(nodelivery)) nodeliveryList = nodelivery.split(",");
StringBuffer sqlStatement = new StringBuffer();
sqlStatement.append("SELECT list.date FROM ");
sqlStatement.append("(SELECT current_date + s.a AS date ");
sqlStatement.append(" FROM GENERATE_SERIES(").append(start).append(",").append(end).append(") AS s(a)) list ");
if(nodeliveryList!=null&&nodeliveryList.length>0){
sqlStatement.append("WHERE list.date NOT IN (");
for (int i=0;i0) sqlStatement.append(",");
sqlStatement.append(SIDBUtil.SQL2Str(nodeliveryList[i]));
}
sqlStatement.append(") ");
}
sqlStatement.append("ORDER BY date");
try {
statement = conn.createStatement();
resultSet = statement.executeQuery(sqlStatement.toString());
while (resultSet.next()) {
lNameValue = new SINameValue(resultSet.getString("date"), resultSet.getString("date"));
coll.add(lNameValue);
}
} catch (Exception ex) {
ex.printStackTrace();
} finally {
SIDBUtil.close(resultSet, statement);
}
return coll;
}
/**
* deliveryDate 指定した区間の日付データのコレクションを返す
*
* @param conn コネクション
* @return
*/
public static Collection paymentDate(Connection conn) {
Collection coll = new ArrayList();
Statement statement = null;
ResultSet resultSet = null;
StringBuffer sqlStatement = new StringBuffer();
String year = "";
String month = "";
int start = 0;
SINameValue lNameValue = new SINameValue();
try {
String first = SIDBUtil.getFirstData(conn, "SELECT MAX(season) FROM inventorytbl");
if (SIUtil.isNull(first)) start = -100;
else {
year = first.substring(0, 4);
month = first.substring(4, 6);
if (month.equals("12")) {
year = SIUtil.add(year, "1");
month = "1";
} else {
month = SIUtil.add(month, "1");
}
start = Integer.parseInt(SIUtil.multi("-1", SIDBUtil.getFirstData(conn, "SELECT CURRENT_DATE - '" + year + "-" + month + "-1'")));
}
sqlStatement = new StringBuffer();
sqlStatement.append("SELECT list.date FROM ");
sqlStatement.append("(SELECT current_date + s.a AS date ");
sqlStatement.append(" FROM GENERATE_SERIES(").append(start).append(",100) AS s(a)) list ");
sqlStatement.append("ORDER BY date");
statement = conn.createStatement();
resultSet = statement.executeQuery(sqlStatement.toString());
while (resultSet.next()) {
lNameValue = new SINameValue(resultSet.getString("date"), resultSet.getString("date"));
coll.add(lNameValue);
}
} catch (Exception ex) {
ex.printStackTrace();
} finally {
SIDBUtil.close(resultSet, statement);
}
return coll;
}
public static boolean paymentDateFixed(Connection lConnection, String lDate) {
if (SIUtil.isNull(lDate)) return false;
String year = "";
String month = "";
try {
String first = SIDBUtil.getFirstData(lConnection, "SELECT MAX(season) FROM inventorytbl");
if (SIUtil.isNull(first)) return false;
else {
year = first.substring(0, 4);
month = first.substring(4, 6);
if (month.equals("12")) {
year = SIUtil.add(year, "1");
month = "1";
} else {
month = SIUtil.add(month, "1");
}
if (month.length() == 1) month = "0" + month;
}
} catch (Exception ex) {
ex.printStackTrace();
}
return (lDate.compareTo(year + "-" + month + "-01") < 0);
}
public static void insertOrderAllocation(Connection conn, int num, String shopCode, String cmdtyCode, String individualCode, String orderCode, int deliveryCode, int detailCode)
throws SIDBAccessException {
insertOrderAllocation(conn,num,shopCode,cmdtyCode,individualCode,orderCode,deliveryCode,detailCode,"");
}
public static void insertOrderAllocation(Connection conn, int num, String shopCode, String cmdtyCode, String individualCode, String orderCode, int deliveryCode, int detailCode, String branchCode)
throws SIDBAccessException {
Statement statement = null;
ResultSet resultSet = null;
String branch = "";
int amount = 0;
int allocate = 0;
StringBuffer sqlStatement = new StringBuffer();
SIInsertRec insertRec = new SIInsertRec();
SIModifyRec modifyRec = new SIModifyRec();
SIDateTime time = new SIDateTime();
sqlStatement.append("SELECT b.branchcode,a.amount FROM storestocktbl a,branchtbl b ");
sqlStatement.append("WHERE a.shopcode = ").append(SIDBUtil.SQL2Str(shopCode," "));
sqlStatement.append("AND a.cmdtycode = ").append(SIDBUtil.SQL2Str(cmdtyCode," "));
sqlStatement.append("AND a.individualcode = ").append(SIDBUtil.SQL2Str(individualCode," "));
if (SIUtil.isNotNull(branchCode)) {//レジモードでは引当支店を固定する
sqlStatement.append("AND a.branchcode = ").append(SIDBUtil.SQL2Str(branchCode," "));
}
sqlStatement.append("AND a.branchcode=b.branchcode ");
sqlStatement.append("AND a.branchcode NOT IN ('16','19') ");
sqlStatement.append("ORDER BY a.salespriorityflg,b.prioritybase,a.branchcode");
// 実行
try {
statement = conn.createStatement();
resultSet = statement.executeQuery(sqlStatement.toString());
while (resultSet.next()) {
branch = resultSet.getString("branchcode");
amount = resultSet.getInt("amount");
if (amount == 0) {
continue;
} else if (num > amount) {
num = num - amount;
allocate = amount;
amount = 0;
modifyRec = new SIModifyRec("storestocktbl");
modifyRec.addCondition("shopcode", shopCode);
modifyRec.addCondition("cmdtycode", cmdtyCode);
modifyRec.addCondition("individualcode", individualCode);
modifyRec.addCondition("branchcode", branch);
modifyRec.add("amount", amount);
modifyRec.execute(conn);
sendAlertMail(conn, cmdtyCode, individualCode);
insertRec = new SIInsertRec("orderallocationinfotbl");
insertRec.add("ordercode", orderCode);
insertRec.add("deliverycode", deliveryCode);
insertRec.add("detailcode", detailCode);
insertRec.add("shopcode", shopCode);
insertRec.add("orderbranchcode", "1");
insertRec.add("cmdtycode", cmdtyCode);
insertRec.add("individualcode", individualCode);
insertRec.add("branchcode", branch);
insertRec.add("orderallocationnumber", allocate);
insertRec.add("initdatetime", time.getFullDate());
insertRec.add("updatedatetime", time.getFullDate());
insertRec.execute(conn);
} else if (num <= amount) {
amount = amount - num;
allocate = num;
num = 0;
modifyRec = new SIModifyRec("storestocktbl");
modifyRec.addCondition("shopcode", shopCode);
modifyRec.addCondition("cmdtycode", cmdtyCode);
modifyRec.addCondition("individualcode", individualCode);
modifyRec.addCondition("branchcode", branch);
modifyRec.add("amount", amount);
modifyRec.execute(conn);
sendAlertMail(conn, cmdtyCode, individualCode);
insertRec = new SIInsertRec("orderallocationinfotbl");
insertRec.add("ordercode", orderCode);
insertRec.add("deliverycode", deliveryCode);
insertRec.add("detailcode", detailCode);
insertRec.add("shopcode", shopCode);
insertRec.add("orderbranchcode", "1");
insertRec.add("cmdtycode", cmdtyCode);
insertRec.add("individualcode", individualCode);
insertRec.add("branchcode", branch);
insertRec.add("orderallocationnumber", allocate);
insertRec.add("initdatetime", time.getFullDate());
insertRec.add("updatedatetime", time.getFullDate());
insertRec.execute(conn);
}
if (num == 0) {
break;
}
}
} catch (Exception ex) {
throw new SIDBAccessException(ex);
} finally {
SIDBUtil.close(resultSet, statement);
}
}
public static String getConsignFlg(Connection lConnection, String shopCode, String lCmdtyCode, String lIndividualCode) throws SIDBAccessException {
Statement lStatement = null;
ResultSet lResultSet = null;
StringBuffer lSqlBuf = new StringBuffer();
String consignGoodsFlg = "";
try {
lSqlBuf.append("SELECT consigngoodsFlg ");
lSqlBuf.append("FROM individualtbl ");
lSqlBuf.append("WHERE shopcode = ").append(SIDBUtil.SQL2Str(shopCode," "));
lSqlBuf.append("AND CmdtyCode = ").append(SIDBUtil.SQL2Str(lCmdtyCode," "));
lSqlBuf.append("AND IndividualCode = ").append(SIDBUtil.SQL2Str(lIndividualCode));
lStatement = lConnection.createStatement();
lResultSet = lStatement.executeQuery(lSqlBuf.toString());
if (lResultSet.next()) {
consignGoodsFlg = lResultSet.getString("consigngoodsFlg");
}
} catch (Exception ex) {
throw new SIDBAccessException(ex);
} finally {
SIDBUtil.close(lResultSet, lStatement);
}
return consignGoodsFlg;
}
//EDBTG003-00 elecs-matsushima mod start
// public static boolean isAllocation(Connection lConnection, String lOrderCode, String lCmdtyCode, String lIndividualCode) throws SIDBAccessException {
public static boolean isAllocation(Connection lConnection, String lOrderCode, String lCmdtyCode, String lIndividualCode, String lDetailCode) throws SIDBAccessException {
// EDBTG003-00 elecs-matsushima mod end
StringBuffer lSqlBuf = new StringBuffer();
lSqlBuf.append("SELECT * FROM orderallocationinfotbl ");
lSqlBuf.append("WHERE OrderCode=").append(SIDBUtil.SQL2Str(lOrderCode," "));
lSqlBuf.append("AND CmdtyCode=").append(SIDBUtil.SQL2Str(lCmdtyCode," "));
lSqlBuf.append("AND IndividualCode=").append(SIDBUtil.SQL2Str(lIndividualCode));
lSqlBuf.append("AND detailcode=").append(SIDBUtil.SQL2Str(lDetailCode)); //EDBTG003-00 elecs-matsushima add
return SIDBUtil.hasData(lConnection, lSqlBuf.toString());
}
public static boolean isAmount(Connection lConnection, String lCmdtyCode, String lIndividualCode) throws SIDBAccessException {
StringBuffer lSqlBuf = new StringBuffer();
lSqlBuf.append("SELECT * FROM individualtbl ");
lSqlBuf.append("WHERE amountflg='1' ");
lSqlBuf.append("AND CmdtyCode=").append(SIDBUtil.SQL2Str(lCmdtyCode," "));
lSqlBuf.append("AND IndividualCode = ").append(SIDBUtil.SQL2Str(lIndividualCode));
return SIDBUtil.hasData(lConnection, lSqlBuf.toString());
}
//EDBTG003-00 elecs-matsushima mod start
// public static String getAllocationNum(Connection lConnection, String orderCode, String shopCode, String lCmdtyCode, String lIndividualCode, String branch)
// throws SIDBAccessException {
public static String getAllocationNum(Connection lConnection, String orderCode, String shopCode, String lCmdtyCode, String lIndividualCode, String branch, String detailCode)
throws SIDBAccessException {
// EDBTG003-00 elecs-matsushima mod end
Statement lStatement = null;
ResultSet lResultSet = null;
StringBuffer lSqlBuf = new StringBuffer();
String allocationNum = "";
try {
lSqlBuf.append("SELECT orderallocationnumber ");
lSqlBuf.append("FROM orderallocationinfotbl ");
lSqlBuf.append("WHERE shopcode=").append(SIDBUtil.SQL2Str(shopCode," "));
lSqlBuf.append("AND orderCode=").append(SIDBUtil.SQL2Str(orderCode," "));
lSqlBuf.append("AND CmdtyCode=").append(SIDBUtil.SQL2Str(lCmdtyCode," "));
lSqlBuf.append("AND IndividualCode=").append(SIDBUtil.SQL2Str(lIndividualCode," "));
lSqlBuf.append("AND branchcode=").append(SIDBUtil.SQL2Str(branch));
//EDBTG003-00 elecs-matsushima add start
if (SIUtil.isNull(detailCode)) {
lSqlBuf.append(" AND detailcode is null ");
} else {
lSqlBuf.append(" AND detailcode=").append(SIDBUtil.SQL2Str(detailCode, " "));
}
// EDBTG003-00 elecs-matsushima add end
lStatement = lConnection.createStatement();
lResultSet = lStatement.executeQuery(lSqlBuf.toString());
if (lResultSet.next()) {
allocationNum = lResultSet.getString("orderallocationnumber");
} else {
allocationNum = "0";
}
} catch (Exception ex) {
throw new SIDBAccessException(ex);
} finally {
SIDBUtil.close(lResultSet, lStatement);
}
return allocationNum;
}
//EDBTG003-00 elecs-matsushima mod start
// public static int existAllocation(Connection lConnection, String orderCode, String shopCode, String lCmdtyCode, String lIndividualCode) throws SIDBAccessException {
public static int existAllocation(Connection lConnection, String orderCode, String shopCode, String lCmdtyCode, String lIndividualCode, String detailCode) throws SIDBAccessException {
// EDBTG003-00 elecs-matsushima mod end
Statement lStatement = null;
ResultSet lResultSet = null;
StringBuffer lSqlBuf = new StringBuffer();
int alloc = 0;
try {
lSqlBuf.append("SELECT sum(orderallocationnumber) as sumalloc ");
lSqlBuf.append("FROM orderallocationinfotbl ");
lSqlBuf.append("WHERE shopcode = ").append(SIDBUtil.SQL2Str(shopCode," "));
lSqlBuf.append("AND orderCode = ").append(SIDBUtil.SQL2Str(orderCode," "));
lSqlBuf.append("AND CmdtyCode = ").append(SIDBUtil.SQL2Str(lCmdtyCode," "));
lSqlBuf.append("AND IndividualCode = ").append(SIDBUtil.SQL2Str(lIndividualCode));
lSqlBuf.append(" AND detailcode = ").append(SIDBUtil.SQL2Str(detailCode, " ")); //EDBTG003-00 elecs-matsushima add
lStatement = lConnection.createStatement();
lResultSet = lStatement.executeQuery(lSqlBuf.toString());
if (lResultSet.next()) {
alloc = lResultSet.getInt("sumalloc");
} else {
alloc = 0;
}
} catch (Exception ex) {
throw new SIDBAccessException(ex);
} finally {
SIDBUtil.close(lResultSet, lStatement);
}
return alloc;
}
public static void updateOrderAllocation(Connection conn, int num, String shopCode, String cmdtyCode, String individualCode, String orderCode, int deliveryCode, int detailCode,
LinkedHashMap map) throws SIDBAccessException {
Statement statement = null;
ResultSet resultSet = null;
Statement rsStatement = null;
Statement daStatement = null;
Statement brStatement = null;
ResultSet brResultSet = null;
String branch = "";
String branchCode = "";
int allocate = 0;
StringBuffer sqlStatement = new StringBuffer();
SIInsertRec insertRec = new SIInsertRec();
SIDateTime time = new SIDateTime();
// 在庫引き戻し
sqlStatement.append("SELECT branchcode,orderallocationnumber FROM orderallocationinfotbl ");
sqlStatement.append("WHERE shopcode=").append(SIDBUtil.SQL2Str(shopCode," "));
sqlStatement.append("AND ordercode=").append(SIDBUtil.SQL2Str(orderCode," "));
sqlStatement.append("AND cmdtycode=").append(SIDBUtil.SQL2Str(cmdtyCode," "));
sqlStatement.append("AND individualcode=").append(SIDBUtil.SQL2Str(individualCode));
sqlStatement.append("AND detailcode=").append(SIDBUtil.SQL2Str(String.valueOf(detailCode))); // EDBTG003-00 elecs-matsushima add
try {
statement = conn.createStatement();
resultSet = statement.executeQuery(sqlStatement.toString());
while (resultSet.next()) {
allocate = resultSet.getInt("orderallocationnumber");
branch = resultSet.getString("branchcode");
StringBuffer lockSql = new StringBuffer();
lockSql.append("SELECT * FROM storestocktbl ");
lockSql.append("WHERE shopcode=").append(SIDBUtil.SQL2Str(shopCode," "));
lockSql.append("AND cmdtycode=").append(SIDBUtil.SQL2Str(cmdtyCode," "));
lockSql.append("AND individualcode=").append(SIDBUtil.SQL2Str(individualCode," "));
lockSql.append("AND branchcode=").append(SIDBUtil.SQL2Str(branch," FOR UPDATE"));
SIDBUtil.hasData(conn, lockSql.toString());
StringBuffer returnAmountSql = new StringBuffer();
returnAmountSql.append("UPDATE storestocktbl SET amount=amount+" + Integer.toString(allocate));
returnAmountSql.append(" WHERE shopcode=").append(SIDBUtil.SQL2Str(shopCode," "));
returnAmountSql.append("AND cmdtycode=").append(SIDBUtil.SQL2Str(cmdtyCode," "));
returnAmountSql.append("AND individualcode=").append(SIDBUtil.SQL2Str(individualCode," "));
returnAmountSql.append("AND branchcode=").append(SIDBUtil.SQL2Str(branch));
rsStatement = conn.createStatement();
rsStatement.execute(returnAmountSql.toString());
if (rsStatement!=null)rsStatement.close();
}
// 引当情報削除
StringBuffer deleteAllocSql = new StringBuffer();
deleteAllocSql.append("DELETE FROM orderallocationinfotbl ");
deleteAllocSql.append("WHERE shopcode=").append(SIDBUtil.SQL2Str(shopCode," "));
deleteAllocSql.append("AND ordercode=").append(SIDBUtil.SQL2Str(orderCode," "));
deleteAllocSql.append("AND cmdtycode=").append(SIDBUtil.SQL2Str(cmdtyCode," "));
deleteAllocSql.append("AND individualcode=").append(SIDBUtil.SQL2Str(individualCode));
deleteAllocSql.append("AND detailcode=").append(SIDBUtil.SQL2Str(String.valueOf(detailCode))); // EDBTG003-00 elecs-matsushima add
daStatement = conn.createStatement();
daStatement.execute(deleteAllocSql.toString());
// 引当情報作成
StringBuffer branchSql = new StringBuffer();
branchSql.append("SELECT branchcode FROM branchtbl");
brStatement = conn.createStatement();
brResultSet = brStatement.executeQuery(branchSql.toString());
while (brResultSet.next()) {
branchCode = brResultSet.getString("branchcode");
String alloc = (String) map.get(branchCode);
if (!alloc.equals("0") && alloc != null) {
StringBuffer lockSql = new StringBuffer();
lockSql.append("SELECT * FROM storestocktbl ");
lockSql.append("WHERE shopcode=").append(SIDBUtil.SQL2Str(shopCode," "));
lockSql.append("AND cmdtycode=").append(SIDBUtil.SQL2Str(cmdtyCode," "));
lockSql.append("AND individualcode=").append(SIDBUtil.SQL2Str(individualCode," "));
lockSql.append("AND branchcode=").append(SIDBUtil.SQL2Str(branchCode," FOR UPDATE"));
SIDBUtil.hasData(conn, lockSql.toString());
StringBuffer returnAmountSql = new StringBuffer();
returnAmountSql.append("UPDATE storestocktbl SET amount=amount-" + alloc);
returnAmountSql.append(" WHERE shopcode=").append(SIDBUtil.SQL2Str(shopCode," "));
returnAmountSql.append("AND cmdtycode=").append(SIDBUtil.SQL2Str(cmdtyCode," "));
returnAmountSql.append("AND individualcode=").append(SIDBUtil.SQL2Str(individualCode," "));
returnAmountSql.append("AND branchcode=").append(SIDBUtil.SQL2Str(branchCode));
rsStatement = conn.createStatement();
rsStatement.execute(returnAmountSql.toString());
if (rsStatement!=null)rsStatement.close();
sendAlertMail(conn, cmdtyCode, individualCode);
insertRec = new SIInsertRec("orderallocationinfotbl");
insertRec.add("ordercode", orderCode);
insertRec.add("deliverycode", deliveryCode);
insertRec.add("detailcode", detailCode);
insertRec.add("shopcode", shopCode);
insertRec.add("orderbranchcode", "1");
insertRec.add("cmdtycode", cmdtyCode);
insertRec.add("individualcode", individualCode);
insertRec.add("branchcode", branchCode);
insertRec.add("orderallocationnumber", alloc);
insertRec.add("initdatetime", time.getFullDate());
insertRec.add("updatedatetime", time.getFullDate());
insertRec.execute(conn);
}
}
} catch (Exception ex) {
throw new SIDBAccessException(ex);
} finally {
SIDBUtil.close(resultSet, statement);
SIDBUtil.close(brResultSet, brStatement);
try {
if (rsStatement!=null)rsStatement.close();
if (daStatement!=null)daStatement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
//EDBTG003-00 elecs-matsushima mod start
// public static void deleteOrderAllocation2(Connection conn, String shopCode, String orderCode, String cmdty, String individual) throws SIDBAccessException {
public static void deleteOrderAllocation2(Connection conn, String shopCode, String orderCode, String cmdty, String individual, String detailCode) throws SIDBAccessException {
//EDBTG003-00 elecs-matsushima mod end
Statement statement = null;
ResultSet resultSet = null;
Statement rsStatement = null;
Statement daStatement = null;
String branch = "";
int allocate = 0;
StringBuffer sqlStatement = new StringBuffer();
// 在庫引き戻し
sqlStatement.append("SELECT branchcode,orderallocationnumber,cmdtycode,individualcode FROM orderallocationinfotbl ");
sqlStatement.append("WHERE shopcode=").append(SIDBUtil.SQL2Str(shopCode," "));
sqlStatement.append("AND ordercode=").append(SIDBUtil.SQL2Str(orderCode," "));
sqlStatement.append("AND individualcode=").append(SIDBUtil.SQL2Str(individual," "));
sqlStatement.append("AND cmdtycode=").append(SIDBUtil.SQL2Str(cmdty));
sqlStatement.append(" AND detailcode=").append(SIDBUtil.SQL2Str(detailCode, " ")); //EDBTG003-00 elecs-matsushima add start
try {
statement = conn.createStatement();
resultSet = statement.executeQuery(sqlStatement.toString());
while (resultSet.next()) {
allocate = resultSet.getInt("orderallocationnumber");
branch = resultSet.getString("branchcode");
StringBuffer lockSql = new StringBuffer();
lockSql.append("SELECT * FROM storestocktbl ");
lockSql.append("WHERE shopcode=").append(SIDBUtil.SQL2Str(shopCode," "));
lockSql.append("AND cmdtycode=").append(SIDBUtil.SQL2Str(cmdty," "));
lockSql.append("AND individualcode=").append(SIDBUtil.SQL2Str(individual," "));
lockSql.append("AND branchcode=").append(SIDBUtil.SQL2Str(branch," FOR UPDATE"));
SIDBUtil.hasData(conn, lockSql.toString());
StringBuffer returnAmountSql = new StringBuffer();
returnAmountSql.append("UPDATE storestocktbl SET amount=amount+" + Integer.toString(allocate));
returnAmountSql.append(" WHERE shopcode=").append(SIDBUtil.SQL2Str(shopCode," "));
returnAmountSql.append("AND cmdtycode=").append(SIDBUtil.SQL2Str(cmdty," "));
returnAmountSql.append("AND individualcode=").append(SIDBUtil.SQL2Str(individual," "));
returnAmountSql.append("AND branchcode=").append(SIDBUtil.SQL2Str(branch));
rsStatement = conn.createStatement();
rsStatement.execute(returnAmountSql.toString());
if(rsStatement!=null)rsStatement.close();
}
// 引当情報削除
StringBuffer deleteAllocSql = new StringBuffer();
deleteAllocSql.append("DELETE FROM orderallocationinfotbl ");
deleteAllocSql.append("WHERE shopcode=").append(SIDBUtil.SQL2Str(shopCode," "));
deleteAllocSql.append("AND ordercode=").append(SIDBUtil.SQL2Str(orderCode," "));
deleteAllocSql.append("AND individualcode=").append(SIDBUtil.SQL2Str(individual," "));
deleteAllocSql.append("AND cmdtycode=").append(SIDBUtil.SQL2Str(cmdty));
deleteAllocSql.append(" AND detailcode=").append(SIDBUtil.SQL2Str(detailCode, " ")); //EDBTG003-00 elecs-matsushima add start
daStatement = conn.createStatement();
daStatement.execute(deleteAllocSql.toString());
if(daStatement!=null)daStatement.close();
} catch (Exception ex) {
try {
conn.rollback();
} catch (SQLException e) {
e.printStackTrace();
}
throw new SIDBAccessException(ex);
} finally {
SIDBUtil.close(resultSet, statement);
try {
if (rsStatement != null) rsStatement.close();
if (daStatement != null) daStatement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void deleteOrderAllocation(Connection conn, String shopCode, String orderCode) throws SIDBAccessException {
Statement statement = null;
ResultSet resultSet = null;
Statement rsStatement = null;
Statement daStatement = null;
String branch = "";
String cmdty = "";
String individual = "";
int allocate = 0;
StringBuffer sqlStatement = new StringBuffer();
// 在庫引き戻し
sqlStatement.append("SELECT branchcode,orderallocationnumber,cmdtycode,individualcode FROM orderallocationinfotbl ");
sqlStatement.append("WHERE shopcode=").append(SIDBUtil.SQL2Str(shopCode," "));
sqlStatement.append("AND ordercode=").append(SIDBUtil.SQL2Str(orderCode));
try {
statement = conn.createStatement();
resultSet = statement.executeQuery(sqlStatement.toString());
while (resultSet.next()) {
allocate = resultSet.getInt("orderallocationnumber");
branch = resultSet.getString("branchcode");
cmdty = resultSet.getString("cmdtycode");
individual = resultSet.getString("individualcode");
StringBuffer lockSql = new StringBuffer();
lockSql.append("SELECT * FROM storestocktbl ");
lockSql.append("WHERE shopcode=").append(SIDBUtil.SQL2Str(shopCode," "));
lockSql.append("AND cmdtycode=").append(SIDBUtil.SQL2Str(cmdty," "));
lockSql.append("AND individualcode=").append(SIDBUtil.SQL2Str(individual," "));
lockSql.append("AND branchcode=").append(SIDBUtil.SQL2Str(branch," FOR UPDATE"));
SIDBUtil.hasData(conn, lockSql.toString());
StringBuffer returnAmountSql = new StringBuffer();
returnAmountSql.append("UPDATE storestocktbl SET amount=amount+" + Integer.toString(allocate));
returnAmountSql.append(" WHERE shopcode=").append(SIDBUtil.SQL2Str(shopCode," "));
returnAmountSql.append("AND cmdtycode=").append(SIDBUtil.SQL2Str(cmdty," "));
returnAmountSql.append("AND individualcode=").append(SIDBUtil.SQL2Str(individual," "));
returnAmountSql.append("AND branchcode=").append(SIDBUtil.SQL2Str(branch));
rsStatement = conn.createStatement();
rsStatement.execute(returnAmountSql.toString());
if (rsStatement!=null)rsStatement.close();
}
// 引当情報削除
StringBuffer deleteAllocSql = new StringBuffer();
deleteAllocSql.append("DELETE FROM orderallocationinfotbl ");
deleteAllocSql.append("WHERE shopcode=").append(SIDBUtil.SQL2Str(shopCode," "));
deleteAllocSql.append("AND ordercode=").append(SIDBUtil.SQL2Str(orderCode));
daStatement = conn.createStatement();
daStatement.execute(deleteAllocSql.toString());
if (daStatement!=null)daStatement.close();
} catch (Exception ex) {
throw new SIDBAccessException(ex);
} finally {
SIDBUtil.close(resultSet, statement);
try {
if (rsStatement != null) rsStatement.close();
if (daStatement != null) daStatement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void returnOrderAllocation(Connection conn, UIRegOrder regOrder, String user) throws SIDBAccessException {
returnOrderAllocation(conn,regOrder,user,null);
}
public static void returnOrderAllocation(Connection conn, UIRegOrder regOrder, String user,String keepNumber) throws SIDBAccessException {
Statement statement = null;
ResultSet resultSet = null;
StringBuffer checkSql = new StringBuffer();
StringBuffer sqlStatement = new StringBuffer();
checkSql.append("SELECT chargecode FROM chargetbl WHERE chargecode = ").append(SIDBUtil.SQL2Str(user));
// キープする方（全て出荷拠点へ在庫引き戻し）
if (regOrder.getKeepItems()!=null && regOrder.getKeepItems().length>0) {
sqlStatement.append("SELECT a.purchaseprice,a.processingexpence*b.orderallocationnumber AS processingexpence2 ");
sqlStatement.append(",b.branchcode,b.orderallocationnumber,a.cmdtycode,a.individualcode ");
sqlStatement.append(",a.consigngoodsfee,a.processingexpence,a.purchaseprice + a.processingexpence AS cost ");
sqlStatement.append(",a.orgprice,a.cmdtyname,a.amount,d.deliverytypecode,d.cmdtysize,a.shippmentdate ");
sqlStatement.append(",a.purchaseprice*a.amount AS shippurchase,a.processingexpence*a.amount AS shipprocess,e.branchcode AS shipbranch ");
sqlStatement.append(",tax(coalesce(a.shippmentdate,current_date)) AS taxRateBefore,tax(current_date) AS taxRateAfter ");
sqlStatement.append("FROM orderdetaillatestvw a ");
sqlStatement.append("LEFT OUTER JOIN orderallocationinfotbl b ");
sqlStatement.append("ON a.ordercode=b.ordercode ");
sqlStatement.append("AND a.cmdtycode=b.cmdtycode ");
sqlStatement.append("AND a.individualcode=b.individualcode ");
sqlStatement.append(",individualtbl c,cmdtymtbl d,orderlatestvw e ");
sqlStatement.append("WHERE a.cmdtycode=c.cmdtycode ");
sqlStatement.append("AND a.cmdtycode=d.cmdtycode ");
sqlStatement.append("AND a.individualcode=c.individualcode ");
sqlStatement.append("AND a.individualcode IN (");
for (int i=0;i0) sqlStatement.append(",");
sqlStatement.append(SIDBUtil.SQL2Str(regOrder.getKeepItems()[i]));
}
sqlStatement.append(") ");
sqlStatement.append("AND a.ordercode=e.ordercode ");
sqlStatement.append("AND a.ordercode=").append(SIDBUtil.SQL2Str(regOrder.getOrderCode()));
try {
if (SIUtil.isNotNull(user)) user = SIDBUtil.getFirstData(conn, checkSql.toString());//担当者になければブランク
statement = conn.createStatement();
resultSet = statement.executeQuery(sqlStatement.toString());
while (resultSet.next()) {
boolean amountFlg = true;
if (SIUtil.isNull(resultSet.getString("orderallocationnumber"))) amountFlg = false;
jp.co.sint.beans.mallmgr.UIStockHistory lStockHistory = new jp.co.sint.beans.mallmgr.UIStockHistory();
lStockHistory.setCmdtyCode(resultSet.getString("cmdtycode"));
lStockHistory.setIndividualCode(resultSet.getString("individualcode"));
lStockHistory.setNewAmount(resultSet.getString("orderallocationnumber"));
lStockHistory.setNewBranch(resultSet.getString("branchcode"));
lStockHistory.setNewCharge(user);//ログインユーザ
lStockHistory.setPurchasePrice(resultSet.getString("purchaseprice"));
lStockHistory.setProcessingExpence(resultSet.getString("processingexpence2"));
if (!resultSet.getString("taxRateBefore").equals(resultSet.getString("taxRateAfter"))) {
String pp = SIUtil.div_LD(resultSet.getString("purchaseprice"), SIUtil.add_DL(SIUtil.div_DL(resultSet.getString("taxRateBefore"), "100"), "1"));
lStockHistory.setPurchasePrice(String.valueOf(Math.ceil(Math.floor(Double.parseDouble(pp) * Double.parseDouble(SIUtil.add_DL(SIUtil.div_DL(resultSet.getString("taxRateAfter"), "100"), "1")) * 10000) / 10000)));
String pe = SIUtil.div_LD(resultSet.getString("processingexpence2"), SIUtil.add_DL(SIUtil.div_DL(resultSet.getString("taxRateBefore"), "100"), "1"));
lStockHistory.setProcessingExpence(String.valueOf(Math.ceil(Math.floor(Double.parseDouble(pe) * Double.parseDouble(SIUtil.add_DL(SIUtil.div_DL(resultSet.getString("taxRateAfter"), "100"), "1")) * 10000) / 10000)));
}
lStockHistory.setType("8");//返品戻り
lStockHistory.setComment("受注"+regOrder.getOrderCode()+" 返品");
SIDateTime lDateTime = new SIDateTime();
SIInsertRec lRec4 = new SIInsertRec("keepdetailtbl");
lRec4.add("keepNumber",keepNumber);
lRec4.add("cmdtycode", resultSet.getString("cmdtycode"));
lRec4.add("individualcode", resultSet.getString("individualcode"));
if (amountFlg) {
lRec4.add("cmdtytype", "0");
lRec4.add("amount", resultSet.getString("orderallocationnumber"));
lRec4.add("branchcode", resultSet.getString("branchcode"));
} else {
lRec4.add("cmdtytype", "1");
lRec4.add("amount", resultSet.getString("amount"));
lRec4.add("branchcode", "999");
}
lRec4.add("cmdtyname", resultSet.getString("cmdtyname"));
lRec4.add("price", resultSet.getString("orgprice"));
lRec4.add("cost", resultSet.getString("cost"));
lRec4.add("deliverytype", resultSet.getString("deliverytypecode"));
lRec4.add("size", resultSet.getString("cmdtysize"));
lRec4.add("purchaseprice", resultSet.getString("purchaseprice"));
lRec4.add("processingexpence", resultSet.getString("processingexpence"));
lRec4.add("consigngoodsfee", resultSet.getString("consigngoodsfee"));
lRec4.add("InitDateTime", lDateTime.getFullDateTime());
lRec4.execute(conn);
if (amountFlg&&SIUtil.isNotNull(resultSet.getString("shippmentdate"))) {//在庫管理する出荷済みの商品のみ
//承認済みの入庫データを作成する（在庫加算、加工登録、原価再計算）
jp.co.sint.servlet.mallmgr.SIRegStockControlSrv.createNewStore(conn, lStockHistory, true);
}else if (!amountFlg&&SIUtil.isNotNull(resultSet.getString("shippmentdate"))){//受発注商品の場合は返品戻りの代わりに販売を取り消す
lDateTime = new SIDateTime();
SIInsertRec lRec2 = new SIInsertRec("shiphistorytbl");
lRec2.add("cmdtycode", lStockHistory.getCmdtyCode());
lRec2.add("individualcode", lStockHistory.getIndividualCode());
lRec2.add("shipnumber", lStockHistory.getNewShipNumber(conn));
lRec2.add("shipbranch", resultSet.getString("shipbranch"));
lRec2.add("shipcharge", lStockHistory.getNewCharge());
lRec2.add("ordercode", regOrder.getOrderCode());
lRec2.add("amount", resultSet.getString("amount"));
if (!resultSet.getString("taxRateBefore").equals(resultSet.getString("taxRateAfter"))) {
String pp = SIUtil.div_LD(resultSet.getString("shippurchase"), SIUtil.add_DL(SIUtil.div_DL(resultSet.getString("taxRateBefore"), "100"), "1"));
lStockHistory.setPurchasePrice(String.valueOf(Math.ceil(Math.floor(Double.parseDouble(pp) * Double.parseDouble(SIUtil.add_DL(SIUtil.div_DL(resultSet.getString("taxRateAfter"), "100"), "1")) * 10000) / 10000)));
String pe = SIUtil.div_LD(resultSet.getString("shipprocess"), SIUtil.add_DL(SIUtil.div_DL(resultSet.getString("taxRateBefore"), "100"), "1"));
lStockHistory.setProcessingExpence(String.valueOf(Math.ceil(Math.floor(Double.parseDouble(pe) * Double.parseDouble(SIUtil.add_DL(SIUtil.div_DL(resultSet.getString("taxRateAfter"), "100"), "1")) * 10000) / 10000)));
} else {
lStockHistory.setPurchasePrice(resultSet.getString("shippurchase"));
lStockHistory.setProcessingExpence(resultSet.getString("shipprocess"));
}
lRec2.add("purchaseprice", lStockHistory.getPurchasePrice());
lRec2.add("processingexpence", lStockHistory.getProcessingExpence());
lRec2.add("shiptype", "0");
lRec2.add("comment", lStockHistory.getComment());
lRec2.add("delflg", "3");
lRec2.add("initdatetime", lDateTime.getFullDate());
lRec2.add("calcdatetime", lDateTime.getFullDateTime());
lRec2.execute(conn);
SIModifyRec lRec3 = new SIModifyRec("shiphistorytbl");
lRec3.addCondition("cmdtycode", lStockHistory.getCmdtyCode());
lRec3.addCondition("individualcode", lStockHistory.getIndividualCode());
lRec3.addCondition("ordercode", regOrder.getOrderCode());
lRec3.addCondition("shiptype", "0");
lRec3.addCondition("delflg", "1");
lRec3.add("delflg", "2");
lRec3.execute(conn);
}
}
} catch (Exception ex) {
throw new SIDBAccessException(ex);
} finally {
SIDBUtil.close(resultSet, statement);
}
}
sqlStatement = new StringBuffer();
//返品する方（未出荷分は引当拠点、それ以外は受注拠点へ戻る）
if (regOrder.getReturnItems()!=null && regOrder.getReturnItems().length>0) {
sqlStatement.append("SELECT a.purchaseprice,a.processingexpence*coalesce(b.orderallocationnumber,a.amount) AS processingexpence2 ");
sqlStatement.append(",CASE WHEN b.branchcode IS NULL THEN e.branchcode ELSE b.branchcode END AS branchcode");
sqlStatement.append(",coalesce(b.orderallocationnumber,a.amount) AS orderallocationnumber,a.cmdtycode,a.individualcode,c.amountflg ");
sqlStatement.append(",a.consigngoodsfee,a.processingexpence,a.purchaseprice + a.processingexpence AS cost ");
sqlStatement.append(",a.orgprice,a.cmdtyname,a.amount,d.deliverytypecode,d.cmdtysize,a.shippmentdate ");
sqlStatement.append(",a.purchaseprice*a.amount AS shippurchase,a.processingexpence*a.amount AS shipprocess,e.branchcode AS shipbranch ");
sqlStatement.append("FROM orderdetaillatestvw a ");
sqlStatement.append("LEFT OUTER JOIN orderallocationinfotbl b ");
sqlStatement.append("ON a.ordercode=b.ordercode ");
sqlStatement.append("AND a.cmdtycode=b.cmdtycode ");
sqlStatement.append("AND a.individualcode=b.individualcode ");
sqlStatement.append("AND a.shippmentdate IS NULL ");
sqlStatement.append(",individualtbl c,cmdtymtbl d,orderlatestvw e ");
sqlStatement.append("WHERE a.cmdtycode=c.cmdtycode ");
sqlStatement.append("AND a.cmdtycode=d.cmdtycode ");
sqlStatement.append("AND a.individualcode=c.individualcode ");
sqlStatement.append("AND a.individualcode IN (");
for (int i=0;i0) sqlStatement.append(",");
sqlStatement.append(SIDBUtil.SQL2Str(regOrder.getReturnItems()[i]));
}
sqlStatement.append(") ");
sqlStatement.append("AND a.ordercode=e.ordercode ");
sqlStatement.append("AND a.ordercode=").append(SIDBUtil.SQL2Str(regOrder.getOrderCode()));
try {
if (SIUtil.isNotNull(user)) user = SIDBUtil.getFirstData(conn, checkSql.toString());//担当者になければブランク
statement = conn.createStatement();
resultSet = statement.executeQuery(sqlStatement.toString());
while (resultSet.next()) {
boolean amountFlg = true;
if ("0".equals(resultSet.getString("amountflg"))) amountFlg = false;
jp.co.sint.beans.mallmgr.UIStockHistory lStockHistory = new jp.co.sint.beans.mallmgr.UIStockHistory();
lStockHistory.setCmdtyCode(resultSet.getString("cmdtycode"));
lStockHistory.setIndividualCode(resultSet.getString("individualcode"));
lStockHistory.setNewAmount(resultSet.getString("orderallocationnumber"));
lStockHistory.setNewBranch(resultSet.getString("branchcode"));
lStockHistory.setNewCharge(user);//ログインユーザ
lStockHistory.setPurchasePrice(resultSet.getString("purchaseprice"));
lStockHistory.setProcessingExpence(resultSet.getString("processingexpence2"));
lStockHistory.setType("8");//返品戻り
lStockHistory.setComment("受注"+regOrder.getOrderCode()+" 返品");
if (amountFlg&&SIUtil.isNotNull(resultSet.getString("shippmentdate"))) {//在庫管理する出荷済みの商品のみ
//承認済みの入庫データを作成する（在庫加算、加工登録、原価再計算）
jp.co.sint.servlet.mallmgr.SIRegStockControlSrv.createNewStore(conn, lStockHistory);
}else if (amountFlg) {
//未出荷返品分の在庫加算
SISpcType lSpc1 = new SISpcType("amount+" + lStockHistory.getNewAmount());
SIModifyRec lRec1 = new SIModifyRec("storestocktbl");
lRec1.addCondition("cmdtycode", lStockHistory.getCmdtyCode());// 親コード
lRec1.addCondition("individualcode", lStockHistory.getIndividualCode());// 個別コード
lRec1.addCondition("branchcode", lStockHistory.getNewBranch());// 支店コード
lRec1.add("amount", lSpc1);
StringBuffer lSqlBuf = new StringBuffer();
lSqlBuf.append("SELECT * FROM storestocktbl WHERE cmdtycode=").append(SIDBUtil.SQL2Str(lStockHistory.getCmdtyCode(), " "));
lSqlBuf.append("AND individualcode=").append(SIDBUtil.SQL2Str(lStockHistory.getIndividualCode(), " "));
lSqlBuf.append("AND branchcode=").append(SIDBUtil.SQL2Str(lStockHistory.getNewBranch(), " "));
lSqlBuf.append("FOR UPDATE");
SIDBUtil.hasData(conn, lSqlBuf.toString());// レコードロック
lRec1.execute(conn);
}else if (!amountFlg&&SIUtil.isNotNull(resultSet.getString("shippmentdate"))){//受発注商品の場合は返品戻りの代わりに販売を取り消す
SIDateTime lDateTime = new SIDateTime();
SIInsertRec lRec2 = new SIInsertRec("shiphistorytbl");
lRec2.add("cmdtycode", lStockHistory.getCmdtyCode());
lRec2.add("individualcode", lStockHistory.getIndividualCode());
lRec2.add("shipnumber", lStockHistory.getNewShipNumber(conn));
lRec2.add("shipbranch", resultSet.getString("shipbranch"));
lRec2.add("shipcharge", lStockHistory.getNewCharge());
lRec2.add("ordercode", regOrder.getOrderCode());
lRec2.add("amount", resultSet.getString("amount"));
lRec2.add("purchaseprice", resultSet.getString("shippurchase"));
lRec2.add("processingexpence", resultSet.getString("shipprocess"));
lRec2.add("shiptype", "0");
lRec2.add("comment", lStockHistory.getComment());
lRec2.add("delflg", "3");
lRec2.add("initdatetime", lDateTime.getFullDate());
lRec2.add("calcdatetime", lDateTime.getFullDateTime());
lRec2.execute(conn);
SIModifyRec lRec3 = new SIModifyRec("shiphistorytbl");
lRec3.addCondition("cmdtycode", lStockHistory.getCmdtyCode());
lRec3.addCondition("individualcode", lStockHistory.getIndividualCode());
lRec3.addCondition("ordercode", regOrder.getOrderCode());
lRec3.addCondition("shiptype", "0");
lRec3.addCondition("delflg", "1");
lRec3.add("delflg", "2");
lRec3.execute(conn);
}
}
} catch (Exception ex) {
throw new SIDBAccessException(ex);
} finally {
SIDBUtil.close(resultSet, statement);
}
}
}
public static String getDeliveryType(Connection lConnection, String shopCode, String lCmdtyCode) throws SIDBAccessException {
Statement lStatement = null;
ResultSet lResultSet = null;
StringBuffer lSqlBuf = new StringBuffer();
String deliveryType = "";
try {
lSqlBuf.append("SELECT deliverytypecode ");
lSqlBuf.append("FROM cmdtymtbl");
lSqlBuf.append("WHERE shopcode=").append(SIDBUtil.SQL2Str(shopCode," "));
lSqlBuf.append("AND CmdtyCode=").append(SIDBUtil.SQL2Str(lCmdtyCode));
lStatement = lConnection.createStatement();
lResultSet = lStatement.executeQuery(lSqlBuf.toString());
if (lResultSet.next()) {
deliveryType = lResultSet.getString("deliverytypecode");
}
} catch (Exception ex) {
throw new SIDBAccessException(ex);
} finally {
SIDBUtil.close(lResultSet, lStatement);
}
return deliveryType;
}
public static void reserveOrderAllocation(Connection conn, int allocate, String shopCode, String cmdtyCode, String individualCode, String orderCode, int deliveryCode,
int detailCode, String branch) throws SIDBAccessException {
StringBuffer sqlStatement = new StringBuffer();
SIDateTime time = new SIDateTime();
// 引当情報チェック
sqlStatement.append("SELECT * FROM orderallocationinfotbl ");
sqlStatement.append("WHERE shopcode=").append(SIDBUtil.SQL2Str(shopCode," "));
sqlStatement.append("AND ordercode=").append(SIDBUtil.SQL2Str(orderCode," "));
sqlStatement.append("AND cmdtycode=").append(SIDBUtil.SQL2Str(cmdtyCode," "));
sqlStatement.append("AND individualcode=").append(SIDBUtil.SQL2Str(individualCode," "));
sqlStatement.append("AND branchcode=").append(SIDBUtil.SQL2Str(branch));
// 実行
try {
if (SIDBUtil.hasData(conn, sqlStatement.toString())) {// 存在する場合
SISpcType lSpc = new SISpcType("orderallocationnumber+" + allocate);
SIModifyRec lRec = new SIModifyRec("orderallocationinfotbl");
lRec.addCondition("shopcode", shopCode);
lRec.addCondition("ordercode", orderCode);
lRec.addCondition("cmdtycode", cmdtyCode);
lRec.addCondition("individualcode", individualCode);
lRec.addCondition("branchcode", branch);
lRec.add("orderallocationnumber", lSpc);
lRec.execute(conn);
} else {// 存在しない場合
SIInsertRec lRec = new SIInsertRec("orderallocationinfotbl");
lRec.add("ordercode", orderCode);
lRec.add("deliverycode", deliveryCode);
lRec.add("detailcode", detailCode);
lRec.add("shopcode", shopCode);
lRec.add("orderbranchcode", "1");
lRec.add("cmdtycode", cmdtyCode);
lRec.add("individualcode", individualCode);
lRec.add("branchcode", branch);
lRec.add("orderallocationnumber", allocate);
lRec.add("initdatetime", time.getFullDate());
lRec.add("updatedatetime", time.getFullDate());
lRec.execute(conn);
}
} catch (Exception ex) {
throw new SIDBAccessException(ex);
}
}
public static void recaOrderAllocation(Connection conn, int allocate, String shopCode, String cmdtyCode, String individualCode, String orderCode, int deliveryCode,
int detailCode, String branch) throws SIDBAccessException {
StringBuffer sqlStatement = new StringBuffer();
SIDateTime time = new SIDateTime();
// 引当情報チェック
sqlStatement.append("SELECT * FROM orderallocationinfotbl ");
sqlStatement.append("WHERE shopcode=").append(SIDBUtil.SQL2Str(shopCode," "));
sqlStatement.append("AND ordercode=").append(SIDBUtil.SQL2Str(orderCode," "));
sqlStatement.append("AND cmdtycode=").append(SIDBUtil.SQL2Str(cmdtyCode," "));
sqlStatement.append("AND individualcode=").append(SIDBUtil.SQL2Str(individualCode," "));
sqlStatement.append("AND branchcode=").append(SIDBUtil.SQL2Str(branch));
try {
if (SIDBUtil.hasData(conn, sqlStatement.toString())) {// 存在する場合
SISpcType lSpc = new SISpcType("orderallocationnumber+" + allocate);
SIModifyRec lRec = new SIModifyRec("orderallocationinfotbl");
lRec.addCondition("shopcode", shopCode);
lRec.addCondition("ordercode", orderCode);
lRec.addCondition("cmdtycode", cmdtyCode);
lRec.addCondition("individualcode", individualCode);
lRec.addCondition("branchcode", branch);
lRec.add("orderallocationnumber", lSpc);
lRec.execute(conn);
} else {// 存在しない場合
SIInsertRec lRec = new SIInsertRec("orderallocationinfotbl");
lRec.add("ordercode", orderCode);
lRec.add("deliverycode", deliveryCode);
lRec.add("detailcode", detailCode);
lRec.add("shopcode", shopCode);
lRec.add("orderbranchcode", "1");
lRec.add("cmdtycode", cmdtyCode);
lRec.add("individualcode", individualCode);
lRec.add("branchcode", branch);
lRec.add("orderallocationnumber", allocate);
lRec.add("initdatetime", time.getFullDate());
lRec.add("updatedatetime", time.getFullDate());
lRec.execute(conn);
}
} catch (Exception ex) {
throw new SIDBAccessException(ex);
}
}
public static String getReserveCmdty(String lReserveCode, Connection lConnection) throws SIDBAccessException {
if (SIUtil.isNull(lReserveCode)) return "";
Statement lStatement = null;
ResultSet lResultSet = null;
StringBuffer lSqlBuf = new StringBuffer();
StringBuffer lResultBuf = new StringBuffer();
try {
lSqlBuf.append("SELECT ReserveCode,CmdtyCode,IndividualCode ");
lSqlBuf.append("FROM ReserveOrderTbl ");
lSqlBuf.append("WHERE ReserveCode=").append(SIDBUtil.SQL2Str(lReserveCode));
log.debug("getReserveCmdtyList:lSqlBuf=" + lSqlBuf.toString());
lStatement = lConnection.createStatement();
lResultSet = lStatement.executeQuery(lSqlBuf.toString());
lResultSet.next();
lResultBuf.append("0~");
lResultBuf.append(lResultSet.getString("CmdtyCode")).append("~");
lResultBuf.append(lResultSet.getString("IndividualCode")).append("~~~~");
} catch (Exception ex) {
throw new SIDBAccessException(ex);
} finally {
SIDBUtil.close(lResultSet, lStatement);
}
return lResultBuf.toString();
}
public static String getCtgryNames(Connection conn, String cmdtyCode) throws SIDBAccessException {
Statement lStatement = null;
ResultSet lResultSet = null;
String res = "";
String str = "SELECT ctgryname FROM ctgrymtbl a,cmdtyctgrymtbl b WHERE a.ctgrycode=b.ctgrycode AND b.cmdtycode=" + SIDBUtil.SQL2Str(cmdtyCode);
// 実行
try {
lStatement = conn.createStatement();
lResultSet = lStatement.executeQuery(str);
StringBuffer buff = new StringBuffer();
int cnt = 0;
// 商品レコードのセットの作成
while (lResultSet.next()) {
if (cnt > 0) {
buff.append("
");
}
buff.append(lResultSet.getString("ctgryname"));
cnt++;
}
res = buff.toString();
} catch (Exception ex) {
throw new SIDBAccessException(ex);
} finally {
SIDBUtil.close(lResultSet, lStatement);
}
return res;
}
public static String getCtgryName(Connection conn, String ctgrycode) throws SIDBAccessException {
String ctgryName = "";
ctgryName = SIDBUtil.getFirstData(conn, "SELECT ctgryname FROM ctgrymtbl WHERE ctgrycode=" + SIDBUtil.SQL2Str(ctgrycode));
if (SIUtil.isNull(ctgryName)) ctgryName = SIConfig.OTHERCATEGORYNAME;
return ctgryName;
}
public static String getDeliveryFee(Connection conn, String prefectureCode, String size, String deliveryType) throws SIDBAccessException {
String deliveryFee = "";
Statement lStatement = null;
ResultSet lResultSet = null;
StringBuffer lSqlBuf = new StringBuffer();
String colName = "size" + size;
try {
lSqlBuf.append("SELECT " + colName);
lSqlBuf.append(" FROM deliveryfeemtbl ");
lSqlBuf.append("WHERE prefectureCode=").append(SIDBUtil.SQL2Str(prefectureCode," "));
lSqlBuf.append("AND deliverytypecode=").append(SIDBUtil.SQL2Str(deliveryType));
log.debug("getReserveCmdtyList:lSqlBuf=" + lSqlBuf.toString());
lStatement = conn.createStatement();
lResultSet = lStatement.executeQuery(lSqlBuf.toString());
while (lResultSet.next()) {
deliveryFee = lResultSet.getString(colName);
}
} catch (Exception ex) {
throw new SIDBAccessException(ex);
} finally {
SIDBUtil.close(lResultSet, lStatement);
}
return deliveryFee;
}
public static boolean deleteKeep(Connection conn, String keepNum, String fromMail) throws SIDBAccessException {
return deleteKeep(conn, keepNum, false, fromMail);
}
public static boolean deleteKeep(Connection conn, String keepNum, boolean isDeleteAll, String fromMail) throws SIDBAccessException {
Statement lStatement = null;
ResultSet lResultSet = null;
boolean sendMail = false;
try {
String status = SIDBUtil.getFirstData(conn, "SELECT a.status FROM keeptbl a,keepdetailtbl b WHERE a.keepnumber=b.keepnumber AND a.keepnumber = " + SIDBUtil.SQL2Str(keepNum));
if (SIUtil.isNotNull(status)&&!status.equals("2")) sendMail = sendKeepDeleteMail(conn, keepNum, isDeleteAll, fromMail);// 未受注に限る
StringBuffer sql = new StringBuffer();
sql.append("DELETE FROM keeptbl ");
sql.append("WHERE keepnumber=").append(SIDBUtil.SQL2Str(keepNum));
Statement daStatement = conn.createStatement();
daStatement.execute(sql.toString());
if (daStatement!=null) daStatement.close();
if (status.equals("0") || status.equals("1")) {
StringBuffer keepdetailSql = new StringBuffer();
keepdetailSql.append("SELECT cmdtycode,individualcode,branchcode,amount,cmdtytype from keepdetailtbl ");
keepdetailSql.append("WHERE keepnumber=").append(SIDBUtil.SQL2Str(keepNum));
lStatement = conn.createStatement();
lResultSet = lStatement.executeQuery(keepdetailSql.toString());
while (lResultSet.next()) {
String cmdtyCode = lResultSet.getString("cmdtycode");
String individualCode = lResultSet.getString("individualcode");
String branchCode = lResultSet.getString("branchcode");
String amount = lResultSet.getString("amount");
String cmdtytype = lResultSet.getString("cmdtytype");
if (!branchCode.equals("999")||cmdtytype.equals("0")) {// 在庫管理する商品のみ在庫引き戻し
StringBuffer lockSsql = new StringBuffer();
lockSsql.append("SELECT * FROM storestocktbl ");
lockSsql.append("WHERE shopcode='0' ");
lockSsql.append("AND cmdtycode=").append(SIDBUtil.SQL2Str(cmdtyCode," "));
lockSsql.append("AND individualcode=").append(SIDBUtil.SQL2Str(individualCode," "));
lockSsql.append("AND branchcode=").append(SIDBUtil.SQL2Str(branchCode," "));
lockSsql.append("FOR UPDATE ");
Statement rsStatement = conn.createStatement();
rsStatement.execute(lockSsql.toString());
if (rsStatement!=null)rsStatement.close();
StringBuffer updateSsql = new StringBuffer();
updateSsql.append("UPDATE storestocktbl SET amount=amount+" + amount +" ");
updateSsql.append("WHERE shopcode='0' ");
updateSsql.append("AND cmdtycode=").append(SIDBUtil.SQL2Str(cmdtyCode," "));
updateSsql.append("AND individualcode=").append(SIDBUtil.SQL2Str(individualCode," "));
updateSsql.append("AND branchcode=").append(SIDBUtil.SQL2Str(branchCode));
Statement rsStatement2 = conn.createStatement();
rsStatement2.execute(updateSsql.toString());
if (rsStatement2!=null)rsStatement2.close();
}
}
}
StringBuffer delSql = new StringBuffer();
delSql.append("DELETE FROM keepdetailtbl ");
delSql.append("WHERE keepnumber=").append(SIDBUtil.SQL2Str(keepNum));
Statement daStatement2 = conn.createStatement();
daStatement2.execute(delSql.toString());
if (daStatement2!=null)daStatement2.close();
// EDBTG003-00 elecs-matsushima add start
// セット品情報の削除
SIDeleteRec lRec = new SIDeleteRec("keepsetcmdtytbl");
lRec.addCondition("keepnumber", keepNum);
lRec.execute(conn);
// EDBTG003-00 elecs-matsushima add end
} catch (Exception ex) {
throw new SIDBAccessException(ex);
} finally {
SIDBUtil.close(lResultSet, lStatement);
}
return sendMail;
}
// 放置キープ一括削除専用
public static void deleteEstimateFromKeep(Connection conn, String keepNumber) throws SIDBAccessException {
String estimateCode = SIDBUtil.getFirstData(conn, "SELECT estimatecode FROM estimatetbl WHERE keepnumber=" + SIDBUtil.SQL2Str(keepNumber));
if (SIUtil.isNull(estimateCode)) {
deleteKeep(conn, keepNumber, true, "");
return;
}
try {
SIDeleteRec delRec = new SIDeleteRec("estimatetbl");
delRec.addCondition("estimatecode", estimateCode);
delRec.execute(conn);
delRec = new SIDeleteRec("estimatedetailtbl");
delRec.addCondition("estimatecode", estimateCode);
delRec.execute(conn);
delRec = new SIDeleteRec("estimatedeliverytbl");
delRec.addCondition("estimatecode", estimateCode);
delRec.execute(conn);
deleteKeep(conn, keepNumber, true, "");
} catch (SIDuplicateKeyException e) {
throw new SIDBAccessException(e);
}
}
public static String getIndividual(HttpServletRequest request,Connection lConnection, String cmdtyCode) throws SIDBAccessException {
return getIndividual(request,lConnection, cmdtyCode, true);
}
public static String getIndividual(HttpServletRequest request,Connection lConnection, String cmdtyCode,boolean withLink) throws SIDBAccessException {
Statement lStatement = null;
ResultSet lResultSet = null;
String res = "";
String str = "SELECT individualcode FROM individualtbl WHERE cmdtycode = " + SIDBUtil.SQL2Str(cmdtyCode) + " ORDER BY individualcode DESC";
// 実行
try {
lStatement = lConnection.createStatement();
lResultSet = lStatement.executeQuery(str);
StringBuffer buff = new StringBuffer();
int cnt = 0;
// 商品レコードのセットの作成
while (lResultSet.next()) {
if (cnt > 0) {
buff.append("
");
}
String individualCode = lResultSet.getString("individualcode");
if (withLink) {
buff.append("");
buff.append(individualCode);
buff.append("");
} else {
buff.append(individualCode);
}
cnt++;
}
res = buff.toString();
} catch (Exception ex) {
throw new SIDBAccessException(ex);
} finally {
SIDBUtil.close(lResultSet, lStatement);
}
return res;
}
public static Collection getCtgrys(Connection conn, String root) {
Collection coll = new ArrayList();
Statement statement = null;
ResultSet resultSet = null;
StringBuffer sqlStatement = new StringBuffer();
SINameValue lNameValue = new SINameValue();
lNameValue = new SINameValue("詳細カテゴリで絞込み", "");
coll.add(lNameValue);
sqlStatement.append("SELECT ctgrycode,ctgryname FROM ctgrymtbl WHERE parentctgrycode = ").append(SIDBUtil.SQL2Str(root," "));
sqlStatement.append("AND ctgrycode NOT IN('category014','category015','category016','category110','VM','VH') ");
sqlStatement.append("ORDER BY ctgrycode ");
try {
statement = conn.createStatement();
resultSet = statement.executeQuery(sqlStatement.toString());
while (resultSet.next()) {
String code = resultSet.getString("ctgrycode");
String name = resultSet.getString("ctgryname");
lNameValue = new SINameValue(name, code);
coll.add(lNameValue);
}
} catch (Exception ex) {
ex.printStackTrace();
} finally {
SIDBUtil.close(resultSet, statement);
}
return coll;
}
public static String getPrefectureCode(Connection conn, String post1, String post2) {
if (SIUtil.isNull(post1) || SIUtil.isNull(post2)) return null;
String lPrefectureCode = "";
StringBuffer lSqlBuf = new StringBuffer();
lSqlBuf.append("SELECT PrefectureCode FROM SpcDeliveryMTbl ");
lSqlBuf.append("WHERE PostCode1=").append(SIDBUtil.SQL2Str(post1, " "));
lSqlBuf.append("AND PostCode2=").append(SIDBUtil.SQL2Str(post2));
try {
lPrefectureCode = SIDBUtil.getFirstData(conn, lSqlBuf.toString());
} catch (Exception e) {
e.printStackTrace();
}
return lPrefectureCode;
}
public static int getStockForKeep(Connection conn, SIKeepDetail keepDetail) {
int existKeep = 0;
int stockAmount = 0;
String exist = "";
String stock = "";
StringBuffer sql1 = new StringBuffer();
StringBuffer sql2 = new StringBuffer();
sql1.append("SELECT amount FROM keepdetailtbl ");
sql1.append("WHERE keepnumber=").append(SIDBUtil.SQL2Str(keepDetail.getKeepNumber()," "));
sql1.append("AND cmdtycode=").append(SIDBUtil.SQL2Str(keepDetail.getCmdtyCode()," "));
sql1.append("AND individualcode=").append(SIDBUtil.SQL2Str(keepDetail.getIndividualCode()," "));
sql1.append("AND branchcode=").append(SIDBUtil.SQL2Str(keepDetail.getBranchCode()));
sql2.append("SELECT amount FROM storestocktbl ");
sql2.append("WHERE cmdtycode=").append(SIDBUtil.SQL2Str(keepDetail.getCmdtyCode()," "));
sql2.append("AND individualcode=").append(SIDBUtil.SQL2Str(keepDetail.getIndividualCode()," "));
sql2.append("AND branchcode=").append(SIDBUtil.SQL2Str(keepDetail.getBranchCode()));
try {
if (SIUtil.isNotNull(keepDetail.getKeepNumber())) exist = SIDBUtil.getFirstData(conn, sql1.toString());
stock = SIDBUtil.getFirstData(conn, sql2.toString());
} catch (Exception e) {
e.printStackTrace();
}
if (SIUtil.isNotNull(exist)) existKeep = Integer.parseInt(exist);
if (SIUtil.isNotNull(stock)) stockAmount = Integer.parseInt(stock);
return existKeep + stockAmount;
}
public static String getSalesSituation(Connection conn, String lCmdtyCode) {
StringBuffer lSql = new StringBuffer();
Statement statement = null;
ResultSet resultSet = null;
StringBuffer situation = new StringBuffer();
lSql.append("SELECT frontDispFlg,amountFlg,amount-bgpAmount AS amount,rsrvEnableFlg,rsrvAmount,inquiryFlg ");
lSql.append(",CASE WHEN soldOutDate IS NULL OR soldOutDate + "+SIConfig.DEFAULT_SOLDOUT+" > CURRENT_DATE THEN 1 WHEN usedNewFlg IN ('1','7') THEN 1 ELSE 0 END AS soldOut ");
lSql.append("FROM cmdtyUnitTbl ");
lSql.append("WHERE cmdtyCode=").append(SIDBUtil.SQL2Str(lCmdtyCode," "));
lSql.append("ORDER BY individualCode DESC");
try {
statement = conn.createStatement();
resultSet = statement.executeQuery(lSql.toString());
while (resultSet.next()){
if(SIUtil.isNotNull(situation.toString())) situation.append("
");
if (resultSet.getString("frontDispFlg").equals("0")) situation.append("フロント非表示");
else if (resultSet.getString("inquiryFlg").equals("1")) situation.append("お問合せ");
else if (resultSet.getString("amountFlg").equals("0")) situation.append("カート");// カート
else if (!resultSet.getString("amount").equals("0")) situation.append("カート");// カート
else if (resultSet.getString("rsrvEnableFlg").equals("1") && (SIUtil.isNull(resultSet.getString("rsrvAmount")) || !resultSet.getString("rsrvAmount").equals("0"))) situation.append("予約");// 予約
else if (resultSet.getString("rsrvEnableFlg").equals("1") && (SIUtil.isNotNull(resultSet.getString("rsrvAmount")) && resultSet.getString("rsrvAmount").equals("0"))) situation.append("SOLD OUT");// 売り切れ
else if (resultSet.getString("soldOut").equals("1"))situation.append("SOLD OUT");// 売り切れ
else situation.append("フロント非表示");
}
} catch (Exception e) {
e.printStackTrace();
} finally {
SIDBUtil.close(resultSet, statement);
}
return situation.toString();
}
public static String getSalesSituation(Connection conn, String lCmdtyCode, String lIndividualCode) {
StringBuffer lSql = new StringBuffer();
Statement statement = null;
ResultSet resultSet = null;
String situation = "";
lSql.append("SELECT frontDispFlg,amountFlg,amount-bgpAmount AS amount,rsrvEnableFlg,rsrvAmount,inquiryFlg ");
lSql.append(",CASE WHEN soldOutDate IS NULL OR soldOutDate + "+SIConfig.DEFAULT_SOLDOUT+" > CURRENT_DATE THEN 1 WHEN usedNewFlg IN ('1','7') THEN 1 ELSE 0 END AS soldOut ");
lSql.append("FROM cmdtyUnitTbl ");
lSql.append("WHERE cmdtyCode=").append(SIDBUtil.SQL2Str(lCmdtyCode," "));
lSql.append("AND individualCode=").append(SIDBUtil.SQL2Str(lIndividualCode));
try {
statement = conn.createStatement();
resultSet = statement.executeQuery(lSql.toString());
resultSet.next();
if (resultSet.getString("frontDispFlg").equals("0")) situation = "none";// フロント非表示
else if (resultSet.getString("inquiryFlg").equals("1")) situation = "inquiry";// 問い合わせ
else if (resultSet.getString("amountFlg").equals("0")) situation = "cart";// カート
else if (!resultSet.getString("amount").equals("0")) situation = "cart";// カート
else if (resultSet.getString("rsrvEnableFlg").equals("1") && (SIUtil.isNull(resultSet.getString("rsrvAmount")) || !resultSet.getString("rsrvAmount").equals("0"))) situation = "reserve";// 予約
else if (resultSet.getString("rsrvEnableFlg").equals("1") && (SIUtil.isNotNull(resultSet.getString("rsrvAmount")) && resultSet.getString("rsrvAmount").equals("0"))) situation = "soldout";// 売り切れ
else if (resultSet.getString("soldOut").equals("1")) situation = "soldout";// 売り切れ
else situation = "none";// 売り切れ
} catch (Exception e) {
e.printStackTrace();
} finally {
SIDBUtil.close(resultSet, statement);
}
return situation;
}
public static String getSalesSituation(String frontdispflg, String inquiryflg, String amountflg, String rsrvenableflg, String rsrvamount, Collection cmdtyStockColl) {
String situation = "";
String amount = "0";
SIStoreStock stock;
Iterator amountIte = cmdtyStockColl.iterator();
while (amountIte.hasNext() && amount.equals("0")) {
stock = (SIStoreStock) amountIte.next();
amount = stock.getAmount();
}
if (frontdispflg.equals("0")) situation = "none";// フロント非表示
else if (inquiryflg.equals("1")) situation = "inquiry";// 問い合わせ
else if (amountflg.equals("0")) situation = "cart";// カート
else if (!amount.equals("0")) situation = "cart";// カート
else if (rsrvenableflg.equals("1") && !rsrvamount.equals("0")) situation = "reserve";// 予約
else situation = "soldout";// 売り切れ
return situation;
}
/*
public static void costCalculationAdd(Connection lConnection, String lCmdtyCode, String lIndividualCode, String lPurchasePrice, String lProcessingExpence, String lAmount,
boolean afterflg) {
try {
String lockSql = "SELECT * FROM individualtbl WHERE cmdtycode=" + SIDBUtil.SQL2Str(lCmdtyCode) + " AND individualcode=" + SIDBUtil.SQL2Str(lIndividualCode) + "FOR UPDATE";
String purchaseSql = "SELECT purchaseprice FROM individualtbl WHERE cmdtycode=" + SIDBUtil.SQL2Str(lCmdtyCode) + " AND individualcode=" + SIDBUtil.SQL2Str(lIndividualCode);
String processingSql = "SELECT processingexpence FROM individualtbl WHERE cmdtycode=" + SIDBUtil.SQL2Str(lCmdtyCode) + " AND individualcode=" + SIDBUtil.SQL2Str(lIndividualCode);
String amountSql = "SELECT Sum(realamount) FROM realstockvw WHERE cmdtycode=" + SIDBUtil.SQL2Str(lCmdtyCode) + " AND individualcode=" + SIDBUtil.SQL2Str(lIndividualCode);
SIDBUtil.hasData(lConnection, lockSql);// レコードロック
String purchaseprice = SIDBUtil.getFirstData(lConnection, purchaseSql);
String processingexpence = SIDBUtil.getFirstData(lConnection, processingSql);
String amount = SIDBUtil.getFirstData(lConnection, amountSql);
if (afterflg) amount = SIUtil.sub(amount, lAmount); // 計算時の帳簿在庫が加算済の場合
String denominator = SIUtil.add(amount, lAmount);
if (denominator.equals("0")) {
purchaseprice = "0";
processingexpence = "0";
} else {
purchaseprice = SIUtil.div(SIUtil.add(SIUtil.multi(purchaseprice, amount), lPurchasePrice), denominator);
processingexpence = SIUtil.div(SIUtil.add(SIUtil.multi(processingexpence, amount), lProcessingExpence), denominator);
}
SIModifyRec lRec = new SIModifyRec("individualtbl");
lRec.addCondition("cmdtycode", lCmdtyCode);
lRec.addCondition("individualcode", lIndividualCode);
lRec.add("purchaseprice", purchaseprice);
lRec.add("processingexpence", processingexpence);
lRec.execute(lConnection);
} catch (Exception e) {
e.printStackTrace();
}
}
public static void costCalculationSub(Connection lConnection, String lCmdtyCode, String lIndividualCode, String lPurchasePrice, String lProcessingExpence, String lAmount,
boolean afterflg) {
try {
String lockSql = "SELECT * FROM individualtbl WHERE cmdtycode=" + SIDBUtil.SQL2Str(lCmdtyCode) + " AND individualcode=" + SIDBUtil.SQL2Str(lIndividualCode) + "FOR UPDATE";
String purchaseSql = "SELECT purchaseprice FROM individualtbl WHERE cmdtycode=" + SIDBUtil.SQL2Str(lCmdtyCode) + " AND individualcode=" + SIDBUtil.SQL2Str(lIndividualCode);
String processingSql = "SELECT processingexpence FROM individualtbl WHERE cmdtycode=" + SIDBUtil.SQL2Str(lCmdtyCode) + " AND individualcode=" + SIDBUtil.SQL2Str(lIndividualCode);
String amountSql = "SELECT Sum(realamount) FROM realstockvw WHERE cmdtycode=" + SIDBUtil.SQL2Str(lCmdtyCode) + " AND individualcode=" + SIDBUtil.SQL2Str(lIndividualCode);
SIDBUtil.hasData(lConnection, lockSql);// レコードロック
String purchaseprice = SIDBUtil.getFirstData(lConnection, purchaseSql);
String processingexpence = SIDBUtil.getFirstData(lConnection, processingSql);
String amount = SIDBUtil.getFirstData(lConnection, amountSql);
if (afterflg) amount = SIUtil.add(amount, lAmount); // 計算時の帳簿在庫が減算済の場合
String denominator = SIUtil.sub(amount, lAmount);
if (denominator.equals("0")) {
purchaseprice = "0";
processingexpence = "0";
} else {
purchaseprice = SIUtil.div(SIUtil.sub(SIUtil.multi(purchaseprice, amount), lPurchasePrice), denominator);
processingexpence = SIUtil.div(SIUtil.sub(SIUtil.multi(processingexpence, amount), lProcessingExpence), denominator);
}
SIModifyRec lRec = new SIModifyRec("individualtbl");
lRec.addCondition("cmdtycode", lCmdtyCode);
lRec.addCondition("individualcode", lIndividualCode);
lRec.add("purchaseprice", purchaseprice);
lRec.add("processingexpence", processingexpence);
lRec.execute(lConnection);
} catch (Exception e) {
e.printStackTrace();
}
}
*/
public static Collection getStockCollection(Connection lConnection, String cmdtyCode, String individualCode) throws SIDBAccessException {
Statement lStatement = null;
ResultSet lResultSet = null;
StringBuffer lSqlBuf = new StringBuffer();
SIStoreStock lStock = new SIStoreStock();
Collection lStocks = new ArrayList();
lSqlBuf.append("SELECT b.branchname,a.amount From storestocktbl AS a, branchtbl AS b ");
lSqlBuf.append("WHERE a.cmdtycode='").append(cmdtyCode).append("' AND a.individualcode='").append(individualCode).append("' ");
lSqlBuf.append("AND a.branchcode=b.branchcode ");
lSqlBuf.append("ORDER BY a.branchcode ASC");
try {
lStatement = lConnection.createStatement();
lResultSet = lStatement.executeQuery(lSqlBuf.toString());
while (lResultSet.next()) {
lStock = new SIStoreStock();
lStock.setEncode(SIConfig.SIENCODE_NONE);
lStock.setBranchName(lResultSet.getString("BranchName"));
lStock.setAmount(lResultSet.getString("Amount"));
lStocks.add(lStock);
}
} catch (SQLException ex) {
ex.printStackTrace();
throw new SIDBAccessException(ex);
} finally {
SIDBUtil.close(lResultSet, lStatement);
}
return lStocks;
}
public static boolean sendAlertMail(Connection lConnection,String cmdtycode, String individualcode) throws SIDBAccessException, SQLException {
boolean lRes = true;
String[] sendList = new String[1];
SISendMail lSendMail = new SISendMail();
try {
lSendMail.setMailTemp(lConnection, "0", SIConfig.SIMAIL_TEMPLATE_STOCK_ALERT);
} catch (SIFatalException e2) {
e2.printStackTrace();
}
StringBuffer lMailBuf = new StringBuffer();
lMailBuf.append("\n-------------");
lMailBuf.append("\n");
Statement lStatement = null;
ResultSet lResultSet = null;
StringBuffer lSqlBuf = new StringBuffer();
lSqlBuf.append("SELECT i.alertamount,c.amount,c.cmdtyname ");
lSqlBuf.append("FROM cmdtyunittbl c,individualtbl i ");
lSqlBuf.append("WHERE i.amountflg = '1' ");
lSqlBuf.append("AND c.cmdtycode = i.cmdtycode ");
lSqlBuf.append("AND c.individualcode = i.individualcode ");
lSqlBuf.append("AND c.cmdtycode = ").append(SIDBUtil.SQL2Str(cmdtycode," "));
lSqlBuf.append("AND c.individualcode = ").append(SIDBUtil.SQL2Str(individualcode));
lStatement = lConnection.createStatement();
lResultSet = lStatement.executeQuery(lSqlBuf.toString());
lResultSet.next();
String alertAmount = lResultSet.getString("alertamount");
String amount = lResultSet.getString("amount");
lMailBuf.append("\n商品名:").append(lResultSet.getString("cmdtyname") + " 【" + individualcode + "】");
lMailBuf.append("\nアラート数量:").append(alertAmount);
lMailBuf.append("\n残り在庫数量:").append(amount);
lMailBuf.append("\n");
lMailBuf.append("\n-------------");
lMailBuf.append("\n");
lSendMail.appendContent1(lMailBuf.toString());
sendList[0] = "hacchu@beautygarage.jp";
try {
if (SIUtil.isNotNull(alertAmount) && Integer.parseInt(alertAmount) > Integer.parseInt(amount)) {
lSendMail.setToMailAddress(sendList);
lSendMail.execute();
}
} catch (SIFatalException e) {
lRes = false;
e.printStackTrace();
} finally {
SIDBUtil.close(lResultSet, lStatement);
}
return lRes;
}
public static boolean isOrderReserve(Connection lConnection, String lOrderCode) {
boolean lRes = false;
try {
StringBuffer lSqlBuf = new StringBuffer();
lSqlBuf.append("SELECT a.individualcode FROM orderallocationinfotbl AS a,orderreservetbl AS b,reserveordertbl AS c ");
lSqlBuf.append("WHERE a.ordercode=b.ordercode AND b.reservecode=c.reservecode AND a.branchcode=c.reservebranch ");
lSqlBuf.append("AND a.ordercode=").append(SIDBUtil.SQL2Str(lOrderCode));
lRes = SIDBUtil.hasData(lConnection, lSqlBuf.toString());
} catch (Exception e) {
e.printStackTrace();
lRes = false;
}
return lRes;
}
public static boolean isOrderReserve(Connection lConnection, String lOrderCode, String lBranchCode) {
boolean lRes = false;
try {
StringBuffer lSqlBuf = new StringBuffer();
lSqlBuf.append("SELECT b.individualcode FROM orderreservetbl AS a,reserveordertbl AS b ");
lSqlBuf.append("WHERE a.reservecode=b.reservecode ");
lSqlBuf.append("AND a.ordercode=").append(SIDBUtil.SQL2Str(lOrderCode, " "));
lSqlBuf.append("AND b.reservebranch=").append(SIDBUtil.SQL2Str(lBranchCode, " "));
lRes = SIDBUtil.hasData(lConnection, lSqlBuf.toString());
} catch (Exception e) {
e.printStackTrace();
lRes = false;
}
return lRes;
}
public static boolean isOrderReserve(Connection lConnection, String lOrderCode, String lCmdtyCode, String lIndividualCode, String lBranchCode) {
boolean lRes = false;
try {
StringBuffer lSqlBuf = new StringBuffer();
lSqlBuf.append("SELECT b.individualcode FROM orderreservetbl AS a,reserveordertbl AS b ");
lSqlBuf.append("WHERE a.reservecode=b.reservecode ");
lSqlBuf.append("AND a.ordercode=").append(SIDBUtil.SQL2Str(lOrderCode, " "));
lSqlBuf.append("AND b.reservebranch=").append(SIDBUtil.SQL2Str(lBranchCode, " "));
lSqlBuf.append("AND b.individualcode=").append(SIDBUtil.SQL2Str(lIndividualCode, " "));
lSqlBuf.append("AND b.cmdtycode=").append(SIDBUtil.SQL2Str(lCmdtyCode));
lRes = SIDBUtil.hasData(lConnection, lSqlBuf.toString());
} catch (Exception e) {
e.printStackTrace();
lRes = false;
}
return lRes;
}
public static String getReserveCode(Connection lConnection, String lOrderCode, String lCmdtyCode, String lIndividualCode, String lBranchCode) {
String lReserveCode = "";
Statement lStatement = null;
ResultSet lResultSet = null;
try {
StringBuffer lSqlBuf = new StringBuffer();
lSqlBuf.append("SELECT a.reservecode FROM orderreservetbl AS a,reserveordertbl AS b ");
lSqlBuf.append("WHERE a.reservecode=b.reservecode ");
lSqlBuf.append("AND a.ordercode=").append(SIDBUtil.SQL2Str(lOrderCode, " "));
lSqlBuf.append("AND b.reservebranch=").append(SIDBUtil.SQL2Str(lBranchCode, " "));
lSqlBuf.append("AND b.individualcode=").append(SIDBUtil.SQL2Str(lIndividualCode, " "));
lSqlBuf.append("AND b.cmdtycode=").append(SIDBUtil.SQL2Str(lCmdtyCode));
lStatement = lConnection.createStatement();
lResultSet = lStatement.executeQuery(lSqlBuf.toString());
while (lResultSet.next()) {
if (SIUtil.isNotNull(lReserveCode)) lReserveCode = lReserveCode + ",";
lReserveCode = lReserveCode + lResultSet.getString(1);
}
} catch (Exception e) {
e.printStackTrace();
lReserveCode = "";
} finally {
SIDBUtil.close(lStatement, lResultSet);
}
return lReserveCode;
}
public static void checkAndDeleteOrderallocation(Connection lConnection, String lOrderCode) {
StringBuffer lSqlBuf = new StringBuffer();
Statement lStatement = null;
ResultSet lResultSet = null;
// EDBTG003-00 elecs-matsushima mod start
// lSqlBuf.append("SELECT DISTINCT cmdtycode,individualcode FROM orderallocationinfotbl WHERE ordercode=").append(SIDBUtil.SQL2Str(lOrderCode, " "));
// lSqlBuf.append("AND NOT individualcode IN (SELECT individualcode FROM orderdetaillatestvw WHERE ordercode=").append(SIDBUtil.SQL2Str(lOrderCode, ")"));
lSqlBuf.append("SELECT DISTINCT cmdtycode,individualcode, detailcode FROM orderallocationinfotbl WHERE ordercode=").append(SIDBUtil.SQL2Str(lOrderCode, " "));
lSqlBuf.append("AND NOT individualcode||'_'||detailcode IN (SELECT individualcode||'_'||detailcode FROM orderdetaillatestvw WHERE ordercode=").append(SIDBUtil.SQL2Str(lOrderCode, ")"));
// EDBTG003-00 elecs-matsushima mod end
try {
if (SIDBUtil.hasData(lConnection, lSqlBuf.toString())) {
lStatement = lConnection.createStatement();
lResultSet = lStatement.executeQuery(lSqlBuf.toString());
while (lResultSet.next()) {
// EDBTG003-00 elecs-matsushima mod start
// log.debug("check and delete allocation data:" + lResultSet.getString("individualcode"));
// deleteOrderAllocation2(lConnection, "0", lOrderCode, lResultSet.getString("cmdtycode"), lResultSet.getString("individualcode"));
log.debug("check and delete allocation data:" + lResultSet.getString("individualcode") + ",detailcode:" + lResultSet.getString("detailcode"));
deleteOrderAllocation2(lConnection, "0", lOrderCode, lResultSet.getString("cmdtycode"), lResultSet.getString("individualcode"), lResultSet.getString("detailcode"));
// EDBTG003-00 elecs-matsushima mod end
}
}
} catch (Exception e) {
e.printStackTrace();
} finally {
SIDBUtil.close(lResultSet, lStatement);
}
}
public static void checkAndInsertOrderallocation(Connection lConnection, String lOrderCode) {
StringBuffer lSqlBuf = new StringBuffer();
Statement lStatement = null;
ResultSet lResultSet = null;
lSqlBuf.append("SELECT a.cmdtycode,a.individualcode,a.amount,a.deliverycode,a.detailcode FROM orderdetaillatestvw a,individualtbl b WHERE a.ordercode=").append(
SIDBUtil.SQL2Str(lOrderCode, " "));
// EDBTG003-00 elecs-matsushima mod start
// lSqlBuf.append("AND NOT a.individualcode IN (SELECT DISTINCT individualcode FROM orderallocationinfotbl WHERE ordercode=").append(SIDBUtil.SQL2Str(lOrderCode, ") "));
lSqlBuf.append("AND NOT a.individualcode||'_'||a.detailcode IN (SELECT DISTINCT individualcode||'_'||detailcode FROM orderallocationinfotbl WHERE ordercode=").append(SIDBUtil.SQL2Str(lOrderCode, ") "));
// EDBTG003-00 elecs-matsushima mod end
lSqlBuf.append("AND a.cmdtycode=b.cmdtycode AND a.individualcode=b.individualcode AND b.amountflg=1 ");
try {
if (SIDBUtil.hasData(lConnection, lSqlBuf.toString())) {
lStatement = lConnection.createStatement();
lResultSet = lStatement.executeQuery(lSqlBuf.toString());
while (lResultSet.next()) {
// EDBTG003-00 elecs-matsushima mod start
// log.debug("check and insert allocation data:" + lResultSet.getString("individualcode"));
log.debug("check and insert allocation data:" + lResultSet.getString("individualcode") + ", detailcode:" + lResultSet.getInt("detailcode"));
// EDBTG003-00 elecs-matsushima mod end
insertOrderAllocation(lConnection, lResultSet.getInt("amount"), "0", lResultSet.getString("cmdtycode"), lResultSet.getString("individualcode"), lOrderCode, lResultSet.getInt("deliverycode"), lResultSet.getInt("detailcode"));
}
}
} catch (Exception e) {
e.printStackTrace();
} finally {
SIDBUtil.close(lResultSet, lStatement);
}
}
public static String getCmdtyAmount(Connection lConnection, String lCmdtyCode) {
String amount = "0";
try {
amount = SIDBUtil.getFirstData(lConnection, "SELECT sum(amount) AS total FROM totalstockvw WHERE cmdtycode=" + SIDBUtil.SQL2Str(lCmdtyCode) + " GROUP BY cmdtycode ");
} catch (Exception e) {
e.printStackTrace();
}
return amount;
}
public static String getCmdtyAmount(Connection lConnection, String lCmdtyCode, String individualcode) {
String amount = "0";
try {
amount = SIDBUtil.getFirstData(lConnection, "SELECT amount FROM totalstockvw WHERE cmdtycode=" + SIDBUtil.SQL2Str(lCmdtyCode) + " AND individualcode=" + SIDBUtil.SQL2Str(individualcode));
} catch (Exception e) {
e.printStackTrace();
}
return amount;
}
private static boolean sendKeepDeleteMail(Connection lConnection, String keepnum, boolean isDeleteAll, String fromMail) {
StringBuffer sendListStr = new StringBuffer();
SISendMail lSendMail = new SISendMail();
try {
if (isDeleteAll) lSendMail.setMailTemp(lConnection, "0", SIConfig.SIMAIL_TEMPLATE_KEEP_ALL_DELETE);
else lSendMail.setMailTemp(lConnection, "0", SIConfig.SIMAIL_TEMPLATE_KEEP_DELETE);
} catch (SIFatalException e2) {
e2.printStackTrace();
}
lSendMail.appendMailTitle("", "【キープ番号：" + keepnum + "】");
StringBuffer lMailBuf = new StringBuffer();
Statement lStatement1 = null;
ResultSet lResultSet1 = null;
Statement lStatement2 = null;
ResultSet lResultSet2 = null;
try {
if (!SIDBUtil.hasData(lConnection, "SELECT * FROM keeptbl WHERE keepnumber=" + SIDBUtil.SQL2Str(keepnum) + " AND keepmaildatetime IS NOT NULL")) return false;
StringBuffer lSqlBuf1 = new StringBuffer();
lSqlBuf1.append("SELECT b.branchname,c.chargename,c.email,current_date AS today,a.amount,a.keepdate ");
lSqlBuf1.append("FROM (SELECT kh.keepnumber,kh.branchcode,kh.chargecode,kh.keepdate,count(kd.individualcode) AS amount ");
lSqlBuf1.append("FROM keeptbl kh,keepdetailtbl kd WHERE kh.keepnumber=kd.keepnumber AND kh.keepnumber=").append(SIDBUtil.SQL2Str(keepnum," "));
lSqlBuf1.append("GROUP BY kh.keepnumber,kh.branchcode,kh.chargecode,kh.keepdate) a ");
lSqlBuf1.append("LEFT OUTER JOIN (SELECT chargecode,chargename,email FROM chargetbl) c ");
lSqlBuf1.append("ON (a.chargecode=c.chargecode) ");
lSqlBuf1.append("LEFT OUTER JOIN (SELECT branchcode,branchname FROM branchtbl) b ");
lSqlBuf1.append("ON (a.branchcode=b.branchcode) ");
lStatement1 = lConnection.createStatement();
lResultSet1 = lStatement1.executeQuery(lSqlBuf1.toString());
String custname = SIDBUtil.getFirstData(lConnection, "SELECT custname FROM estimatetbl WHERE keepnumber='" + keepnum + "'");
String allocate = "";
String branchlog = "";
String branch = "";
String charge = "";
while (lResultSet1.next()) {
lMailBuf.append("\n-------------");
lMailBuf.append("\nキープ番号:").append(keepnum);
if (SIUtil.isNull(lResultSet1.getString("branchname"))) branch = "なし";
else branch = lResultSet1.getString("branchname") + "支店";
lMailBuf.append("\n担当支店:").append(branch);
if (SIUtil.isNull(lResultSet1.getString("chargename"))) charge = "なし";
else charge = lResultSet1.getString("chargename");
lMailBuf.append(" 担当者:").append(charge);
if (SIUtil.isNotNull(custname)) lMailBuf.append("\n顧客名:").append(custname);
lMailBuf.append("\n取り置き解除商品数:").append(lResultSet1.getString("amount")).append("点");
lMailBuf.append("\n取り置き日:").append(lResultSet1.getString("keepdate"));
lMailBuf.append("\n解除日:").append(lResultSet1.getString("today"));
if (SIUtil.isNotNull(lResultSet1.getString("email"))) {
if (sendListStr.length() != 0) sendListStr.append(",");
sendListStr.append(lResultSet1.getString("email"));
}
}
StringBuffer lSqlBuf2 = new StringBuffer();
lSqlBuf2.append("SELECT kd.cmdtyname,kd.individualcode,kd.amount,kd.initdatetime,co.colorname ");
lSqlBuf2.append(",CASE WHEN kd.cmdtytype = '0' THEN db.email ELSE '' END AS email ");
lSqlBuf2.append(",CASE WHEN kd.cmdtytype = '0' THEN db.branchname ELSE '' END AS dbranch ");
lSqlBuf2.append("FROM keepdetailtbl kd ");
lSqlBuf2.append("LEFT OUTER JOIN (SELECT branchcode,branchname,email FROM branchtbl) db ");
lSqlBuf2.append("ON (kd.branchcode=db.branchcode) ");
lSqlBuf2.append(",colortbl co ,cmdtymtbl cm ");
lSqlBuf2.append("WHERE kd.keepnumber = ").append(SIDBUtil.SQL2Str(keepnum," "));
lSqlBuf2.append("AND kd.cmdtycode = cm.cmdtycode ");
lSqlBuf2.append("AND co.colorcode = cm.colorcode ");
lSqlBuf2.append("ORDER BY db.branchcode,dbranch DESC ");
lStatement2 = lConnection.createStatement();
lResultSet2 = lStatement2.executeQuery(lSqlBuf2.toString());
while (lResultSet2.next()) {
lMailBuf.append("\n-------------");
lMailBuf.append("\n商品名:").append(lResultSet2.getString("cmdtyname") + " 【" + lResultSet2.getString("individualcode") + "】");
if (SIUtil.isNull(lResultSet2.getString("dbranch"))) allocate = "在庫管理なし";
else allocate = lResultSet2.getString("dbranch");
lMailBuf.append("\nカラー:").append(lResultSet2.getString("colorname"));
lMailBuf.append("\n取り置き店舗:").append(allocate);
lMailBuf.append("\n取り置き数量:").append(lResultSet2.getString("amount"));
lMailBuf.append("\n取り置き日:").append(lResultSet2.getString("initdatetime"));
lMailBuf.append("\n");
if (!allocate.equals(branchlog)) {// 一つ前と同じ支店の場合はアドレスを追加しない
branchlog = allocate;
if (SIUtil.isNotNull(lResultSet2.getString("email"))) {
if (sendListStr.length() != 0) sendListStr.append(",");
sendListStr.append(lResultSet2.getString("email"));
}
}
}
lMailBuf.append("\n-------------");
lMailBuf.append("\n");
lSendMail.appendContent1(lMailBuf.toString());
try {
lSendMail.setFromMailAddress(fromMail);
lSendMail.setToMailAddress(sendListStr.toString().split(","));
lSendMail.execute();
SIDBUtil.close(lStatement1, lResultSet1);
SIDBUtil.close(lStatement2, lResultSet2);
return true;
} catch (SIFatalException e) {
e.printStackTrace();
return false;
}
} catch (Exception e) {
e.printStackTrace();
return false;
}
}
public static void sendOrderDeleteMail(Connection lConnection, String ordercode, String fromMail) {
StringBuffer sendListStr = new StringBuffer();
SISendMail lSendMail = new SISendMail();
try {
lSendMail.setMailTemp(lConnection, "0", SIConfig.SIMAIL_TEMPLATE_KEEP_DELETE);
} catch (SIFatalException e2) {
e2.printStackTrace();
}
String reservemsg = "";
if (isOrderReserve(lConnection, ordercode)) {
//sendListStr.append("reserve@beautygarage.jp");
reservemsg = "※予約商品含む";
}
lSendMail.appendMailTitle("", "【受注番号：" + ordercode + "】" + reservemsg);
StringBuffer lMailBuf = new StringBuffer();
Statement lStatement1 = null;
ResultSet lResultSet1 = null;
Statement lStatement2 = null;
ResultSet lResultSet2 = null;
try {
if (!SIDBUtil.hasData(lConnection, "SELECT * FROM orderlatestvw WHERE ordercode="+ SIDBUtil.SQL2Str(ordercode) + " AND (keepmaildate IS NOT NULL OR shippmentmaildate IS NOT NULL)"))
return;
StringBuffer lSqlBuf1 = new StringBuffer();
lSqlBuf1.append("SELECT b.branchname,c.chargename,c.email,current_date AS today,a.custname,a.initdatetime,a.paymethodname,a.amount ");
lSqlBuf1.append("FROM (SELECT oh.ordercode,oh.branchcode,oh.chargecode,oh.custname,oh.initdatetime,oh.paymethodname,count(od.individualcode) AS amount ");
lSqlBuf1.append("FROM orderlatestvw oh,orderdetaillatestvw od WHERE oh.ordercode=od.ordercode AND oh.ordercode=").append(SIDBUtil.SQL2Str(ordercode," "));
lSqlBuf1.append(" AND od.setdetailflg <> ").append(SIDBUtil.SQL2Str(SIConfig.SET_DETAIL_FLG_OPTION," ")); // EDBTG003-00 elecs-matsushima add
lSqlBuf1.append("GROUP BY oh.ordercode,oh.branchcode,oh.chargecode,oh.custname,oh.initdatetime,oh.paymethodname) a ");
lSqlBuf1.append("LEFT OUTER JOIN (SELECT chargecode,chargename,email FROM chargetbl) c ");
lSqlBuf1.append("ON (a.chargecode=c.chargecode) ");
lSqlBuf1.append("LEFT OUTER JOIN (SELECT branchcode,branchname FROM branchtbl) b ");
lSqlBuf1.append("ON (a.branchcode=b.branchcode) ");
lStatement1 = lConnection.createStatement();
lResultSet1 = lStatement1.executeQuery(lSqlBuf1.toString());
String allocate = "";
String branchlog = "";
String branch = "";
String charge = "";
while (lResultSet1.next()) {
lMailBuf.append("\n-------------");
lMailBuf.append("\n受注番号:").append(ordercode);
if (SIUtil.isNull(lResultSet1.getString("branchname"))) branch = "なし";
else branch = lResultSet1.getString("branchname") + "支店";
lMailBuf.append("\n担当支店:").append(branch);
if (SIUtil.isNull(lResultSet1.getString("chargename"))) charge = "なし";
else charge = lResultSet1.getString("chargename");
lMailBuf.append(" 担当者:").append(charge);
lMailBuf.append("\n顧客名:").append(lResultSet1.getString("custname"));
lMailBuf.append("\n支払方法:").append(lResultSet1.getString("paymethodname"));
lMailBuf.append("\n取り置き解除商品数:").append(lResultSet1.getString("amount")).append("点");
lMailBuf.append("\n受注日:").append(lResultSet1.getString("initdatetime"));
lMailBuf.append("\n解除日:").append(lResultSet1.getString("today"));
if (SIUtil.isNotNull(lResultSet1.getString("email"))) {
if (sendListStr.length() != 0) sendListStr.append(",");
sendListStr.append(lResultSet1.getString("email"));
}
}
StringBuffer lSqlBuf2 = new StringBuffer();
// EDBTG003-00 elecs-matsushima mod start
// lSqlBuf2.append("SELECT od.cmdtyname,od.individualcode,coalesce(oa.orderallocationnumber,od.amount) AS amount ");
lSqlBuf2.append("SELECT od.cmdtyname,od.individualcode, SUM(coalesce(oa.orderallocationnumber,od.amount)) AS amount ");
// EDBTG003-00 elecs-matsushima mod end
lSqlBuf2.append(",db.email,db.branchname,oa.branchcode,od.cmdtycode,co.colorname ");
lSqlBuf2.append("FROM orderdetaillatestvw od ");
// EDBTG003-00 elecs-matsushima mod start
// lSqlBuf2.append("LEFT OUTER JOIN (SELECT ordercode,branchcode,cmdtycode,individualcode,orderallocationnumber FROM orderallocationinfotbl) oa ");
// lSqlBuf2.append("ON (od.ordercode=oa.ordercode AND od.cmdtycode=oa.cmdtycode AND od.individualcode=oa.individualcode) ");
lSqlBuf2.append("LEFT OUTER JOIN (SELECT ordercode, detailcode, branchcode,cmdtycode,individualcode,orderallocationnumber FROM orderallocationinfotbl) oa ");
lSqlBuf2.append("ON (od.ordercode=oa.ordercode AND od.cmdtycode=oa.cmdtycode AND od.individualcode=oa.individualcode AND od.detailcode=oa.detailcode) ");
// EDBTG003-00 elecs-matsushima mod end
lSqlBuf2.append("LEFT OUTER JOIN (SELECT branchcode,branchname,email FROM branchtbl) db ");
lSqlBuf2.append("ON (oa.branchcode=db.branchcode) ");
lSqlBuf2.append(",colortbl co ,cmdtymtbl cm ");
lSqlBuf2.append("WHERE od.ordercode=").append(SIDBUtil.SQL2Str(ordercode," "));
lSqlBuf2.append("AND od.cmdtycode=cm.cmdtycode ");
lSqlBuf2.append("AND co.colorcode=cm.colorcode ");
// EDBTG003-00 elecs-matsushima add start
lSqlBuf2.append("AND od.setdetailflg <> ").append(SIDBUtil.SQL2Str(SIConfig.SET_DETAIL_FLG_OPTION," "));
lSqlBuf2.append("GROUP BY od.cmdtyname,od.individualcode,db.email,db.branchname,oa.branchcode,od.cmdtycode,co.colorname,db.branchcode ");
// EDBTG003-00 elecs-matsushima add end
lSqlBuf2.append("ORDER BY db.branchcode ");
lStatement2 = lConnection.createStatement();
lResultSet2 = lStatement2.executeQuery(lSqlBuf2.toString());
while (lResultSet2.next()) {
lMailBuf.append("\n-------------");
lMailBuf.append("\n商品名:").append(lResultSet2.getString("cmdtyname") + " 【" + lResultSet2.getString("individualcode") + "】");
if (isOrderReserve(lConnection, ordercode, lResultSet2.getString("cmdtycode"), lResultSet2.getString("individualcode"), lResultSet2.getString("branchcode"))) {
lMailBuf.append(" 【予約商品】");
lMailBuf.append("\n予約番号:").append(
getReserveCode(lConnection, ordercode, lResultSet2.getString("cmdtycode"), lResultSet2.getString("individualcode"), lResultSet2.getString("branchcode")));
}
if (SIUtil.isNull(lResultSet2.getString("branchname"))) allocate = "在庫管理なし";
else allocate = lResultSet2.getString("branchname");
lMailBuf.append("\nカラー:").append(lResultSet2.getString("colorname"));
lMailBuf.append("\n取り置き店舗:").append(allocate);
lMailBuf.append("\n取り置き数量:").append(lResultSet2.getString("amount"));
lMailBuf.append("\n");
if (!allocate.equals(branchlog)) {// 一つ前と同じ支店の場合はアドレスを追加しない
branchlog = allocate;
if (SIUtil.isNotNull(lResultSet2.getString("email"))) {
if (sendListStr.length() != 0) sendListStr.append(",");
sendListStr.append(",").append(lResultSet2.getString("email"));
}
}
}
lMailBuf.append("\n-------------");
lMailBuf.append("\n");
lSendMail.appendContent1(lMailBuf.toString());
try {
lSendMail.setFromMailAddress(fromMail);
lSendMail.setToMailAddress(sendListStr.toString().split(","));
SIDBUtil.close(lStatement1, lResultSet1);
SIDBUtil.close(lStatement2, lResultSet2);
} catch (Exception e) {
e.printStackTrace();
}
} catch (Exception e) {
e.printStackTrace();
}
}
public static void sendKeepMail(Connection lConnection, String keepnum, String comment, String fromMail) throws SIDBAccessException, SQLException {
StringBuffer sendListStr = new StringBuffer();
SISendMail lSendMail = new SISendMail();
try {
lSendMail.setMailTemp(lConnection, "0", SIConfig.SIMAIL_TEMPLATE_KEEP_INSERT);
} catch (SIFatalException e2) {
e2.printStackTrace();
}
StringBuffer lMailBuf = new StringBuffer();
Statement lStatement1 = null;
ResultSet lResultSet1 = null;
Statement lStatement2 = null;
ResultSet lResultSet2 = null;
if (!SIDBUtil.hasData(lConnection, "SELECT * FROM keepdetailtbl WHERE keepnumber=" + SIDBUtil.SQL2Str(keepnum))) return;
if (SIDBUtil.hasData(lConnection, "SELECT * FROM keeptbl WHERE keepnumber=" + SIDBUtil.SQL2Str(keepnum) + " AND keepmailflg='0'")) lSendMail.appendMailTitle("", "【キープ番号：" + keepnum + "】");
else {
lSendMail.appendMailTitle("【再送】", "【キープ番号：" + keepnum + "】");
}
StringBuffer lSqlBuf1 = new StringBuffer();
lSqlBuf1.append("SELECT b.branchname,c.chargename,c.email,a.keepdate,a.amount ");
lSqlBuf1.append("FROM (SELECT kh.keepnumber,kh.branchcode,kh.chargecode,kh.keepdate,count(kd.individualcode) AS amount ");
lSqlBuf1.append("FROM keeptbl kh,keepdetailtbl kd WHERE kh.keepnumber=kd.keepnumber AND kh.keepnumber=").append(SIDBUtil.SQL2Str(keepnum," "));
// EDBTG003-00 mng-paku add start
lSqlBuf1.append("AND kd.setdetailflg != ").append(SIDBUtil.SQL2Str(SIConfig.SET_DETAIL_FLG_OPTION, " "));
// EDBTG003-00 mng-paku add end
lSqlBuf1.append("GROUP BY kh.keepnumber,kh.branchcode,kh.chargecode,kh.keepdate) a ");
lSqlBuf1.append("LEFT OUTER JOIN (SELECT chargecode,chargename,email FROM chargetbl) c ");
lSqlBuf1.append("ON (a.chargecode=c.chargecode) ");
lSqlBuf1.append("LEFT OUTER JOIN (SELECT branchcode,branchname FROM branchtbl) b ");
lSqlBuf1.append("ON (a.branchcode=b.branchcode) ");
lStatement1 = lConnection.createStatement();
lResultSet1 = lStatement1.executeQuery(lSqlBuf1.toString());
String custname = SIDBUtil.getFirstData(lConnection, "SELECT custname FROM estimatetbl WHERE keepnumber=" + SIDBUtil.SQL2Str(keepnum));
String allocate = "";
String branchlog = "";
String branch = "";
String charge = "";
while (lResultSet1.next()) {
lMailBuf.append("\n-------------");
lMailBuf.append("\nキープ番号:").append(keepnum);
if (SIUtil.isNull(lResultSet1.getString("branchname"))) branch = "なし";
else branch = lResultSet1.getString("branchname") + "支店";
lMailBuf.append("\n担当支店:").append(branch);
if (SIUtil.isNull(lResultSet1.getString("chargename"))) charge = "なし";
else charge = lResultSet1.getString("chargename");
lMailBuf.append(" 担当者:").append(charge);
if (SIUtil.isNotNull(custname)) lMailBuf.append("\n顧客名:").append(custname);
lMailBuf.append("\n取り置き商品数:").append(lResultSet1.getString("amount")).append("点");
lMailBuf.append("\n取り置き日:").append(lResultSet1.getString("keepdate"));
if (SIUtil.isNotNull(lResultSet1.getString("email"))) {
if (sendListStr.length() != 0) sendListStr.append(",");
sendListStr.append(lResultSet1.getString("email"));
}
}
if (SIUtil.isNotNull(comment)) {
lMailBuf.append("\n-------------");
lMailBuf.append("\n").append(comment).append("\n");
}
StringBuffer lSqlBuf2 = new StringBuffer();
lSqlBuf2.append("SELECT kd.cmdtyname,kd.individualcode,kd.amount,kd.initdatetime,co.colorname ");
lSqlBuf2.append(",CASE WHEN kd.cmdtytype = 0 THEN db.email ELSE '' END AS email ");
lSqlBuf2.append(",CASE WHEN kd.cmdtytype = 0 THEN db.branchname ELSE '' END AS dbranch ");
lSqlBuf2.append(",kd.branchcode ");
lSqlBuf2.append("FROM keepdetailtbl kd ");
lSqlBuf2.append("LEFT OUTER JOIN branchtbl db ");
lSqlBuf2.append("ON (kd.branchcode=db.branchcode) ");
lSqlBuf2.append(",colortbl co ,cmdtymtbl cm ");
lSqlBuf2.append("WHERE kd.keepnumber = " + SIDBUtil.SQL2Str(keepnum," "));
lSqlBuf2.append("AND kd.cmdtycode = cm.cmdtycode ");
lSqlBuf2.append("AND co.colorcode = cm.colorcode ");
// EDBTG003-00 mng-paku add start
lSqlBuf2.append("AND kd.setdetailflg != ").append(SIDBUtil.SQL2Str(SIConfig.SET_DETAIL_FLG_OPTION, " "));
// EDBTG003-00 mng-paku add end
lSqlBuf2.append("ORDER BY db.branchcode,dbranch DESC ");
lStatement2 = lConnection.createStatement();
lResultSet2 = lStatement2.executeQuery(lSqlBuf2.toString());
while (lResultSet2.next()) {
lMailBuf.append("\n-------------");
lMailBuf.append("\n商品名:").append(lResultSet2.getString("cmdtyname") + " 【" + lResultSet2.getString("individualcode") + "】");
if (SIUtil.isNull(lResultSet2.getString("dbranch"))) allocate = "在庫管理なし";
else allocate = lResultSet2.getString("dbranch");
lMailBuf.append("\nカラー:").append(lResultSet2.getString("colorname"));
lMailBuf.append("\n取り置き店舗:").append(allocate);
lMailBuf.append("\n取り置き数量:").append(lResultSet2.getString("amount"));
lMailBuf.append("\n取り置き日:").append(lResultSet2.getString("initdatetime"));
lMailBuf.append("\n");
if (!allocate.equals(branchlog)&&!lResultSet2.getString("branchcode").equals("18")) {// 一つ前と同じ支店or物流センターの場合はアドレスを追加しない
branchlog = allocate;
if (SIUtil.isNotNull(lResultSet2.getString("email"))) {
if (sendListStr.length() != 0) sendListStr.append(",");
sendListStr.append(lResultSet2.getString("email"));
}
}
}
lMailBuf.append("\n-------------");
lMailBuf.append("\n");
lSendMail.appendContent1(lMailBuf.toString());
try {
lSendMail.setFromMailAddress(fromMail);
lSendMail.setToMailAddress(sendListStr.toString().split(","));
lSendMail.execute();
SIDBUtil.close(lStatement1, lResultSet1);
SIDBUtil.close(lStatement2, lResultSet2);
SIDateTime lDateTime = new SIDateTime();
SIModifyRec lRec = new SIModifyRec("keeptbl");
lRec.addCondition("keepnumber", keepnum);
lRec.add("keepmaildatetime", lDateTime.getFullDateTime());// 現在時刻
lRec.add("keepmailflg", "1");
lRec.execute(lConnection);
} catch (SIFatalException e) {
e.printStackTrace();
} catch (SIDuplicateKeyException e) {
e.printStackTrace();
}
}
public static boolean sendReserveMail(Connection lConnection, String reservecode, String fromMail) throws SIDBAccessException, SQLException {
StringBuffer sendListStr = new StringBuffer();
boolean lRes = true;
SISendMail lSendMail = new SISendMail();
try {
lSendMail.setMailTemp(lConnection, "0", SIConfig.SIMAIL_TEMPLATE_KEEP_INSERT);
} catch (SIFatalException e2) {
e2.printStackTrace();
}
StringBuffer lMailBuf = new StringBuffer();
Statement lStatement = null;
ResultSet lResultSet = null;
if (!SIDBUtil.hasData(lConnection, "SELECT * FROM reserveordertbl WHERE reservecode=" + SIDBUtil.SQL2Str(reservecode))) return false;
lSendMail.appendMailTitle("", "【予約番号：" + reservecode + "】");
StringBuffer lSqlBuf = new StringBuffer();
lSqlBuf.append("SELECT b.branchname,b.email,a.custname,a.cmdtyname,a.individualcode,a.assignamount,current_date AS initdate,c.colorname ");
lSqlBuf.append("FROM reserveordertbl a ");
lSqlBuf.append("LEFT OUTER JOIN (SELECT branchcode,branchname,email FROM branchtbl) b ");
lSqlBuf.append("ON (a.reservebranch=b.branchcode) ");
lSqlBuf.append(",colortbl c ,cmdtymtbl d ");
lSqlBuf.append("WHERE a.reservecode=").append(SIDBUtil.SQL2Str(reservecode," "));
lSqlBuf.append("AND a.cmdtycode = d.cmdtycode ");
lSqlBuf.append("AND c.colorcode = d.colorcode ");
lStatement = lConnection.createStatement();
lResultSet = lStatement.executeQuery(lSqlBuf.toString());
while (lResultSet.next()) {
lMailBuf.append("\n-------------");
lMailBuf.append("\n予約番号:").append(reservecode);
lMailBuf.append("\n顧客名:").append(lResultSet.getString("custname"));
lMailBuf.append("\n取り置き日:").append(lResultSet.getString("initdate"));
lMailBuf.append("\n-------------");
lMailBuf.append("\n商品名:").append(lResultSet.getString("cmdtyname") + " 【" + lResultSet.getString("individualcode") + "】【予約商品】");
lMailBuf.append("\nカラー:").append(lResultSet.getString("colorname"));
lMailBuf.append("\n取り置き店舗:").append(lResultSet.getString("branchname"));
lMailBuf.append("\n取り置き数量:").append(lResultSet.getString("assignamount"));
lMailBuf.append("\n");
sendListStr.append(lResultSet.getString("email"));
}
lMailBuf.append("\n-------------");
lMailBuf.append("\n");
lSendMail.appendContent1(lMailBuf.toString());
try {
lSendMail.setFromMailAddress(fromMail);
lSendMail.setToMailAddress(sendListStr.toString().split(","));
lRes = lSendMail.execute();
lStatement.close();
lResultSet.close();
SIDateTime lDateTime = new SIDateTime();
SIModifyRec lRec = new SIModifyRec("reserveordertbl");
lRec.addCondition("reservecode", reservecode);
lRec.add("reservemaildatetime", lDateTime.getFullDateTime());// 現在時刻
lRec.execute(lConnection);
} catch (SIFatalException e) {
e.printStackTrace();
} catch (SIDuplicateKeyException e) {
e.printStackTrace();
}
return lRes;
}
public static void sendReserveDeleteMail(Connection lConnection, String reservecode, String fromMail) throws SIDBAccessException, SQLException {
StringBuffer sendListStr = new StringBuffer("reserve@beautygarage.jp");
SISendMail lSendMail = new SISendMail();
try {
lSendMail.setMailTemp(lConnection, "0", SIConfig.SIMAIL_TEMPLATE_KEEP_DELETE);
} catch (SIFatalException e2) {
e2.printStackTrace();
}
StringBuffer lMailBuf = new StringBuffer();
Statement lStatement = null;
ResultSet lResultSet = null;
if (!SIDBUtil.hasData(lConnection, "SELECT * FROM reserveordertbl WHERE reservecode=" + SIDBUtil.SQL2Str(reservecode) + " AND reservebranch IS NOT NULL")) return;
lSendMail.appendMailTitle("", "【予約番号：" + reservecode + "】");
StringBuffer lSqlBuf = new StringBuffer();
lSqlBuf.append("SELECT b.branchname,b.email,a.custname,a.cmdtyname,a.individualcode,a.assignamount,a.reservemaildatetime::date,c.colorname ");
lSqlBuf.append("FROM reserveordertbl a ");
lSqlBuf.append("LEFT OUTER JOIN (SELECT branchcode,branchname,email FROM branchtbl) b ");
lSqlBuf.append("ON (a.reservebranch=b.branchcode) ");
lSqlBuf.append(",colortbl c ,cmdtymtbl d ");
lSqlBuf.append("WHERE a.reservecode=").append(SIDBUtil.SQL2Str(reservecode," "));
lSqlBuf.append("AND a.cmdtycode = d.cmdtycode ");
lSqlBuf.append("AND c.colorcode = d.colorcode ");
lStatement = lConnection.createStatement();
lResultSet = lStatement.executeQuery(lSqlBuf.toString());
while (lResultSet.next()) {
lMailBuf.append("\n-------------");
lMailBuf.append("\n予約番号:").append(reservecode);
lMailBuf.append("\n顧客名:").append(lResultSet.getString("custname"));
lMailBuf.append("\n取り置き日:").append(lResultSet.getString("reservemaildatetime"));
lMailBuf.append("\n-------------");
lMailBuf.append("\n商品名:").append(lResultSet.getString("cmdtyname") + " 【" + lResultSet.getString("individualcode") + "】【予約商品】");
lMailBuf.append("\nカラー:").append(lResultSet.getString("colorname"));
lMailBuf.append("\n取り置き店舗:").append(lResultSet.getString("branchname"));
lMailBuf.append("\n取り置き数量:").append(lResultSet.getString("assignamount"));
lMailBuf.append("\n");
sendListStr.append(",").append(lResultSet.getString("email"));
}
lMailBuf.append("\n-------------");
lMailBuf.append("\n");
lSendMail.appendContent1(lMailBuf.toString());
try {
lSendMail.setFromMailAddress(fromMail);
lSendMail.setToMailAddress(sendListStr.toString().split(","));
lSendMail.execute();
SIDBUtil.close(lStatement, lResultSet);
} catch (SIFatalException e) {
e.printStackTrace();
}
}
//EDBTG003-00 elecs-matsushima mod start
// public static String getKeepBranchList(Connection lConnection,String keepNumber,String cmdtyCode,String individualCode){
public static String getKeepBranchList(Connection lConnection,String keepNumber,String cmdtyCode,String individualCode, String setCode, String setDetailCode){
// EDBTG003-00 elecs-matsushima mod end
Statement lStatement = null;
ResultSet lResultSet = null;
if (SIUtil.isNull(keepNumber)||SIUtil.isNull(cmdtyCode)||SIUtil.isNull(individualCode)) return "";
StringBuffer str = new StringBuffer();
StringBuffer sql = new StringBuffer();
sql.append("SELECT CASE WHEN a.cmdtytype = 1 THEN '在庫管理なし' ELSE b.branchName END AS branchName,a.amount ");
sql.append("FROM keepdetailtbl a,branchtbl b ");
sql.append("WHERE a.branchCode=b.branchCode ");
sql.append("AND a.keepNumber = ").append(SIDBUtil.SQL2Str(keepNumber," "));
sql.append("AND a.cmdtyCode = ").append(SIDBUtil.SQL2Str(cmdtyCode," "));
sql.append("AND a.individualCode = ").append(SIDBUtil.SQL2Str(individualCode," "));
// EDBTG003-00 elecs-matsushima add start
if (SIUtil.isNotNull(setCode)){
sql.append("AND a.setcode = ").append(SIDBUtil.SQL2Str(setCode," "));
} else {
// EDBTG003-00 [不具合管理票-No29] 2013/03/19 nagayoshi mod start
// sql.append("AND a.setdetailcode is null ");
// 通常品（デフォルト値）の場合
sql.append("AND a.setcode = -1 ");
// EDBTG003-00 [不具合管理票-No29] 2013/03/19 nagayoshi mod end
}
// EDBTG003-00 [不具合管理票-No29] 2013/03/19 nagayoshi mod end
if (SIUtil.isNotNull(setDetailCode)){
sql.append("AND a.setdetailcode = ").append(SIDBUtil.SQL2Str(setDetailCode," "));
} else {
// EDBTG003-00 [不具合管理票-No29] 2013/03/19 nagayoshi mod start
// 通常品（デフォルト値対応）
// sql.append("AND a.setdetailcode is null ");
sql.append("AND a.setdetailcode = 0 ");
// EDBTG003-00 [不具合管理票-No29] 2013/03/19 nagayoshi mod end
}
// EDBTG003-00 elecs-matsushima add end
sql.append("ORDER BY a.branchCode");
try{
lStatement = lConnection.createStatement();
lResultSet = lStatement.executeQuery(sql.toString());
while (lResultSet.next()) {
if (SIUtil.isNotNull(str.toString())) str.append("
");
str.append(lResultSet.getString("branchName")).append(lResultSet.getString("amount"));
}
} catch (Exception e) {
str = new StringBuffer();
} finally {
SIDBUtil.close(lStatement, lResultSet);
}
return str.toString();
}
public static void readCookie(HttpServletRequest request, UIUserLogin userLogin) throws IOException {
Cookie[] cookies = request.getCookies();
Cookie mailCookie = null;
Cookie passCookie = null;
if (cookies != null) {
for (int i = 0; i < cookies.length; i++) {
if (cookies[i].getName().equals("ws_mailaddressBG")) mailCookie = cookies[i];
if (cookies[i].getName().equals("ws_passwordBG")) passCookie = cookies[i];
}
}
if (mailCookie != null) userLogin.setEmailTxt(URLDecoder.decode(mailCookie.getValue(), "Shift_JIS"));
if (passCookie != null) userLogin.setPasswordTxt(URLDecoder.decode(passCookie.getValue(), "Shift_JIS"));
}
public static Collection getDisclosureColl(Connection lConnection) {
SIOrder lOrder = new SIOrder();
StringBuffer lSqlBuf = new StringBuffer();
Statement lStatement = null;
ResultSet lResultSet = null;
Collection lCollection = new ArrayList();
int count = 1;
lSqlBuf.append("SELECT initdatetime,sumofprice ");
lSqlBuf.append(",CASE address1 WHEN '京都府' THEN '京都' WHEN '大阪府' THEN '大阪' WHEN '東京都' THEN '東京' ELSE replace(address1,'県','') END AS address1 ");
lSqlBuf.append("FROM rfmanalysetbl ");
lSqlBuf.append("WHERE initdatetime>=current_timestamp + '-36 hours'::interval ");
lSqlBuf.append("ORDER BY initdatetime DESC ");
lSqlBuf.append("LIMIT 100");
try {
lStatement = lConnection.createStatement();
lResultSet = lStatement.executeQuery(lSqlBuf.toString());
while (lResultSet.next()) {
lOrder = new SIOrder();
lOrder.setOrderCode(String.valueOf(count));
lOrder.setInitDateTime(SIDBUtil.getDateTime(lResultSet.getTimestamp("initDateTime")));
lOrder.setAddress1(lResultSet.getString("address1"));
lOrder.setTotalOfPrice(lResultSet.getString("sumofprice"));
lCollection.add(lOrder);
count++;
}
} catch(Exception e) {
e.printStackTrace();
lCollection = new ArrayList();
} finally {
SIDBUtil.close(lStatement, lResultSet);
}
return lCollection;
}
public static String getStockNumberPicking(Connection lConnection,String individualCode) {
String res="";
StringBuffer str = new StringBuffer();
str.append("SELECT stocknumber FROM stocknumbertbl ");
str.append("WHERE stocktype='1' AND individualcode=").append(SIDBUtil.SQL2Str(individualCode));
try{
res = SIDBUtil.getFirstData(lConnection, str.toString());
}catch(Exception e){
e.printStackTrace();
}
return res;
}
public static String getStockNumberAll(Connection lConnection,String individualCode) {
StringBuffer res = new StringBuffer();
StringBuffer str = new StringBuffer();
str.append("SELECT stocknumber FROM stocknumbertbl ");
str.append("WHERE individualcode=").append(SIDBUtil.SQL2Str(individualCode," "));
str.append("ORDER BY stocktype DESC,stocknumber ");
Statement lStatement = null;
ResultSet lResultSet = null;
try{
lStatement = lConnection.createStatement();
lResultSet = lStatement.executeQuery(str.toString());
while (lResultSet.next()) {
if (!lResultSet.isFirst()) res.append(" ");
res.append(lResultSet.getString(1));
}
}catch(Exception e){
e.printStackTrace();
}finally{
SIDBUtil.close(lResultSet, lStatement);
}
return res.toString();
}
public static String googleAnalyticsGetImageUrl(HttpServletRequest request,String param) throws UnsupportedEncodingException {
String GA_ACCOUNT = "MO-1040584-11";
String GA_PIXEL = SIHTMLUtil.getRootPath(request) + "/ga.jsp";
StringBuffer url = new StringBuffer();
url.append(GA_PIXEL + "?");
url.append("utmac=").append(GA_ACCOUNT);
url.append("&utmn=").append(Integer.toString((int) (Math.random() * 0x7fffffff)));
String referer = request.getHeader("referer");
String query = request.getQueryString();
String path = request.getRequestURI();
if (referer == null || "".equals(referer)) referer = "-";
url.append("&utmr=").append(URLEncoder.encode(referer, "UTF-8"));
if (path != null) {
if (query != null) path += "?" + query;
url.append("&utmp=").append(URLEncoder.encode(path, "UTF-8"));
}
url.append(param);
url.append("&guid=ON");
return url.toString().replaceAll("&", "&");
}
// EDBTG003-00 nagayoshi add start
public static String getSetIndividual(HttpServletRequest request,Connection lConnection, String cmdtyCode, boolean linkFlg) throws SIDBAccessException {
return getSetIndividual(request,lConnection, cmdtyCode, "", linkFlg);
}
public static String getSetIndividual(HttpServletRequest request,Connection lConnection, String cmdtyCode, String individualCode, boolean linkFlg) throws SIDBAccessException {
Statement lStatement = null;
ResultSet lResultSet = null;
String res = "";
StringBuffer sql = new StringBuffer();
// SQL生成
sql.append("SELECT A.SHOPCODE AS SETSHOPCODE ");
sql.append(",A.CMDTYCODE AS SETCMDTYCODE ");
sql.append(",A.INDIVIDUALCODE AS SETINDIVIDUALCODE ");
sql.append("FROM CMDTYCOMPOSITIONMTBL A ");
sql.append("WHERE ");
sql.append(" (EXISTS (SELECT * FROM COMPOSITIONGROUPDETAILTBL BB ");
sql.append(" WHERE A.SHOPCODE = BB.SHOPCODE AND A.CMDTYCODE = BB.CMDTYCODE AND A.INDIVIDUALCODE = BB.INDIVIDUALCODE ");
if (SIUtil.isNotNull(individualCode)) {
sql.append(" AND BB.DETAILINDIVIDUALCODE = ").append(SIDBUtil.SQL2Str(individualCode));
}
sql.append(" AND BB.DETAILCMDTYCODE = ").append(SIDBUtil.SQL2Str(cmdtyCode,") "));
sql.append(" OR ");
sql.append(" EXISTS (SELECT * FROM COMPOSITIONOPTIONTBL CC ");
sql.append(" WHERE A.SHOPCODE = CC.SHOPCODE AND A.CMDTYCODE = CC.CMDTYCODE AND A.INDIVIDUALCODE = CC.INDIVIDUALCODE ");
if (SIUtil.isNotNull(individualCode)) {
sql.append(" AND CC.DETAILINDIVIDUALCODE = ").append(SIDBUtil.SQL2Str(individualCode));
}
sql.append(" AND CC.DETAILCMDTYCODE = ").append(SIDBUtil.SQL2Str(cmdtyCode,") "));
sql.append(" OR ");
sql.append(" EXISTS (SELECT * FROM APPENDEDCMDTYTBL DD ");
sql.append(" WHERE A.SHOPCODE = DD.SHOPCODE AND A.CMDTYCODE = DD.CMDTYCODE AND A.INDIVIDUALCODE = DD.INDIVIDUALCODE ");
if (SIUtil.isNotNull(individualCode)) {
sql.append(" AND DD.DETAILINDIVIDUALCODE = ").append(SIDBUtil.SQL2Str(individualCode));
}
sql.append(" AND DD.DETAILCMDTYCODE = ").append(SIDBUtil.SQL2Str(cmdtyCode,")) "));
sql.append("ORDER BY A.INDIVIDUALCODE DESC");
// 実行
try {
lStatement = lConnection.createStatement();
lResultSet = lStatement.executeQuery(sql.toString());
StringBuffer buff = new StringBuffer();
String sep = "";
// 商品レコードのセットの作成
while (lResultSet.next()) {
buff.append(sep);
String setShopCode = lResultSet.getString("SETSHOPCODE");
String setCmdtyCode = lResultSet.getString("SETCMDTYCODE");
String setIndividualCode = lResultSet.getString("SETINDIVIDUALCODE");
if (linkFlg) {
buff.append("");
}
buff.append(setIndividualCode);
if (linkFlg) {
buff.append("");
}
sep = "
";
}
res = buff.toString();
} catch (Exception ex) {
throw new SIDBAccessException(ex);
} finally {
SIDBUtil.close(lResultSet, lStatement);
}
return res;
}
public static String getSetDetailIndividual(HttpServletRequest request,Connection lConnection, String shopCode, String cmdtyCode, String individualCode, boolean linkFlg) throws SIDBAccessException {
Statement lStatement = null;
ResultSet lResultSet = null;
String res = "";
StringBuffer sql = new StringBuffer();
// SQL生成
sql.append(" SELECT");
sql.append(" SETCOMDTY.CMDTYCODE");
sql.append(" ,SETCOMDTY.INDIVIDUALCODE");
sql.append(" FROM");
sql.append(" ((SELECT");
sql.append(" BB.DETAILCMDTYCODE AS CMDTYCODE");
sql.append(" ,BB.DETAILINDIVIDUALCODE AS INDIVIDUALCODE");
sql.append(" FROM");
sql.append(" CMDTYCOMPOSITIONMTBL A");
sql.append(" INNER JOIN COMPOSITIONGROUPMTBL B");
sql.append(" ON A.SHOPCODE = B.SHOPCODE");
sql.append(" AND A.CMDTYCODE = B.CMDTYCODE");
sql.append(" AND A.INDIVIDUALCODE = B.INDIVIDUALCODE");
sql.append(" INNER JOIN COMPOSITIONGROUPDETAILTBL BB");
sql.append(" ON B.SHOPCODE = BB.SHOPCODE");
sql.append(" AND B.CMDTYCODE = BB.CMDTYCODE");
sql.append(" AND B.INDIVIDUALCODE = BB.INDIVIDUALCODE");
sql.append(" AND B.GROUPCODE = BB.GROUPCODE");
sql.append(" WHERE A.SHOPCODE = ").append(SIDBUtil.SQL2Str(shopCode));
sql.append(" AND A.CMDTYCODE = ").append(SIDBUtil.SQL2Str(cmdtyCode));
sql.append(" AND A.INDIVIDUALCODE = ").append(SIDBUtil.SQL2Str(individualCode,")"));
sql.append(" UNION ALL");
sql.append(" (SELECT");
sql.append(" C.DETAILCMDTYCODE AS CMDTYCODE");
sql.append(" ,C.DETAILINDIVIDUALCODE AS INDIVIDUALCODE");
sql.append(" FROM");
sql.append(" CMDTYCOMPOSITIONMTBL A");
sql.append(" INNER JOIN COMPOSITIONOPTIONTBL C");
sql.append(" ON A.SHOPCODE = C.SHOPCODE");
sql.append(" AND A.CMDTYCODE = C.CMDTYCODE");
sql.append(" AND A.INDIVIDUALCODE = C.INDIVIDUALCODE");
sql.append(" WHERE A.SHOPCODE = ").append(SIDBUtil.SQL2Str(shopCode));
sql.append(" AND A.CMDTYCODE = ").append(SIDBUtil.SQL2Str(cmdtyCode));
sql.append(" AND A.INDIVIDUALCODE = ").append(SIDBUtil.SQL2Str(individualCode,")"));
sql.append(" UNION ALL");
sql.append(" (SELECT");
sql.append(" D.DETAILCMDTYCODE AS CMDTYCODE");
sql.append(" ,D.DETAILINDIVIDUALCODE AS INDIVIDUALCODE");
sql.append(" FROM");
sql.append(" CMDTYCOMPOSITIONMTBL A");
sql.append(" INNER JOIN APPENDEDCMDTYTBL D");
sql.append(" ON A.SHOPCODE = D.SHOPCODE");
sql.append(" AND A.CMDTYCODE = D.CMDTYCODE");
sql.append(" AND A.INDIVIDUALCODE = D.INDIVIDUALCODE");
sql.append(" WHERE A.SHOPCODE = ").append(SIDBUtil.SQL2Str(shopCode));
sql.append(" AND A.CMDTYCODE = ").append(SIDBUtil.SQL2Str(cmdtyCode));
sql.append(" AND A.INDIVIDUALCODE = ").append(SIDBUtil.SQL2Str(individualCode,")"));
sql.append(" ) SETCOMDTY");
sql.append(" GROUP BY");
sql.append(" SETCOMDTY.CMDTYCODE,SETCOMDTY.INDIVIDUALCODE");
sql.append(" ORDER BY");
sql.append(" SETCOMDTY.INDIVIDUALCODE DESC");
// 実行
try {
lStatement = lConnection.createStatement();
// パラメータ設定
lResultSet = lStatement.executeQuery(sql.toString());
StringBuffer buff = new StringBuffer();
String sep = "";
// 商品レコードのセットの作成
while (lResultSet.next()) {
buff.append(sep);
String resCmdtyCode = lResultSet.getString("CMDTYCODE");
String resIndividualCode = lResultSet.getString("INDIVIDUALCODE");
if (linkFlg) {
buff.append("");
}
buff.append(resIndividualCode);
if (linkFlg) {
buff.append("");
}
sep = "
";
}
res = buff.toString();
} catch (Exception ex) {
throw new SIDBAccessException(ex);
} finally {
SIDBUtil.close(lResultSet, lStatement);
}
return res;
}
// EDBTG003-00 nagayoshi add end
// EDBTG003-00 [不具合管理票-No23] 2013/03/14 nagayoshi add start
public static int getSetAmount(Connection lConnection, String shopCode, String cmdtyCode, String individualCode) {
// 返却変数
int amount = 0;
// 在庫管理フラグ
boolean amountFlg = false;
// SQL
StringBuffer sql = new StringBuffer();
sql.append("SELECT min(amount) as amount FROM ");
sql.append("(SELECT c.shopcode,c.cmdtycode,c.individualcode,trunc((a.amount - a.bgpamount) / c.compositioncount) AS amount ");
sql.append("FROM cmdtyunittbl a,compositiongroupdetailtbl c ");
sql.append("WHERE c.detailindividualcode=a.individualcode AND a.amountflg=1 ");
sql.append("UNION ALL ");
sql.append("SELECT c.shopcode,c.cmdtycode,c.individualcode, ");
sql.append("case when c.appendedcount < c.setcount ");
sql.append(" then trunc((a.amount - a.bgpamount) / (c.appendedcount / c.setcount)) + (c.setcount - 1) ");
sql.append(" else trunc((a.amount - a.bgpamount) / (c.appendedcount / c.setcount)) ");
sql.append("end AS amount ");
sql.append("FROM cmdtyunittbl a,appendedcmdtytbl c ");
sql.append("WHERE c.detailindividualcode=a.individualcode AND a.amountflg=1 ");
sql.append("UNION ALL ");
sql.append("SELECT c1.shopcode,c1.cmdtycode,c1.individualcode, ");
sql.append("case when c1.appendedcount < c1.setcount ");
sql.append(" then trunc((a.amount - a.bgpamount) / ((c1.appendedcount / c1.setcount) + c2.compositioncount)) + (c1.setcount - 1) ");
sql.append(" else trunc((a.amount - a.bgpamount) / ((c1.appendedcount / c1.setcount) + c2.compositioncount)) ");
sql.append("end AS amount ");
sql.append("FROM cmdtyunittbl a,appendedcmdtytbl c1,compositiongroupdetailtbl c2 ");
sql.append("WHERE c1.detailindividualcode=a.individualcode ");
sql.append("AND c1.detailindividualcode=c2.detailindividualcode ");
sql.append("AND c1.individualcode=c2.individualcode ");
sql.append("AND a.amountflg=1) list ");
sql.append("WHERE list.shopcode = ").append(SIDBUtil.SQL2Str(shopCode," "));
sql.append("AND list.cmdtycode = ").append(SIDBUtil.SQL2Str(cmdtyCode," "));
sql.append("AND list.individualcode = ").append(SIDBUtil.SQL2Str(individualCode));
// Statementインスタンス
Statement statement=null;
// ResultSetインスタンス
ResultSet resultset=null;
try {
// Statement生成
statement = lConnection.createStatement();
// SQL発行
resultset = statement.executeQuery(sql.toString());
// 結果セット
while(resultset.next()) {
// 固定セット品在庫コード設定
amount = resultset.getInt("amount");
if(SIUtil.isNotNull(resultset.getString("amount"))) amountFlg = true;
}
// 結果がない（全て受発注商品）時は-1を返す
if (!amountFlg) amount=-1;
else if (amount<0) amount=0;
} catch (SQLException e) {
e.printStackTrace();
} finally {
SIDBUtil.close(resultset, statement);
}
return amount;
}
// EDBTG003-00 [不具合管理票-No23] 2013/03/14 nagayoshi add end
public static String getTransactionData(Connection lConnection,String orderCode) {
Statement lStatement = null;
ResultSet lResultSet = null;
StringBuffer res = new StringBuffer();
StringBuffer sql = new StringBuffer();
try{
sql.append("SELECT ordercode,totalofprice+totalofdeliveryfee+discountdeliveryfee+fee+discountfee-sumbypoint-sumofdiscount-setdiscount AS total ");
sql.append(",totalofdeliveryfee+discountdeliveryfee AS deliveryfee ");
sql.append("FROM ordersumvw WHERE ordercode=").append(orderCode);
lStatement = lConnection.createStatement();
lResultSet = lStatement.executeQuery(sql.toString());
if (lResultSet.next()) {
res.append("'transactionId': '").append(orderCode).append("',");
res.append("'transactionAffiliation': 'ビューティガレージEC',");
res.append("'transactionTotal': ").append(lResultSet.getString("total")).append(",");
res.append("'transactionShipping': ").append(lResultSet.getString("deliveryfee")).append(",");
res.append("'transactionProducts': [");
}
sql = new StringBuffer();
sql.append("SELECT individualcode,translate(cmdtyname,e'\\'',e'’') AS cmdtyname,substring(individualcode,0,3) AS ctgry,price,amount ");
sql.append("FROM orderdetaillatestvw WHERE setindividualcode IS NULL AND ordercode=").append(orderCode);
sql.append(" UNION ALL ");
sql.append("SELECT s.setindividualcode AS individualcode,translate(s.cmdtyname,e'\\'',e'’') AS cmdtyname,substring(s.setindividualcode,0,3) AS ctgry,trunc(d.price/s.setamount)-s.setdiscount AS price,s.setamount AS amount ");
sql.append("FROM ordersetcmdtytbl s,ordertbl o");
sql.append(",(SELECT ordercode,orderbranchcode,setcode,setindividualcode,sum(price*amount) AS price FROM orderdetailtbl GROUP BY ordercode,orderbranchcode,setcode,setindividualcode) d ");
sql.append("WHERE s.ordercode=o.ordercode AND s.orderbranchcode=o.orderbranchcode AND s.ordercode=d.ordercode ");
sql.append("AND s.setindividualcode=d.setindividualcode AND s.orderbranchcode=d.orderbranchcode AND s.setcode=d.setcode ");
sql.append("AND o.enabledflg=1 AND o.ordercode=").append(orderCode);
lResultSet = lStatement.executeQuery(sql.toString());
while (lResultSet.next()){
if (!lResultSet.isFirst()) res.append(",");
res.append("{");
res.append("'sku': '").append(lResultSet.getString("individualcode")).append("',");
res.append("'name': '").append(lResultSet.getString("cmdtyname")).append("',");
res.append("'category': '").append(lResultSet.getString("ctgry")).append("',");
res.append("'price': ").append(lResultSet.getString("price")).append(",");
res.append("'quantity': ").append(lResultSet.getString("amount"));
res.append("}");
}
res.append("]");
}catch(Exception e){
e.printStackTrace();
}finally{
SIDBUtil.close(lResultSet, lStatement);
}
return res.toString();
}
public static boolean isSoldOutSet(Connection lConnection,String individualCode) {
Statement lStatement = null;
ResultSet lResultSet = null;
boolean res = false;
try{
String cmdtyCompositionFlg = "";
StringBuffer checkStr = new StringBuffer();
checkStr.append("SELECT x.cmdtycompositionflg,z.detailindividualcode ");
checkStr.append("FROM cmdtyunittbl x ");
checkStr.append("LEFT OUTER JOIN (SELECT a.detailindividualcode,a.individualcode FROM appendedcmdtytbl a,cmdtyunittbl c ");
checkStr.append(" WHERE a.detailindividualcode=c.individualcode AND c.amount-c.bgpamount