/**
* Copyright (c) 2003-2004 System Integrator Corporation.
* All Rights Reserved.
*/
package jp.co.sint.servlet.mallmgr;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.SQLException;
import java.text.ParseException;
import java.util.HashMap;
import javax.naming.NamingException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import jp.co.sint.basic.SICmdtyCtgry;
import jp.co.sint.basic.SICmdtyStndrd;
import jp.co.sint.basic.SIHacchuDetail;
import jp.co.sint.basic.SILogin;
import jp.co.sint.basic.SIPDFUtil;
import jp.co.sint.basic.SIPointMan;
import jp.co.sint.basic.SIStoreStock;
import jp.co.sint.basic.SITax;
import jp.co.sint.beans.mallmgr.UICmdtyKeep;
import jp.co.sint.beans.mallmgr.UICmdtyParent;
import jp.co.sint.beans.mallmgr.UICtgryListCond;
import jp.co.sint.beans.mallmgr.UICustAddress;
import jp.co.sint.beans.mallmgr.UIHacchuDetailListCond;
import jp.co.sint.beans.mallmgr.UIIOCsv;
import jp.co.sint.beans.mallmgr.UIIndividual;
import jp.co.sint.beans.mallmgr.UIRegCust;
import jp.co.sint.beans.mallmgr.UIRegHacchu;
import jp.co.sint.beans.mallmgr.UIRegHacchuPayment;
import jp.co.sint.beans.mallmgr.UIRegStndrdContent;
import jp.co.sint.beans.mallmgr.UIRegStndrdName;
import jp.co.sint.beans.mallmgr.UIRegWrapping;
import jp.co.sint.beans.mallmgr.UIStockHistory;
import jp.co.sint.config.SICSVConf;
import jp.co.sint.config.SIConfig;
import jp.co.sint.config.SIDBMultiConf;
import jp.co.sint.config.SIPDFConf;
import jp.co.sint.database.SIDBAccessException;
import jp.co.sint.database.SIDBUtil;
import jp.co.sint.database.SIDatabaseConnection;
import jp.co.sint.database.SIDateType;
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.database.SITableCondition;
import jp.co.sint.tools.SIBGPdfTool;
import jp.co.sint.tools.SICSVRead;
import jp.co.sint.tools.SICSVWrite;
import jp.co.sint.tools.SICheckDataConf;
import jp.co.sint.tools.SICheckUtil;
import jp.co.sint.tools.SICheckValid;
import jp.co.sint.tools.SICustomError;
import jp.co.sint.tools.SICustomErrors;
import jp.co.sint.tools.SIDateTime;
import jp.co.sint.tools.SIErrorException;
import jp.co.sint.tools.SIErrorFactory;
import jp.co.sint.tools.SIFatalException;
import jp.co.sint.tools.SIFileExistException;
import jp.co.sint.tools.SIHTMLUtil;
import jp.co.sint.tools.SIStringUtil;
import jp.co.sint.tools.SIURLParameter;
import jp.co.sint.tools.SIUtil;
import jp.co.sint.upload.SIUploadSrv;
import org.apache.commons.fileupload.FileUploadException;
import org.apache.log4j.Category;
import com.lowagie.text.DocumentException;
/**
* @version $Id: SIIOCsvSrv.java,v 1.0 2003/12/16 Exp $
* @author Jinwang Chen
*
Description:1.各テーブルからCSVファイルのデータを出力します。
* 2.各CSVファイルからデータを取って、それなりのテーブルにレコードを作製します。
* History
* Author Date Reason
* ============ ========== ===========================
* Jinwang Chen 2003/12/16 11:08:12 Original
*/
public class SIIOCsvSrv extends SIUploadSrv {
// ログ用のインスタンスの生成
private static Category log = Category.getInstance(SIConfig.SILOG4J_WEBSHOP_CATEGORY_NAME);
/**
* doUpdate HTTP リクエストの処理
*
* @param request リクエスト
* @param response
* @return なし
* @throws ServletException
* @throws IOException
*/
public void doUpdate(HttpServletRequest request, HttpServletResponse response) throws ServletException, FileNotFoundException, IOException {
SILogin manLogin = SIHTMLUtil.getLogin(request);
// 7.3.0 ST2023 追加 ここから
SIRegMngAccesslogSrv siRegMngAccesslogSrv = new SIRegMngAccesslogSrv();
String strDBDateTime = null;
// 7.3.0 ST2023 追加 ここまで
if (!manLogin.isLogin()) {
forwardKey(request, response, "webshop.jsp.manager.login");
return;
}
HttpSession session = request.getSession(true);// セッションの取得
SIDatabaseConnection databaseConnection = new SIDatabaseConnection();// DBへのコネクションの作成
SIURLParameter urlParam = new SIURLParameter(request);// 7.1.1 ST0236 追加
try {
String actionName = this.getActionName(urlParam);// 画面からのアクション //7.1.1 ST0236 修正
String editMode = this.getEditMode(urlParam);// DBへの編集モード //7.1.1 ST0236 修正
log.debug("actionName=" + actionName);
if (SIConfig.SIACTION_CSV.equalsIgnoreCase(actionName)) {// CSVファイルへの出力
UIIOCsv lIOCsv = new UIIOCsv();
lIOCsv.init(request,urlParam);// 7.1.1 ST0236 修正
if (lIOCsv.validate(request)) {
this.produceCSVFile(request, response, databaseConnection.getConnection(), lIOCsv);
}
if (!response.isCommitted()) {
session.setAttribute(SIConfig.SISESSION_MAN_IO_CSV_NAME,lIOCsv);
SICustomErrors errors = (SICustomErrors) request.getAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY);
if (errors == null) errors = new SICustomErrors();
if ((!SIConfig.SITABLE_SALESDATA_NAME.equals(lIOCsv.getCsvTblNameSel()) &&!SIConfig.SIVIEW_FLAGUPDATELOG_LIST_NAME.equals(lIOCsv.getCsvTblNameSel()))
|| errors.isEmpty()){
request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME, SIErrorFactory.getErrorMsg("database.query.notexist", "CSVデータ"));
}
forwardKey(request, response, "webshop.jsp.manager.output.csv");
// 7.3.0 ST2023 追加 ここから 管理ログ出力 (else以降を追加)
} else {
try {
if (lIOCsv.getCsvTblNameSel().equalsIgnoreCase(SIConfig.SITABLE_CUST_NAME)) {// 顧客マスタ
siRegMngAccesslogSrv.insertTableData(databaseConnection.getConnection(), request, "8", null);
} else if (lIOCsv.getCsvTblNameSel().equalsIgnoreCase(SIConfig.SITABLE_CUST_ADDRESS_NAME)) {// 顧客アドレス帳
siRegMngAccesslogSrv.insertTableData(databaseConnection.getConnection(), request, "10", null);
}
} catch (SIDBAccessException e) {
e.printStackTrace();
}
// 7.3.0 ST2023 追加 ここまで
}
}else if(SIConfig.SIACTION_SEARCH.equalsIgnoreCase(actionName)){//検索項目表示
UIIOCsv lIOCsv=new UIIOCsv();
lIOCsv.init(request,urlParam);
lIOCsv.reset();
session.setAttribute(SIConfig.SISESSION_MAN_IO_CSV_NAME,lIOCsv);
forwardKey(request, response, "webshop.jsp.manager.output.csv");
}else if(SIConfig.SIACTION_PDF.equalsIgnoreCase(actionName)){//PDF出力
UIIOCsv lIOCsv=new UIIOCsv();
lIOCsv.init(request,urlParam);
session.setAttribute(SIConfig.SISESSION_MAN_IO_CSV_NAME,lIOCsv);
try {
producePriceCardPDFFile(response, databaseConnection.getConnection(), lIOCsv);
} catch (SIDBAccessException e1) {
e1.printStackTrace();
} catch (DocumentException e2) {
e2.printStackTrace();
}
if (!response.isCommitted()) forwardKey(request, response, "webshop.jsp.manager.output.csv");
} else {// CSVファイル取込
File lCsvTempDir = (File) this.getServletContext().getAttribute("javax.servlet.context.tempdir");
File lCsvTempFile = new File("");
try {
lCsvTempFile = File.createTempFile("read_csv_", ".csv", lCsvTempDir);
} catch (IOException ioe) {
ioe.printStackTrace();
}
HashMap lCsvMap = new HashMap();
lCsvMap.put("destFile", lCsvTempFile.getAbsolutePath());
SICSVRead lRead = new SICSVRead();
UIIOCsv lIOCsv = new UIIOCsv();
try {
super.doUpload(request, response, lCsvMap);
lRead = new SICSVRead(lCsvTempFile.getAbsolutePath());
lIOCsv.setCsvTblNameSel(this.getMultiParameter(request, "csvTblNameSel"));
lIOCsv.setEditModeTxt(this.getMultiParameter(request, "editModeTxt"));
// ファイルをアップロードする時に、設定されるエラーメッセージを取得します。
String lRes = (String) request.getAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME);
strDBDateTime = SIDBUtil.getDBDateTime(databaseConnection.getConnection());// 7.3.0 ST2023 追加 DBサーバ日時取得
if (SIUtil.isNotNull(lRes)) {// エラーメッセージがあれば、CSVファイルのアップロード不成功
/*
} else if (lIOCsv.getCsvTblNameSel().equalsIgnoreCase(SIConfig.SITABLE_CUST_NAME)) {// 顧客
if (manLogin.isMall()) {// セキュリティのチェック
lRes = writeCust(request, response, databaseConnection.getConnection(), lRead, lIOCsv.getEditModeTxt(), strDBDateTime); // 7.3.0 ST2023 修正 引数追加
} else {
lRes = "
" + SIErrorFactory.getErrorMsg("manager.app.access.disable");
}
} else if (lIOCsv.getCsvTblNameSel().equalsIgnoreCase(SIConfig.SITABLE_CUST_ADDRESS_NAME)) {// 顧客アドレス帳
if (manLogin.isMall()) {// セキュリティのチェック
lRes = writeCustAddress(request, response, databaseConnection.getConnection(), lRead, lIOCsv.getEditModeTxt());
} else {
lRes = "
" + SIErrorFactory.getErrorMsg("manager.app.access.disable");
}
} else if (lIOCsv.getCsvTblNameSel().equalsIgnoreCase(SIConfig.SITABLE_CMDTY_NAME)) {// 商品
lRes = writeCmdty(request, response, databaseConnection.getConnection(), lRead, lIOCsv.getEditModeTxt());
} else if (lIOCsv.getCsvTblNameSel().equalsIgnoreCase(SIConfig.SITABLE_INDIVIDUAL_NAME)) {// 商品（在庫）
lRes = writeIndividual(request, response, databaseConnection.getConnection(), lRead, lIOCsv.getEditModeTxt());
} else if (lIOCsv.getCsvTblNameSel().equalsIgnoreCase(SIConfig.SITABLE_WRAPPING_NAME)) {// ラッピング
lRes = writeWrapping(request, response, databaseConnection.getConnection(), lRead, lIOCsv.getEditModeTxt());
} else if (lIOCsv.getCsvTblNameSel().equalsIgnoreCase(SIConfig.SITABLE_CMDTY_STNDRD_NAME)) {// 商品規格
lRes = writeCmdtyStndrd(request, response, databaseConnection.getConnection(), lRead, lIOCsv.getEditModeTxt());
} else if (lIOCsv.getCsvTblNameSel().equalsIgnoreCase(SIConfig.SITABLE_CMDTY_CTGRY_NAME)) {// 商品カテゴリ
if (lIOCsv.getEditModeTxt().equals(SIConfig.SIEDIT_MODE_INSERT)) {
lRes = writeCmdtyCtgry(request, response, databaseConnection.getConnection(), lRead, lIOCsv.getEditModeTxt());
} else {
lRes = "
" + SIErrorFactory.getErrorMsg("manager.app.only.insert", "商品カテゴリ関連マスタ");
}
} else if (lIOCsv.getCsvTblNameSel().equalsIgnoreCase(SIConfig.SITABLE_STNDRD_NAME_NAME)) {// 規格
lRes = writeStndrd(request, response, databaseConnection.getConnection(), lRead, lIOCsv.getEditModeTxt());
} else if (lIOCsv.getCsvTblNameSel().equalsIgnoreCase(SIConfig.SITABLE_STNDRD_CONTENT_NAME)) {// エレメント
lRes = writeStndrdContent(request, response, databaseConnection.getConnection(), lRead, lIOCsv.getEditModeTxt());
} else if (lIOCsv.getCsvTblNameSel().equalsIgnoreCase(SIConfig.SITABLE_STORE_STOCK_NAME)) {
lRes = this.writeStoreStock(request, response, databaseConnection.getConnection(), lRead, lIOCsv.getEditModeTxt());
} else if (lIOCsv.getCsvTblNameSel().equalsIgnoreCase(SIConfig.SITABLE_CTGRY_NAME)) {
lRes = this.writeCtgry(request, response, databaseConnection.getConnection(), lRead, lIOCsv.getEditModeTxt());
} else if (lIOCsv.getCsvTblNameSel().equalsIgnoreCase("storeHistoryTbl")) {
lRes = this.writeNewStore(request, response, databaseConnection.getConnection(), lRead);
*/
} else if (lIOCsv.getCsvTblNameSel().equalsIgnoreCase(SIConfig.SITABLE_HACCHUPAYMENTTBL_NAME)) {
lRes = this.writePayment(request, response, databaseConnection.getConnection(), lRead);
} else if (lIOCsv.getCsvTblNameSel().equalsIgnoreCase(SIConfig.SITABLE_KEEP_NAME)) {
lRes = this.writeKeep(request, response, databaseConnection.getConnection(), lRead);
} else if (lIOCsv.getCsvTblNameSel().equalsIgnoreCase(SIConfig.SITABLE_SHIPHISTORY_NAME)) {
lRes = this.writeMove(request, response, databaseConnection.getConnection(), lRead);
} else if (lIOCsv.getCsvTblNameSel().equalsIgnoreCase(SIConfig.SITABLE_ORDER_NAME)) {
lRes = this.writeRakutenOrder(request, response, databaseConnection.getConnection(), lRead);
} else if (lIOCsv.getCsvTblNameSel().equalsIgnoreCase(SIConfig.SIVIEW_WAREHOUSE_SHIP_LIST_NAME)) {
lRes = this.writeWarehouseShipData(request, response, databaseConnection.getConnection(), lRead);
} else if (lIOCsv.getCsvTblNameSel().equalsIgnoreCase(SIConfig.SITABLE_HACCHU_NAME)) {
lRes = this.writeHacchuData(request, response, databaseConnection.getConnection(), lRead);
} else if (lIOCsv.getCsvTblNameSel().equalsIgnoreCase(SIConfig.SIVIEW_WAREHOUSE_STORE_LIST_NAME)) {
lRes = this.writeWarehouseStoreData(request, response, databaseConnection.getConnection(), lRead);
} else if (lIOCsv.getCsvTblNameSel().equalsIgnoreCase(SIConfig.SIVIEW_MAILMAGAZINE_RESULT_NAME)){
lRes = this.writeMailData(request, response, databaseConnection.getConnection(), lRead);
} else if (lIOCsv.getCsvTblNameSel().equalsIgnoreCase(SIConfig.SITABLE_ORDER_PAYMENT_NAME)){
lRes = this.writePaymentData(request, response, databaseConnection.getConnection(), lRead);
}
// メッセージの出力
if (SIUtil.isNotNull(lRes)) {
try {databaseConnection.getConnection().rollback();} catch (SQLException e1) {}
request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME, lRes);
} else {
try {databaseConnection.getConnection().commit();} catch (SQLException e1) {}
try {
if (lIOCsv.getCsvTblNameSel().equalsIgnoreCase(SIConfig.SITABLE_CUST_NAME)) { // 顧客マスタ
siRegMngAccesslogSrv.insertTableData(databaseConnection.getConnection(), request, "11", strDBDateTime);
} else if (lIOCsv.getCsvTblNameSel().equalsIgnoreCase(SIConfig.SITABLE_CUST_ADDRESS_NAME)) { // 顧客アドレス帳
siRegMngAccesslogSrv.insertTableData(databaseConnection.getConnection(), request, "12", strDBDateTime);
}
} catch (SIDBAccessException e) {
e.printStackTrace();
}
if (lIOCsv.getEditModeTxt().equals(SIConfig.SIEDIT_MODE_INSERT)||lIOCsv.getCsvTblNameSel().equalsIgnoreCase(SIConfig.SITABLE_HACCHUPAYMENTTBL_NAME)) {
request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME, SIErrorFactory.getErrorMsg("manager.message.success.insert"));
} else {
request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME, SIErrorFactory.getErrorMsg("manager.message.success.modify"));
}
}
} catch (FileNotFoundException e) {
request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME, SIErrorFactory.getErrorMsg("manager.message.failure.exist", "ファイル"));
log.warn(e);
} catch (FileUploadException e) {
request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME, SIErrorFactory.getErrorMsg("manager.message.failure.upload"));
log.warn(e);
} catch (SIFileExistException e) {
request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME, e.toString());
log.warn(e);
} finally {
lRead.close();
lCsvTempFile.delete();
session.setAttribute(SIConfig.SISESSION_MAN_IO_CSV_NAME, lIOCsv);
lIOCsv = new UIIOCsv();// データソースを選択しないまま、取込ボタンを押すと、ほかの項目を保持する
lIOCsv.setCsvTblNameSel(this.getMultiParameter(request, "csvTblNameSel"));
lIOCsv.setEditModeTxt(this.getMultiParameter(request, "editModeTxt"));
session.setAttribute(SIConfig.SISESSION_MAN_IO_CSV_NAME, lIOCsv);
}
forwardKey(request, response, "webshop.jsp.manager.input.csv");
}
} catch (SQLException e) {
e.printStackTrace();
throw new ServletException();
} catch (NamingException e) {
e.printStackTrace();
throw new ServletException();
} finally {
databaseConnection.close();
}
}
/**
* produceCSVFile CSVファイルを作成します。
*
* @param response クライアントまでのresponse
* @param lConnection DBへのコネクション
* @param lIOCsv CSVファイルに出力するテーブル名
* @return なし
* @throws なし
*/
private void produceCSVFile(HttpServletRequest request, HttpServletResponse response, Connection lConnection, UIIOCsv lIOCsv) {
int lIndex = 0;
// 顧客のログイン情報を取得
SILogin lLogin = SIHTMLUtil.getLogin(request);
// テーブルの取得
if (lIOCsv.getCsvTblNameSel().equalsIgnoreCase(SIConfig.SITABLE_CUST_NAME)) {// 顧客
lIndex = SICSVConf.SICSV_CUST_INX;
} else if (lIOCsv.getCsvTblNameSel().equalsIgnoreCase(SIConfig.SITABLE_CUST_ADDRESS_NAME)) {// 顧客アドレス帳
lIndex = SICSVConf.SICSV_CUST_ADDRESS_INX;
} else if (lIOCsv.getCsvTblNameSel().equalsIgnoreCase(SIConfig.SITABLE_CUST_ATTR_NAME)) {// 顧客属性
lIndex = SICSVConf.SICSV_CUST_ATTR_INX;
} else if (lIOCsv.getCsvTblNameSel().equalsIgnoreCase(SIConfig.SITABLE_CUST_HOBBY_NAME)) {// 顧客趣味
lIndex = SICSVConf.SICSV_CUST_HOBBY_INX;
} else if (lIOCsv.getCsvTblNameSel().equalsIgnoreCase(SIConfig.SITABLE_MEMBER_LEVEL_NAME)) {// 会員種別
lIndex = SICSVConf.SICSV_MEMBER_LEVEL_INX;
} else if (lIOCsv.getCsvTblNameSel().equalsIgnoreCase(SIConfig.SITABLE_PAYEE_INFO_NAME)) {// 支払先情報
lIndex = SICSVConf.SICSV_PAYEE_INFO_INX;
} else if (lIOCsv.getCsvTblNameSel().equalsIgnoreCase(SIConfig.SITABLE_PAY_METHOD_NAME)) {// 支払方法
lIndex = SICSVConf.SICSV_PAY_METHOD_INX;
} else if (lIOCsv.getCsvTblNameSel().equalsIgnoreCase(SIConfig.SITABLE_PAY_METHOD_FEE_NAME)) {// 支払方法手数料
lIndex = SICSVConf.SICSV_PAY_METHOD_FEE_INX;
} else if (lIOCsv.getCsvTblNameSel().equalsIgnoreCase(SIConfig.SITABLE_DELIVERY_TIME_NAME)) {// 時間帯
lIndex = SICSVConf.SICSV_DELIVERY_TIME_INX;
} else if (lIOCsv.getCsvTblNameSel().equalsIgnoreCase(SIConfig.SITABLE_CMDTY_NAME)) {// 商品テーブル
lIndex = SICSVConf.SICSV_CMDTY_LIST_INX;
} else if (lIOCsv.getCsvTblNameSel().equalsIgnoreCase(SIConfig.SITABLE_INDIVIDUAL_NAME)) {// 商品（在庫）テーブル
lIndex = SICSVConf.SICSV_INDIVIDUAL_LIST_INX;
} else if (lIOCsv.getCsvTblNameSel().equalsIgnoreCase(SIConfig.SITABLE_STORE_STOCK_NAME)) {// 支店別在庫テーブル
lIndex = SICSVConf.SICSV_STOCK_LIST_INX;
} else if (lIOCsv.getCsvTblNameSel().equalsIgnoreCase(SIConfig.SITABLE_CMDTY_CTGRY_NAME)) {// 商品カテゴリテーブル
lIndex = SICSVConf.SICSV_CMDTY_CTGRY_INX;
} else if (lIOCsv.getCsvTblNameSel().equalsIgnoreCase(SIConfig.SITABLE_CMDTY_STNDRD_NAME)) {// 商品規格テーブル
lIndex = SICSVConf.SICSV_CMDTY_STNDRD_INX;
} else if (lIOCsv.getCsvTblNameSel().equalsIgnoreCase(SIConfig.SITABLE_CONN_CMDTY_NAME)) {// 関連商品テーブル
lIndex = SICSVConf.SICSV_CONN_CMDTY_INX;
} else if (lIOCsv.getCsvTblNameSel().equalsIgnoreCase(SIConfig.SITABLE_STNDRD_CONTENT_NAME)) {// 規格内容
lIndex = SICSVConf.SICSV_STNDRD_CONTENT_INX;
} else if (lIOCsv.getCsvTblNameSel().equalsIgnoreCase(SIConfig.SITABLE_STNDRD_NAME_NAME)) {// 規格
lIndex = SICSVConf.SICSV_STNDRD_NAME_INX;
} else if (lIOCsv.getCsvTblNameSel().equalsIgnoreCase(SIConfig.SITABLE_WRAPPING_NAME)) {// ラッピング
lIndex = SICSVConf.SICSV_WRAPPING_INX;
} else if (lIOCsv.getCsvTblNameSel().equalsIgnoreCase(SIConfig.SITABLE_STOCK_STATUS_NAME)) {// 在庫状況
lIndex = SICSVConf.SICSV_STOCK_STATUS_INX;
} else if (lIOCsv.getCsvTblNameSel().equalsIgnoreCase(SIConfig.SITABLE_STOCK_NAME)) {// 仕入先
lIndex = SICSVConf.SICSV_STOCK_INX;
} else if (lIOCsv.getCsvTblNameSel().equalsIgnoreCase(SIConfig.SITABLE_CTGRY_NAME)) {// カテゴリ
lIndex = SICSVConf.SICSV_CTGRY_INX;
} else if (lIOCsv.getCsvTblNameSel().equalsIgnoreCase(SIConfig.SIVIEW_KAITORI_LIST_NAME)) {// 買取入庫リスト
lIndex = SICSVConf.SICSV_KAITORISTOCK_LIST_INX;
} else if (lIOCsv.getCsvTblNameSel().equalsIgnoreCase(SIConfig.SIVIEW_INVENTORY_LIST_NAME)) {// 買取入庫リスト
lIndex = SICSVConf.SICSV_INVENTORY_LIST_INX;
} else if (lIOCsv.getCsvTblNameSel().equalsIgnoreCase(SIConfig.SIVIEW_UNAPPROVAL_STORE_LIST_NAME)) {// 未承認入庫リスト
lIndex = SICSVConf.SICSV_UNAPPROVAL_STORE_LIST_INX;
} else if (lIOCsv.getCsvTblNameSel().equalsIgnoreCase(SIConfig.SIVIEW_UNAPPROVAL_SHIP_LIST_NAME)) {// 未承認出庫リスト
lIndex = SICSVConf.SICSV_UNAPPROVAL_SHIP_LIST_INX;
} else if (lIOCsv.getCsvTblNameSel().equalsIgnoreCase(SIConfig.SIVIEW_MIKANSAI_LIST_NAME)) {// 出荷済未完済リスト
lIndex = SICSVConf.SICSV_MIKANSAI_LIST_INX;
} else if (lIOCsv.getCsvTblNameSel().equalsIgnoreCase(SIConfig.SIVIEW_URIAGE_LIST_NAME)) {// 過去3ヶ月売上リスト
lIndex = SICSVConf.SICSV_URIAGE_LIST_INX;
} else if (lIOCsv.getCsvTblNameSel().equalsIgnoreCase(SIConfig.SIVIEW_URIAGE_LIST6_NAME)) {// 過去6ヶ月売上リスト
lIndex = SICSVConf.SICSV_URIAGE_LIST6_INX;
} else if (lIOCsv.getCsvTblNameSel().equalsIgnoreCase(SIConfig.SIVIEW_LACK_LIST_NAME)) {// 欠品管理リスト
lIndex = SICSVConf.SICSV_LACK_LIST_INX;
} else if (lIOCsv.getCsvTblNameSel().equalsIgnoreCase(SIConfig.SITABLE_SALESDATA_NAME)){
lIndex = SICSVConf.SICSV_SALES_DATA_LIST_INX;
} else if (lIOCsv.getCsvTblNameSel().equalsIgnoreCase(SIConfig.SIVIEW_CUST_ADDRESS_NAME)){
lIndex = SICSVConf.SICSV_CUST_ADDRESS_VIEW_INX;
} else if (lIOCsv.getCsvTblNameSel().equalsIgnoreCase(SIConfig.SIVIEW_CUST_ROUTE_NAME)){
lIndex = SICSVConf.SICSV_CUST_ROUTE_VIEW_INX;
} else if (lIOCsv.getCsvTblNameSel().equalsIgnoreCase(SIConfig.SIVIEW_CUST_JOB_NAME)){
lIndex = SICSVConf.SICSV_CUST_JOB_VIEW_INX;
} else if (lIOCsv.getCsvTblNameSel().equalsIgnoreCase(SIConfig.SIVIEW_KAITORILATEST_LIST_NAME)){
lIndex = SICSVConf.SICSV_KAITORILATEST_LIST_INX;
} else if (lIOCsv.getCsvTblNameSel().equalsIgnoreCase(SIConfig.SIVIEW_ORDER_ROUTE_NAME)){
lIndex = SICSVConf.SICSV_ORDER_ROUTE_VIEW_INX;
} else if (lIOCsv.getCsvTblNameSel().equalsIgnoreCase(SIConfig.SIVIEW_URIAGEBASE_LIST_NAME)){
lIndex = SICSVConf.SICSV_URIAGEBASE_LIST_VIEW_INX;
} else if (lIOCsv.getCsvTblNameSel().equalsIgnoreCase(SIConfig.SITABLE_POINTRECORD_NAME)){
lIndex = SICSVConf.SICSV_POINTRECORD_LIST_INX;
} else if (lIOCsv.getCsvTblNameSel().equalsIgnoreCase(SIConfig.SIVIEW_ARRIVALSCHEDULEMSG_LIST_NAME)){
lIndex = SICSVConf.SICSV_ARRIVALSCHEDULEMSG_LIST_INX;
} else if (lIOCsv.getCsvTblNameSel().equalsIgnoreCase(SIConfig.SIVIEW_LACK_LIST_NAME2)){
lIndex = SICSVConf.SICSV_LACK_LIST2_INX;
} else if (lIOCsv.getCsvTblNameSel().equalsIgnoreCase(SIConfig.SIVIEW_RESERVE_LIST_NAME)){
lIndex = SICSVConf.SICSV_RESERVE_LIST_INX;
} else if (lIOCsv.getCsvTblNameSel().equalsIgnoreCase(SIConfig.SIVIEW_INVESTPOINT_LOG_NAME)){
lIndex = SICSVConf.SICSV_INVEST_POINT_LOG_LIST_INX;
} else if (lIOCsv.getCsvTblNameSel().equalsIgnoreCase(SIConfig.SIVIEW_SOLDOUT_LIST_NAME)){
lIndex = SICSVConf.SICSV_SOLDOUT_LIST_INX;
} else if (lIOCsv.getCsvTblNameSel().equalsIgnoreCase(SIConfig.SITABLE_CMDTYDATA_NAME)){
lIndex = SICSVConf.SICSV_CMDTYDATA_LIST_INX;
} else if (lIOCsv.getCsvTblNameSel().equalsIgnoreCase(SIConfig.SIVIEW_LACK_LIST_RAKUTEN_NAME)) {
lIndex = SICSVConf.SICSV_LACK_LIST_RAKUTEN_INX;
}else if (lIOCsv.getCsvTblNameSel().equalsIgnoreCase(SIConfig.SIVIEW_FLAGUPDATELOG_LIST_NAME)) {
lIndex = SICSVConf.SICSV_FLAGUPDATELOG_LIST_INX;
}else if (lIOCsv.getCsvTblNameSel().equalsIgnoreCase(SIConfig.SITABLE_OPINION_NAME)) {
lIndex = SICSVConf.SICSV_OPINION_LIST_INX;
}else if (lIOCsv.getCsvTblNameSel().equalsIgnoreCase(SIConfig.SIVIEW_WAREHOUSE_STORE_LIST_NAME)) {
lIndex = SICSVConf.SICSV_WAREHOUSE_STORE_LIST_INX;
}else if (lIOCsv.getCsvTblNameSel().equalsIgnoreCase(SIConfig.SIVIEW_WAREHOUSE_SHIP_LIST_NAME)) {
lIndex = SICSVConf.SICSV_WAREHOUSE_SHIP_LIST_INX;
}else if (lIOCsv.getCsvTblNameSel().equalsIgnoreCase(SIConfig.SIVIEW_WAREHOUSE_CMDTY_LIST_NAME)) {
lIndex = SICSVConf.SICSV_WAREHOUSE_CMDTY_LIST_INX;
}else if (lIOCsv.getCsvTblNameSel().equalsIgnoreCase(SIConfig.SIVIEW_BRANCH_DEADSTOCK_LIST_NAME)) {
lIndex = SICSVConf.SICSV_BRANCH_DEADSTOCK_LIST_INX;
}else if (lIOCsv.getCsvTblNameSel().equalsIgnoreCase(SIConfig.SIVIEW_REPEAT_SALES_LIST_NAME)) {
lIndex = SICSVConf.SICSV_REPEAT_SALES_LIST_INX;
}else if (lIOCsv.getCsvTblNameSel().equalsIgnoreCase(SIConfig.SIVIEW_EG_POINT_LIST_NAME)) {
lIndex = SICSVConf.SICSV_EG_POINT_LIST_INX;
}else if (lIOCsv.getCsvTblNameSel().equalsIgnoreCase(SIConfig.SIVIEW_EYELASH_SALES_LIST_NAME)) {
lIndex = SICSVConf.SICSV_EYELASH_SALES_LIST_INX;
}else if (lIOCsv.getCsvTblNameSel().equalsIgnoreCase(SIConfig.SITABLE_CUSTSALES_NAME)) {
lIndex = SICSVConf.SICSV_CUST_SALES_DATA_LIST_INX;
}else if (lIOCsv.getCsvTblNameSel().equalsIgnoreCase(SIConfig.SITABLE_PURCHASECOMMENT_NAME)) {
lIndex = SICSVConf.SICSV_PURCHASECOMMENT_LIST_INX;
}
// CSVファイル名の作成
String lCsvFileName = SICSVConf.getCsvFileName(lIndex);
SICSVWrite lCsv = new SICSVWrite(lCsvFileName);
StringBuffer lSqlBuf = new StringBuffer("SELECT ").append(SICSVConf.getFieldListSQL(lIndex)).append(" ");
// 7.3.0 ST2027 修正 ここから
if (!(lLogin.isShop() && (lIndex == SICSVConf.SICSV_CUST_INX || lIndex == SICSVConf.SICSV_CUST_ADDRESS_INX || lIndex == SICSVConf.SICSV_CUST_HOBBY_INX))) {
if (lIndex == SICSVConf.SICSV_CUST_ADDRESS_INX) { // アドレス帳の場合
lSqlBuf.append("FROM ( SELECT a.* FROM ").append(lIOCsv.getCsvTblNameSel()).append(" ");
lSqlBuf.append(" " + SIDBMultiConf.SIALIAS_CURR_NAME + " a, CustTbl " + SIDBMultiConf.SIALIAS_CURR_NAME + " b ");
lSqlBuf.append(" WHERE a.CustCode = b.CustCode AND b.DelFlg < " + SIConfig.SIDEL_FLG_DELETE + " ) " + SIDBMultiConf.SIALIAS_CURR_NAME + " c ");
} else if (lIndex == SICSVConf.SICSV_CUST_HOBBY_INX) { // 施術メニュー（顧客属性3）の場合
lSqlBuf.append("FROM (SELECT CASE WHEN a.hobbyname = 'その他' THEN a.hobbyname||'（'||b.othermenu||'）' ELSE hobbyname END AS hobbyname ");
lSqlBuf.append(",a.hobbycode,a.custcode FROM custhobbytbl a,custtbl b WHERE a.custcode=b.custcode) list ");
} else if (lIndex == SICSVConf.SICSV_CUST_INX) {// 顧客マスタの場合
lSqlBuf.append("FROM (SELECT *,CASE receivableflg WHEN 1 THEN '売掛' ELSE '前金' END AS receivableflgvalue ");
lSqlBuf.append(" ,CASE WHEN memberlevelfixedflg=1 THEN '固定する' ELSE '固定しない' END AS memberlevelfixedtype ");
lSqlBuf.append(" ,CASE WHEN nopointflg=1 THEN '付与しない' ELSE '付与する' END AS nopointtype ");
lSqlBuf.append(" ,CASE WHEN nodeliveryflg=1 THEN '郵送不可' ELSE '郵送可' END AS nodeliverytype ");
lSqlBuf.append("FROM custtbl) c ");
lSqlBuf.append(" LEFT OUTER JOIN ");
lSqlBuf.append(" (SELECT CASE WHEN SUM(CASE makercode WHEN '1230' THEN 1 ELSE 0 END) = 1 THEN '契約済み' ELSE '' END AS contract1 ");
lSqlBuf.append(" ,CASE WHEN SUM(CASE makercode WHEN '1231' THEN 1 ELSE 0 END) = 1 THEN '契約済み' ELSE '' END AS contract2 ");
lSqlBuf.append(" ,CASE WHEN SUM(CASE makercode WHEN '1232' THEN 1 ELSE 0 END) = 1 THEN '契約済み' ELSE '' END AS contract3 ");
lSqlBuf.append(" ,CASE WHEN SUM(CASE makercode WHEN '1250' THEN 1 ELSE 0 END) = 1 THEN '契約済み' ELSE '' END AS contract4 ");
lSqlBuf.append(" ,custcode AS custcode2 FROM custmakertbl GROUP BY custcode) con ");
lSqlBuf.append(" ON c.custcode = con.custcode2 ");
lSqlBuf.append(" LEFT OUTER JOIN (SELECT chargecode,chargename AS charge FROM chargetbl) ch ON c.chargecode=ch.chargecode ");
lSqlBuf.append(" ,(SELECT memberlevelcode,memberlevelname FROM memberlevelmtbl) ml ");
lSqlBuf.append(" WHERE c.DelFlg != ").append(SIDBUtil.SQL2Str(SIConfig.SIDEL_FLG_DELETE," "));
lSqlBuf.append(" AND c.memberlevelcode = ml.memberlevelcode ");
} else if (lIndex == SICSVConf.SICSV_SALES_DATA_LIST_INX || lIndex == SICSVConf.SICSV_LACK_LIST_INX) {
lSqlBuf.append("");
} else {
lSqlBuf.append("FROM ").append(lIOCsv.getCsvTblNameSel()).append(" ");
}
}
// 7.3.0 ST2027 修正 ここまで
if (lLogin.isShop()) {
if (lIndex == SICSVConf.SICSV_PAYEE_INFO_INX || lIndex == SICSVConf.SICSV_PAY_METHOD_INX || lIndex == SICSVConf.SICSV_PAY_METHOD_FEE_INX
|| lIndex == SICSVConf.SICSV_DELIVERY_TIME_INX) {
lSqlBuf.append("WHERE MallShopCode=" + SIDBUtil.SQL2Str(lLogin.getMallShopCode(), " "));
} else if (lIndex == SICSVConf.SICSV_WRAPPING_INX || lIndex == SICSVConf.SICSV_CMDTY_INX || lIndex == SICSVConf.SICSV_CMDTY_CTGRY_INX
|| lIndex == SICSVConf.SICSV_CMDTY_STNDRD_INX || lIndex == SICSVConf.SICSV_CONN_CMDTY_INX || lIndex == SICSVConf.SICSV_STNDRD_NAME_INX
|| lIndex == SICSVConf.SICSV_STNDRD_CONTENT_INX || lIndex == SICSVConf.SICSV_STOCK_INX) {
lSqlBuf.append("WHERE ShopCode=" + SIDBUtil.SQL2Str(lLogin.getMallShopCode(), " "));
} else if (lIndex == SICSVConf.SICSV_CUST_INX || lIndex == SICSVConf.SICSV_CUST_ADDRESS_INX || lIndex == SICSVConf.SICSV_CUST_HOBBY_INX) {
lSqlBuf.append("FROM (SELECT a.* FROM ").append(lIOCsv.getCsvTblNameSel()).append(SIDBMultiConf.SIALIAS_CURR_NAME).append("a,");
lSqlBuf.append("ShopCustConnVW").append(SIDBMultiConf.SIALIAS_CURR_NAME).append("b ");
// 7.3.0 ST2027 追加 ここから アドレス帳の場合
if (lIndex == SICSVConf.SICSV_CUST_ADDRESS_INX) {
lSqlBuf.append(", CustTbl " + SIDBMultiConf.SIALIAS_CURR_NAME + " bb ");
}
// 7.3.0 ST2027 追加 ここまで
lSqlBuf.append("WHERE a.CustCode=b.CustCode ");
// 7.3.0 ST2027 追加 ここから アドレス帳の場合
if (lIndex == SICSVConf.SICSV_CUST_ADDRESS_INX) {
lSqlBuf.append(" AND a.CustCode = bb.CustCode ");
lSqlBuf.append(" AND bb.DelFlg < " + SIConfig.SIDEL_FLG_DELETE + " ");
}
// 7.3.0 ST2027 追加 ここまで
lSqlBuf.append("AND b.ShopCode=" + SIDBUtil.SQL2Str(lLogin.getMallShopCode(), ")")).append(SIDBMultiConf.SIALIAS_CURR_NAME).append("c");
// 7.3.0 ST2027 追加 ここから 顧客マスタの場合
if (lIndex == SICSVConf.SICSV_CUST_INX) {
lSqlBuf.append(" WHERE DelFlg < " + SIConfig.SIDEL_FLG_DELETE + " ");
}
// 7.3.0 ST2027 追加 ここまで
}
}
if (lIndex == SICSVConf.SICSV_ORDER_ROUTE_VIEW_INX) {
lSqlBuf.append(" WHERE 1=1 ").append(lIOCsv.getConditionOrderRoute());
} else if (lIndex == SICSVConf.SICSV_URIAGEBASE_LIST_VIEW_INX) {
lSqlBuf.append(" WHERE 1=1 ").append(lIOCsv.getConditionUriageBase());
} else if (lIndex == SICSVConf.SICSV_POINTRECORD_LIST_INX) {
lSqlBuf.append(" WHERE 1=1 ").append(lIOCsv.getConditionPointRecord());
} else if (lIndex == SICSVConf.SICSV_FLAGUPDATELOG_LIST_INX) {
lSqlBuf.append(" WHERE 1=1 ").append(lIOCsv.getConditionFlagUpdateLog());
} else if (lIndex == SICSVConf.SICSV_OPINION_LIST_INX) {
lSqlBuf.append(" WHERE 1=1 ").append(lIOCsv.getConditionOpinionList());
} else if (lIndex == SICSVConf.SICSV_EG_POINT_LIST_INX) {
lSqlBuf.append(" WHERE 1=1 ").append(lIOCsv.getConditionEgPoint());
} else if (lIndex == SICSVConf.SICSV_EYELASH_SALES_LIST_INX) {
lSqlBuf.append(" WHERE 1=1 ").append(lIOCsv.getConditionEyelashSales());
}
if (lIndex != SICSVConf.SICSV_KAITORISTOCK_LIST_INX && lIndex != SICSVConf.SICSV_INVENTORY_LIST_INX
&& lIndex != SICSVConf.SICSV_UNAPPROVAL_STORE_LIST_INX && lIndex != SICSVConf.SICSV_UNAPPROVAL_SHIP_LIST_INX
&& lIndex != SICSVConf.SICSV_MIKANSAI_LIST_INX && lIndex != SICSVConf.SICSV_URIAGE_LIST_INX
&& lIndex != SICSVConf.SICSV_LACK_LIST_INX && lIndex != SICSVConf.SICSV_SALES_DATA_LIST_INX
&& lIndex != SICSVConf.SICSV_URIAGE_LIST6_INX && lIndex != SICSVConf.SICSV_LACK_LIST2_INX
&& lIndex != SICSVConf.SICSV_RESERVE_LIST_INX && lIndex != SICSVConf.SICSV_CMDTYDATA_LIST_INX
&& lIndex != SICSVConf.SICSV_LACK_LIST_RAKUTEN_INX && lIndex != SICSVConf.SICSV_WAREHOUSE_CMDTY_LIST_INX
&& lIndex != SICSVConf.SICSV_WAREHOUSE_STORE_LIST_INX && lIndex != SICSVConf.SICSV_WAREHOUSE_SHIP_LIST_INX
&& lIndex != SICSVConf.SICSV_BRANCH_DEADSTOCK_LIST_INX && lIndex != SICSVConf.SICSV_REPEAT_SALES_LIST_INX
&& lIndex != SICSVConf.SICSV_EG_POINT_LIST_INX && lIndex != SICSVConf.SICSV_EYELASH_SALES_LIST_INX
&& lIndex != SICSVConf.SICSV_CUST_SALES_DATA_LIST_INX && lIndex != SICSVConf.SICSV_PURCHASECOMMENT_LIST_INX) {
lSqlBuf.append(SICSVConf.getOrderBySQL(lIndex));
}
SIDateTime lDateTime = new SIDateTime();
if(lIndex == SICSVConf.SICSV_SALES_DATA_LIST_INX){//販売分析データはUIIOCsvでSQL作成
lSqlBuf = new StringBuffer(lIOCsv.getSalesSql());
}else if(lIndex == SICSVConf.SICSV_LACK_LIST_INX){//欠品リストはUIIOCsvでSQL作成
lSqlBuf = new StringBuffer(lIOCsv.getLackSql());
}else if(lIndex == SICSVConf.SICSV_CMDTYDATA_LIST_INX){//商品詳細データリストはUIIOCsvでSQL作成
lSqlBuf = new StringBuffer(lIOCsv.getCmdtySql());
} else if (lIndex == SICSVConf.SICSV_WAREHOUSE_CMDTY_LIST_INX) {
lSqlBuf = new StringBuffer(lIOCsv.getWarehouseCmdtySql());
} else if (lIndex == SICSVConf.SICSV_WAREHOUSE_STORE_LIST_INX) {
lSqlBuf = new StringBuffer(lIOCsv.getWarehouseStoreSql());
} else if (lIndex == SICSVConf.SICSV_WAREHOUSE_SHIP_LIST_INX) {
lSqlBuf = new StringBuffer(lIOCsv.getWarehouseShipSql(lDateTime.getFullDateTime()));
} else if (lIndex == SICSVConf.SICSV_BRANCH_DEADSTOCK_LIST_INX) {
lSqlBuf = new StringBuffer(lIOCsv.getBranchDeadStockSql());
} else if (lIndex == SICSVConf.SICSV_REPEAT_SALES_LIST_INX) {
lSqlBuf = new StringBuffer(lIOCsv.getRepeatSalesDataSql());
} else if (lIndex == SICSVConf.SICSV_CUST_SALES_DATA_LIST_INX) {
lSqlBuf = new StringBuffer(lIOCsv.getCustSalesDataSql());
} else if (lIndex == SICSVConf.SICSV_PURCHASECOMMENT_LIST_INX) {
lSqlBuf = new StringBuffer(lIOCsv.getPurchaseCommentSql());
}
log.debug("lSqlBuf=" + lSqlBuf.toString());
// SQL文の設定
lCsv.setSqlStatement(lSqlBuf.toString());
// タイトルの設定
lCsv.setCsvTitleAndField(SICSVConf.getTitleAndFieldName(lIndex));
if (SIDBMultiConf.SIDB_CURRENT_INX == SIDBMultiConf.SIDB_POSTGRESQL_INX) {} else {
lCsv.setFieldType(SICSVConf.getFieldType(lIndex));
}
// CSVファイルの出力
if (lIndex == SICSVConf.SICSV_WAREHOUSE_STORE_LIST_INX || lIndex == SICSVConf.SICSV_WAREHOUSE_CMDTY_LIST_INX) {
lCsv.execute(lConnection, response, true);
}else if (lIndex == SICSVConf.SICSV_WAREHOUSE_SHIP_LIST_INX ) {
lCsv.execute(lConnection, response, true);
if ("0".equals(lIOCsv.getCheckFlg())) {//未出力分DLのとき
SIModifyRec lRec = new SIModifyRec("shippmentrequesttbl");
if (SIUtil.isNotNull(lIOCsv.getSalesDateFrom()) && SIUtil.isNotNull(lIOCsv.getSalesDateTo())) {
lRec.addCondition(new SITableCondition("sendmaildatetime::date <=" + SIDBUtil.SQL2Str(lIOCsv.getSalesDateTo()) + "AND sendmaildatetime::date >=" + SIDBUtil.SQL2Str(lIOCsv.getSalesDateFrom())));
} else if (SIUtil.isNotNull(lIOCsv.getSalesDateFrom())) {
lRec.addCondition(new SITableCondition("sendmaildatetime::date >=" + SIDBUtil.SQL2Str(lIOCsv.getSalesDateFrom())));
} else if (SIUtil.isNotNull(lIOCsv.getSalesDateTo())) {
lRec.addCondition(new SITableCondition("sendmaildatetime::date <=" + SIDBUtil.SQL2Str(lIOCsv.getSalesDateTo())));
}
if (SIUtil.isNotNull(lIOCsv.getOrderCode())) lRec.addCondition("ordercode",lIOCsv.getOrderCode());
if (SIUtil.isNotNull(lIOCsv.getBranchCode())) lRec.addCondition("branchcode",lIOCsv.getBranchCode());
lRec.addCondition("enabledflg","1");
lRec.addCondition("checkflg","0");
lRec.addCondition(new SITableCondition("AND sendmaildatetime<" + SIDBUtil.SQL2Str(lDateTime.getFullDateTime())));
lRec.add("checkflg","1");
lRec.add("outputdatetime",lDateTime.getFullDateTime());
lRec.add("outputusercode",lLogin.getUserCode());
try {
lRec.execute(lConnection);
lConnection.commit();
}catch (SIDBAccessException ex){
ex.printStackTrace();
}catch (SIDuplicateKeyException ex){
ex.printStackTrace();
}catch (SQLException ex){
ex.printStackTrace();
}
}
} else {
lCsv.execute(lConnection, response);
}
}
/**
* writeIndividual CSVファイルから取った商品データで商品(在庫)マスターに新規レコードを作成します。
*
* @param request リクエスト
* @param response
* @param lConnection DBへのコネクション
* @param lItemList データリスト
* @return エラーメッセージ
* @throws なし
*/
public String writeIndividual(HttpServletRequest request, HttpServletResponse response, Connection lConnection, SICSVRead lRead, String lEditModeTxt) {
SICustomErrors errors = new SICustomErrors();
StringBuffer lResBuf = new StringBuffer();
SILogin lLogin = SIHTMLUtil.getLogin(request);
while (lRead.hasNextLine()) {
String[] lItemList;
try {
lItemList = lRead.itemList();
if (lItemList == null || lItemList.length == 0) continue;
if (lItemList.length != SICSVConf.getFieldName(SICSVConf.SICSV_INDIVIDUAL_LIST_INX).length) {
lResBuf.append("
Line[").append(lRead.getLineNo()).append("]:エラー");
lResBuf.append(SIErrorFactory.getErrorMsg("manager.app.enough.item"));
continue;
}
errors = new SICustomErrors();
// データの設定
UIIndividual lRegIndividual = new UIIndividual();
lRegIndividual.setEncode(SIConfig.SIENCODE_NONE);
lRegIndividual.setEditModeTxt(lEditModeTxt);
lRegIndividual.setShopCode(lItemList[0]);
lRegIndividual.setCmdtyCode(lItemList[1]);
lRegIndividual.setIndividualCode(lItemList[2]);
lRegIndividual.setUsedNewFlg(lItemList[3]);
lRegIndividual.setArrivalDate(lItemList[4]);
lRegIndividual.setProcessingExpence(lItemList[5]);
lRegIndividual.setTenureofuse(lItemList[6]);
lRegIndividual.setSalesSituationFlg(lItemList[7]);
lRegIndividual.setSoldOutDispFlg(lItemList[8]);
lRegIndividual.setCampaignPriceFlg(lItemList[9]);
lRegIndividual.setInquiryFlg(lItemList[10]);
lRegIndividual.setFrontDispFlg(lItemList[11]);
lRegIndividual.setCustCode(lItemList[12]);
lRegIndividual.setCustName(lItemList[13]);
lRegIndividual.setComment(lItemList[14]);
lRegIndividual.setBgPriceDispFlg(lItemList[15]);
lRegIndividual.setConsignGoodsFlg(lItemList[16]);
lRegIndividual.setConsignmentMemo(lItemList[17]);
lRegIndividual.setCommodityExplanation(lItemList[18]);
lRegIndividual.setStockCode(lItemList[19]);
lRegIndividual.setPurchasePrice(lItemList[20]);
lRegIndividual.setBgPrice(lItemList[21]);
lRegIndividual.setSpecialPrice(lItemList[22]);
lRegIndividual.setRecommendationFlg(lItemList[23]);
lRegIndividual.setCommodityBannerFile(lItemList[24]);
lRegIndividual.setMemberDiscountFlg(lItemList[25]);
lRegIndividual.setSellFromDate(lItemList[26]);
lRegIndividual.setEndSellFlg(lItemList[27]);
lRegIndividual.setSellToDate(lItemList[28]);
lRegIndividual.setSalesFromdate(lItemList[29]);
lRegIndividual.setSalesToDate(lItemList[30]);
lRegIndividual.setRsrvEnableFlg(lItemList[31]);
lRegIndividual.setRsrvAmount(lItemList[32]);
lRegIndividual.setNoStockFlg(lItemList[33]);
lRegIndividual.setAmountFlg(lItemList[34]);
lRegIndividual.setStockStatusCode(lItemList[35]);
lRegIndividual.setSoldOutDate(lItemList[36]);
lRegIndividual.setGuaranteedTerm(lItemList[37]);
lRegIndividual.setWrappingFlg(lItemList[38]);
lRegIndividual.setDiscountFlg(lItemList[39]);
lRegIndividual.setSalesDiscountFlg(lItemList[40]);
lRegIndividual.setAdviceDispOrder(lItemList[41]);
lRegIndividual.setImageName1(lItemList[42]);
lRegIndividual.setImageName2(lItemList[43]);
lRegIndividual.setImageName3(lItemList[44]);
lRegIndividual.setImageName4(lItemList[45]);
lRegIndividual.setImageName5(lItemList[46]);
lRegIndividual.setImageNamet(lItemList[47]);
lRegIndividual.setRemarks(lItemList[48]);
lRegIndividual.setInitDateTime(lItemList[49]);
lRegIndividual.setSpCampaignFlg(lItemList[50]);
lRegIndividual.setNomalPrice(lItemList[51]);
lRegIndividual.setAlertAmount(lItemList[52]);
lRegIndividual.setPurchasePrice2(lItemList[53]);
if (SIUtil.isNull(lRegIndividual.getProcessingExpence())) lRegIndividual.setProcessingExpence("0");
if (SIUtil.isNull(lRegIndividual.getPurchasePrice())) lRegIndividual.setPurchasePrice("0");
if (lRegIndividual.validateIndividual(request, lConnection)) {
try {
if (lLogin.isShop() && !lRegIndividual.getShopCode().equals(lLogin.getMallShopCode())) {
lResBuf.append("
Line[").append(lRead.getLineNo()).append("]:エラー");
lResBuf.append(SIErrorFactory.getErrorMsg("manager.app.mismatch.shop"));
continue;
}
if (lRegIndividual.getEditModeTxt().equals(SIConfig.SIEDIT_MODE_INSERT)) {
SIRegIndividualSrv.insertTableDataIndividual(lConnection, lRegIndividual);
} else {
SIRegIndividualSrv.updateTableDataIndividual(lConnection, lRegIndividual);
}
} catch (SIDuplicateKeyException e) {
errors.addError(new SICustomError("database.insert.duplicate"));
e.printStackTrace();
} catch (SIDBAccessException e) {
errors.addError(new SICustomError("database.execute.error"));
e.printStackTrace();
}
} else {
errors = (SICustomErrors) request.getAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY);
}
if (SIUtil.isNotNull(errors.getErrorMsg())) {
lResBuf.append("
Line[").append(lRead.getLineNo()).append("]:エラー");
lResBuf.append(errors.getErrorMsg());
}
} catch (SIErrorException e) {
lResBuf.append("
Line[").append(lRead.getLineNo()).append("]:エラー");
lResBuf.append(SIErrorFactory.getErrorMsg("input.data.csv.format"));
}
request.removeAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY);
}
return lResBuf.toString();
}
/**
* writeCmdty CSVファイルから取った商品データで商品マスターに新規レコードを作成します。
*
* @param request リクエスト
* @param response
* @param lConnection DBへのコネクション
* @param lItemList データリスト
* @return エラーメッセージ
* @throws なし
*/
public String writeCmdty(HttpServletRequest request, HttpServletResponse response, Connection lConnection, SICSVRead lRead, String lEditModeTxt) {
SICustomErrors errors = new SICustomErrors();
StringBuffer lResBuf = new StringBuffer();
SILogin lLogin = SIHTMLUtil.getLogin(request);
while (lRead.hasNextLine()) {
String[] lItemList;
try {
lItemList = lRead.itemList();
if (lItemList == null || lItemList.length == 0) continue;
if (lItemList.length != SICSVConf.getFieldName(SICSVConf.SICSV_CMDTY_LIST_INX).length) {
lResBuf.append("
Line[").append(lRead.getLineNo()).append("]:エラー");
lResBuf.append(SIErrorFactory.getErrorMsg("manager.app.enough.item"));
continue;
}
errors = new SICustomErrors();
// データの設定
UICmdtyParent lRegCmdty = new UICmdtyParent();
lRegCmdty.setEncode(SIConfig.SIENCODE_NONE);
lRegCmdty.setEditModeTxt(lEditModeTxt);
lRegCmdty.setShopCode(lItemList[0]);// ショップコード
lRegCmdty.setCmdtyCode(lItemList[1]);// 商品コード
lRegCmdty.setCmdtyName(lItemList[2]);// 商品名
lRegCmdty.setJanCode(lItemList[3]);// JANコード
lRegCmdty.setMakerCode(lItemList[4]);// ブランドコード
lRegCmdty.setColorCode(lItemList[5]);// 色コード
lRegCmdty.setNewFixedPrice(lItemList[6]);// M価
lRegCmdty.setBannerURL(lItemList[7]);// バナーURL
lRegCmdty.setSrchKeyName1(lItemList[8]);// 検索項目１
lRegCmdty.setSrchKeyName2(lItemList[9]);// 検索項目２
lRegCmdty.setSrchKeyName3(lItemList[10]);// 検索項目３
lRegCmdty.setTaxFlg(lItemList[11]);// 本体税区分
lRegCmdty.setDeliveryTypeCode(lItemList[12]);// 配送種別
lRegCmdty.setCmdtySize(lItemList[13]);// 商品サイズ
lRegCmdty.setRanking(lItemList[14]);// ランキング
lRegCmdty.setRankingDateTime(lItemList[15]);// ランキング集計日時
lRegCmdty.setInitDateTime(lItemList[16]);// 登録日
lRegCmdty.setDisableFlg(lItemList[17]);// 死活フラグ
if (lRegCmdty.validate(request, lConnection, true)) {
try {
if (lLogin.isShop() && !lRegCmdty.getShopCode().equals(lLogin.getMallShopCode())) {
lResBuf.append("
Line[").append(lRead.getLineNo()).append("]:エラー");
lResBuf.append(SIErrorFactory.getErrorMsg("manager.app.mismatch.shop"));
continue;
}
if (lRegCmdty.getEditModeTxt().equals(SIConfig.SIEDIT_MODE_INSERT)) {
SIRegCmdtySrv.insertTableDataBG(lConnection, lRegCmdty);
} else {
SIRegCmdtySrv.updateTableDataBG(lConnection, lRegCmdty);
}
} catch (SIDuplicateKeyException e) {
errors.addError(new SICustomError("database.insert.duplicate"));
e.printStackTrace();
} catch (SIDBAccessException e) {
errors.addError(new SICustomError("database.execute.error"));
e.printStackTrace();
}
} else {
errors = (SICustomErrors) request.getAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY);
}
if (SIUtil.isNotNull(errors.getErrorMsg())) {
lResBuf.append("
Line[").append(lRead.getLineNo()).append("]:エラー");
lResBuf.append(errors.getErrorMsg());
}
} catch (SIErrorException e) {
lResBuf.append("
Line[").append(lRead.getLineNo()).append("]:エラー");
lResBuf.append(SIErrorFactory.getErrorMsg("input.data.csv.format"));
}
request.removeAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY);
}
return lResBuf.toString();
}
/**
* writeCust CSVファイルから取った顧客データで顧客テーブルに新規レコードを作成します。
*
* @param request リクエスト
* @param response
* @param lConnection DBへのコネクション
* @param lItemList データリスト
* @param String DB日時 (7.3.0 ST2023で追加)
* @return エラーメッセージ
* @throws なし
*/
public String writeCust(HttpServletRequest request, HttpServletResponse response, Connection lConnection, SICSVRead lRead, String lEditModeTxt, String strDBDateTime) { // 7.3.0
// ST2023
// 修正 引数追加
SICustomErrors errors = new SICustomErrors();
StringBuffer lResBuf = new StringBuffer();
while (lRead.hasNextLine()) {
String[] lItemList;
try {
lItemList = lRead.itemList();
if (lItemList == null || lItemList.length == 0) continue;
if (lItemList.length != SICSVConf.getFieldName(SICSVConf.SICSV_CUST_INX).length) {
lResBuf.append("
Line[").append(lRead.getLineNo()).append("]:エラー");
lResBuf.append(SIErrorFactory.getErrorMsg("manager.app.enough.item"));
continue;
}
errors = new SICustomErrors();
// データの設定
UIRegCust lRegCust = new UIRegCust();
lRegCust.setEncode(SIConfig.SIENCODE_NONE);
lRegCust.setEditModeTxt(lEditModeTxt);
lRegCust.setCustCode(lItemList[0]);// 顧客コード
lRegCust.setMemberLevelCode(lItemList[1]);// 会員種別コード
lRegCust.setCustCompanyFlg(lItemList[2]);// 法人フラグ
lRegCust.setCustName(lItemList[3]);// 氏名
lRegCust.setCustPronName(lItemList[4]);// フリガナ
lRegCust.setEmail(lItemList[5]);// メールアドレス
lRegCust.setPassword(lItemList[6]);// パスワード
lRegCust.setPWQuestion(lItemList[7]);// PWリマインダ質問
lRegCust.setPWAnswer(lItemList[8]);// PWリマインダ回答
lRegCust.setPostCode1(lItemList[9]);// 郵便番号1
lRegCust.setPostCode2(lItemList[10]);// 郵便番号2
lRegCust.setAddress1(lItemList[11]);// 住所１
lRegCust.setAddress2(lItemList[12]);// 住所２
lRegCust.setAddress3(lItemList[13]);// 住所３
lRegCust.setCompanyName(lItemList[14]);// サロン名
lRegCust.setCorporationName(lItemList[15]);// 会社名
lRegCust.setStoreTel(lItemList[16]);// 店舗電話番号
lRegCust.setTel(lItemList[17]);// 連絡先電話番号
lRegCust.setFax(lItemList[18]);// 連絡先FAX番号
if (SIUtil.isNotNull(lItemList[19])) {
lRegCust.setPayMethodCode(lItemList[19]);// お支払い方法
} else {
lRegCust.setPayMethodCode("0");// お支払い方法
}
lRegCust.setBirthDate(lItemList[20]);// 生年月日
lRegCust.setSex(lItemList[21]);// 性別
lRegCust.setJob(lItemList[22]);// 営業形態
lRegCust.setHowToKnow(lItemList[23]);// 従業員数
//lRegCust.setRegistRoute(lItemList[24]);// その他の施術メニュー
lRegCust.setRegistRoute(lItemList[24]);// 登録経路 7.3.0 PI-NES0501 追加
lRegCust.setNewMailFlg(lItemList[25]);// 新着メール
lRegCust.setDelFlg(Integer.parseInt(lItemList[27]));// 削除フラグ 7.3.0 PI-NES0501 追加
lRegCust.setLoginErrCount(lItemList[29]);// ログインエラー回数
if (lRegCust.validate(request, lConnection, true)) {
try {
if (lRegCust.getEditModeTxt().equals(SIConfig.SIEDIT_MODE_INSERT)) {
lRegCust.setCustCode(SIDBUtil.getFirstData(lConnection, SIDBUtil.getNextVal("CUSTTBL_CUSTCD_SEQ")));
SIRegCustSrv.insertTableData(lConnection, lRegCust, request, strDBDateTime);// 7.3.0 ST2029 修正 パラメータ追加 //7.3.0 ST2023 修正 引数追加
} else {
SIRegCustSrv.updateTableData(lConnection, lRegCust, request, strDBDateTime);// 7.3.0 ST2023 修正 引数追加
}
} catch (SIDuplicateKeyException e) {
errors.addError(new SICustomError("database.insert.duplicate"));
e.printStackTrace();
} catch (SIDBAccessException e) {
errors.addError(new SICustomError("database.execute.error"));
e.printStackTrace();
}
} else {
errors = (SICustomErrors) request.getAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY);
}
if (SIUtil.isNotNull(errors.getErrorMsg())) {
lResBuf.append("
Line[").append(lRead.getLineNo()).append("]:エラー");
lResBuf.append(errors.getErrorMsg());
}
} catch (SIErrorException e) {
lResBuf.append("
Line[").append(lRead.getLineNo()).append("]:エラー");
lResBuf.append(SIErrorFactory.getErrorMsg("input.data.csv.format"));
}
request.removeAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY);
}
return lResBuf.toString();
}
/**
* writeCustAddress CSVファイルから取った顧客アドレス帳データでアドレス帳テーブルに新規レコードを作成します。
*
* @param request リクエスト
* @param response
* @param lConnection DBへのコネクション
* @param lRead データリスト
* @return エラーメッセージ
* @throws なし
*/
public String writeCustAddress(HttpServletRequest request, HttpServletResponse response, Connection lConnection, SICSVRead lRead, String lEditModeTxt) {
SICustomErrors errors = new SICustomErrors();
StringBuffer lResBuf = new StringBuffer();
while (lRead.hasNextLine()) {
String[] lItemList;
try {
lItemList = lRead.itemList();
if (lItemList == null || lItemList.length == 0) continue;
// 項目数のチェック
if (lItemList.length != SICSVConf.getFieldName(SICSVConf.SICSV_CUST_ADDRESS_INX).length) {
lResBuf.append("
Line[").append(lRead.getLineNo()).append("]:エラー");
lResBuf.append(SIErrorFactory.getErrorMsg("manager.app.enough.item"));
continue;
}
errors = new SICustomErrors();
// データの設定
UICustAddress lRegCustAddr = new UICustAddress();
lRegCustAddr.setEncode(SIConfig.SIENCODE_NONE);
lRegCustAddr.setEditModeTxt(lEditModeTxt);
lRegCustAddr.setCustCode(lItemList[0]);// 顧客コード
lRegCustAddr.setAddressCode(lItemList[1]);// アドレス帳コード
lRegCustAddr.setCustCompanyFlg(lItemList[2]);// 法人個人区分
lRegCustAddr.setDeliveryName(lItemList[3]);// 配送先呼称
lRegCustAddr.setDeliveryAddressee(lItemList[4]);// 配送先正式名
lRegCustAddr.setEmail(null);// EMAIL
lRegCustAddr.setPostCode1(lItemList[5]);// 郵便番号1
lRegCustAddr.setPostCode2(lItemList[6]);// 郵便番号2
lRegCustAddr.setAddress1(lItemList[7]);// 住所1
lRegCustAddr.setAddress2(lItemList[8]);// 住所2
lRegCustAddr.setAddress3(lItemList[9]);// 住所3
lRegCustAddr.setCompanyName(lItemList[10]);// サロン名
lRegCustAddr.setCorporationName(lItemList[11]);// 会社名
lRegCustAddr.setTel(lItemList[12]);// 連絡先電話番号
lRegCustAddr.setFax(null);// 連絡先FAX番号
lRegCustAddr.setSendMailFlg(null);// 送信フラグ
lRegCustAddr.setDaysBeforehand(null);// 何日前
lRegCustAddr.setMailMonth(null);// お知らせ月
lRegCustAddr.setMailDay(null);// お知らせ日
lRegCustAddr.setLastDeliveryDate(lItemList[14]);// 最終配送日
if (lRegCustAddr.validate(request, lConnection, true)) {
try {
if (lRegCustAddr.getEditModeTxt().equals(SIConfig.SIEDIT_MODE_INSERT)) {
SIRegAddrSrv.insertTableData(lConnection, lRegCustAddr, request, null);// 7.3.0 ST2023 修正 引数追加
} else {
SIRegAddrSrv.updateTableData(lConnection, lRegCustAddr, request, null);// 7.3.0 ST2023 修正 引数追加
}
} catch (SIDuplicateKeyException e) {
errors.addError(new SICustomError("database.insert.duplicate"));
e.printStackTrace();
} catch (SIDBAccessException e) {
errors.addError(new SICustomError("database.execute.error"));
e.printStackTrace();
}
} else {
errors = (SICustomErrors) request.getAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY);
}
if (SIUtil.isNotNull(errors.getErrorMsg())) {
lResBuf.append("
Line[").append(lRead.getLineNo()).append("]:エラー");
lResBuf.append(errors.getErrorMsg());
}
} catch (SIErrorException e) {
lResBuf.append("
Line[").append(lRead.getLineNo()).append("]:エラー");
lResBuf.append(SIErrorFactory.getErrorMsg("input.data.csv.format"));
}
request.removeAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY);
}
return lResBuf.toString();
}
/**
* writeWrapping CSVファイルから取ったラッピングデータでラッピングテーブルに新規レコードを作成します。
*
* @param request リクエスト
* @param response
* @param lConnection DBへのコネクション
* @param lRead データリスト
* @return エラーメッセージ
* @throws なし
*/
public String writeWrapping(HttpServletRequest request, HttpServletResponse response, Connection lConnection, SICSVRead lRead, String lEditModeTxt) {
SICustomErrors errors = new SICustomErrors();
SILogin lLogin = SIHTMLUtil.getLogin(request);
StringBuffer lResBuf = new StringBuffer();
while (lRead.hasNextLine()) {
String[] lItemList;
try {
lItemList = lRead.itemList();
if (lItemList == null || lItemList.length == 0) continue;
// 項目数のチェック
if (lItemList.length != SICSVConf.getFieldName(SICSVConf.SICSV_WRAPPING_INX).length) {
lResBuf.append("
Line[").append(lRead.getLineNo()).append("]:エラー");
lResBuf.append(SIErrorFactory.getErrorMsg("manager.app.enough.item"));
continue;
}
errors = new SICustomErrors();
// データの設定
UIRegWrapping lRegWrapping = new UIRegWrapping();
lRegWrapping.setEncode(SIConfig.SIENCODE_NONE);
lRegWrapping.setEditModeTxt(lEditModeTxt);
lRegWrapping.setShopCode(lItemList[0]);// ショップコード
lRegWrapping.setWrappingCode(lItemList[1]);// ラッピングコード
lRegWrapping.setWrappingName(lItemList[2]);// ラッピング名
lRegWrapping.setDescription(lItemList[3]);// 説明
lRegWrapping.setPrice(lItemList[4]);// 価格
lRegWrapping.setTaxFlg(lItemList[5]);// 価格税区分
if (lRegWrapping.validate(request, lConnection, true)) {
try {
if (lLogin.isShop() && !lRegWrapping.getShopCode().equals(lLogin.getMallShopCode())) {
lResBuf.append("
Line[").append(lRead.getLineNo()).append("]:エラー");
lResBuf.append(SIErrorFactory.getErrorMsg("manager.app.mismatch.shop"));
continue;
}
StringBuffer lSqlBuf = new StringBuffer();
if (lRegWrapping.getEditModeTxt().equals(SIConfig.SIEDIT_MODE_INSERT)) {
if (SIUtil.isNotNull(lRegWrapping.getShopCode()) && SIUtil.isNotNull(lRegWrapping.getWrappingCode())) {
lSqlBuf = new StringBuffer("SELECT ShopCode FROM WrappingMTbl WHERE ShopCode=");
lSqlBuf.append(SIDBUtil.SQL2Str(lRegWrapping.getShopCode(), " "));
lSqlBuf.append("AND WrappingCode=").append(SIDBUtil.SQL2Str(lRegWrapping.getWrappingCode()));
if (SIDBUtil.hasData(lConnection, lSqlBuf.toString())) {
errors.addError(new SICustomError("input.data.csv.duplication", "ラッピングコード"));
}
}
if (errors.isEmpty()) {
SIRegWrappingSrv.insertTableData(lConnection, lRegWrapping);
}
} else {
if (SIUtil.isNotNull(lRegWrapping.getShopCode()) && SIUtil.isNotNull(lRegWrapping.getWrappingCode())) {
lSqlBuf = new StringBuffer("SELECT ShopCode FROM WrappingMTbl WHERE ShopCode=");
lSqlBuf.append(SIDBUtil.SQL2Str(lRegWrapping.getShopCode(), " "));
lSqlBuf.append("AND WrappingCode=").append(SIDBUtil.SQL2Str(lRegWrapping.getWrappingCode()));
SICheckValid.checkExist(errors, lConnection, "ラッピングコード", lSqlBuf.toString());
}
if (errors.isEmpty()) {
SIRegWrappingSrv.updateTableData(lConnection, lRegWrapping);
}
}
} catch (SIDuplicateKeyException e) {
errors.addError(new SICustomError("database.insert.duplicate"));
e.printStackTrace();
} catch (SIDBAccessException e) {
errors.addError(new SICustomError("database.execute.error"));
e.printStackTrace();
}
} else {
errors = (SICustomErrors) request.getAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY);
}
if (SIUtil.isNotNull(errors.getErrorMsg())) {
lResBuf.append("
Line[").append(lRead.getLineNo()).append("]:エラー");
lResBuf.append(errors.getErrorMsg());
}
} catch (SIErrorException e) {
lResBuf.append("
Line[").append(lRead.getLineNo()).append("]:エラー");
lResBuf.append(SIErrorFactory.getErrorMsg("input.data.csv.format"));
}
request.removeAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY);
}
return lResBuf.toString();
}
/**
* writeCmdtyStndrd CSVファイルから取った商品規格データで商品規格テーブルに新規レコードを作成します。
*
* @param request リクエスト
* @param response
* @param lConnection DBへのコネクション
* @param lItemList データリスト
* @return エラーメッセージ
* @throws なし
*/
public String writeCmdtyStndrd(HttpServletRequest request, HttpServletResponse response, Connection lConnection, SICSVRead lRead, String lEditModeTxt) {
SICustomErrors errors = new SICustomErrors();
SILogin lLogin = SIHTMLUtil.getLogin(request);
StringBuffer lResBuf = new StringBuffer();
while (lRead.hasNextLine()) {
String[] lItemList;
try {
lItemList = lRead.itemList();
if (lItemList == null || lItemList.length == 0) continue;
// 項目数のチェック
if (lItemList.length != SICSVConf.getFieldName(SICSVConf.SICSV_CMDTY_STNDRD_INX).length) {
lResBuf.append("
Line[").append(lRead.getLineNo()).append("]:エラー");
lResBuf.append(SIErrorFactory.getErrorMsg("manager.app.enough.item"));
continue;
}
errors = new SICustomErrors();
// データの設定
SICmdtyStndrd lCmdtyStndrd = new SICmdtyStndrd();
lCmdtyStndrd.setEncode(SIConfig.SIENCODE_NONE);
lCmdtyStndrd.setEditModeTxt(lEditModeTxt);
lCmdtyStndrd.setCmdtyStndrdCode(lItemList[0]);// ショップコード
lCmdtyStndrd.setShopCode(lItemList[1]);// ショップコード
lCmdtyStndrd.setCmdtyCode(lItemList[2]);// 商品コード
lCmdtyStndrd.setStndrdCode1(lItemList[3]);// 規格1
lCmdtyStndrd.setElementCode1(lItemList[4]);// エレメント1
lCmdtyStndrd.setStndrdCode2(lItemList[5]);// 規格2
lCmdtyStndrd.setElementCode2(lItemList[6]);// エレメント2
lCmdtyStndrd.setAmount(lItemList[7]);// 数量
if (lCmdtyStndrd.validate(request, lConnection, true)) {
try {
if (lLogin.isShop() && !lCmdtyStndrd.getShopCode().equals(lLogin.getMallShopCode())) {
lResBuf.append("
Line[").append(lRead.getLineNo()).append("]:エラー");
lResBuf.append(SIErrorFactory.getErrorMsg("manager.app.mismatch.shop"));
continue;
}
if (lCmdtyStndrd.getEditModeTxt().equals(SIConfig.SIEDIT_MODE_INSERT)) {
SIInsertRec lRec = new SIInsertRec("CmdtyStndrdMTbl");
lRec.add("ShopCode", lCmdtyStndrd.getShopCode());// ショップコード
lRec.add("CmdtyCode", lCmdtyStndrd.getCmdtyCode());// 商品コード
lRec.add("StndrdCode1", lCmdtyStndrd.getStndrdCode1());// 規格コード1
lRec.add("ElementCode1", lCmdtyStndrd.getElementCode1());// エレメントコード1
lRec.add("StndrdCode2", lCmdtyStndrd.getStndrdCode2());// 規格コード2
lRec.add("ElementCode2", lCmdtyStndrd.getElementCode2());// エレメントコード2
lRec.add("Amount", lCmdtyStndrd.getAmount());// 数量
lRec.execute(lConnection);
} else {
SIModifyRec lRec = new SIModifyRec("CmdtyStndrdMTbl");
lRec.addCondition("ShopCode", lCmdtyStndrd.getShopCode());// ショップコード
lRec.addCondition("CmdtyCode", lCmdtyStndrd.getCmdtyCode());// 商品コード
lRec.addCondition("StndrdCode1", lCmdtyStndrd.getStndrdCode1());// 規格コード1
lRec.addCondition("ElementCode1", lCmdtyStndrd.getElementCode1());// エレメントコード1
lRec.addCondition("StndrdCode2", lCmdtyStndrd.getStndrdCode2());// 規格コード2
lRec.addCondition("ElementCode2", lCmdtyStndrd.getElementCode2());// エレメントコード2
lRec.add("Amount", lCmdtyStndrd.getAmount());// 数量
lRec.execute(lConnection);
}
} catch (SIDuplicateKeyException e) {
errors.addError(new SICustomError("database.insert.duplicate"));
e.printStackTrace();
} catch (SIDBAccessException e) {
errors.addError(new SICustomError("database.execute.error"));
e.printStackTrace();
}
} else {
errors = (SICustomErrors) request.getAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY);
}
if (SIUtil.isNotNull(errors.getErrorMsg())) {
lResBuf.append("
Line[").append(lRead.getLineNo()).append("]:エラー");
lResBuf.append(errors.getErrorMsg());
}
} catch (SIErrorException e) {
lResBuf.append("
Line[").append(lRead.getLineNo()).append("]:エラー");
lResBuf.append(SIErrorFactory.getErrorMsg("input.data.csv.format"));
}
request.removeAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY);
}
return lResBuf.toString();
}
/**
* writeCmdtyCtgry CSVファイルから取った商品カテゴリのデータで商品カテゴリテーブルに新規（既存）レコードを作成します。
*
* @param request リクエスト
* @param response
* @param lConnection DBへのコネクション
* @param lRead データリスト
* @return エラーメッセージ
* @throws なし
*/
public String writeCmdtyCtgry(HttpServletRequest request, HttpServletResponse response, Connection lConnection, SICSVRead lRead, String lEditModeTxt) {
SICustomErrors errors = new SICustomErrors();
SILogin lLogin = SIHTMLUtil.getLogin(request);
StringBuffer lResBuf = new StringBuffer();
while (lRead.hasNextLine()) {
String[] lItemList;
try {
lItemList = lRead.itemList();
if (lItemList == null || lItemList.length == 0) continue;
// 項目数のチェック
if (lItemList.length != SICSVConf.getFieldName(SICSVConf.SICSV_CMDTY_CTGRY_INX).length) {
lResBuf.append("
Line[").append(lRead.getLineNo()).append("]:エラー");
lResBuf.append(SIErrorFactory.getErrorMsg("manager.app.enough.item"));
continue;
}
errors = new SICustomErrors();
// データの設定
SICmdtyCtgry lCmdtyCtgry = new SICmdtyCtgry();
lCmdtyCtgry.setEncode(SIConfig.SIENCODE_NONE);
lCmdtyCtgry.setEditModeTxt(lEditModeTxt);
lCmdtyCtgry.setShopCode(lItemList[0]);// ショップコード
lCmdtyCtgry.setCmdtyCode(lItemList[1]);// 商品コード
lCmdtyCtgry.setCtgryCode(lItemList[2]);// カテゴリコード
if (lCmdtyCtgry.validate(request, lConnection, true)) {
try {
if (lLogin.isShop() && !lCmdtyCtgry.getShopCode().equals(lLogin.getMallShopCode())) {
lResBuf.append("
Line[").append(lRead.getLineNo()).append("]:エラー");
lResBuf.append(SIErrorFactory.getErrorMsg("manager.app.mismatch.shop"));
continue;
}
SIInsertRec lRec = new SIInsertRec("CmdtyCtgryMTbl");
lRec.add("ShopCode", lCmdtyCtgry.getShopCode());// ショップコード
lRec.add("CmdtyCode", lCmdtyCtgry.getCmdtyCode());// 商品コード
lRec.add("CtgryCode", lCmdtyCtgry.getCtgryCode());// カテゴリコード
lRec.execute(lConnection);
} catch (SIDuplicateKeyException e) {
errors.addError(new SICustomError("database.insert.duplicate"));
e.printStackTrace();
} catch (SIDBAccessException e) {
errors.addError(new SICustomError("database.execute.error"));
e.printStackTrace();
}
} else {
errors = (SICustomErrors) request.getAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY);
}
if (SIUtil.isNotNull(errors.getErrorMsg())) {
lResBuf.append("
Line[").append(lRead.getLineNo()).append("]:エラー");
lResBuf.append(errors.getErrorMsg());
}
} catch (SIErrorException e) {
lResBuf.append("
Line[").append(lRead.getLineNo()).append("]:エラー");
lResBuf.append(SIErrorFactory.getErrorMsg("input.data.csv.format"));
}
request.removeAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY);
}
return lResBuf.toString();
}
/**
* writeStndrd CSVファイルから取った規格データで規格テーブルに新規レコードを作成します。
*
* @param request リクエスト
* @param response
* @param lConnection DBへのコネクション
* @param lItemList データリスト
* @return エラーメッセージ
* @throws なし
*/
public String writeStndrd(HttpServletRequest request, HttpServletResponse response, Connection lConnection, SICSVRead lRead, String lEditModeTxt) {
SICustomErrors errors = new SICustomErrors();
SILogin lLogin = SIHTMLUtil.getLogin(request);
StringBuffer lResBuf = new StringBuffer();
while (lRead.hasNextLine()) {
String[] lItemList;
try {
lItemList = lRead.itemList();
if (lItemList == null || lItemList.length == 0) continue;
// 項目数のチェック
if (lItemList.length != SICSVConf.getFieldName(SICSVConf.SICSV_STNDRD_NAME_INX).length) {
lResBuf.append("
Line[").append(lRead.getLineNo()).append("]:エラー");
lResBuf.append(SIErrorFactory.getErrorMsg("manager.app.enough.item"));
continue;
}
errors = new SICustomErrors();
// データの設定
UIRegStndrdName lStndrdName = new UIRegStndrdName();
lStndrdName.setEncode(SIConfig.SIENCODE_NONE);
lStndrdName.setEditModeTxt(lEditModeTxt);
try {
if (lStndrdName.getEditModeTxt().equals(SIConfig.SIEDIT_MODE_INSERT)) {
lStndrdName.setShopCode(lItemList[0]);// ショップコード
lStndrdName.setStndrdCode(lItemList[1]);// 規格コード
lStndrdName.setStndrdName(lItemList[2]);// 規格名
if (lStndrdName.validateInsert(request, lConnection, true)) {
if (lLogin.isShop() && !lStndrdName.getShopCode().equals(lLogin.getMallShopCode())) {
lResBuf.append("
Line[").append(lRead.getLineNo()).append("]:エラー");
lResBuf.append(SIErrorFactory.getErrorMsg("manager.app.mismatch.shop"));
continue;
}
SIRegStndrdNameSrv.insertTableData(lConnection, lStndrdName);
} else {
errors = (SICustomErrors) request.getAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY_BAK);
}
} else {
lStndrdName.setRegShopCodeTxt(lItemList[0]);// ショップコード
lStndrdName.setRegStndrdCodeTxt(lItemList[1]);// 規格コード
lStndrdName.setRegStndrdNameTxt(lItemList[2]);// 規格名
if (lStndrdName.validateModify(request, lConnection, true)) {
if (lLogin.isShop() && !lStndrdName.getRegShopCodeTxt().equals(lLogin.getMallShopCode())) {
lResBuf.append("
Line[").append(lRead.getLineNo()).append("]:エラー");
lResBuf.append(SIErrorFactory.getErrorMsg("manager.app.mismatch.shop"));
continue;
}
SIRegStndrdNameSrv.updateTableData(lConnection, lStndrdName);// 更新
} else {
errors = (SICustomErrors) request.getAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY_BAK);
}
}
} catch (SIDuplicateKeyException e1) {
e1.printStackTrace();
errors.addError(new SICustomError("database.insert.duplicate"));
} catch (SIDBAccessException e1) {
e1.printStackTrace();
errors.addError(new SICustomError("database.execute.error"));
}
if (SIUtil.isNotNull(errors.getErrorMsg())) {
lResBuf.append("
Line[").append(lRead.getLineNo()).append("]:エラー");
lResBuf.append(errors.getErrorMsg());
}
} catch (SIErrorException e) {
lResBuf.append("
Line[").append(lRead.getLineNo()).append("]:エラー");
lResBuf.append(SIErrorFactory.getErrorMsg("input.data.csv.format"));
}
request.removeAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY);
}
return lResBuf.toString();
}
/**
* writeStndrdContent CSVファイルから取ったエレメントデータでエレメントテーブルに新規レコードを作成します。
*
* @param request リクエスト
* @param response
* @param lConnection DBへのコネクション
* @param lItemList データリスト
* @return エラーメッセージ
* @throws なし
*/
public String writeStndrdContent(HttpServletRequest request, HttpServletResponse response, Connection lConnection, SICSVRead lRead, String lEditModeTxt) {
SICustomErrors errors = new SICustomErrors();
SILogin lLogin = SIHTMLUtil.getLogin(request);
StringBuffer lResBuf = new StringBuffer();
while (lRead.hasNextLine()) {
String[] lItemList;
try {
lItemList = lRead.itemList();
if (lItemList == null || lItemList.length == 0) continue;
// 項目数のチェック
if (lItemList.length != SICSVConf.getFieldName(SICSVConf.SICSV_STNDRD_CONTENT_INX).length) {
lResBuf.append("
Line[").append(lRead.getLineNo()).append("]:エラー");
lResBuf.append(SIErrorFactory.getErrorMsg("manager.app.enough.item"));
continue;
}
errors = new SICustomErrors();
// データの設定
UIRegStndrdContent lStndrdContent = new UIRegStndrdContent();
lStndrdContent.setEncode(SIConfig.SIENCODE_NONE);
lStndrdContent.setEditModeTxt(lEditModeTxt);
try {
if (lStndrdContent.getEditModeTxt().equals(SIConfig.SIEDIT_MODE_INSERT)) {
lStndrdContent.setShopCode(lItemList[0]);// ショップコード
lStndrdContent.setStndrdCode(lItemList[1]);// 規格コード
lStndrdContent.setElementCode(lItemList[2]);// エレメントコード
lStndrdContent.setDispOrder(lItemList[3]);// 表示順
lStndrdContent.setElementName(lItemList[4]);// エレメント名
if (lStndrdContent.validateInsert(request, lConnection, true)) {
if (lLogin.isShop() && !lStndrdContent.getShopCode().equals(lLogin.getMallShopCode())) {
lResBuf.append("
Line[").append(lRead.getLineNo()).append("]:エラー");
lResBuf.append(SIErrorFactory.getErrorMsg("manager.app.mismatch.shop"));
continue;
}
SIRegStndrdContentSrv.insertTableData(lConnection, lStndrdContent);// 新規
} else {
errors = (SICustomErrors) request.getAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY_BAK);
}
} else {
lStndrdContent.setShopCode(lItemList[0]);// ショップコード
lStndrdContent.setStndrdCode(lItemList[1]);// 規格コード
lStndrdContent.setRegElementCodeTxt(lItemList[2]);// エレメントコード
lStndrdContent.setRegDispOrderTxt(lItemList[3]);// 表示順
lStndrdContent.setRegElementNameTxt(lItemList[4]);// エレメント名
if (lStndrdContent.validateUpdate(request, lConnection, true)) {
if (lLogin.isShop() && !lStndrdContent.getShopCode().equals(lLogin.getMallShopCode())) {
lResBuf.append("
Line[").append(lRead.getLineNo()).append("]:エラー");
lResBuf.append(SIErrorFactory.getErrorMsg("manager.app.mismatch.shop"));
continue;
}
SIRegStndrdContentSrv.updateTableData(lConnection, lStndrdContent);// 更新
} else {
errors = (SICustomErrors) request.getAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY_BAK);
}
}
} catch (SIDuplicateKeyException e1) {
e1.printStackTrace();
errors.addError(new SICustomError("database.insert.duplicate"));
} catch (SIDBAccessException e1) {
e1.printStackTrace();
errors.addError(new SICustomError("database.execute.error"));
}
if (SIUtil.isNotNull(errors.getErrorMsg())) {
lResBuf.append("
Line[").append(lRead.getLineNo()).append("]:エラー");
lResBuf.append(errors.getErrorMsg());
}
} catch (SIErrorException e) {
lResBuf.append("
Line[").append(lRead.getLineNo()).append("]:エラー");
lResBuf.append(SIErrorFactory.getErrorMsg("input.data.csv.format"));
}
request.removeAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY);
}
return lResBuf.toString();
}
// 7.2.0 ST0306 追加 ここから
/**
* writeCtgry CSVファイルから取ったカテゴリデータでカテゴリテーブルの更新を行います。
*
* @param request リクエスト
* @param response
* @param lConnection DBへのコネクション
* @param lRead データリスト
* @return エラーメッセージ
* @throws なし
*/
public String writeCtgry(HttpServletRequest request, HttpServletResponse response, Connection lConnection, SICSVRead lRead, String lEditModeTxt) {
SICustomErrors errors = new SICustomErrors();
StringBuffer lResBuf = new StringBuffer();
while (lRead.hasNextLine()) {
String[] lItemList;
try {
lItemList = lRead.itemList();
if (lItemList == null || lItemList.length == 0) continue;
// 項目数のチェック
if (lItemList.length != SICSVConf.getFieldName(SICSVConf.SICSV_CTGRY_INX).length) {
lResBuf.append("
Line[").append(lRead.getLineNo()).append("]:エラー");
lResBuf.append(SIErrorFactory.getErrorMsg("manager.app.enough.item"));
continue;
}
errors = new SICustomErrors();
// データの設定
UICtgryListCond ctgryList = new UICtgryListCond();
ctgryList.setEncode(SIConfig.SIENCODE_NONE);
ctgryList.setEditModeTxt(lEditModeTxt);
ctgryList.setCtgryCode(lItemList[0]); // カテゴリコード
ctgryList.setParentCtgryCode(lItemList[1]); // 親カテゴリコード
ctgryList.setCtgryName(lItemList[2]); // カテゴリ名
ctgryList.setAbbvCtgryName(lItemList[3]); // カテゴリ略名
ctgryList.setCmdtyFlagTxt(lItemList[5]); // フロントでの初期表示
if (ctgryList.validateReg(request, lConnection)) {
try {
if (ctgryList.getEditModeTxt().equals(SIConfig.SIEDIT_MODE_INSERT)) {
SIRegCtgrySrv.insertTableData(lConnection, ctgryList);
// SIRegCtgrySrv.updateSrchCtgryTable(lConnection, ctgryList);
} else {
SIRegCtgrySrv.updateTableData(lConnection, ctgryList, false);
}
} catch (SIDuplicateKeyException e) {
errors.addError(new SICustomError("database.insert.duplicate"));
e.printStackTrace();
} catch (SIDBAccessException e) {
errors.addError(new SICustomError("database.execute.error"));
e.printStackTrace();
}
} else {
errors = (SICustomErrors) request.getAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY);
}
if (SIUtil.isNotNull(errors.getErrorMsg())) {
lResBuf.append("
Line[").append(lRead.getLineNo()).append("]:エラー");
lResBuf.append(errors.getErrorMsg());
}
} catch (SIErrorException e) {
lResBuf.append("
Line[").append(lRead.getLineNo()).append("]:エラー");
lResBuf.append(SIErrorFactory.getErrorMsg("input.data.csv.format"));
} catch (SQLException e) {
e.printStackTrace();
} catch (NamingException e) {
e.printStackTrace();
}
request.removeAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY);
}
return lResBuf.toString();
}
// 7.2.0 ST0306 追加 ここまで
/**
* writeStoreStock CSVファイルから取った在庫データで店舗在庫テーブルに新規レコードを作成します。
*
* @param request リクエスト
* @param response
* @param lConnection DBへのコネクション
* @param lRead データリスト
* @return エラーメッセージ
* @throws なし
*/
public String writeStoreStock(HttpServletRequest request, HttpServletResponse response, Connection lConnection, SICSVRead lRead, String lEditModeTxt) {
SICustomErrors errors = new SICustomErrors();
StringBuffer lResBuf = new StringBuffer();
while (lRead.hasNextLine()) {
String[] lItemList;
try {
lItemList = lRead.itemList();
if (lItemList == null || lItemList.length == 0) continue;
// 項目数のチェック
if (lItemList.length != SICSVConf.getFieldName(SICSVConf.SICSV_STOCK_LIST_INX).length) {
lResBuf.append("
Line[").append(lRead.getLineNo()).append("]:エラー");
lResBuf.append(SIErrorFactory.getErrorMsg("manager.app.enough.item"));
continue;
}
errors = new SICustomErrors();
// データの設定
SIStoreStock lRegStock = new SIStoreStock();
lRegStock.setEncode(SIConfig.SIENCODE_NONE);
lRegStock.setEditModeTxt(lEditModeTxt);
lRegStock.setShopCode(lItemList[0]);
lRegStock.setCmdtyCode(lItemList[1]);
lRegStock.setIndividualCode(lItemList[2]);
lRegStock.setBranchCode(lItemList[3]);
lRegStock.setSalesPriorityFlg(lItemList[4]);
lRegStock.setAmount(lItemList[5]);
lRegStock.setInitDateTime(lItemList[6]);
lRegStock.setUpdateDateTime(lItemList[7]);
if (lRegStock.validate(request, lConnection)) {
try {
if (lRegStock.getEditModeTxt().equals(SIConfig.SIEDIT_MODE_INSERT)) {
SIRegIndividualSrv.insertTableDataStock(lConnection, lRegStock);// 7.3.0 ST2023 修正 引数追加
} else {
SIRegIndividualSrv.updateTableDataStock(lConnection, lRegStock);// 7.3.0 ST2023 修正 引数追加
}
} catch (SIDuplicateKeyException e) {
errors.addError(new SICustomError("database.insert.duplicate"));
e.printStackTrace();
} catch (SIDBAccessException e) {
errors.addError(new SICustomError("database.execute.error"));
e.printStackTrace();
}
} else {
errors = (SICustomErrors) request.getAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY);
}
if (SIUtil.isNotNull(errors.getErrorMsg())) {
lResBuf.append("
Line[").append(lRead.getLineNo()).append("]:エラー");
lResBuf.append(errors.getErrorMsg());
}
} catch (SIErrorException e) {
lResBuf.append("
Line[").append(lRead.getLineNo()).append("]:エラー");
lResBuf.append(SIErrorFactory.getErrorMsg("input.data.csv.format"));
}
request.removeAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY);
}
return lResBuf.toString();
}
/**
* writeStoreStock CSVファイルから取った入庫データで新規入庫データを作成します。
*
* @param request リクエスト
* @param response
* @param lConnection DBへのコネクション
* @param lRead データリスト
* @return エラーメッセージ
* @throws なし
*/
public String writeNewStore(HttpServletRequest request, HttpServletResponse response, Connection lConnection, SICSVRead lRead) {
SICustomErrors errors = new SICustomErrors();
StringBuffer lResBuf = new StringBuffer();
int errorcount = 0;
while (lRead.hasNextLine()) {
String[] lItemList;
try {
lItemList = lRead.itemList();
if (lItemList == null || lItemList.length == 0) continue;
// 項目数のチェック
if (lItemList.length != 8) {
lResBuf.append("
Line[").append(lRead.getLineNo()).append("]:エラー");
lResBuf.append(SIErrorFactory.getErrorMsg("manager.app.enough.item"));
continue;
}
errors = new SICustomErrors();
// データの設定
UIStockHistory stockHistory = new UIStockHistory();
stockHistory.setEncode(SIConfig.SIENCODE_NONE);
stockHistory.setIndividualCode(lItemList[0]);
stockHistory.setNewBranch(lItemList[1]);
stockHistory.setNewCharge(lItemList[2]);
stockHistory.setNewAmount(lItemList[3]);
stockHistory.setPurchasePrice(lItemList[4]);
stockHistory.setProcessingExpence(lItemList[5]);
stockHistory.setType(lItemList[6]);
stockHistory.setComment(lItemList[7]);
if (stockHistory.validateNewStore(request, lConnection, true)) {
try {
if (stockHistory.getType().equals("4") || stockHistory.getType().equals("6")) {// その他・訂正の場合は未承認
SIRegStockControlSrv.createNewStore2(lConnection, stockHistory);
} else {
SIRegStockControlSrv.createNewStore(lConnection, stockHistory);
}
} catch (SIDBAccessException e) {
errors.addError(new SICustomError("database.execute.error"));
e.printStackTrace();
}
} else {
errors = (SICustomErrors) request.getAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY);
}
if (SIUtil.isNotNull(errors.getErrorMsg())) {
lResBuf.append("
Line[").append(lRead.getLineNo()).append("]:エラー");
lResBuf.append(errors.getErrorMsg());
errorcount++;
}
} catch (SIErrorException e) {
lResBuf.append("
Line[").append(lRead.getLineNo()).append("]:エラー");
lResBuf.append(SIErrorFactory.getErrorMsg("input.data.csv.format"));
errorcount++;
}
request.removeAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY);
}
if(errorcount == 0) {
try {
lConnection.commit();
} catch (SQLException sqle) {}
} else {
try {
lConnection.rollback();
} catch (SQLException sqle) {}
}
return lResBuf.toString();
}
public String writePayment(HttpServletRequest request, HttpServletResponse response, Connection lConnection, SICSVRead lRead) {
SICustomErrors errors = new SICustomErrors();
SILogin lLogin = SIHTMLUtil.getLogin(request);
StringBuffer lResBuf = new StringBuffer();
while (lRead.hasNextLine()) {
String[] lItemList;
try {
lItemList = lRead.itemList();
if (lItemList == null || lItemList.length == 0) continue;
// 項目数のチェック
if (lItemList.length != 4) {
lResBuf.append("
Line[").append(lRead.getLineNo()).append("]:エラー");
lResBuf.append(SIErrorFactory.getErrorMsg("manager.app.enough.item"));
continue;
}
errors = new SICustomErrors();
// データの設定
UIRegHacchuPayment lPayment = new UIRegHacchuPayment();
lPayment.setEncode(SIConfig.SIENCODE_NONE);
lPayment.setStockCode(lItemList[0]);
lPayment.setPaymentPriceTxt(lItemList[1]);
lPayment.setPaymentDateCSV(lItemList[2]);
lPayment.setMemo(lItemList[3]);
if (lPayment.validateInsertCSV(request, lConnection)) {
try {
SIInsertRec lRec = new SIInsertRec("HacchuPaymentTbl");
lRec.add("StockCode", lPayment.getStockCode());
lRec.add("PaymentPrice", lPayment.getPaymentPriceTxt());
lRec.add("PaymentDate", lPayment.getPaymentDateCSV());
lRec.add("Memo", lPayment.getMemo());
lRec.add("InitUserCode", lLogin.getUserCode());
lRec.add("Index", "5");
lRec.execute(lConnection);
} catch (SIDuplicateKeyException e) {
errors.addError(new SICustomError("database.insert.duplicate"));
e.printStackTrace();
} catch (SIDBAccessException e) {
errors.addError(new SICustomError("database.execute.error"));
e.printStackTrace();
}
} else {
errors = (SICustomErrors) request.getAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY);
}
if (SIUtil.isNotNull(errors.getErrorMsg())) {
lResBuf.append("
Line[").append(lRead.getLineNo()).append("]:エラー");
lResBuf.append(errors.getErrorMsg());
}
} catch (SIErrorException e) {
lResBuf.append("
Line[").append(lRead.getLineNo()).append("]:エラー");
lResBuf.append(SIErrorFactory.getErrorMsg("input.data.csv.format"));
}
request.removeAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY);
}
return lResBuf.toString();
}
public String writeKeep(HttpServletRequest request, HttpServletResponse response, Connection lConnection, SICSVRead lRead) {
SICustomErrors errors = new SICustomErrors();
SILogin lLogin = SIHTMLUtil.getLogin(request);
StringBuffer lResBuf = new StringBuffer();
String keepNumber = "";
Statement lStatement = null;
ResultSet lResultSet = null;
String lDate = new SIDateTime().getFullDate();
try{
keepNumber = SIDBUtil.getFirstData(lConnection, "SELECT max(keepnumber)+1 FROM keepTbl");
String branchCode = SIDBUtil.getFirstData(lConnection, "SELECT belongingbranchcode FROM chargetbl WHERE chargecode="+SIDBUtil.SQL2Str(lLogin.getUserCode()));
if (SIUtil.isNull(branchCode)) {
lResBuf.append("ログインユーザエラー：担当者アカウントでログインして下さい");
return lResBuf.toString();
}else{
SIInsertRec lRec = new SIInsertRec("KeepTbl");
lRec.add("KeepNumber", keepNumber);
lRec.add("BranchCode", branchCode);
lRec.add("ChargeCode", lLogin.getUserCode());
lRec.add("Status", "0");
lRec.add("KeepMailFlg", "0");
lRec.add("KeepMailDateTime", "");
lRec.add("ProtectedFlg", "0");
lRec.add("KeepDate", lDate);
lRec.execute(lConnection);
}
}catch(Exception e){
lResBuf.append(SIErrorFactory.getErrorMsg("database.execute.error"));
return lResBuf.toString();
}
while (lRead.hasNextLine()) {
String[] lItemList;
try {
lItemList = lRead.itemList();
if (lItemList == null || lItemList.length == 0) continue;
// 項目数のチェック
if (lItemList.length != 3) {
lResBuf.append("
Line[").append(lRead.getLineNo()).append("]:エラー");
lResBuf.append(SIErrorFactory.getErrorMsg("manager.app.enough.item"));
continue;
}
errors = new SICustomErrors();
// データの設定
UICmdtyKeep lKeepDetail = new UICmdtyKeep();
lKeepDetail.setEncode(SIConfig.SIENCODE_NONE);
lKeepDetail.setIndividualCode(lItemList[0]);
lKeepDetail.setAmount(lItemList[1]);
lKeepDetail.setBranchCode(lItemList[2]);
StringBuffer lSql = new StringBuffer();
lSql.append("SELECT i.cmdtycode,i.individualcode,c.cmdtyname,i.bgprice, ");
lSql.append("CASE i.amountflg WHEN 0 THEN 1 ELSE 0 END AS cmdtytype, ");
lSql.append("CASE i.amountflg WHEN 0 THEN i.purchaseprice2 ELSE i.purchaseprice END AS purchaseprice, ");
lSql.append("coalesce(i.processingexpence,0) AS processingexpence, ");
lSql.append("CASE i.amountflg WHEN 0 THEN i.purchaseprice2 ELSE i.purchaseprice END + coalesce(i.processingexpence,0) AS cost, ");
lSql.append("c.deliverytypecode,c.cmdtysize,i.consigngoodsfee ");
lSql.append("FROM cmdtymtbl c,individualtbl i ");
lSql.append("WHERE c.cmdtycode=i.cmdtycode ");
lSql.append("AND i.individualcode=").append(SIDBUtil.SQL2Str(lKeepDetail.getIndividualCode()));
try {
lStatement = lConnection.createStatement();
lResultSet = lStatement.executeQuery(lSql.toString());
if (lResultSet.next()) {
if ("1".equals(lResultSet.getString("CmdtyType"))) {
lKeepDetail.setBranchCode("999");
} else {
StringBuffer lAmountSql = new StringBuffer();
lAmountSql.append("SELECT amount FROM storestocktbl ");
lAmountSql.append("WHERE individualcode=").append(SIDBUtil.SQL2Str(lKeepDetail.getIndividualCode()));
lAmountSql.append(" AND branchcode=").append(SIDBUtil.SQL2Str(lKeepDetail.getBranchCode()));
lAmountSql.append(" AND amount>=").append(SIDBUtil.SQL2Str(lKeepDetail.getAmount()));
if(SIDBUtil.hasData(lConnection, lAmountSql.toString())) {
SISpcType lSpc = new SISpcType("amount-" + lKeepDetail.getAmount());
SIModifyRec lModRec = new SIModifyRec("StoreStockTbl");
lModRec.addCondition("CmdtyCode",lResultSet.getString("CmdtyCode"));
lModRec.addCondition("IndividualCode",lResultSet.getString("IndividualCode"));
lModRec.addCondition("BranchCode",lKeepDetail.getBranchCode());
lModRec.add("Amount",lSpc);
lModRec.execute(lConnection);
} else {
lResBuf.append("
Line[").append(lRead.getLineNo()).append("]:エラー");
lResBuf.append("支店コード").append(lKeepDetail.getBranchCode()).append("／");
lResBuf.append(lKeepDetail.getIndividualCode()).append("の在庫数量が足りません");
continue;
}
}
SIInsertRec lRec = new SIInsertRec("KeepDetailTbl");
lRec.add("KeepNumber", keepNumber);
lRec.add("CmdtyCode",lResultSet.getString("CmdtyCode"));
lRec.add("IndividualCode",lResultSet.getString("IndividualCode"));
lRec.add("CmdtyName",lResultSet.getString("CmdtyName"));
lRec.add("BranchCode",lKeepDetail.getBranchCode());
lRec.add("Amount",lKeepDetail.getAmount());
lRec.add("Price",lResultSet.getString("BGPrice"));
lRec.add("PurchasePrice",lResultSet.getString("PurchasePrice"));
lRec.add("ProcessingExpence",lResultSet.getString("ProcessingExpence"));
lRec.add("Cost",lResultSet.getString("Cost"));
lRec.add("CmdtyType",lResultSet.getString("CmdtyType"));
lRec.add("DeliveryType",lResultSet.getString("DeliveryTypeCode"));
lRec.add("Size",lResultSet.getString("CmdtySize"));
lRec.add("InitDateTime", lDate);
lRec.execute(lConnection);
} else {
errors.addError(new SICustomError("input.data.not.exist","在庫コード","商品マスタ"));
}
SIDBUtil.close(lStatement, lResultSet);
} catch (SIDuplicateKeyException e) {
errors.addError(new SICustomError("database.insert.duplicate"));
e.printStackTrace();
} catch (SIDBAccessException e) {
errors.addError(new SICustomError("database.execute.error"));
e.printStackTrace();
} catch (SQLException e) {
errors.addError(new SICustomError("database.execute.error"));
e.printStackTrace();
}
if (SIUtil.isNotNull(errors.getErrorMsg())) {
lResBuf.append("
Line[").append(lRead.getLineNo()).append("]:エラー");
lResBuf.append(errors.getErrorMsg());
}
} catch (SIErrorException e) {
lResBuf.append("
Line[").append(lRead.getLineNo()).append("]:エラー");
lResBuf.append(SIErrorFactory.getErrorMsg("input.data.csv.format"));
}
request.removeAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY);
}
return lResBuf.toString();
}
public String writeMove(HttpServletRequest request, HttpServletResponse response, Connection lConnection, SICSVRead lRead) {
SICustomErrors errors = new SICustomErrors();
SILogin lLogin = SIHTMLUtil.getLogin(request);
StringBuffer lResBuf = new StringBuffer();
Statement lStatement = null;
ResultSet lResultSet = null;
String lDate = new SIDateTime().getFullDate();
String lDateTime = new SIDateTime().getFullDateTime();
//ログインアカウントチェック
if(!"3".equals(lLogin.getMngCmdtyLevel())) {
lResBuf.append("ログインユーザエラー：承認管理者権限のあるアカウントでログインして下さい");
return lResBuf.toString();
}
try{
if (!SIDBUtil.hasData(lConnection, "SELECT chargeCode FROM chargetbl WHERE chargecode="+SIDBUtil.SQL2Str(lLogin.getUserCode()))) {
lResBuf.append("ログインユーザエラー：担当者アカウントでログインして下さい");
return lResBuf.toString();
}
}catch(Exception e){
lResBuf.append(SIErrorFactory.getErrorMsg("database.execute.error"));
return lResBuf.toString();
}
while (lRead.hasNextLine()) {
String[] lItemList;
try {
lItemList = lRead.itemList();
if (lItemList == null || lItemList.length == 0) continue;
// 項目数のチェック
if (lItemList.length != 4) {
lResBuf.append("
Line[").append(lRead.getLineNo()).append("]:エラー");
lResBuf.append(SIErrorFactory.getErrorMsg("manager.app.enough.item"));
continue;
}
errors = new SICustomErrors();
// データの設定
UICmdtyKeep lKeepDetail = new UICmdtyKeep();
lKeepDetail.setEncode(SIConfig.SIENCODE_NONE);
lKeepDetail.setIndividualCode(lItemList[0]);
lKeepDetail.setAmount(lItemList[1]);
lKeepDetail.setBranchCode(lItemList[2]);
// データの設定
UIStockHistory shipData = new UIStockHistory();
shipData.setEncode(SIConfig.SIENCODE_NONE);
shipData.setIndividualCode(lItemList[0]);
shipData.setNewAmount(lItemList[1]);
shipData.setNewBranch(lItemList[2]);
shipData.setNewCharge(lLogin.getUserCode());
UIStockHistory storeData = new UIStockHistory();
storeData.setEncode(SIConfig.SIENCODE_NONE);
storeData.setIndividualCode(lItemList[0]);
storeData.setNewAmount(lItemList[1]);
storeData.setNewBranch(lItemList[3]);
storeData.setNewCharge(lLogin.getUserCode());
StringBuffer cmdtyCheckSql = new StringBuffer();
cmdtyCheckSql.append("SELECT individualcode FROM individualtbl WHERE amountflg=1 AND individualcode=").append(SIDBUtil.SQL2Str(shipData.getIndividualCode()));
StringBuffer fromCheckSql = new StringBuffer();
fromCheckSql.append("SELECT branchname FROM branchtbl WHERE branchcode=").append(SIDBUtil.SQL2Str(shipData.getNewBranch()));
StringBuffer toCheckSql = new StringBuffer();
toCheckSql.append("SELECT branchname FROM branchtbl WHERE branchcode=").append(SIDBUtil.SQL2Str(storeData.getNewBranch()));
StringBuffer amountCheckSql = new StringBuffer();
amountCheckSql.append("SELECT individualcode FROM storestocktbl ");
amountCheckSql.append("WHERE individualcode=").append(SIDBUtil.SQL2Str(shipData.getIndividualCode()));
amountCheckSql.append(" AND branchcode=").append(SIDBUtil.SQL2Str(shipData.getNewBranch()));
amountCheckSql.append(" AND amount>=").append(SIDBUtil.SQL2Str(shipData.getNewAmount()));
StringBuffer lSql = new StringBuffer();
lSql.append("SELECT individual.cmdtycode,individual.individualcode, ");
lSql.append("ship.shipnumber,store.storenumber ");
lSql.append("FROM individualtbl individual ");
lSql.append(",(SELECT individualcode,max(shipnumber)+1 AS shipnumber FROM shiphistorytbl ");
lSql.append(" WHERE individualcode=").append(SIDBUtil.SQL2Str(shipData.getIndividualCode()));
lSql.append(" GROUP BY individualcode) ship ");
lSql.append(",(SELECT individualcode,max(storenumber)+1 AS storenumber FROM storehistorytbl ");
lSql.append(" WHERE individualcode=").append(SIDBUtil.SQL2Str(shipData.getIndividualCode()));
lSql.append(" GROUP BY individualcode) store ");
lSql.append("WHERE individual.individualcode=").append(SIDBUtil.SQL2Str(shipData.getIndividualCode()));
lSql.append(" AND individual.individualcode=ship.individualcode AND individual.individualcode=store.individualcode ");
String fromBranch = "";
String toBranch = "";
try {
if(!SIDBUtil.hasData(lConnection, cmdtyCheckSql.toString())) {
lResBuf.append("
Line[").append(lRead.getLineNo()).append("]:エラー");
lResBuf.append(SIErrorFactory.getErrorMsg("input.data.not.exist","在庫コード","商品マスタ"));
continue;
}
fromBranch = SIDBUtil.getFirstData(lConnection, fromCheckSql.toString());
if(SIUtil.isNull(fromBranch)) {
lResBuf.append("
Line[").append(lRead.getLineNo()).append("]:エラー");
lResBuf.append(SIErrorFactory.getErrorMsg("input.data.not.exist","出庫支店コード","支店マスタ"));
continue;
}
toBranch = SIDBUtil.getFirstData(lConnection, toCheckSql.toString());
if(SIUtil.isNull(toBranch)) {
lResBuf.append("
Line[").append(lRead.getLineNo()).append("]:エラー");
lResBuf.append(SIErrorFactory.getErrorMsg("input.data.not.exist","入庫支店コード","支店マスタ"));
continue;
}
if(!SIDBUtil.hasData(lConnection, amountCheckSql.toString())) {
lResBuf.append("
Line[").append(lRead.getLineNo()).append("]:エラー");
lResBuf.append("支店コード").append(shipData.getNewBranch()).append("／");
lResBuf.append(shipData.getIndividualCode()).append("の在庫数量が足りません");
continue;
}
if(Integer.parseInt(shipData.getNewAmount())<1) {
lResBuf.append("
Line[").append(lRead.getLineNo()).append("]:エラー");
lResBuf.append("1以上の数量を入力して下さい");
continue;
}
lStatement = lConnection.createStatement();
lResultSet = lStatement.executeQuery(lSql.toString());
if (lResultSet.next()) {
//出庫数マイナス
SISpcType lSpc1 = new SISpcType("amount-" + shipData.getNewAmount());
SIModifyRec lModRec1 = new SIModifyRec("StoreStockTbl");
lModRec1.addCondition("CmdtyCode",lResultSet.getString("CmdtyCode"));
lModRec1.addCondition("IndividualCode",lResultSet.getString("IndividualCode"));
lModRec1.addCondition("BranchCode",shipData.getNewBranch());
lModRec1.add("Amount",lSpc1);
lModRec1.execute(lConnection);
//入庫数プラス
SISpcType lSpc2 = new SISpcType("amount+" + storeData.getNewAmount());
SIModifyRec lModRec2 = new SIModifyRec("StoreStockTbl");
lModRec2.addCondition("CmdtyCode",lResultSet.getString("CmdtyCode"));
lModRec2.addCondition("IndividualCode",lResultSet.getString("IndividualCode"));
lModRec2.addCondition("BranchCode",storeData.getNewBranch());
lModRec2.add("Amount",lSpc2);
lModRec2.execute(lConnection);
//出庫データ作成
SIInsertRec lRec1 = new SIInsertRec("ShipHistoryTbl");
lRec1.add("ShipNumber", lResultSet.getString("ShipNumber"));
lRec1.add("CmdtyCode",lResultSet.getString("CmdtyCode"));
lRec1.add("IndividualCode",lResultSet.getString("IndividualCode"));
lRec1.add("ShipBranch",shipData.getNewBranch());
lRec1.add("ShipCharge",shipData.getNewCharge());
lRec1.add("Amount",shipData.getNewAmount());
lRec1.add("PurchasePrice","0");
lRec1.add("ProcessingExpence","0");
lRec1.add("ShipType","3");
lRec1.add("DelFlg","4");
lRec1.add("InitDateTime", lDate);
lRec1.add("CalcDateTime", lDateTime);
lRec1.add("Comment", toBranch + "へ出庫");
lRec1.execute(lConnection);
//入庫データ作成
SIInsertRec lRec2 = new SIInsertRec("StoreHistoryTbl");
lRec2.add("StoreNumber", lResultSet.getString("StoreNumber"));
lRec2.add("CmdtyCode",lResultSet.getString("CmdtyCode"));
lRec2.add("IndividualCode",lResultSet.getString("IndividualCode"));
lRec2.add("StoreBranch",storeData.getNewBranch());
lRec2.add("StoreCharge",storeData.getNewCharge());
lRec2.add("Amount",storeData.getNewAmount());
lRec2.add("PurchasePrice","0");
lRec2.add("ProcessingExpence","0");
lRec2.add("StoreType","3");
lRec2.add("DelFlg","4");
lRec2.add("OrderCode", lResultSet.getString("ShipNumber"));
lRec2.add("InitDateTime", lDate);
lRec2.add("CalcDateTime", lDateTime);
lRec2.add("Comment", fromBranch + "から入庫");
lRec2.execute(lConnection);
} else {
errors.addError(new SICustomError("input.data.not.exist","在庫コード","商品マスタ"));
}
} catch (SIDuplicateKeyException e) {
errors.addError(new SICustomError("database.insert.duplicate"));
e.printStackTrace();
} catch (SIDBAccessException e) {
errors.addError(new SICustomError("database.execute.error"));
e.printStackTrace();
} catch (SQLException e) {
errors.addError(new SICustomError("database.execute.error"));
e.printStackTrace();
} finally {
SIDBUtil.close(lResultSet, lStatement);
}
if (SIUtil.isNotNull(errors.getErrorMsg())) {
lResBuf.append("
Line[").append(lRead.getLineNo()).append("]:エラー");
lResBuf.append(errors.getErrorMsg());
}
} catch (SIErrorException e) {
lResBuf.append("
Line[").append(lRead.getLineNo()).append("]:エラー");
lResBuf.append(SIErrorFactory.getErrorMsg("input.data.csv.format"));
}
request.removeAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY);
}
return lResBuf.toString();
}
public String writeRakutenOrder(HttpServletRequest request, HttpServletResponse response, Connection lConnection, SICSVRead lRead) {
SICustomErrors errors = new SICustomErrors();
SILogin lLogin = SIHTMLUtil.getLogin(request);
StringBuffer lResBuf = new StringBuffer();
StringBuffer lSql = new StringBuffer();
lSql.append("SELECT a.cmdtycode,a.individualcode,a.purchaseprice,a.processingexpence,b.amount ");
lSql.append("FROM individualtbl a,storestocktbl b ");
lSql.append("WHERE a.individualcode=b.individualcode AND b.branchcode=19 ");
try{
if (!SIDBUtil.hasData(lConnection, "SELECT chargeCode FROM chargetbl WHERE chargecode="+SIDBUtil.SQL2Str(lLogin.getUserCode()))) {
lResBuf.append("ログインユーザエラー：担当者アカウントでログインして下さい");
return lResBuf.toString();
}
}catch(Exception e){
lResBuf.append(SIErrorFactory.getErrorMsg("database.execute.error"));
return lResBuf.toString();
}
String taxRate = SITax.getTaxRate(lConnection);
boolean isFirst = true;
while (lRead.hasNextLine()) {
String[] lItemList;
try {
lItemList = lRead.itemList2();
if (lItemList == null || lItemList.length == 0) continue;
if (isFirst) {
isFirst = false;
continue;
}
// 項目数のチェック
if (lItemList.length != 40) {
lResBuf.append("
Line[").append(lRead.getLineNo2()).append("]:エラー");
lResBuf.append(SIErrorFactory.getErrorMsg("manager.app.enough.item"));
continue;
}
errors = new SICustomErrors();
// データの設定
String[] temp = new String[40];
for (int i=0;i<40;i++) {
temp[i] = lItemList[i];
}
try {
if (!SICheckValid.checkDuplicate(errors,lConnection, "レコードナンバー"+temp[1], "SELECT * FROM rakutenordertbl WHERE column2="+SIDBUtil.SQL2Str(temp[1]))){
lResBuf.append("
Line[").append(lRead.getLineNo2()).append("]:エラー");
lResBuf.append(errors.getErrorMsg());
continue;
}
SIInsertRec lRec = new SIInsertRec("rakutenordertbl");
for (int j=0;j<40;j++) {
lRec.add("column"+String.valueOf(j+1), temp[j]);
}
lRec.add("status", "0");
lRec.add("initdatetime", new SISpcType("TO_CHAR(now(),'YYYY/MM/DD HH24:MI:SS')::timestamp"));
lRec.execute(lConnection);
} catch (SIDuplicateKeyException e) {
errors.addError(new SICustomError("database.insert.duplicate"));
e.printStackTrace();
} catch (SIDBAccessException e) {
errors.addError(new SICustomError("database.execute.error"));
e.printStackTrace();
}
if (SIUtil.isNotNull(errors.getErrorMsg())) {
lResBuf.append("
Line[").append(lRead.getLineNo2()).append("]:エラー");
lResBuf.append(errors.getErrorMsg());
}
} catch (SIErrorException e) {
lResBuf.append("
Line[").append(lRead.getLineNo2()).append("]:エラー");
lResBuf.append(SIErrorFactory.getErrorMsg("input.data.csv.format"));
}
if (errors.isEmpty()) request.removeAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY);
}
if (SIUtil.isNotNull(lResBuf.toString())) return lResBuf.toString();
try {
Statement lStatement = lConnection.createStatement();
StringBuffer sql = new StringBuffer("SELECT ");
for (int j=0;j<40;j++) {
if(j>0) sql.append(",");
sql.append("coalesce(column").append(String.valueOf(j+1)).append(",'') AS column").append(String.valueOf(j+1));
}
sql.append(" FROM rakutenordertbl WHERE status=0 ORDER BY column1,column2");
ResultSet lResultSet = lStatement.executeQuery(sql.toString());
String lastNumber = "";
String orderCode = "";
String paymentType = "";
int detailCode = 0;
while (lResultSet.next()) {
if (!lResultSet.getString("column1").equals(lastNumber)) {
SIInsertRec lRec1 = new SIInsertRec("ordertbl");
orderCode = SIDBUtil.getFirstData(lConnection, SIDBUtil.getNextVal("ordertbl_ordercd_seq"));
lRec1.add("ordercode", orderCode);
lRec1.add("custcode", "3");
lRec1.add("custcompanyflg", "0");
lRec1.add("custName", lResultSet.getString("column9")+lResultSet.getString("column10"));
lRec1.add("custpronname", lResultSet.getString("column11")+lResultSet.getString("column12"));
lRec1.add("email", "r.order@beautygarage.jp");
lRec1.add("orderaddressee", lResultSet.getString("column9")+lResultSet.getString("column10"));
lRec1.add("postcode1", lResultSet.getString("column13"));
lRec1.add("postcode2", lResultSet.getString("column14"));
lRec1.add("address1", lResultSet.getString("column15"));
lRec1.add("address2", lResultSet.getString("column16"));
lRec1.add("address3", lResultSet.getString("column17"));
lRec1.add("storetel", lResultSet.getString("column18")+lResultSet.getString("column19")+lResultSet.getString("column20"));
if (SIUtil.isNotNull(lResultSet.getString("column33"))) {
if (lResultSet.getString("column33").equals("クレジットカード")) {
lRec1.add("paymethodname", "楽天カード");
lRec1.add("paymentflg", "2");
lRec1.add("receiptdate", lResultSet.getString("column3")+" "+lResultSet.getString("column4"));
paymentType = "楽天カード";
} else if (lResultSet.getString("column33").equals("楽天バンク決済")) {
lRec1.add("paymethodname", "楽天バンク");
lRec1.add("paymentflg", "2");
lRec1.add("receiptdate", lResultSet.getString("column3")+" "+lResultSet.getString("column4"));
paymentType = "楽天バンク";
} else if (lResultSet.getString("column33").equals("代金引換")) {
lRec1.add("paymethodname", "楽天代引");
lRec1.add("paymentflg", "2");
} else if (lResultSet.getString("column33").equals("銀行振込")) {
lRec1.add("paymethodname", "楽天銀振");
lRec1.add("paymentflg", "0");
} else {
lRec1.add("paymethodname", lResultSet.getString("column33"));
lRec1.add("paymentflg", "0");
}
}
lRec1.add("fee", "0");
lRec1.add("taxrate", taxRate);
lRec1.add("discountFee", lResultSet.getString("column36"));
lRec1.add("discountDeliveryFee", lResultSet.getString("column35"));
if ("1".equals(lResultSet.getString("column40"))) lRec1.add("sumbypoint", lResultSet.getString("column39"));
else lRec1.add("sumbypoint", "0");
lRec1.add("status", "1");
lRec1.add("feetaxflg", "2");
lRec1.add("memo", lResultSet.getString("column34"));
lRec1.add("receiptmailflg", "0");
lRec1.add("pointenableflg", "1");
lRec1.add("orderbranchcode", "1");
lRec1.add("enabledflg", "1");
lRec1.add("status", "1");
lRec1.add("job", "楽天");
lRec1.add("branchcode", "19");
if(lLogin.getUserCode().startsWith("019")) {
lRec1.add("chargecode", lLogin.getUserCode());//楽天アカウントからの場合
} else {
lRec1.add("chargecode", "01902");//それ以外の場合は松本さん固定
}
lRec1.add("initdatetime", lResultSet.getString("column3")+" "+lResultSet.getString("column4"));
lRec1.add("updatedatetime", lResultSet.getString("column3")+" "+lResultSet.getString("column4"));
lRec1.add("orderroute", "7");
lRec1.execute(lConnection);
if (SIUtil.isNotNull(paymentType)) {
SIInsertRec lRec1p = new SIInsertRec("paymenttbl");
lRec1p.add("ordercode", orderCode);
lRec1p.add("paymentnumber", "1");
lRec1p.add("paymentprice", lResultSet.getString("column37"));
lRec1p.add("paymentfee", "0");
lRec1p.add("paymenttype", paymentType);
lRec1p.add("expectedpaymentdate", lResultSet.getString("column3"));
lRec1p.add("paymentdate", lResultSet.getString("column3"));
lRec1p.add("status", "0");
lRec1p.execute(lConnection);
paymentType = "";
}
SIInsertRec lRec2 = new SIInsertRec("orderdeliverytbl");
lRec2.add("ordercode", orderCode);
lRec2.add("deliverycode", "1");
lRec2.add("deliveryname", "本人");
lRec2.add("deliveryaddressee", lResultSet.getString("column21")+lResultSet.getString("column22"));
lRec2.add("homeflg", "0");
lRec2.add("custcompanyflg", "0");
lRec2.add("email", "r.order@beautygarage.jp");
lRec2.add("postcode1", lResultSet.getString("column25"));
lRec2.add("postcode2", lResultSet.getString("column26"));
lRec2.add("address1", lResultSet.getString("column27"));
lRec2.add("address2", lResultSet.getString("column28"));
lRec2.add("address3", lResultSet.getString("column29"));
lRec2.add("tel", lResultSet.getString("column30")+lResultSet.getString("column31")+lResultSet.getString("column32"));
lRec2.add("deliveryfee", "0");
lRec2.add("deliverytaxflg", "2");
lRec2.add("taxrate", taxRate);
lRec2.add("deliverytypename", "個別");
lRec2.add("shippmentmailflg", "0");
lRec2.add("custcode", "3");
lRec2.add("addresscode", "0");
lRec2.add("mallshopcode", "0");
lRec2.add("deliverytypecode", "0");
lRec2.add("orderbranchcode", "1");
lRec2.execute(lConnection);
detailCode = 0;
}
lastNumber = lResultSet.getString("column1");
detailCode++;
errors = new SICustomErrors();
if (!SICheckValid.checkExist(errors,lConnection,"在庫コード"+lResultSet.getString("column6"),"SELECT * FROM individualtbl WHERE individualcode="+SIDBUtil.SQL2Str(lResultSet.getString("column6")))) {
lResBuf.append(errors.getErrorMsg());
throw new SQLException();
}
if (!SICheckValid.checkDuplicate(errors,lConnection,lResultSet.getString("column1")+"：在庫コード"+lResultSet.getString("column6"),"SELECT * FROM orderdetailtbl WHERE individualcode="+SIDBUtil.SQL2Str(lResultSet.getString("column6"))+" AND ordercode="+orderCode)) {
lResBuf.append(errors.getErrorMsg());
throw new SQLException();
}
Statement lStatementDetail = lConnection.createStatement();
ResultSet lResultSetDetail = lStatementDetail.executeQuery(lSql.toString()+"AND a.individualcode="+SIDBUtil.SQL2Str(lResultSet.getString("column6")));
if (lResultSetDetail.next()) {
if (!SICheckValid.checkValueGreaterValid(errors,lResultSet.getString("column6")+"の楽天在庫数量", "注文数", SIUtil.add(lResultSetDetail.getString("amount"),"1"), lResultSet.getString("column7"))) {
lResBuf.append(errors.getErrorMsg());
throw new SQLException();
}
SIInsertRec lRec3 = new SIInsertRec("orderdetailtbl");
lRec3.add("ordercode", orderCode);
lRec3.add("deliverycode", "1");
lRec3.add("detailcode", detailCode);
lRec3.add("shopcode", "0");
lRec3.add("cmdtycode", lResultSetDetail.getString("cmdtycode"));
lRec3.add("individualcode", lResultSet.getString("column6"));
lRec3.add("shopname", "ビューティガレージ楽天市場支店");
lRec3.add("cmdtyname", lResultSet.getString("column5"));
lRec3.add("amount", lResultSet.getString("column7"));
lRec3.add("orgprice", lResultSet.getString("column8"));
lRec3.add("price", lResultSet.getString("column8"));
lRec3.add("taxflg", "2");
lRec3.add("taxrate", taxRate);
lRec3.add("cmdtysize", "1");
lRec3.add("deliveryfee", "0");
lRec3.add("deliverydate", "");
lRec3.add("wrappingTaxFlg", "0");
lRec3.add("purchaseprice", lResultSetDetail.getString("purchaseprice"));
lRec3.add("processingexpence", lResultSetDetail.getString("processingexpence"));
lRec3.add("consigngoodsfee", "0");
lRec3.add("orderbranchcode", "1");
lRec3.execute(lConnection);
SIInsertRec lRec4 = new SIInsertRec("orderallocationinfotbl");
lRec4.add("ordercode", orderCode);
lRec4.add("deliverycode", "1");
lRec4.add("detailcode", detailCode);
lRec4.add("shopcode", "0");
lRec4.add("orderbranchcode", "1");
lRec4.add("cmdtycode", lResultSetDetail.getString("cmdtycode"));
lRec4.add("individualcode", lResultSet.getString("column6"));
lRec4.add("branchcode", "19");
lRec4.add("orderallocationnumber", lResultSet.getString("column7"));
lRec4.add("initdatetime", lResultSet.getString("column3")+" "+lResultSet.getString("column4"));
lRec4.add("updatedatetime", lResultSet.getString("column3")+" "+lResultSet.getString("column4"));
lRec4.execute(lConnection);
SISpcType lSpc = new SISpcType("amount-" + lResultSet.getString("column7"));
SIModifyRec lRec5 = new SIModifyRec("storestocktbl");
lRec5.addCondition("cmdtycode", lResultSetDetail.getString("cmdtycode"));
lRec5.addCondition("individualcode", lResultSet.getString("column6"));
lRec5.addCondition("branchcode", "19");
lRec5.add("amount", lSpc);
lRec5.execute(lConnection);
}
}
SIDBUtil.close(lResultSet, lStatement);
SIModifyRec lRec6 = new SIModifyRec("rakutenordertbl");
lRec6.addCondition("status", "0");
lRec6.add("status", "1");
lRec6.add("ordercode", orderCode);
lRec6.execute(lConnection);
} catch (SIDuplicateKeyException e) {
errors.addError(new SICustomError("database.insert.duplicate"));
e.printStackTrace();
} catch (SIDBAccessException e) {
errors.addError(new SICustomError("database.execute.error"));
e.printStackTrace();
} catch (SQLException e) {
errors.addError(new SICustomError("database.execute.error"));
e.printStackTrace();
}
return lResBuf.toString();
}
public String writeWarehouseShipData(HttpServletRequest request, HttpServletResponse response, Connection lConnection, SICSVRead lRead) {
SICustomErrors errors = new SICustomErrors();
SILogin lLogin = SIHTMLUtil.getLogin(request);
StringBuffer lResBuf = new StringBuffer();
Statement lStatement = null;
ResultSet lResultSet = null;
String orderCode = "";
String branchCode = "";
try{
if (!lLogin.getUserCode().equals("tohai")&&!SIDBUtil.hasData(lConnection, "SELECT chargeCode FROM chargetbl WHERE chargecode="+SIDBUtil.SQL2Str(lLogin.getUserCode()))) {
lResBuf.append("ログインユーザエラー：担当者アカウントでログインして下さい");
return lResBuf.toString();
}
}catch(Exception e){
lResBuf.append(SIErrorFactory.getErrorMsg("database.execute.error"));
return lResBuf.toString();
}
//出荷完了履歴登録
while (lRead.hasNextLine()) {
String[] lItemList;
try {
lItemList = lRead.itemList();
if (lItemList == null || lItemList.length == 0) continue;
// 項目数のチェック
/* 項目数チェックはしない
if (lItemList.length != 4) {
lResBuf.append("
Line[").append(lRead.getLineNo()).append("]:エラー");
lResBuf.append(SIErrorFactory.getErrorMsg("manager.app.enough.item"));
continue;
}
*/
errors = new SICustomErrors();
// データの設定
/*
String[] temp = new String[4];
for (int i=0;i<4;i++) {
temp[i] = lItemList[i];
}
*/
try {
SIInsertRec lRec = new SIInsertRec("shippmentrecordtbl");
boolean escapeFlg = false;
if (lItemList[0].startsWith("0")) {
orderCode = lItemList[0].substring(1, lItemList[0].indexOf("-"));
escapeFlg= true;
} else {
orderCode = lItemList[0].substring(0, lItemList[0].indexOf("-"));
escapeFlg= false;
}
lRec.add("ordercode", orderCode);
if (lLogin.getUserCode().equals("tohai")) {
branchCode="15";
lRec.add("forwardingagent", "西濃運輸");
} else {
branchCode="18";
lRec.add("forwardingagent", "佐川急便");
}
lRec.add("branchcode", branchCode);
String deliverydate = lItemList[1];
if (SICheckUtil.isDigit(deliverydate)&&deliverydate.length()==8) {
deliverydate = lItemList[1].substring(0,4) + "-" + lItemList[1].substring(4, 6) + "-" + lItemList[1].substring(6, 8);
}
lRec.add("deliverydate", deliverydate);
lRec.add("slipnumber", lItemList[2]);
lRec.add("daibikiprice", lItemList[3]);
if ("-01".equals(lItemList[0].substring(lItemList[0].indexOf("-"),lItemList[0].length()))) {
if (escapeFlg) {
lRec.add("status", "3");//単納期、チェック無
} else {
lRec.add("status", "0");//単納期、チェック有
}
} else {
if (escapeFlg) {
lRec.add("status", "4");//多納期、チェック無
} else {
lRec.add("status", "2");//多納期、チェック有
}
}
lRec.execute(lConnection);
} catch (Exception e) {
errors.addError(new SICustomError("manager.message.freeword","このデータは既に登録されているか、データが壊れているため取り込みできません"));
e.printStackTrace();
}
if (SIUtil.isNotNull(errors.getErrorMsg())) {
lResBuf.append("
Line[").append(lRead.getLineNo()).append("]:エラー");
lResBuf.append(errors.getErrorMsg());
}
} catch (SIErrorException e) {
lResBuf.append("
Line[").append(lRead.getLineNo()).append("]:エラー");
lResBuf.append(SIErrorFactory.getErrorMsg("input.data.csv.format"));
}
if (errors.isEmpty()) request.removeAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY);
}
if (SIUtil.isNotNull(lResBuf.toString())) return lResBuf.toString();
try {
//重複受注チェック
log.debug("重複受注チェック開始");
StringBuffer sql = new StringBuffer("");
sql.append("SELECT s.ordercode ");
sql.append("FROM shippmentrecordtbl s ");
sql.append("WHERE s.status<>'1' ");
sql.append("GROUP BY s.ordercode ");
sql.append("HAVING count(*)>1 ");
lStatement = lConnection.createStatement();
lResultSet = lStatement.executeQuery(sql.toString());
while (lResultSet.next()) {
lResBuf.append("
ファイル内で受注番号：").append(lResultSet.getString("ordercode")).append("が重複しています");
}
SIDBUtil.close(lStatement, lResultSet);
log.debug("重複受注チェック終了");
//納期単数チェック
log.debug("納期単数チェック開始");
sql = new StringBuffer("");
sql.append("SELECT d.ordercode ");
sql.append("FROM shippmentrecordtbl s,orderdetaillatestvw d,orderallocationinfotbl a ");
sql.append("WHERE s.ordercode=d.ordercode AND d.ordercode=a.ordercode AND d.individualcode=a.individualcode ");
sql.append("AND s.status IN ('0','3') AND a.branchcode=").append(SIDBUtil.SQL2Str(branchCode," "));
sql.append("GROUP BY d.ordercode HAVING count(DISTINCT d.deliverydate)>1 ");
lStatement = lConnection.createStatement();
lResultSet = lStatement.executeQuery(sql.toString());
while (lResultSet.next()) {
lResBuf.append("
受注番号：").append(lResultSet.getString("ordercode")).append("-01には納期が複数存在するため、-02以降の枝番を指定して下さい");
}
SIDBUtil.close(lStatement, lResultSet);
log.debug("納期単数チェック終了");
//出荷実績重複取込チェックA（単納期）
log.debug("重複取込チェックA開始");
sql = new StringBuffer("");
sql.append("SELECT DISTINCT s1.ordercode ");
sql.append("FROM shippmentrecordtbl s1,shippmentrecordtbl s2 ");
sql.append("WHERE s1.ordercode=s2.ordercode AND s1.branchcode=s2.branchcode ");
sql.append("AND s1.status IN ('0','3') AND s2.status='1'");
lStatement = lConnection.createStatement();
lResultSet = lStatement.executeQuery(sql.toString());
while (lResultSet.next()) {
lResBuf.append("
受注番号：").append(lResultSet.getString("ordercode")).append("は過去に出荷実績取込済です");
}
SIDBUtil.close(lStatement, lResultSet);
log.debug("重複取込チェックA終了");
//出荷実績重複取込チェックB（多納期）
log.debug("重複取込チェックB開始");
sql = new StringBuffer("");
sql.append("SELECT DISTINCT s1.ordercode,coalesce(s1.deliverydate::text,'最短到着日') AS dd ");
sql.append("FROM shippmentrecordtbl s1,shippmentrecordtbl s2 ");
sql.append("WHERE s1.ordercode=s2.ordercode AND s1.branchcode=s2.branchcode ");
sql.append("AND CASE WHEN s1.deliverydate IS NULL AND s2.deliverydate IS NULL THEN true ");
sql.append(" ELSE s1.deliverydate=s2.deliverydate END ");
sql.append("AND s1.status IN ('2','4') AND s2.status='1'");
lStatement = lConnection.createStatement();
lResultSet = lStatement.executeQuery(sql.toString());
while (lResultSet.next()) {
lResBuf.append("
受注番号：").append(lResultSet.getString("ordercode")).append("["+lResultSet.getString("dd")+"]").append("は過去に出荷実績取込済です");
}
SIDBUtil.close(lStatement, lResultSet);
log.debug("重複取込チェックB終了");
//出荷済みチェックA（単納期）
log.debug("出荷済みチェックA開始");
sql = new StringBuffer("");
sql.append("SELECT DISTINCT s.ordercode,d.shippmentdate ");
sql.append("FROM orderdetaillatestvw d,orderallocationinfotbl a,shippmentrecordtbl s ");
sql.append("WHERE d.ordercode=a.ordercode AND d.individualcode=a.individualcode ");
sql.append("AND d.ordercode=s.ordercode AND a.branchcode=s.branchcode ");
sql.append("AND d.ordercode=a.ordercode AND d.individualcode=a.individualcode AND d.detailcode=a.detailcode AND a.branchcode=s.branchcode ");
sql.append("AND s.status IN ('0','3') AND d.shippmentdate IS NOT NULL");
lStatement = lConnection.createStatement();
lResultSet = lStatement.executeQuery(sql.toString());
while (lResultSet.next()) {
lResBuf.append("
受注番号：").append(lResultSet.getString("ordercode")).append("は出荷済です");
lResBuf.append("（").append(lResultSet.getString("shippmentdate")).append("）");
}
SIDBUtil.close(lStatement, lResultSet);
log.debug("出荷済みチェックA終了");
//出荷済みチェックB（多納期）
log.debug("出荷済みチェックB開始");
sql = new StringBuffer("");
sql.append("SELECT DISTINCT s.ordercode,coalesce(s.deliverydate::text,'最短到着日') AS dd,d.shippmentdate ");
sql.append("FROM orderdetaillatestvw d,orderallocationinfotbl a,shippmentrecordtbl s ");
sql.append("WHERE d.ordercode=a.ordercode AND d.individualcode=a.individualcode ");
sql.append("AND d.ordercode=s.ordercode AND a.branchcode=s.branchcode ");
sql.append("AND CASE WHEN s.deliverydate IS NULL AND d.deliverydate IS NULL THEN true ");
sql.append(" WHEN s.deliverydate IS NULL AND d.deliverydate = '2000-01-01' THEN true ");
sql.append(" WHEN s.deliverydate=d.deliverydate THEN true ELSE false END ");
sql.append("AND d.ordercode=a.ordercode AND d.individualcode=a.individualcode AND d.detailcode=a.detailcode AND a.branchcode=s.branchcode ");
sql.append("AND s.status IN ('2','4') AND d.shippmentdate IS NOT NULL");
lStatement = lConnection.createStatement();
lResultSet = lStatement.executeQuery(sql.toString());
while (lResultSet.next()) {
lResBuf.append("
受注番号：").append(lResultSet.getString("ordercode")).append("["+lResultSet.getString("dd")+"]").append("は出荷済です");
lResBuf.append("（").append(lResultSet.getString("shippmentdate")).append("）");
}
SIDBUtil.close(lStatement, lResultSet);
log.debug("出荷済みチェックB終了");
//キャンセル返品チェック
log.debug("キャンセル返品チェック開始");
sql = new StringBuffer("");
sql.append("SELECT DISTINCT s.ordercode ");
sql.append("FROM orderlatestvw h,shippmentrecordtbl s ");
sql.append("WHERE h.ordercode=s.ordercode AND s.status<>'1' ");
sql.append("AND h.status<>'1' ");
lStatement = lConnection.createStatement();
lResultSet = lStatement.executeQuery(sql.toString());
while (lResultSet.next()) {
lResBuf.append("
受注番号：").append(lResultSet.getString("ordercode")).append("はキャンセル・返品状態です");
}
SIDBUtil.close(lStatement, lResultSet);
log.debug("キャンセル返品チェック終了");
//不在チェックA（単納期）
log.debug("不在チェックA開始");
sql = new StringBuffer("");
sql.append("SELECT DISTINCT s.ordercode ");
sql.append("FROM shippmentrecordtbl s ");
sql.append("WHERE s.status IN ('0','3') ");
sql.append("AND NOT EXISTS (SELECT a.ordercode FROM orderallocationinfotbl a ");
sql.append("WHERE s.ordercode=a.ordercode AND s.branchcode=a.branchcode) ");
lStatement = lConnection.createStatement();
lResultSet = lStatement.executeQuery(sql.toString());
while (lResultSet.next()) {
lResBuf.append("
受注番号：").append(lResultSet.getString("ordercode")).append("の出荷データが存在しません");
}
SIDBUtil.close(lStatement, lResultSet);
log.debug("不在チェックA終了");
//不在チェックB（多納期）
log.debug("不在チェックB開始");
sql = new StringBuffer("");
sql.append("SELECT DISTINCT s.ordercode,coalesce(s.deliverydate::text,'最短到着日') AS dd ");
sql.append("FROM shippmentrecordtbl s ");
sql.append("WHERE s.status IN ('2','4') ");
sql.append("AND NOT EXISTS (SELECT d.ordercode FROM orderdetaillatestvw d,orderallocationinfotbl a ");
sql.append("WHERE d.ordercode=a.ordercode AND d.detailcode=a.detailcode AND d.individualcode=a.individualcode ");
sql.append("AND s.ordercode=a.ordercode AND s.branchcode=a.branchcode ");
sql.append("AND CASE WHEN s.deliverydate IS NULL AND d.deliverydate IS NULL THEN true ");
sql.append(" WHEN s.deliverydate IS NULL AND d.deliverydate = '2000-01-01' THEN true ");
sql.append(" WHEN s.deliverydate=d.deliverydate THEN true ELSE false END) ");
lStatement = lConnection.createStatement();
lResultSet = lStatement.executeQuery(sql.toString());
while (lResultSet.next()) {
lResBuf.append("
受注番号：").append(lResultSet.getString("ordercode")).append("["+lResultSet.getString("dd")+"]").append("の出荷データが存在しません");
}
SIDBUtil.close(lStatement, lResultSet);
log.debug("不在チェックB終了");
//依頼内容相違チェックA
log.debug("依頼内容相違チェックA開始");
sql = new StringBuffer("");
sql.append("SELECT ordercode FROM shippmentrecordtbl WHERE status IN ('0','2') AND branchcode=").append(SIDBUtil.SQL2Str(branchCode," "));
sql.append("AND ordercode NOT IN (SELECT ordercode FROM shippmentrequesttbl WHERE enabledflg='1' AND checkflg='1' AND branchcode=").append(SIDBUtil.SQL2Str(branchCode,") "));
lStatement = lConnection.createStatement();
lResultSet = lStatement.executeQuery(sql.toString());
while (lResultSet.next()) {
lResBuf.append("
受注番号：").append(lResultSet.getString("ordercode")).append("は出荷依頼データが存在しないか最新版の取込が完了していません");
}
SIDBUtil.close(lStatement, lResultSet);
log.debug("依頼内容相違チェックA終了");
//依頼内容相違チェックB
log.debug("依頼内容相違チェックB開始");
sql = new StringBuffer("");
sql.append("SELECT q.ordercode,q.individualcode ");
sql.append("FROM (SELECT * FROM shippmentrequesttbl WHERE enabledflg='1' AND checkflg='1' AND branchcode=").append(SIDBUtil.SQL2Str(branchCode," "));
sql.append(" AND ordercode IN (SELECT ordercode FROM shippmentrecordtbl WHERE status IN ('0','2'))) q ");
sql.append("LEFT OUTER JOIN (SELECT * FROM orderallocationinfotbl WHERE branchcode=").append(SIDBUtil.SQL2Str(branchCode," "));
sql.append(" AND ordercode IN (SELECT ordercode FROM shippmentrecordtbl WHERE status IN ('0','2'))) a ");
sql.append("ON q.ordercode=a.ordercode AND q.individualcode=a.individualcode ");
sql.append("WHERE a.orderallocationnumber IS NULL ");
sql.append("AND q.ordercode IN (SELECT ordercode FROM orderallocationinfotbl WHERE branchcode=").append(SIDBUtil.SQL2Str(branchCode,") "));
lStatement = lConnection.createStatement();
lResultSet = lStatement.executeQuery(sql.toString());
while (lResultSet.next()) {
lResBuf.append("
受注番号：").append(lResultSet.getString("ordercode")).append("["+lResultSet.getString("individualcode")+"]").append("は受注データに存在しません");
}
SIDBUtil.close(lStatement, lResultSet);
log.debug("依頼内容相違チェックB終了");
//依頼内容相違チェックC
log.debug("依頼内容相違チェックC開始");
sql = new StringBuffer("");
sql.append("SELECT a.ordercode,a.individualcode ");
sql.append("FROM (SELECT * FROM orderallocationinfotbl WHERE branchcode=").append(SIDBUtil.SQL2Str(branchCode," "));
sql.append(" AND ordercode IN (SELECT ordercode FROM shippmentrecordtbl WHERE status IN ('0','2'))) a ");
sql.append("LEFT OUTER JOIN (SELECT * FROM shippmentrequesttbl WHERE enabledflg='1' AND checkflg='1' ");
sql.append(" AND branchcode=").append(SIDBUtil.SQL2Str(branchCode," "));
sql.append(" AND ordercode IN (SELECT ordercode FROM shippmentrecordtbl WHERE status IN ('0','2'))) q ");
sql.append("ON q.ordercode=a.ordercode AND q.individualcode=a.individualcode ");
sql.append("WHERE q.amount IS NULL ");
sql.append("AND a.ordercode IN (SELECT ordercode FROM shippmentrequesttbl WHERE enabledflg='1' AND checkflg='1' AND branchcode=").append(SIDBUtil.SQL2Str(branchCode,") "));
lStatement = lConnection.createStatement();
lResultSet = lStatement.executeQuery(sql.toString());
while (lResultSet.next()) {
lResBuf.append("
受注番号：").append(lResultSet.getString("ordercode")).append("["+lResultSet.getString("individualcode")+"]").append("は出荷依頼データに存在しません");
}
SIDBUtil.close(lStatement, lResultSet);
log.debug("依頼内容相違チェックC終了");
//依頼内容相違チェックD
log.debug("依頼内容相違チェックD開始");
sql = new StringBuffer("");
sql.append("SELECT DISTINCT c.ordercode,q.individualcode,q.amount1,a.amount2 ");
sql.append("FROM (SELECT * FROM shippmentrecordtbl WHERE status IN ('0','2')) c ");
sql.append("INNER JOIN (SELECT ordercode,individualcode,sum(amount) AS amount1 ");
sql.append("FROM shippmentrequesttbl WHERE enabledflg='1' AND checkflg='1' AND branchcode=").append(SIDBUtil.SQL2Str(branchCode," "));
sql.append("GROUP BY ordercode,individualcode) q ");
sql.append("ON c.ordercode=q.ordercode ");
sql.append("INNER JOIN (SELECT ordercode,individualcode,sum(orderallocationnumber) AS amount2 ");
sql.append("FROM orderallocationinfotbl WHERE branchcode=").append(SIDBUtil.SQL2Str(branchCode," "));
sql.append("GROUP BY ordercode,individualcode) a ");
sql.append("ON q.ordercode=a.ordercode AND q.individualcode=a.individualcode ");
sql.append("WHERE q.amount1<>a.amount2 ");
lStatement = lConnection.createStatement();
lResultSet = lStatement.executeQuery(sql.toString());
while (lResultSet.next()) {
lResBuf.append("
受注番号：").append(lResultSet.getString("ordercode")).append("["+lResultSet.getString("individualcode")+"]").append("は出荷数量が変更されています");
lResBuf.append("（").append(lResultSet.getString("amount1")).append("→").append(lResultSet.getString("amount2")).append("）");
}
SIDBUtil.close(lStatement, lResultSet);
log.debug("依頼内容相違チェックD終了");
//エラー終了
if (SIUtil.isNotNull(lResBuf.toString())) {
String msg = "エラーが発生しました。" + lResBuf.toString();
return msg;
}
SIDateTime lDateTime = new SIDateTime();
//代引き入金設定
log.debug("代引き入金データ作成処理開始");
sql = new StringBuffer("");
sql.append("SELECT DISTINCT s.ordercode,s.daibikiprice,coalesce(p.maxpaymentnumber,0) +1 AS number ");
sql.append("FROM shippmentrecordtbl s,paymentheadertbl p ");
sql.append("WHERE s.ordercode=p.ordercode AND s.status<>'1' AND s.daibikiprice IS NOT NULL AND s.daibikiprice>0 ");
sql.append("ORDER BY s.ordercode");
lStatement = lConnection.createStatement();
lResultSet = lStatement.executeQuery(sql.toString());
String lastOrderCode = "";
while (lResultSet.next()) {
if (lResultSet.getString(1).equals(lastOrderCode)) {//重複受注番号無効のため現状ではエラー発生しない
String msg = "エラーが発生しました。
受注番号" + lResultSet.getString(1) + "の代引金額が複数設定されています。";
SIDBUtil.close(lStatement, lResultSet);
return msg;
}
SIInsertRec lRec = new SIInsertRec("paymenttbl");
lRec.add("ordercode", lResultSet.getString(1));
lRec.add("paymentprice", lResultSet.getString(2));
lRec.add("paymentnumber", lResultSet.getString(3));
lRec.add("paymentfee", "0");
lRec.add("paymenttype", "代金引換");
lRec.add("expectedpaymentdate", "0");
lRec.add("expectedpaymentdate", lDateTime.getFullDate());
lRec.add("paymentdate", lDateTime.getFullDate());
lRec.add("status", "0");
lRec.execute(lConnection);
SIModifyRec lMod = new SIModifyRec("ordertbl");
lMod.addCondition("ordercode", lResultSet.getString(1));
lMod.addCondition("enabledflg", "1");
lMod.add("receiptdate", lDateTime.getFullDate());
lMod.execute(lConnection);
lastOrderCode = lResultSet.getString(1);
}
SIDBUtil.close(lStatement, lResultSet);
log.debug("代引き入金データ作成処理終了");
//出荷日設定A（単納期）
log.debug("出荷日設定候補検出A開始");
sql = new StringBuffer("");
sql.append("SELECT DISTINCT d.ordercode,d.deliverycode,d.detailcode ");
sql.append("FROM orderdetaillatestvw d,orderallocationinfotbl a,shippmentrecordtbl s ");
sql.append("WHERE d.ordercode=a.ordercode AND d.individualcode=a.individualcode ");
sql.append("AND d.ordercode=s.ordercode AND a.branchcode=s.branchcode ");
sql.append("AND s.status IN ('0','3') AND d.shippmentdate IS NULL");
lStatement = lConnection.createStatement();
lResultSet = lStatement.executeQuery(sql.toString());
log.debug("出荷日設定候補検出A終了");
log.debug("出荷日設定A開始");
while (lResultSet.next()) {
updateShippmentDate(request, lConnection, lResultSet.getString(1), lResultSet.getString(2), lResultSet.getString(3), lDateTime.getFullDate());
}
SIDBUtil.close(lStatement, lResultSet);
log.debug("出荷日設定A終了");
//出荷日設定B（多納期）
log.debug("出荷日設定候補検出B開始");
sql = new StringBuffer("");
sql.append("SELECT DISTINCT d.ordercode,d.deliverycode,d.detailcode ");
sql.append("FROM orderdetaillatestvw d,orderallocationinfotbl a,shippmentrecordtbl s ");
sql.append("WHERE d.ordercode=a.ordercode AND d.individualcode=a.individualcode ");
sql.append("AND d.ordercode=s.ordercode AND a.branchcode=s.branchcode ");
sql.append("AND CASE WHEN s.deliverydate IS NULL AND d.deliverydate IS NULL THEN true ");
sql.append(" WHEN s.deliverydate IS NULL AND d.deliverydate = '2000-01-01' THEN true ");
sql.append(" WHEN s.deliverydate=d.deliverydate THEN true ELSE false END ");
sql.append("AND s.status IN ('2','4') AND d.shippmentdate IS NULL");
lStatement = lConnection.createStatement();
lResultSet = lStatement.executeQuery(sql.toString());
log.debug("出荷日設定候補検出B終了");
log.debug("出荷日設定B開始");
while (lResultSet.next()) {
updateShippmentDate(request, lConnection, lResultSet.getString(1), lResultSet.getString(2), lResultSet.getString(3), lDateTime.getFullDate());
}
SIDBUtil.close(lStatement, lResultSet);
log.debug("出荷日設定B終了");
SIModifyRec lRec6 = new SIModifyRec("shippmentrecordtbl");
lRec6.addCondition("status", "0");
lRec6.add("status", "1");
lRec6.execute(lConnection);
lRec6 = new SIModifyRec("shippmentrecordtbl");
lRec6.addCondition("status", "2");
lRec6.add("status", "1");
lRec6.execute(lConnection);
lRec6 = new SIModifyRec("shippmentrecordtbl");
lRec6.addCondition("status", "3");
lRec6.add("status", "1");
lRec6.execute(lConnection);
lRec6 = new SIModifyRec("shippmentrecordtbl");
lRec6.addCondition("status", "4");
lRec6.add("status", "1");
lRec6.execute(lConnection);
} catch (SIDuplicateKeyException e) {
errors.addError(new SICustomError("database.insert.duplicate"));
e.printStackTrace();
lResBuf.append(SIErrorFactory.getErrorMsg("database.insert.duplicate"));
} catch (SIDBAccessException e) {
errors.addError(new SICustomError("database.execute.error"));
e.printStackTrace();
lResBuf.append(SIErrorFactory.getErrorMsg("database.execute.error"));
} catch (SQLException e) {
errors.addError(new SICustomError("database.execute.error"));
e.printStackTrace();
lResBuf.append(SIErrorFactory.getErrorMsg("database.execute.error"));
}
return lResBuf.toString();
}
public String writeWarehouseStoreData(HttpServletRequest request, HttpServletResponse response, Connection lConnection, SICSVRead lRead) {
SICustomErrors errors = new SICustomErrors();
SILogin lLogin = SIHTMLUtil.getLogin(request);
StringBuffer lResBuf = new StringBuffer();
Statement lStatement = null;
ResultSet lResultSet = null;
try{
if (!lLogin.getUserCode().equals("tohai")&&!SIDBUtil.hasData(lConnection, "SELECT chargeCode FROM chargetbl WHERE chargecode="+SIDBUtil.SQL2Str(lLogin.getUserCode()))) {
lResBuf.append("ログインユーザエラー：担当者アカウントでログインして下さい");
return lResBuf.toString();
}
}catch(Exception e){
lResBuf.append(SIErrorFactory.getErrorMsg("database.execute.error"));
return lResBuf.toString();
}
while (lRead.hasNextLine()) {
String[] lItemList;
try {
lItemList = lRead.itemList();
if (lItemList == null || lItemList.length == 0) continue;
// 項目数のチェック
if (lItemList.length != 34) {
lResBuf.append("
Line[").append(lRead.getLineNo2()).append("]:エラー");
lResBuf.append(SIErrorFactory.getErrorMsg("manager.app.enough.item"));
continue;
}
errors = new SICustomErrors();
// データの設定
UIRegHacchu lCmdty = new UIRegHacchu();
lCmdty.setEncode(SIConfig.SIENCODE_NONE);
if (lItemList[33].indexOf("-")>0) lCmdty.setHacchuCode(lItemList[33].substring(0, lItemList[33].indexOf("-")));
else lCmdty.setHacchuCode(lItemList[33]);// 発注コード
lCmdty.setIndividualCodeTxt(lItemList[6]);// 在庫コード
lCmdty.setAmount(lItemList[10]);// 入荷数
if (lCmdty.validateWarehouseStore(request)) {
try {
SIInsertRec lRec = new SIInsertRec("WarehouseStoreTbl");
lRec.add("HacchuCode", lCmdty.getHacchuCode());// 発注コード
lRec.add("IndividualCode", lCmdty.getIndividualCodeTxt());// 在庫コード
lRec.add("Amount", lCmdty.getAmount());// 入荷数
lRec.execute(lConnection);
} catch (SIDuplicateKeyException e) {
errors.addError(new SICustomError("database.insert.duplicate"));
e.printStackTrace();
} catch (SIDBAccessException e) {
errors.addError(new SICustomError("database.execute.error"));
e.printStackTrace();
}
} else {
errors = (SICustomErrors) request.getAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY);
}
if (SIUtil.isNotNull(errors.getErrorMsg())) {
lResBuf.append("
Line[").append(lRead.getLineNo()).append("]:エラー");
lResBuf.append(errors.getErrorMsg());
}
} catch (SIErrorException e) {
lResBuf.append("
Line[").append(lRead.getLineNo()).append("]:エラー");
lResBuf.append(SIErrorFactory.getErrorMsg("input.data.csv.format"));
}
request.removeAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY);
}
// エラー無い場合は仕入日設定実行
if (errors.isEmpty()) {
StringBuffer lStr = new StringBuffer();
lStr.append("SELECT s.hacchucode,s.individualcode,s.amount AS storeamount,h.hacchuamount FROM warehousestoretbl s ");
lStr.append("LEFT OUTER JOIN (SELECT h.hacchucode,d.individualcode,d.amount+d.amountadd AS hacchuamount FROM hacchutbl h,hacchudetailtbl d WHERE h.hacchucode=d.hacchucode ");
lStr.append(" AND h.layercode=d.layercode AND h.enabledflg=1 AND h.status=1 AND d.shippmentdate IS NULL) h ON h.hacchucode=s.hacchucode AND h.individualcode=s.individualcode ");
lStr.append("WHERE 1=1 ");
try{
if (SIDBUtil.hasData(lConnection, lStr.toString()+" AND h.hacchucode IS NULL ")){
lResBuf.append("入力内容エラーによりアップロードに失敗しました。仕入日設定済、あるいは存在しない発注データが含まれています。");
} else {
lStatement = lConnection.createStatement();
lResultSet = lStatement.executeQuery(lStr.toString()+" AND h.hacchuamount<>s.amount ");
while (lResultSet.next()) {
lResBuf.append("
発注番号：").append(lResultSet.getString("hacchucode"));
lResBuf.append("、在庫コード：").append(lResultSet.getString("individualcode"));
lResBuf.append("の入荷数と発注数が一致しません。（発注").append(lResultSet.getString("hacchuamount"));
lResBuf.append("、入荷").append(lResultSet.getString("storeamount")).append("）");
}
if (SIUtil.isNull(lResBuf.toString())) {
lStatement = lConnection.createStatement();
lResultSet = lStatement.executeQuery(lStr.toString());
while (lResultSet.next()) {
this.updateShippmentDate(request,lConnection,lResultSet.getString("hacchucode"),lResultSet.getString("individualcode"));
}
SIDBUtil.execSQL(lConnection, "DELETE FROM warehousestoretbl");
}
}
}catch (Exception e) {
e.printStackTrace();
lResBuf.append("データベースエラーによりアップロードに失敗しました。");
}
}
return lResBuf.toString();
}
public static void updateShippmentDate(HttpServletRequest lRequest, Connection lConnection, String lOrderCode, String lDeliveryCode, String lDetailCode, String ShippmentDate)
throws SIDuplicateKeyException, SIDBAccessException, SQLException {
SIDateTime lDateTime = new SIDateTime();
StringBuffer lStr = new StringBuffer();
lStr.append("SELECT * FROM orderdetailtbl ");
lStr.append("WHERE OrderCode=").append(lOrderCode);
lStr.append(" AND DeliveryCode=").append(lDeliveryCode);
lStr.append(" AND DetailCode=").append(lDetailCode);
lStr.append(" FOR UPDATE");
SIDBUtil.hasData(lConnection, lStr.toString());//レコードロック
SIModifyRec lRec1 = new SIModifyRec(SIConfig.SITABLE_ORDER_DETAIL_NAME); // 7.2.0 ST1030
lRec1.addCondition("OrderCode", lOrderCode);// 受注番号
lRec1.addCondition("DeliveryCode", lDeliveryCode);// 受注配送先項番
lRec1.addCondition("DetailCode", lDetailCode);// 受注明細番号
lRec1.add("ShippmentDate", ShippmentDate);// 出荷日
lRec1.execute(lConnection);
// 出庫データの作成
Statement lStatement = null;
ResultSet lResultSet = null;
StringBuffer lSqlBuf = new StringBuffer();
lSqlBuf.append("SELECT a.cmdtycode,a.individualcode,b.purchaseprice,b.processingexpence ");
lSqlBuf.append(",c.chargecode,d.branchcode,d.orderallocationnumber ");
lSqlBuf.append("FROM individualtbl a,ordertbl c,orderdetailtbl b LEFT OUTER JOIN orderallocationinfotbl d ");
lSqlBuf.append("ON (b.ordercode=d.ordercode AND b.detailcode=d.detailcode AND b.cmdtycode=d.cmdtycode AND b.individualcode=d.individualcode) ");
lSqlBuf.append("WHERE b.ordercode=").append(SIDBUtil.SQL2Str(lOrderCode, " "));
lSqlBuf.append("AND b.deliverycode=").append(SIDBUtil.SQL2Str(lDeliveryCode, " "));
lSqlBuf.append("AND b.detailcode=").append(SIDBUtil.SQL2Str(lDetailCode, " "));
lSqlBuf.append("AND b.ordercode=c.ordercode ");
lSqlBuf.append("AND b.cmdtycode=a.cmdtycode ");
lSqlBuf.append("AND b.individualcode=a.individualcode ");
lSqlBuf.append("AND c.enabledflg='1' AND b.orderbranchcode=c.orderbranchcode ");
lStatement = lConnection.createStatement();
lResultSet = lStatement.executeQuery(lSqlBuf.toString());
while (lResultSet.next()) {
SIInsertRec lRec6 = new SIInsertRec("shiphistorytbl");
lRec6.add("cmdtycode", lResultSet.getString("cmdtycode"));
lRec6.add("individualcode", lResultSet.getString("individualcode"));
lRec6.add("shipnumber", SIDBUtil.getFirstData(lConnection,"SELECT coalesce(MAX(shipnumber),0)+1 FROM shiphistorytbl WHERE individualcode=" + SIDBUtil.SQL2Str(lResultSet.getString("individualcode"))));
lRec6.add("shipbranch", lResultSet.getString("branchcode"));
lRec6.add("shipcharge", lResultSet.getString("chargecode"));
lRec6.add("amount", lResultSet.getString("orderallocationnumber"));
lRec6.add("purchaseprice", SIUtil.multi(lResultSet.getString("purchaseprice"), lResultSet.getString("orderallocationnumber")));
lRec6.add("processingexpence", SIUtil.multi(lResultSet.getString("processingexpence"), lResultSet.getString("orderallocationnumber")));
lRec6.add("shiptype", "0");
lRec6.add("comment", "受注" + lOrderCode + "-" + lDeliveryCode + "-" + lDetailCode);
lRec6.add("ordercode", lOrderCode);
lRec6.add("delflg", "1");
lRec6.add("initdatetime", new SIDateType(ShippmentDate));
lRec6.add("calcdatetime", lDateTime.getFullDateTime());
lRec6.execute(lConnection);
lDateTime.addSecond(1);
// ポイントを使用できるかどうか
SIModifyRec lRec9 = new SIModifyRec("ordertbl");
lRec9.addCondition("OrderCode", lOrderCode);// 受注番号
lRec9.addCondition("pointEnableFlg", "2");
lRec9.add("pointEnableFlg", "1");// 出荷日
lRec9.execute(lConnection);
}
SIDBUtil.close(lResultSet, lStatement);
// ポイント使用区分の変更
SIPointMan.changeEnableFlg(lConnection, lOrderCode);
}
/*
public String writeWarehouseShipData2(HttpServletRequest request, HttpServletResponse response, Connection lConnection, SICSVRead lRead) {
SICustomErrors errors = new SICustomErrors();
SILogin lLogin = SIHTMLUtil.getLogin(request);
StringBuffer lResBuf = new StringBuffer();
try{
if (!SIDBUtil.hasData(lConnection, "SELECT chargeCode FROM chargetbl WHERE chargecode="+SIDBUtil.SQL2Str(lLogin.getUserCode()))) {
lResBuf.append("ログインユーザエラー：担当者アカウントでログインして下さい");
return lResBuf.toString();
}
}catch(Exception e){
lResBuf.append(SIErrorFactory.getErrorMsg("database.execute.error"));
return lResBuf.toString();
}
//出荷完了履歴登録
while (lRead.hasNextLine()) {
String[] lItemList;
try {
lItemList = lRead.itemList();
if (lItemList == null || lItemList.length == 0) continue;
// 項目数のチェック
/* 項目数チェックはしない
if (lItemList.length != 4) {
lResBuf.append("
Line[").append(lRead.getLineNo()).append("]:エラー");
lResBuf.append(SIErrorFactory.getErrorMsg("manager.app.enough.item"));
continue;
}
＊/
errors = new SICustomErrors();
// データの設定
/*
String[] temp = new String[4];
for (int i=0;i<4;i++) {
temp[i] = lItemList[i];
}
＊/
try {
SIInsertRec lRec = new SIInsertRec("shippmentrecordtbl");
lRec.add("ordercode", lItemList[0].substring(0, lItemList[0].indexOf("-")));
lRec.add("branchcode", "18");
lRec.add("forwardingagent", "佐川急便");
String deliverydate = lItemList[1];
if (SICheckUtil.isDigit(deliverydate)&&deliverydate.length()==8) {
deliverydate = lItemList[1].substring(0,4) + "-" + lItemList[1].substring(4, 6) + "-" + lItemList[1].substring(6, 8);
}
lRec.add("deliverydate", deliverydate);
lRec.add("slipnumber", lItemList[2]);
lRec.add("daibikiprice", lItemList[3]);
lRec.add("status", "0");
lRec.execute(lConnection);
} catch (Exception e) {
errors.addError(new SICustomError("manager.message.freeword","このデータは既に登録されているか、データが壊れているため取り込みできません"));
e.printStackTrace();
}
if (SIUtil.isNotNull(errors.getErrorMsg())) {
lResBuf.append("
Line[").append(lRead.getLineNo()).append("]:エラー");
lResBuf.append(errors.getErrorMsg());
}
} catch (SIErrorException e) {
lResBuf.append("
Line[").append(lRead.getLineNo()).append("]:エラー");
lResBuf.append(SIErrorFactory.getErrorMsg("input.data.csv.format"));
}
if (errors.isEmpty()) request.removeAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY);
}
if (SIUtil.isNotNull(lResBuf.toString())) return lResBuf.toString();
try {
Statement lStatement = lConnection.createStatement();
ResultSet lResultSet;
//重複受注チェック
StringBuffer sql = new StringBuffer("");
sql.append("SELECT s.ordercode ");
sql.append("FROM shippmentrecordtbl s ");
sql.append("WHERE s.status='0' ");
sql.append("GROUP BY s.ordercode ");
sql.append("HAVING count(*)>1 ");
lResultSet = lStatement.executeQuery(sql.toString());
while (lResultSet.next()) {
lResBuf.append("
ファイル内で受注番号：").append(lResultSet.getString("ordercode")).append("が重複しています");
}
//出荷実績重複取込チェック
sql = new StringBuffer("");
sql.append("SELECT DISTINCT s1.ordercode,coalesce(s1.deliverydate::text,'最短到着日') AS dd ");
sql.append("FROM shippmentrecordtbl s1 ");
sql.append("LEFT OUTER JOIN ");
sql.append("(SELECT d.ordercode,count(DISTINCT coalesce(d.deliverydate,'2000-01-01')) AS cnt ");
sql.append("FROM orderdetaillatestvw d,orderallocationinfotbl a ");
sql.append("WHERE d.ordercode=a.ordercode AND d.individualcode=a.individualcode AND a.branchcode=18 ");
sql.append("GROUP BY d.ordercode) d ");
sql.append("ON s1.ordercode=d.ordercode ");
sql.append(",shippmentrecordtbl s2 ");
sql.append("WHERE s1.ordercode=s2.ordercode ");
sql.append("AND CASE WHEN d.cnt=1 THEN true ");
sql.append("WHEN d.cnt IS NULL THEN true ");
sql.append("ELSE (CASE WHEN s1.deliverydate IS NULL AND s2.deliverydate IS NULL THEN true ELSE s1.deliverydate=s2.deliverydate END) END ");
sql.append("AND s1.status='0' AND s2.status='1'");
lResultSet = lStatement.executeQuery(sql.toString());
while (lResultSet.next()) {
lResBuf.append("
受注番号：").append(lResultSet.getString("ordercode")).append("["+lResultSet.getString("dd")+"]").append("は過去に出荷実績取込済です");
}
//出荷済みチェック（１納期）
sql = new StringBuffer("");
sql.append("SELECT DISTINCT s.ordercode,d.shippmentdate ");
sql.append("FROM orderdetaillatestvw d,orderallocationinfotbl a,shippmentrecordtbl s,orderlatestvw h ");
sql.append("WHERE d.ordercode=a.ordercode AND d.individualcode=a.individualcode AND d.ordercode=h.ordercode ");
sql.append("AND d.ordercode=s.ordercode AND a.branchcode=s.branchcode AND h.status='1' ");
sql.append("AND d.ordercode IN (SELECT d.ordercode FROM orderdetaillatestvw d,orderallocationinfotbl a ");
sql.append(" WHERE d.ordercode=a.ordercode AND d.individualcode=a.individualcode AND a.branchcode=18 ");
sql.append(" GROUP BY d.ordercode HAVING count(DISTINCT coalesce(d.deliverydate,'2000-01-01'))=1) ");
sql.append("AND NOT EXISTS (SELECT aa.* FROM orderallocationinfotbl aa WHERE aa.ordercode=d.ordercode AND aa.individualcode=d.individualcode AND aa.branchcode<>s.branchcode) ");
sql.append("AND s.status='0' AND d.shippmentdate IS NOT NULL");
lResultSet = lStatement.executeQuery(sql.toString());
while (lResultSet.next()) {
lResBuf.append("
受注番号：").append(lResultSet.getString("ordercode")).append("は出荷済です");
lResBuf.append("（").append(lResultSet.getString("shippmentdate")).append("）");
}
//出荷済みチェック（２納期以上）
sql = new StringBuffer("");
sql.append("SELECT DISTINCT s.ordercode,coalesce(s.deliverydate::text,'最短到着日') AS dd,d.shippmentdate ");
sql.append("FROM orderdetaillatestvw d,orderallocationinfotbl a,shippmentrecordtbl s,orderlatestvw h ");
sql.append("WHERE d.ordercode=a.ordercode AND d.individualcode=a.individualcode AND d.ordercode=h.ordercode ");
sql.append("AND d.ordercode=s.ordercode AND a.branchcode=s.branchcode AND h.status='1' ");
sql.append("AND CASE WHEN s.deliverydate IS NULL AND d.deliverydate IS NULL THEN true ");
sql.append(" WHEN s.deliverydate IS NULL AND d.deliverydate = '2000-01-01' THEN true ");
sql.append(" WHEN s.deliverydate=d.deliverydate THEN true ELSE false END ");
sql.append("AND d.ordercode IN (SELECT d.ordercode FROM orderdetaillatestvw d,orderallocationinfotbl a ");
sql.append(" WHERE d.ordercode=a.ordercode AND d.individualcode=a.individualcode AND a.branchcode=18 ");
sql.append(" GROUP BY d.ordercode HAVING count(DISTINCT coalesce(d.deliverydate,'2000-01-01'))>1) ");
sql.append("AND NOT EXISTS (SELECT aa.* FROM orderallocationinfotbl aa WHERE aa.ordercode=d.ordercode AND aa.individualcode=d.individualcode AND aa.branchcode<>s.branchcode) ");
sql.append("AND s.status='0' AND d.shippmentdate IS NOT NULL");
lResultSet = lStatement.executeQuery(sql.toString());
while (lResultSet.next()) {
lResBuf.append("
受注番号：").append(lResultSet.getString("ordercode")).append("["+lResultSet.getString("dd")+"]").append("は出荷済です");
lResBuf.append("（").append(lResultSet.getString("shippmentdate")).append("）");
}
//キャンセル返品チェック
sql = new StringBuffer("");
sql.append("SELECT DISTINCT s.ordercode ");
sql.append("FROM orderlatestvw h,shippmentrecordtbl s ");
sql.append("WHERE h.ordercode=s.ordercode AND s.status='0' ");
sql.append("AND h.status!='1' ");
lResultSet = lStatement.executeQuery(sql.toString());
while (lResultSet.next()) {
lResBuf.append("
受注番号：").append(lResultSet.getString("ordercode")).append("はキャンセル・返品状態です");
}
//不在チェック（１納期）
sql = new StringBuffer("");
sql.append("SELECT DISTINCT s.ordercode,coalesce(s.deliverydate::text,'最短到着日') AS dd ");
sql.append("FROM shippmentrecordtbl s ");
sql.append("WHERE s.status='0' ");
sql.append("AND s.ordercode IN (SELECT d.ordercode FROM orderdetaillatestvw d,orderallocationinfotbl a ");
sql.append(" WHERE d.ordercode=a.ordercode AND d.individualcode=a.individualcode AND a.branchcode=18 ");
sql.append(" GROUP BY d.ordercode HAVING count(DISTINCT coalesce(d.deliverydate,'2000-01-01'))=1) ");
sql.append("AND NOT EXISTS (SELECT a.* FROM orderallocationinfotbl a ");
sql.append("WHERE s.ordercode=a.ordercode AND s.branchcode=a.branchcode) ");
lResultSet = lStatement.executeQuery(sql.toString());
while (lResultSet.next()) {
lResBuf.append("
受注番号：").append(lResultSet.getString("ordercode")).append("の出荷データが存在しません");
}
//不在チェック（２納期以上）
sql = new StringBuffer("");
sql.append("SELECT DISTINCT s.ordercode,coalesce(s.deliverydate::text,'最短到着日') AS dd ");
sql.append("FROM shippmentrecordtbl s ");
sql.append("WHERE s.status='0' ");
sql.append("AND s.ordercode IN (SELECT d.ordercode FROM orderdetaillatestvw d,orderallocationinfotbl a ");
sql.append(" WHERE d.ordercode=a.ordercode AND d.individualcode=a.individualcode AND a.branchcode=18 ");
sql.append(" GROUP BY d.ordercode HAVING count(DISTINCT coalesce(d.deliverydate,'2000-01-01'))>1) ");
sql.append("AND NOT EXISTS (SELECT d.* FROM orderdetaillatestvw d,orderallocationinfotbl a ");
sql.append("WHERE s.ordercode=d.ordercode AND d.individualcode=a.individualcode AND s.branchcode=a.branchcode ");
sql.append("AND CASE WHEN s.deliverydate IS NULL AND d.deliverydate IS NULL THEN true ");
sql.append(" WHEN s.deliverydate IS NULL AND d.deliverydate = '2000-01-01' THEN true ");
sql.append(" WHEN s.deliverydate=d.deliverydate THEN true ELSE false END) ");
lResultSet = lStatement.executeQuery(sql.toString());
while (lResultSet.next()) {
lResBuf.append("
受注番号：").append(lResultSet.getString("ordercode")).append("["+lResultSet.getString("dd")+"]").append("の出荷データが存在しません");
}
//エラー終了
if (SIUtil.isNotNull(lResBuf.toString())) {
String msg = "エラーが発生しました。" + lResBuf.toString();
return msg;
}
SIDateTime lDateTime = new SIDateTime();
//代引き入金設定
sql = new StringBuffer("");
sql.append("SELECT DISTINCT s.ordercode,s.daibikiprice,coalesce(p.number,0) +1 AS number ");
sql.append("FROM shippmentrecordtbl s ");
sql.append("LEFT OUTER JOIN (SELECT ordercode,max(paymentnumber) AS number FROM paymenttbl GROUP BY ordercode) p ");
sql.append("ON s.ordercode=p.ordercode ");
sql.append("WHERE s.status='0' AND s.daibikiprice IS NOT NULL AND s.daibikiprice>0 ");
sql.append("ORDER BY s.ordercode");
lResultSet = lStatement.executeQuery(sql.toString());
String lastOrderCode = "";
while (lResultSet.next()) {
if (lResultSet.getString(1).equals(lastOrderCode)) {
String msg = "エラーが発生しました。
受注番号" + lResultSet.getString(1) + "の代引金額が複数設定されています。";
return msg;
}
SIInsertRec lRec = new SIInsertRec("paymenttbl");
lRec.add("ordercode", lResultSet.getString(1));
lRec.add("paymentprice", lResultSet.getString(2));
lRec.add("paymentnumber", lResultSet.getString(3));
lRec.add("paymentfee", "0");
lRec.add("paymenttype", "代金引換");
lRec.add("expectedpaymentdate", "0");
lRec.add("expectedpaymentdate", lDateTime.getFullDate());
lRec.add("paymentdate", lDateTime.getFullDate());
lRec.add("status", "0");
lRec.execute(lConnection);
SIModifyRec lMod = new SIModifyRec("ordertbl");
lMod.addCondition("ordercode", lResultSet.getString(1));
lMod.addCondition("enabledflg", "1");
lMod.add("receiptdate", lDateTime.getFullDate());
lMod.execute(lConnection);
lastOrderCode = lResultSet.getString(1);
}
//出荷日設定（１納期）
sql = new StringBuffer("");
sql.append("SELECT DISTINCT d.ordercode,d.deliverycode,d.detailcode ");
sql.append("FROM orderdetaillatestvw d,orderallocationinfotbl a,shippmentrecordtbl s ");
sql.append("WHERE d.ordercode=a.ordercode AND d.individualcode=a.individualcode ");
sql.append("AND d.ordercode=s.ordercode AND a.branchcode=s.branchcode ");
sql.append("AND s.ordercode IN (SELECT d.ordercode FROM orderdetaillatestvw d,orderallocationinfotbl a ");
sql.append(" WHERE d.ordercode=a.ordercode AND d.individualcode=a.individualcode AND a.branchcode=18 ");
sql.append(" GROUP BY d.ordercode HAVING count(DISTINCT coalesce(d.deliverydate,'2000-01-01'))=1) ");
sql.append("AND s.status='0' AND d.shippmentdate IS NULL");
lResultSet = lStatement.executeQuery(sql.toString());
while (lResultSet.next()) {
SIRegShippmentSrv.updateShippmentDate(request, lConnection, lResultSet.getString(1), lResultSet.getString(2), lResultSet.getString(3), lDateTime.getFullDate());
}
//出荷日設定（２納期以上）
sql = new StringBuffer("");
sql.append("SELECT DISTINCT d.ordercode,d.deliverycode,d.detailcode ");
sql.append("FROM orderdetaillatestvw d,orderallocationinfotbl a,shippmentrecordtbl s ");
sql.append("WHERE d.ordercode=a.ordercode AND d.individualcode=a.individualcode ");
sql.append("AND d.ordercode=s.ordercode AND a.branchcode=s.branchcode ");
sql.append("AND s.ordercode IN (SELECT d.ordercode FROM orderdetaillatestvw d,orderallocationinfotbl a ");
sql.append(" WHERE d.ordercode=a.ordercode AND d.individualcode=a.individualcode AND a.branchcode=18 ");
sql.append(" GROUP BY d.ordercode HAVING count(DISTINCT coalesce(d.deliverydate,'2000-01-01'))>1) ");
sql.append("AND CASE WHEN s.deliverydate IS NULL AND d.deliverydate IS NULL THEN true ");
sql.append(" WHEN s.deliverydate IS NULL AND d.deliverydate = '2000-01-01' THEN true ");
sql.append(" WHEN s.deliverydate=d.deliverydate THEN true ELSE false END ");
sql.append("AND s.status='0' AND d.shippmentdate IS NULL");
lResultSet = lStatement.executeQuery(sql.toString());
while (lResultSet.next()) {
SIRegShippmentSrv.updateShippmentDate(request, lConnection, lResultSet.getString(1), lResultSet.getString(2), lResultSet.getString(3), lDateTime.getFullDate());
}
SIModifyRec lRec6 = new SIModifyRec("shippmentrecordtbl");
lRec6.addCondition("status", "0");
lRec6.add("status", "1");
lRec6.execute(lConnection);
} catch (SIDuplicateKeyException e) {
errors.addError(new SICustomError("database.insert.duplicate"));
e.printStackTrace();
} catch (SIDBAccessException e) {
errors.addError(new SICustomError("database.execute.error"));
e.printStackTrace();
} catch (SQLException e) {
errors.addError(new SICustomError("database.execute.error"));
e.printStackTrace();
}
return lResBuf.toString();
}
*/
private void updateShippmentDate(HttpServletRequest lRequest,Connection lConnection,String hacchuCode,String individualCode) throws SIDBAccessException,SIDuplicateKeyException{
SIDateTime lDate = new SIDateTime();
StringBuffer lStr = new StringBuffer();
String hacchuIndividualCode = SIDBUtil.getFirstData(lConnection, "SELECT d.hacchuindividualcode FROM hacchudetailtbl d,hacchutbl h WHERE d.shippmentdate IS NULL AND h.enabledflg=1 AND h.hacchucode=d.hacchucode AND h.layercode=d.layercode AND h.hacchucode="+hacchuCode+" AND d.individualcode="+SIDBUtil.SQL2Str(individualCode));
if (SIUtil.isNull(hacchuIndividualCode)) throw new SIDBAccessException();
String hacchuBranchCode = SIDBUtil.getFirstData(lConnection, "SELECT d.hacchubranchcode FROM hacchudetailtbl d,hacchutbl h WHERE d.shippmentdate IS NULL AND h.enabledflg=1 AND h.hacchucode=d.hacchucode AND h.layercode=d.layercode AND h.hacchucode="+hacchuCode+" AND d.individualcode="+SIDBUtil.SQL2Str(individualCode));
if (SIUtil.isNull(hacchuBranchCode)) throw new SIDBAccessException();
lStr.append("SELECT * FROM hacchudetailtbl ");
lStr.append("WHERE hacchuCode=").append(hacchuCode);
lStr.append(" AND hacchuindividualCode=").append(hacchuIndividualCode);
lStr.append(" AND hacchubranchCode=").append(hacchuBranchCode);
lStr.append(" FOR UPDATE");
SIDBUtil.hasData(lConnection, lStr.toString());//レコードロック
SIHacchuDetail lDetail = new SIHacchuDetail(hacchuCode,hacchuIndividualCode,hacchuBranchCode);
lDetail.reset(lConnection);
UIRegHacchu lHeader = new UIRegHacchu();
lHeader.setHacchuCode(hacchuCode);
lHeader.reset(lConnection);
StringBuffer sql1 = new StringBuffer();
sql1.append("SELECT hacchucode FROM hacchutbl ");
sql1.append("WHERE status=2");
sql1.append(" AND ordercode=").append(SIDBUtil.SQL2Str(lHeader.getOrderCode()));
sql1.append(" AND stockcode=").append(SIDBUtil.SQL2Str(lHeader.getStockCode()));
if (SIUtil.isNotNull(lHeader.getOrderCode()) && SIDBUtil.hasData(lConnection,sql1.toString())) lHeader.setOrderCode("");
SIModifyRec lRec1 = new SIModifyRec("hacchudetailtbl");
lRec1.addCondition("hacchuCode",hacchuCode);
lRec1.addCondition("individualCode",individualCode);
lRec1.addCondition("hacchuBranchCode",hacchuBranchCode);
lRec1.addCondition("layerCode",lHeader.getLayerCode());
lRec1.add("shippmentDate", lDate.getFullDate());
lRec1.execute(lConnection);
UIStockHistory lStoreData = new UIStockHistory();
lStoreData.setCmdtyCode(SIDBUtil.getFirstData(lConnection, "SELECT cmdtycode FROM individualtbl WHERE individualcode="+SIDBUtil.SQL2Str(lDetail.getIndividualCode())));
lStoreData.setIndividualCode(lDetail.getIndividualCode());
lStoreData.setNewCharge(lHeader.getChargeCode());
lStoreData.setNewAmount(SIUtil.add(lDetail.getAmount(), lDetail.getAmountAddZero()));
if (SIUtil.isNotNull(lHeader.getOrderCode())) {
lStoreData.setComment("発注"+hacchuCode+"-"+lDetail.getHacchuIndividualCode()+"-"+lDetail.getHacchuBranchCode()+"(受注"+lHeader.getOrderCode()+")");
} else {
lStoreData.setComment("発注"+hacchuCode+"-"+lDetail.getHacchuIndividualCode()+"-"+lDetail.getHacchuBranchCode());
}
if ("1".equals(lHeader.getCmdtyType())) {
lStoreData.setNewBranch(lDetail.getBranchCode());
lStoreData.setPurchasePrice(SIUtil.multi(lHeader.getUnitPurchase(lDetail.getIndividualCode(),lDetail.getHacchuIndividualCode()),SIUtil.add(lDetail.getAmount(), lDetail.getAmountAddZero())));
lStoreData.setProcessingExpence(SIUtil.multi(lHeader.getUnitProcessing(lDetail.getIndividualCode(),lDetail.getHacchuIndividualCode()),SIUtil.add(lDetail.getAmount(), lDetail.getAmountAddZero())));
} else {
lStoreData.setNewBranch(lHeader.getBranchCode());
lStoreData.setPurchasePrice(lHeader.getTotalPurchase(lDetail.getIndividualCode(),lDetail.getHacchuIndividualCode()));
lStoreData.setProcessingExpence(lDetail.getProcessingExpence());
}
String storeTaxRate = SIDBUtil.getFirstData(lConnection, "SELECT tax("+SIDBUtil.SQL2Str(lDate.getFullDate(),")"));
if (!lHeader.getTaxRate().equals(storeTaxRate)){
try {
String pp = SIUtil.div_LD(lStoreData.getPurchasePrice(), SIUtil.add_DL(SIUtil.div_DL(lHeader.getTaxRate(), "100"), "1"));
lStoreData.setPurchasePrice(String.valueOf(Math.ceil(Math.floor(Double.parseDouble(pp) * Double.parseDouble(SIUtil.add_DL(SIUtil.div_DL(storeTaxRate, "100"), "1")) * 10000) / 10000)));
String pe = SIUtil.div_LD(lStoreData.getProcessingExpence(), SIUtil.add_DL(SIUtil.div_DL(lHeader.getTaxRate(), "100"), "1"));
lStoreData.setProcessingExpence(String.valueOf(Math.ceil(Math.floor(Double.parseDouble(pe) * Double.parseDouble(SIUtil.add_DL(SIUtil.div_DL(storeTaxRate, "100"), "1")) * 10000) / 10000)));
}catch(SIFatalException e){
e.printStackTrace();
}
}
String storeNumber = SIRegStockControlSrv.createNewStoreHacchu(lConnection, lStoreData, lHeader.getOrderCode() ,lDate.getFullDate() ,"1".equals(lHeader.getCmdtyType()));
SIModifyRec lRec3 = new SIModifyRec("hacchudetailtbl");
lRec3.addCondition("hacchuCode",hacchuCode);
lRec3.addCondition("hacchuindividualCode",hacchuIndividualCode);
lRec3.addCondition("hacchuBranchCode",hacchuBranchCode);
lRec3.addCondition("layerCode",lHeader.getLayerCode());
lRec3.add("storeNumber", storeNumber);
lRec3.execute(lConnection);
if (!"1".equals(lHeader.getCmdtyType()) && SIUtil.isNull(lHeader.getOrderCode())) {
} else if (!"1".equals(lHeader.getCmdtyType())) {
try {
StringBuffer lSql = new StringBuffer();
lSql.append("SELECT b.ordercode,b.deliverycode,b.detailcode ");
lSql.append("FROM orderlatestvw a,orderdetaillatestvw b ");
lSql.append("WHERE a.ordercode=b.ordercode ");
lSql.append("AND a.status='1' ");
lSql.append("AND a.ordercode=").append(SIDBUtil.SQL2Str(lHeader.getOrderCode()," "));
lSql.append("AND b.individualcode=").append(SIDBUtil.SQL2Str(lDetail.getIndividualCode()));
lSql.append("AND b.amount=").append(SIDBUtil.SQL2Str(SIUtil.add(lDetail.getAmount(), lDetail.getAmountAddZero())));
lSql.append("AND b.shippmentdate IS NULL ");
Statement lStatement = lConnection.createStatement();
ResultSet lResultSet = lStatement.executeQuery(lSql.toString());
if (lResultSet.next()&&SIUtil.isNotNull(lResultSet.getString("ordercode"))) {
SIRegShippmentSrv.updateShippmentDate(lRequest,lConnection,lResultSet.getString("ordercode"),lResultSet.getString("deliverycode"),lResultSet.getString("detailcode"),lDate.getFullDate());
}
SIDBUtil.close(lStatement, lResultSet);
} catch (SQLException e) {
throw new SIDBAccessException();
}
}
SIModifyRec lRec = new SIModifyRec("IndividualTbl");
lRec.addCondition("IndividualCode", lDetail.getIndividualCode());
lRec.addCondition("AmountFlg", "1");
lRec.add("ArrivalScheduleMsg", "入荷日未定");
lRec.execute(lConnection);
}
public void producePriceCardPDFFile(HttpServletResponse response, Connection conn, UIIOCsv lCsv) throws IOException, DocumentException, SIDBAccessException {
File lOrderTempDir = (File) this.getServletContext().getAttribute("javax.servlet.context.tempdir");
File lOrderTempFile = new File("");
//テンプレート
String template_used = (String) this.getServletContext().getRealPath("/docs/pricecardUsed.pdf");
String template_bgused = (String) this.getServletContext().getRealPath("/docs/pricecardBGUsed.pdf");
String template_new = (String) this.getServletContext().getRealPath("/docs/pricecardNew.pdf");
String template_outlet = (String) this.getServletContext().getRealPath("/docs/pricecardOutlet.pdf");
String template = template_used;
//座標設定
int[] individualVerticalAxis = {765,765,575,575,385,385,195,195};
int[] individualHorizontalAxis = {45,320,45,320,45,320,45,320};
int[] makerVerticalAxis = {780,780,590,590,400,400,210,210};
int[] makerHorizontalAxis = {220,495,220,495,220,495,220,495};
int[] cmdtynameVerticalAxis = {742,742,552,552,362,362,172,172};
int[] cmdtynameHorizontalAxis = {45,320,45,320,45,320,45,320};
int[] newfixedpriceVerticalAxis = {717,717,527,527,337,337,147,147};
int[] newfixedpriceHorizontalAxis = {62,337,62,337,62,337,62,337};
int[] bgpriceVerticalAxis = {677,677,487,487,297,297,107,107};
int[] bgpriceHorizontalAxis = {84,359,84,359,84,359,84,359};
int[] deliveryfeeVerticalAxis = {659,659,469,469,279,279,89,89};
int[] deliveryfeeHorizontalAxis = {210,485,210,485,210,485,210,485};
int[] guaranteedtermVerticalAxis = {640,640,450,450,260,260,70,70};
int[] guaranteedtermHorizontalAxis = {255,530,255,530,255,530,255,530};
try {
lOrderTempFile = File.createTempFile("pricecard_" , ".pdf", lOrderTempDir);
} catch (IOException e1) {
e1.printStackTrace();
}
String lFileName = lOrderTempFile.getAbsolutePath();
SIBGPdfTool priceCardPdf = new SIBGPdfTool();
priceCardPdf.initialize(template, lOrderTempFile.getPath());
priceCardPdf.fontSelect("Goth");
//各項80件（10ページ）に制限する
String sql1 = lCsv.getCmdtySql("used")+" LIMIT 80";
String sql2 = lCsv.getCmdtySql("bgused")+" LIMIT 80";
String sql3 = lCsv.getCmdtySql("new")+" LIMIT 80";
String sql4 = lCsv.getCmdtySql("outlet")+" LIMIT 80";
Statement lStatement = null;
ResultSet lResultSet = null;
try {
int index = 0;
boolean firstFlg = true;
lStatement = conn.createStatement();
lResultSet = lStatement.executeQuery(sql1);
while(lResultSet.next()) {
if (index==8) {
priceCardPdf.addPage(template_used);
index=0;
}
priceCardPdf.setFontSize(9);//在庫コード、ブランド、保証
priceCardPdf.writeRtoL(lResultSet.getString("makerName"), makerHorizontalAxis[index], makerVerticalAxis[index]);
priceCardPdf.write(lResultSet.getString("individualCode"), individualHorizontalAxis[index], individualVerticalAxis[index]);
if (SIUtil.isNotNull(lResultSet.getString("guaranteedterm"))) {
priceCardPdf.writeRtoL(lResultSet.getString("guaranteedterm")+"保証", guaranteedtermHorizontalAxis[index], guaranteedtermVerticalAxis[index]);
}
priceCardPdf.setFontSize(10);//M価、送料
if (SIUtil.isNotNull(lResultSet.getString("newfixedprice"))&&!"0".equals(lResultSet.getString("newfixedprice"))) {
priceCardPdf.write("￥"+SIStringUtil.numberFormat(lResultSet.getString("newfixedprice")), newfixedpriceHorizontalAxis[index], newfixedpriceVerticalAxis[index]);
}
priceCardPdf.write("送料:"+lResultSet.getString("cmdtysize"), deliveryfeeHorizontalAxis[index], deliveryfeeVerticalAxis[index]);
if (lResultSet.getString("cmdtyname").getBytes().length > 40) {
priceCardPdf.setFontSize(8);//商品名
} else if (lResultSet.getString("cmdtyname").getBytes().length > 30) {
priceCardPdf.setFontSize(12);//商品名
} else {
priceCardPdf.setFontSize(15);//商品名
}
priceCardPdf.write(lResultSet.getString("cmdtyname"), cmdtynameHorizontalAxis[index], cmdtynameVerticalAxis[index]);
priceCardPdf.setFontSize(20);//BG卸価（￥）
priceCardPdf.write("￥", bgpriceHorizontalAxis[index]-17, bgpriceVerticalAxis[index]);
priceCardPdf.setFontSize(40);//BG卸価
priceCardPdf.write(SIStringUtil.numberFormat(lResultSet.getString("bgprice")), bgpriceHorizontalAxis[index], bgpriceVerticalAxis[index]);
int pointlength = ((lResultSet.getString("bgprice").length() - 1) / 3) * 8;
priceCardPdf.setFontSize(15);//BG卸価（税込）
priceCardPdf.write("(税込)", bgpriceHorizontalAxis[index]+(lResultSet.getString("bgprice")).length()*20+15+pointlength, bgpriceVerticalAxis[index]);
index++;
firstFlg=false;
}
SIDBUtil.close(lResultSet, lStatement);
lStatement = conn.createStatement();
lResultSet = lStatement.executeQuery(sql2);
if (!firstFlg) index = 8;
while(lResultSet.next()) {
if (index==8) {
priceCardPdf.addPage(template_bgused);
index=0;
}
priceCardPdf.setFontSize(9);//在庫コード、ブランド、保証
priceCardPdf.writeRtoL(lResultSet.getString("makerName"), makerHorizontalAxis[index], makerVerticalAxis[index]);
priceCardPdf.write(lResultSet.getString("individualCode"), individualHorizontalAxis[index], individualVerticalAxis[index]);
if (SIUtil.isNotNull(lResultSet.getString("guaranteedterm"))) {
priceCardPdf.writeRtoL(lResultSet.getString("guaranteedterm")+"保証", guaranteedtermHorizontalAxis[index], guaranteedtermVerticalAxis[index]);
}
priceCardPdf.setFontSize(10);//M価、送料
if (SIUtil.isNotNull(lResultSet.getString("newfixedprice"))&&!"0".equals(lResultSet.getString("newfixedprice"))) {
priceCardPdf.write("￥"+SIStringUtil.numberFormat(lResultSet.getString("newfixedprice")), newfixedpriceHorizontalAxis[index], newfixedpriceVerticalAxis[index]);
}
priceCardPdf.write("送料:"+lResultSet.getString("cmdtysize"), deliveryfeeHorizontalAxis[index], deliveryfeeVerticalAxis[index]);
if (lResultSet.getString("cmdtyname").getBytes().length > 40) {
priceCardPdf.setFontSize(8);//商品名
} else if (lResultSet.getString("cmdtyname").getBytes().length > 30) {
priceCardPdf.setFontSize(12);//商品名
} else {
priceCardPdf.setFontSize(15);//商品名
}
priceCardPdf.write(lResultSet.getString("cmdtyname"), cmdtynameHorizontalAxis[index], cmdtynameVerticalAxis[index]);
priceCardPdf.setFontSize(20);//BG卸価（￥）
priceCardPdf.write("￥", bgpriceHorizontalAxis[index]-17, bgpriceVerticalAxis[index]);
priceCardPdf.setFontSize(40);//BG卸価
priceCardPdf.write(SIStringUtil.numberFormat(lResultSet.getString("bgprice")), bgpriceHorizontalAxis[index], bgpriceVerticalAxis[index]);
int pointlength = ((lResultSet.getString("bgprice").length() - 1) / 3) * 8;
priceCardPdf.setFontSize(15);//BG卸価（税込）
priceCardPdf.write("(税込)", bgpriceHorizontalAxis[index]+(lResultSet.getString("bgprice")).length()*20+15+pointlength, bgpriceVerticalAxis[index]);
index++;
firstFlg=false;
}
SIDBUtil.close(lResultSet, lStatement);
lStatement = conn.createStatement();
lResultSet = lStatement.executeQuery(sql3);
if (!firstFlg) index = 8;
while(lResultSet.next()) {
if (index==8) {
priceCardPdf.addPage(template_new);
index=0;
}
priceCardPdf.setFontSize(9);//在庫コード、ブランド、保証
priceCardPdf.writeRtoL(lResultSet.getString("makerName"), makerHorizontalAxis[index], makerVerticalAxis[index]);
priceCardPdf.write(lResultSet.getString("individualCode"), individualHorizontalAxis[index], individualVerticalAxis[index]);
if (SIUtil.isNotNull(lResultSet.getString("guaranteedterm"))) {
priceCardPdf.writeRtoL(lResultSet.getString("guaranteedterm")+"保証", guaranteedtermHorizontalAxis[index], guaranteedtermVerticalAxis[index]);
}
priceCardPdf.setFontSize(10);//M価、送料
if (SIUtil.isNotNull(lResultSet.getString("newfixedprice"))&&!"0".equals(lResultSet.getString("newfixedprice"))) {
priceCardPdf.write("￥"+SIStringUtil.numberFormat(lResultSet.getString("newfixedprice")), newfixedpriceHorizontalAxis[index], newfixedpriceVerticalAxis[index]);
}
priceCardPdf.write("送料:"+lResultSet.getString("cmdtysize"), deliveryfeeHorizontalAxis[index], deliveryfeeVerticalAxis[index]);
if (lResultSet.getString("cmdtyname").getBytes().length > 40) {
priceCardPdf.setFontSize(8);//商品名
} else if (lResultSet.getString("cmdtyname").getBytes().length > 30) {
priceCardPdf.setFontSize(12);//商品名
} else {
priceCardPdf.setFontSize(15);//商品名
}
priceCardPdf.write(lResultSet.getString("cmdtyname"), cmdtynameHorizontalAxis[index], cmdtynameVerticalAxis[index]);
priceCardPdf.setFontSize(20);//BG卸価（￥）
priceCardPdf.write("￥", bgpriceHorizontalAxis[index]-17, bgpriceVerticalAxis[index]);
priceCardPdf.setFontSize(40);//BG卸価
priceCardPdf.write(SIStringUtil.numberFormat(lResultSet.getString("bgprice")), bgpriceHorizontalAxis[index], bgpriceVerticalAxis[index]);
int pointlength = ((lResultSet.getString("bgprice").length() - 1) / 3) * 8;
priceCardPdf.setFontSize(15);//BG卸価（税込）
priceCardPdf.write("(税込)", bgpriceHorizontalAxis[index]+(lResultSet.getString("bgprice")).length()*20+15+pointlength, bgpriceVerticalAxis[index]);
index++;
firstFlg=false;
}
SIDBUtil.close(lResultSet, lStatement);
lStatement = conn.createStatement();
lResultSet = lStatement.executeQuery(sql4);
if (!firstFlg) index = 8;
while(lResultSet.next()) {
if (index==8) {
priceCardPdf.addPage(template_outlet);
index=0;
}
priceCardPdf.setFontSize(9);//在庫コード、ブランド、保証
priceCardPdf.writeRtoL(lResultSet.getString("makerName"), makerHorizontalAxis[index], makerVerticalAxis[index]);
priceCardPdf.write(lResultSet.getString("individualCode"), individualHorizontalAxis[index], individualVerticalAxis[index]);
if (SIUtil.isNotNull(lResultSet.getString("guaranteedterm"))) {
priceCardPdf.writeRtoL(lResultSet.getString("guaranteedterm")+"保証", guaranteedtermHorizontalAxis[index], guaranteedtermVerticalAxis[index]);
}
priceCardPdf.setFontSize(10);//M価、送料
if (SIUtil.isNotNull(lResultSet.getString("newfixedprice"))&&!"0".equals(lResultSet.getString("newfixedprice"))) {
priceCardPdf.write("￥"+SIStringUtil.numberFormat(lResultSet.getString("newfixedprice")), newfixedpriceHorizontalAxis[index], newfixedpriceVerticalAxis[index]);
}
priceCardPdf.write("送料:"+lResultSet.getString("cmdtysize"), deliveryfeeHorizontalAxis[index], deliveryfeeVerticalAxis[index]);
if (lResultSet.getString("cmdtyname").getBytes().length > 40) {
priceCardPdf.setFontSize(8);//商品名
} else if (lResultSet.getString("cmdtyname").getBytes().length > 30) {
priceCardPdf.setFontSize(12);//商品名
} else {
priceCardPdf.setFontSize(15);//商品名
}
priceCardPdf.write(lResultSet.getString("cmdtyname"), cmdtynameHorizontalAxis[index], cmdtynameVerticalAxis[index]);
priceCardPdf.setFontSize(20);//BG卸価（￥）
priceCardPdf.write("￥", bgpriceHorizontalAxis[index]-17, bgpriceVerticalAxis[index]);
priceCardPdf.setFontSize(40);//BG卸価
priceCardPdf.write(SIStringUtil.numberFormat(lResultSet.getString("bgprice")), bgpriceHorizontalAxis[index], bgpriceVerticalAxis[index]);
int pointlength = ((lResultSet.getString("bgprice").length() - 1) / 3) * 8;
priceCardPdf.setFontSize(15);//BG卸価（税込）
priceCardPdf.write("(税込)", bgpriceHorizontalAxis[index]+(lResultSet.getString("bgprice")).length()*20+15+pointlength, bgpriceVerticalAxis[index]);
index++;
}
} catch (Exception e){
e.printStackTrace();
} finally {
SIDBUtil.close(lResultSet, lStatement);
}
priceCardPdf.close();
SIPDFUtil lPdfUtil = new SIPDFUtil();
String lOutputFileName = "pricecard_" + (new SIDateTime().getDateTimeString()) + "." + SIPDFConf.SIPDF_EXTENSION_NAME;
lPdfUtil.execute(response, lFileName, lOutputFileName);
// 臨時ファイルの削除
lOrderTempFile.delete();
}
public String writeHacchuData(HttpServletRequest request, HttpServletResponse response, Connection lConnection, SICSVRead lRead) {
SICustomErrors errors = new SICustomErrors();
SILogin lLogin = SIHTMLUtil.getLogin(request);
StringBuffer lResBuf = new StringBuffer();
String branchCode = "";
String hacchuCode = "";
String individualCode = "";
String amount = "";
String amountAdd = "";
String purchasePrice = "";
String totalOfPrice = "0";
String lastStockCode = "";
String lastStockName = "";
String planDateStr = "";
String arrivalPlanDate = "";
String arrivalScheduleMsg = "";
String comment = "";
Statement lStatement = null;
ResultSet lResultSet = null;
boolean firstFlg = true;
SIDateTime lDate = new SIDateTime();
try{
branchCode = SIDBUtil.getFirstData(lConnection, "SELECT belongingbranchcode FROM chargetbl WHERE chargecode="+SIDBUtil.SQL2Str(lLogin.getUserCode()));
if (SIUtil.isNull(branchCode)) {
lResBuf.append("ログインユーザエラー：担当者アカウントでログインして下さい");
return lResBuf.toString();
}
}catch(Exception e){
lResBuf.append(SIErrorFactory.getErrorMsg("database.execute.error"));
return lResBuf.toString();
}
while (lRead.hasNextLine()) {
String[] lItemList;
try {
lItemList = lRead.itemList();
if (lItemList == null || lItemList.length == 0) continue;
// 項目数のチェック
if (lItemList.length!=2 && lItemList.length!=3 && lItemList.length!=4 && lItemList.length!=5 && lItemList.length!=6
&& lItemList.length!=25 && lItemList.length!=26 && lItemList.length!=27 && lItemList.length!=28 && lItemList.length!=29) {
lResBuf.append("
Line[").append(lRead.getLineNo()).append("]:エラー");
lResBuf.append(SIErrorFactory.getErrorMsg("manager.app.enough.item"));
continue;
}
errors = new SICustomErrors();
if (lItemList.length==2) {
individualCode=lItemList[0];
amount=lItemList[1];
amountAdd="";
comment="";
planDateStr="";
purchasePrice="";
} else if (lItemList.length==3) {
individualCode=lItemList[0];
amount=lItemList[1];
amountAdd=lItemList[2];
comment="";
planDateStr="";
purchasePrice="";
} else if (lItemList.length==4) {
individualCode=lItemList[0];
amount=lItemList[1];
amountAdd=lItemList[2];
comment=lItemList[3];
planDateStr="";
purchasePrice="";
} else if (lItemList.length==5) {
individualCode=lItemList[0];
amount=lItemList[1];
amountAdd=lItemList[2];
comment=lItemList[3];
planDateStr=lItemList[4];
purchasePrice="";
} else if (lItemList.length==6) {
individualCode=lItemList[0];
amount=lItemList[1];
amountAdd=lItemList[2];
comment=lItemList[3];
planDateStr=lItemList[4];
arrivalScheduleMsg=lItemList[5];
purchasePrice="";
} else if (lItemList.length==25) {
individualCode=lItemList[0];
purchasePrice=lItemList[19];
amount=lItemList[24];
amountAdd="";
comment="";
planDateStr="";
} else if (lItemList.length==26) {
individualCode=lItemList[0];
purchasePrice=lItemList[19];
amount=lItemList[24];
amountAdd=lItemList[25];
comment="";
planDateStr="";
} else if (lItemList.length==27) {
individualCode=lItemList[0];
purchasePrice=lItemList[19];
amount=lItemList[24];
amountAdd=lItemList[25];
comment=lItemList[26];
} else if (lItemList.length==28) {
individualCode=lItemList[0];
purchasePrice=lItemList[19];
amount=lItemList[24];
amountAdd=lItemList[25];
comment=lItemList[26];
planDateStr=lItemList[27];
} else {
individualCode=lItemList[0];
purchasePrice=lItemList[19];
amount=lItemList[24];
amountAdd=lItemList[25];
comment=lItemList[26];
planDateStr=lItemList[27];
arrivalScheduleMsg=lItemList[28];
}
if (firstFlg && (!SICheckUtil.isAlphaDigit(individualCode) || !SICheckUtil.isBetweenLenB(individualCode, 1, 10))) {
firstFlg = false;
continue;
}else if (firstFlg) firstFlg=false;
boolean noErrorFlg1 = true;
noErrorFlg1 &= SICheckValid.checkValid(errors, "在庫コード", individualCode, SICheckDataConf.SICHECK_DATA_EMPTY_TYPE + SICheckDataConf.SICHECK_DATA_ALPHA_DIGIT_TYPE);
noErrorFlg1 &= SICheckValid.checkValid(errors, "在庫コード", individualCode, SICheckDataConf.SICHECK_DATA_BYTE_LEN_WITHIN_TYPE, 8);
noErrorFlg1 &= SICheckValid.checkValid(errors, "発注数", amount, SICheckDataConf.SICHECK_DATA_EMPTY_TYPE + SICheckDataConf.SICHECK_DATA_DIGIT_TYPE);
noErrorFlg1 &= SICheckValid.checkValid(errors, "発注数", amount, SICheckDataConf.SICHECK_DATA_BYTE_LEN_WITHIN_TYPE, 8);
if("0".equals(amount)) {
errors.addError(new SICustomError("manager.message.freeword","「発注数」は1以上の数を入力して下さい"));
noErrorFlg1 = false;
}
if (SIUtil.isNull(amountAdd)) amountAdd = "0";
noErrorFlg1 &= SICheckValid.checkValid(errors, "添付数", amountAdd, SICheckDataConf.SICHECK_DATA_DIGIT_TYPE);
noErrorFlg1 &= SICheckValid.checkValid(errors, "添付数", amountAdd, SICheckDataConf.SICHECK_DATA_BYTE_LEN_WITHIN_TYPE, 8);
noErrorFlg1 &= SICheckValid.checkValid(errors, "発注単価", purchasePrice, SICheckDataConf.SICHECK_DATA_DIGIT_TYPE);
noErrorFlg1 &= SICheckValid.checkValid(errors, "発注単価", purchasePrice, SICheckDataConf.SICHECK_DATA_BYTE_LEN_WITHIN_TYPE, 8);
noErrorFlg1 &= SICheckValid.checkValid(errors, "備考", comment, SICheckDataConf.SICHECK_DATA_BYTE_LEN_WITHIN_TYPE, 128);
if (SIUtil.isNotNull(planDateStr)){
if (planDateStr.length()==8) {
arrivalPlanDate=planDateStr.substring(0, 4)+"-"+planDateStr.substring(4, 6)+"-"+planDateStr.substring(6, 8);
noErrorFlg1 &= SICheckValid.checkValid(errors, "納期", arrivalPlanDate, SICheckDataConf.SICHECK_DATA_DATE_TYPE);
} else {
errors.addError(new SICustomError("manager.message.freeword","「納期」は数値８桁（YYYYMMDD）で入力して下さい"));
noErrorFlg1 = false;
}
} else {
arrivalPlanDate="";
}
if (noErrorFlg1) {
try {
SIInsertRec lRec = new SIInsertRec("hacchuTempTbl");
lRec.add("IndividualCode",individualCode);
lRec.add("Amount",amount);
lRec.add("AmountAdd",amountAdd);
lRec.add("PurchasePrice",purchasePrice);
lRec.add("InitUserCode", lLogin.getUserCode());
lRec.add("InitDateTime", lDate.getFullDateTime());
lRec.add("ArrivalPlanDate", arrivalPlanDate);
lRec.add("Comment", comment);
lRec.add("Status", "0");
lRec.execute(lConnection);
if (SIUtil.isNotNull(arrivalScheduleMsg)){
SIModifyRec lRec2= new SIModifyRec("individualTbl");
lRec2.addCondition("IndividualCode",individualCode);
lRec2.add("ArrivalScheduleMsg", arrivalScheduleMsg);
lRec2.execute(lConnection);
}
} catch (SIDuplicateKeyException e) {
errors.addError(new SICustomError("database.insert.duplicate"));
e.printStackTrace();
} catch (SIDBAccessException e) {
errors.addError(new SICustomError("database.insert.duplicate"));
e.printStackTrace();
}
}
if (SIUtil.isNotNull(errors.getErrorMsg())) {
lResBuf.append("
Line[").append(lRead.getLineNo()).append("]:エラー");
lResBuf.append(errors.getErrorMsg());
}
} catch (SIErrorException e) {
lResBuf.append("
Line[").append(lRead.getLineNo()).append("]:エラー");
lResBuf.append(SIErrorFactory.getErrorMsg("input.data.csv.format"));
}
request.removeAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY);
}
if (SIUtil.isNull(lResBuf.toString())){//エラーひとつもない時
boolean noErrorFlg2 = true;
arrivalPlanDate="";
try {
if (SIDBUtil.hasData(lConnection, "SELECT * FROM hacchutemptbl WHERE status=0 AND individualcode NOT IN (SELECT individualcode FROM individualtbl)")){
lResBuf.append("
登録時エラー");
lResBuf.append(SIErrorFactory.getErrorMsg("manager.message.freeword","存在しない在庫コードが含まれるため登録できません"));
noErrorFlg2 = false;
}
if (SIDBUtil.hasData(lConnection, "SELECT * FROM hacchutemptbl WHERE status=0 AND individualcode IN (SELECT individualcode FROM individualtbl WHERE amountflg=0)")){
lResBuf.append("
登録時エラー");
lResBuf.append(SIErrorFactory.getErrorMsg("manager.message.freeword","受注発注商品が含まれるため登録できません"));
noErrorFlg2 = false;
}
/* 一回制限解除
if (SIDBUtil.hasData(lConnection, "SELECT * FROM hacchutemptbl t1,hacchutemptbl t2,individualtbl i WHERE t1.status=0 AND t2.status=1 AND t1.initdate=t2.initdate AND t1.individualcode=i.individualcode AND i.stockcode=t2.stockcode")){
lResBuf.append("
登録時エラー");
lResBuf.append(SIErrorFactory.getErrorMsg("manager.message.freeword","本日一括登録済の発注先があるため登録できません"));
noErrorFlg2 = false;
}
*/
if (noErrorFlg2) {
//作成
StringBuffer lSqlStr = new StringBuffer();
lSqlStr.append("SELECT s.stockcode,s.stockname,t.individualcode,c.cmdtyname,l.colorname,t.arrivalPlanDate,t.comment ");
lSqlStr.append(",t.amount,t.amountadd,CASE WHEN t.purchaseprice IS NULL THEN i.purchaseprice2 ELSE t.purchaseprice END AS purchaseprice ");
lSqlStr.append("FROM hacchutemptbl t,cmdtymtbl c,individualtbl i,stockmtbl s,colortbl l ");
lSqlStr.append("WHERE t.individualcode=i.individualcode AND i.cmdtycode=c.cmdtycode ");
lSqlStr.append("AND c.colorcode=l.colorcode AND i.stockcode=s.stockcode AND i.amountflg='1' AND t.status='0' ");
lSqlStr.append("ORDER BY s.stockcode,i.individualcode");
hacchuCode = SIDBUtil.getFirstData(lConnection, "SELECT coalesce(max(hacchucode),0) + 1 FROM hacchutbl");
lStatement = lConnection.createStatement();
lResultSet = lStatement.executeQuery(lSqlStr.toString());
int count = 1;
while (lResultSet.next()) {
if (!lResultSet.isFirst()&&!lastStockCode.equals(lResultSet.getString("stockCode"))) {
SIInsertRec insertRec = new SIInsertRec("hacchutbl");
insertRec.add("hacchuCode", hacchuCode);
insertRec.add("orderFlg", "0");
insertRec.add("stockCode", lastStockCode);
insertRec.add("stockName", lastStockName);
insertRec.add("branchCode", branchCode);
insertRec.add("chargeCode", lLogin.getUserCode());
insertRec.add("directFlg", "0");
insertRec.add("initDateTime", lDate.getFullDateTime());
insertRec.add("updateDateTime", lDate.getFullDateTime());
insertRec.add("ArrivalPlanDate", arrivalPlanDate);
insertRec.add("hacchuMailFlg", "0");
if (SICheckUtil.isGreater(totalOfPrice, "100000")) {
insertRec.add("status", "3");
} else {
insertRec.add("status", "1");
}
insertRec.add("taxRate", SITax.getTaxRate(lConnection));
insertRec.add("layercode", "1");
insertRec.add("enabledflg", "1");
insertRec.execute(lConnection);
hacchuCode = SIDBUtil.getFirstData(lConnection, "SELECT coalesce(max(hacchucode),0) + 1 FROM hacchutbl");
count = 1;
totalOfPrice = "0";
}
lastStockCode = lResultSet.getString("stockCode");
lastStockName = lResultSet.getString("stockName");
arrivalPlanDate = lResultSet.getString("arrivalPlanDate");
SIInsertRec insertRec2 = new SIInsertRec("hacchudetailtbl");
insertRec2.add("hacchuCode", hacchuCode);
insertRec2.add("hacchuIndividualCode", String.valueOf(count));
insertRec2.add("hacchuBranchCode", "1");
insertRec2.add("individualCode", lResultSet.getString("individualCode"));
insertRec2.add("cmdtyName", lResultSet.getString("cmdtyName"));
insertRec2.add("colorName", lResultSet.getString("colorName"));
insertRec2.add("branchCode", "18");
insertRec2.add("branchName", "物流センター");
insertRec2.add("shipFlg", "0");
insertRec2.add("storeNumber", "");
insertRec2.add("amount", lResultSet.getString("amount"));
insertRec2.add("amountAdd", lResultSet.getString("amountAdd"));
insertRec2.add("purchasePrice", lResultSet.getString("purchasePrice"));
insertRec2.add("importCost", "0");
insertRec2.add("processingExpence", "0");
insertRec2.add("deliveryFee", "0");
insertRec2.add("comment", lResultSet.getString("comment"));
insertRec2.add("shippmentDate", "");
insertRec2.add("layercode", "1");
insertRec2.execute(lConnection);
count++;
totalOfPrice = SIUtil.add(totalOfPrice, SIUtil.multi(lResultSet.getString("amount"), lResultSet.getString("purchasePrice")));
SIModifyRec lMod = new SIModifyRec("hacchuTempTbl");
lMod.addCondition("status","0");
lMod.addCondition("individualCode",lResultSet.getString("individualCode"));
lMod.add("hacchuCode",hacchuCode);
lMod.add("purchasePrice", lResultSet.getString("purchasePrice"));
lMod.add("stockCode", lastStockCode);
lMod.add("status","1");
lMod.execute(lConnection);
if (lResultSet.isLast()) {
SIInsertRec insertRec3 = new SIInsertRec("hacchutbl");
insertRec3.add("hacchuCode", hacchuCode);
insertRec3.add("orderFlg", "0");
insertRec3.add("stockCode", lastStockCode);
insertRec3.add("stockName", lastStockName);
insertRec3.add("branchCode", branchCode);
insertRec3.add("chargeCode", lLogin.getUserCode());
insertRec3.add("directFlg", "0");
insertRec3.add("initDateTime", lDate.getFullDateTime());
insertRec3.add("updateDateTime", lDate.getFullDateTime());
insertRec3.add("arrivalPlanDate", arrivalPlanDate);
insertRec3.add("hacchuMailFlg", "0");
if (SICheckUtil.isGreater(totalOfPrice, "100000")) {
insertRec3.add("status", "3");
} else {
insertRec3.add("status", "1");
}
insertRec3.add("taxRate", SITax.getTaxRate(lConnection));
insertRec3.add("layercode", "1");
insertRec3.add("enabledflg", "1");
insertRec3.execute(lConnection);
}
}
SIDBUtil.close(lStatement, lResultSet);
}
} catch (SIDuplicateKeyException e) {
errors.addError(new SICustomError("database.insert.duplicate"));
e.printStackTrace();
lResBuf.append(errors.getErrorMsg());
} catch (SQLException e) {
errors.addError(new SICustomError("database.execute.error"));
e.printStackTrace();
lResBuf.append(errors.getErrorMsg());
} catch (SIDBAccessException e) {
errors.addError(new SICustomError("database.execute.error"));
e.printStackTrace();
lResBuf.append(errors.getErrorMsg());
}
}
return lResBuf.toString();
}
public String writeMailData(HttpServletRequest request, HttpServletResponse response, Connection lConnection, SICSVRead lRead) {
SICustomErrors errors = new SICustomErrors();
StringBuffer lResBuf = new StringBuffer();
while (lRead.hasNextLine()) {
String[] lItemList;
try {
lItemList = lRead.itemList();
if (lItemList == null || lItemList.length == 0 || lRead.getLineNo() == 1) continue;
// 項目数のチェック
if (lItemList.length!=10) {
lResBuf.append("
Line[").append(lRead.getLineNo()).append("]:エラー");
lResBuf.append(SIErrorFactory.getErrorMsg("manager.app.enough.item"));
continue;
}
errors = new SICustomErrors();
SICheckValid.checkValid(errors, "顧客コード", lItemList[1], SICheckDataConf.SICHECK_DATA_EMPTY_TYPE + SICheckDataConf.SICHECK_DATA_DIGIT_TYPE);
SICheckValid.checkValid(errors, "ステータス", lItemList[7], SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
if (errors.isEmpty()) {
try {
String mailStatus = "";
if (lItemList[7].equals("送信成功")) {
mailStatus = "0";
} else if (lItemList[7].equals("未送信(配信停止リスト)")&&SIDBUtil.hasData(lConnection, "SELECT * FROM custtbl WHERE mailstatus IN (0,8,9) AND custcode="+SIDBUtil.SQL2Str(lItemList[1]))) {
mailStatus = "1";
} else if (lItemList[7].equals("未送信(配信停止リスト)")) {
mailStatus = "";
} else if (lItemList[7].equals("未送信(不正な受信者または送信者のアドレス)")) {
mailStatus = "1";
} else if (lItemList[7].equals("未到達(エラーメールとして処理されました)")) {
if (lItemList[9].startsWith("メールアドレスのドメインが存在しない")) {
mailStatus = "2";
} else if (lItemList[9].startsWith("メールアドレスのユーザーが存在しない")) {
mailStatus = "3";
} else if (lItemList[9].startsWith("ユーザー側でのドメイン指定受信または指定拒否")) {
mailStatus = "4";
} else if (lItemList[9].startsWith("相手先メールサーバーからの受信拒否")) {
mailStatus = "5";
} else if (lItemList[9].startsWith("相手先メールサーバーに接続失敗")) {
mailStatus = "6";
} else if (lItemList[9].startsWith("その他の持続的な送信失敗")) {
mailStatus = "7";
} else if (lItemList[9].startsWith("相手先のメールボックスが一杯なため受信拒否")) {
mailStatus = "8";
} else if (lItemList[9].startsWith("メールサイズが上限を超えているため受信拒否")) {
mailStatus = "9";
} else if (lItemList[9].startsWith("その他の一時的な送信失敗")) {
mailStatus = "9";
} else if (lItemList[9].startsWith("エラーメールの要因判別不可")) {
mailStatus = "10";
} else {
mailStatus = "11";
}
}
if (SIUtil.isNotNull(mailStatus)) {
SIModifyRec lRec = new SIModifyRec("custTbl");
lRec.addCondition("CustCode",lItemList[1]);
lRec.add("MailStatus", mailStatus);
if (mailStatus.equals("11")) lRec.add("OtherReason", lItemList[9]);
lRec.execute(lConnection);
}
} catch (SIDuplicateKeyException e) {
errors.addError(new SICustomError("database.insert.duplicate"));
e.printStackTrace();
} catch (SIDBAccessException e) {
errors.addError(new SICustomError("database.insert.duplicate"));
e.printStackTrace();
}
}
if (SIUtil.isNotNull(errors.getErrorMsg())) {
lResBuf.append("
Line[").append(lRead.getLineNo()).append("]:エラー");
lResBuf.append(errors.getErrorMsg());
}
} catch (SIErrorException e) {
lResBuf.append("
Line[").append(lRead.getLineNo()).append("]:エラー");
lResBuf.append(SIErrorFactory.getErrorMsg("input.data.csv.format"));
}
request.removeAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY);
}
return lResBuf.toString();
}
public String writePaymentData(HttpServletRequest request, HttpServletResponse response, Connection lConnection, SICSVRead lRead) {
SICustomErrors errors = new SICustomErrors();
StringBuffer lResBuf = new StringBuffer();
SIDateTime now = new SIDateTime();
while (lRead.hasNextLine()) {
String[] lItemList;
try {
lItemList = lRead.itemList();
if (lItemList == null || lItemList.length == 0) continue;
// 項目数のチェック
if (lItemList.length!=5) {
lResBuf.append("
Line[").append(lRead.getLineNo()).append("]:エラー");
lResBuf.append(SIErrorFactory.getErrorMsg("manager.app.enough.item"));
continue;
}
errors = new SICustomErrors();
SICheckValid.checkValid(errors, "顧客コード", lItemList[0], SICheckDataConf.SICHECK_DATA_EMPTY_TYPE + SICheckDataConf.SICHECK_DATA_DIGIT_TYPE);
SICheckValid.checkValid(errors, "受注番号", lItemList[1], SICheckDataConf.SICHECK_DATA_EMPTY_TYPE + SICheckDataConf.SICHECK_DATA_DIGIT_TYPE);
SICheckValid.checkValid(errors, "入金額", lItemList[2], SICheckDataConf.SICHECK_DATA_EMPTY_TYPE + SICheckDataConf.SICHECK_DATA_DIGIT_TYPE);
SICheckValid.checkValid(errors, "入金日", lItemList[3], SICheckDataConf.SICHECK_DATA_EMPTY_TYPE + SICheckDataConf.SICHECK_DATA_DATE_TYPE);
SICheckValid.checkValid(errors, "入力タイプ", lItemList[4], SICheckDataConf.SICHECK_DATA_EMPTY_TYPE + SICheckDataConf.SICHECK_DATA_DIGIT_TYPE);
if (errors.isEmpty()) {
try {
if (SIDBUtil.hasData(lConnection, "SELECT orderCode FROM orderLatestVw WHERE status=1 AND custCode="+SIDBUtil.SQL2Str(lItemList[0])+" AND orderCode="+SIDBUtil.SQL2Str(lItemList[1]))){
if ("1".equals(lItemList[4])) {
if (SIDBUtil.hasData(lConnection, "SELECT orderCode FROM paymentTbl WHERE status=0 AND paymentDate IS NULL AND orderCode="+SIDBUtil.SQL2Str(lItemList[1]))){
throw new SIDuplicateKeyException();
}
String price = SIDBUtil.getFirstData(lConnection, "SELECT orderTotal-paymentTotal FROM orderPaymentVw WHERE orderCode="+SIDBUtil.SQL2Str(lItemList[1]));
SICheckValid.checkValid(errors, "入金額","未入金残高", lItemList[2], price,SICheckDataConf.SICHECK_DATA_MATCH_TYPE);
SIDateTime paymentDate=new SIDateTime(lItemList[3],SIConfig.SIDATE_FORMAT0);
SICheckValid.checkValid(errors, "入金日", "本日", paymentDate.getFullDate(), now.getFullDate(), SICheckDataConf.SICHECK_DATA_DATE_GREATER_EQUAL_TYPE);
if (errors.isEmpty()) {
String paymentNumber = SIDBUtil.getFirstData(lConnection, "SELECT coalesce(max(paymentNumber),0)+1 FROM paymentTbl WHERE orderCode="+SIDBUtil.SQL2Str(lItemList[1]));
SIInsertRec lRec = new SIInsertRec("paymentTbl");
lRec.add("OrderCode",lItemList[1]);
lRec.add("PaymentNumber",paymentNumber);
lRec.add("PaymentPrice", lItemList[2]);
lRec.add("PaymentFee","0");
lRec.add("PaymentType","銀行振込");
lRec.add("ExpectedPaymentDate",lItemList[3]);
lRec.add("Status","0");
lRec.execute(lConnection);
SIPointMan.changeEnableFlg(lConnection, lItemList[1]);
}
}else if ("2".equals(lItemList[4])) {
String dataCount = SIDBUtil.getFirstData(lConnection, "SELECT coalesce(count(orderCode),0) FROM paymentTbl WHERE status=0 AND paymentDate IS NULL AND orderCode="+SIDBUtil.SQL2Str(lItemList[1]));
if ("0".equals(dataCount)){
errors.addError(new SICustomError("manager.message.freeword","該当の入金予定データが存在しません"));
}else if (!"1".equals(dataCount)){
errors.addError(new SICustomError("manager.message.freeword","該当の入金予定データが複数存在するため処理に失敗しました"));
}else{
String price = SIDBUtil.getFirstData(lConnection, "SELECT orderTotal-paymentTotal FROM orderPaymentVw WHERE orderCode="+SIDBUtil.SQL2Str(lItemList[1]));
SICheckValid.checkValid(errors, "入金額","未入金残高", lItemList[2], price,SICheckDataConf.SICHECK_DATA_MATCH_TYPE);
SIDateTime paymentDate=new SIDateTime(lItemList[3],SIConfig.SIDATE_FORMAT0);
SICheckValid.checkValid(errors, "入金日", "本日", paymentDate.getFullDate(), now.getFullDate(), SICheckDataConf.SICHECK_DATA_DATE_LESS_EQUAL_TYPE);
SIDateTime inventoryDate = new SIDateTime(SIDBUtil.getFirstData(lConnection, "SELECT ((substring(max(season),0,5)||'-'||substring(max(season),5,6)||'-01')::date + '1 month'::interval)::date AS initdate FROM inventorytbl"),SIConfig.SIDATE_FORMAT0);
SICheckValid.checkValid(errors, "入金日", "締処理日", paymentDate.getFullDate(), inventoryDate.getFullDate(), SICheckDataConf.SICHECK_DATA_DATE_GREATER_EQUAL_TYPE);
}
if (errors.isEmpty()) {
SIModifyRec lRec = new SIModifyRec("paymentTbl");
lRec.addCondition("OrderCode",lItemList[1]);
lRec.addCondition("PaymentPrice", lItemList[2]);
lRec.add("PaymentDate",lItemList[3]);
lRec.execute(lConnection);
lRec = new SIModifyRec("orderTbl");
lRec.addCondition("OrderCode",lItemList[1]);
lRec.add("ReceiptDate",lItemList[3]);
lRec.execute(lConnection);
SIPointMan.changeEnableFlg(lConnection, lItemList[1]);
}
}else if ("3".equals(lItemList[4])) {
if (SIDBUtil.hasData(lConnection, "SELECT orderCode FROM paymentTbl WHERE status=0 AND paymentDate IS NULL AND orderCode="+SIDBUtil.SQL2Str(lItemList[1]))){
throw new SIDuplicateKeyException();
}else{
String price = SIDBUtil.getFirstData(lConnection, "SELECT orderTotal-paymentTotal FROM orderPaymentVw WHERE orderCode="+SIDBUtil.SQL2Str(lItemList[1]));
SICheckValid.checkValid(errors, "入金額","未入金残高", lItemList[2], price,SICheckDataConf.SICHECK_DATA_MATCH_TYPE);
SIDateTime paymentDate=new SIDateTime(lItemList[3],SIConfig.SIDATE_FORMAT0);
SICheckValid.checkValid(errors, "入金日", "本日", paymentDate.getFullDate(), now.getFullDate(), SICheckDataConf.SICHECK_DATA_DATE_LESS_EQUAL_TYPE);
SIDateTime inventoryDate = new SIDateTime(SIDBUtil.getFirstData(lConnection, "SELECT ((substring(max(season),0,5)||'-'||substring(max(season),5,6)||'-01')::date + '1 month'::interval)::date AS initdate FROM inventorytbl"),SIConfig.SIDATE_FORMAT0);
SICheckValid.checkValid(errors, "入金日", "締処理日", paymentDate.getFullDate(), inventoryDate.getFullDate(), SICheckDataConf.SICHECK_DATA_DATE_GREATER_EQUAL_TYPE);
}
if (errors.isEmpty()) {
String paymentNumber = SIDBUtil.getFirstData(lConnection, "SELECT coalesce(max(paymentNumber),0)+1 FROM paymentTbl WHERE orderCode="+SIDBUtil.SQL2Str(lItemList[1]));
SIInsertRec lRec1 = new SIInsertRec("paymentTbl");
lRec1.add("OrderCode",lItemList[1]);
lRec1.add("PaymentNumber",paymentNumber);
lRec1.add("PaymentPrice", lItemList[2]);
lRec1.add("PaymentFee","0");
lRec1.add("PaymentType","銀行振込");
lRec1.add("ExpectedPaymentDate",lItemList[3]);
lRec1.add("PaymentDate",lItemList[3]);
lRec1.add("Status","0");
lRec1.execute(lConnection);
SIModifyRec lRec2 = new SIModifyRec("orderTbl");
lRec2.addCondition("OrderCode",lItemList[1]);
lRec2.add("ReceiptDate",lItemList[3]);
lRec2.execute(lConnection);
SIPointMan.changeEnableFlg(lConnection, lItemList[1]);
}
}else{
errors.addError(new SICustomError("manager.message.freeword","入力タイプは1〜3の数字で指定して下さい"));
}
}else{
errors.addError(new SICustomError("manager.message.freeword","該当のキャンセル・返品状態でない受注が存在しません"));
}
} catch (SIDuplicateKeyException e) {
errors.addError(new SICustomError("database.insert.duplicate"));
e.printStackTrace();
} catch (ParseException e) {
errors.addError(new SICustomError("database.execute.error"));
e.printStackTrace();
} catch (SQLException e) {
errors.addError(new SICustomError("database.execute.error"));
e.printStackTrace();
} catch (SIDBAccessException e) {
errors.addError(new SICustomError("database.execute.error"));
e.printStackTrace();
}
}
if (SIUtil.isNotNull(errors.getErrorMsg())) {
lResBuf.append("
Line[").append(lRead.getLineNo()).append("]:エラー");
lResBuf.append(errors.getErrorMsg());
}
} catch (SIErrorException e) {
lResBuf.append("
Line[").append(lRead.getLineNo()).append("]:エラー");
lResBuf.append(SIErrorFactory.getErrorMsg("input.data.csv.format"));
}
request.removeAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY);
}
return lResBuf.toString();
}
public void destroy() {}
}