/*******************************************************************************
* All Rights reserved,Copyright (c) K-Opticom
********************************************************************************
*＜プログラム内容＞
* システム名 ：eo顧客基幹システム
* モジュール名 ：JSYejbETU0041B014TPDA
* ソースファイル名：JSYejbETU0041B014TPDA.java
* 作成者 ：EK908575
* 日付 ：2015年11月12日
*＜機能概要＞
* 申込事業所別番ポ工事申込依頼件数取得テンプレートDBアクセス部品です。
*＜修正履歴＞
* バージョン 修正日 修正者 修正内容
* v3.00.00 2012/03/17 FJ)楠神 新規作成
* v3.00.00 2012/03/17 FJ)楠神 ANK-0045-01-00
* v3.00.01 2012/06/13 FJ)宮本 TAI-2012-0000054
* v4.00.00 2012/06/13 FJ)宮本 ANK-0079-01-00
* v4.00.01 2012/09/12 FJ)宮本 ANK-0079-01-00
* v4.00.02 2012/10/10 FJ)宮本 IT1-2012-0001209
* v6.00.00 2013/12/02 FJ)高川 ANK-1762-00-00
* v6.00.01 2014/01/06 FJ)谷川 ANK-1741-00-00
* v6.00.02 2014/03/10 FJ)高橋 ANK-1741-00-00
* v20.00.00 2015/11/12 FJ)中山 【OM-2015-0002945】性能改善
* v72.00.00 2024/03/06 FJ)三角 ANK-4494-00-00_【eo定期】 双方向番ポ対応
********************************************************************************/
package eo.ejb.cbs.sqlf;
import com.fujitsu.futurity.model.base.CAANMsg;
import com.fujitsu.futurity.model.base.CAANSQLFacility;
import com.fujitsu.futurity.model.base.CAANRuntimeException;
import com.fujitsu.futurity.model.base.CAANJDBCUtil;
import com.fujitsu.futurity.model.ejb.common.JSYejbConnection;
import com.fujitsu.futurity.model.ejb.common.JSYejbLog;
import com.fujitsu.futurity.model.ejb.common.StatusCodes;
import com.fujitsu.futurity.model.ejb.common.fw.TemplateDBAccessHandler;
import com.fujitsu.futurity.model.ejb.common.fw.AgentDispatchContext;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import eo.common.constant.JTUStrConst;
import eo.ejb.cbs.cbsmsg.ETU0041B014CBSMsg;
import eo.ejb.cbs.cbsmsg.ETU0041B014CBSMsg1List;
import eo.ejb.cbm.entity.TU0041ETMsg;
import eo.ejb.common.JTUModelCommon;
/**
*
* テンプレートDBアクセス部品 ETU0041B014_SQLF
* 入力された項目からSQL文を動的に組み立て、
* 検索結果をCBSMsgにセットします。
*/
public class JSYejbETU0041B014TPDA extends CAANSQLFacility implements TemplateDBAccessHandler{
/**
* コンストラクタ
*/
public JSYejbETU0041B014TPDA() {
}
/**
* ETU0041B014_SQLF
* @param inMsg 入力値の格納されたCBSMsg
* @return void
*/
public void invoke(CAANMsg inMsg,AgentDispatchContext inContext){
// コネクション
Connection con1 = null;
// プリペアステートメント
PreparedStatement pstmt = null;
// リザルトセット
ResultSet rsltQuery = null;
/********************
* SQL文の作成
********************/
try{
//コネクション取得
con1 = JSYejbConnection.getConnection(TU0041ETMsg.getTableName());
// ANK-4494-00-00 MOD START
// // SQL文_基本部1
// StringBuffer sql_Buff = new StringBuffer();
// sql_Buff.append(" SELECT MJ.MSKM_JIGSHO_NO ")
// .append(" , MJ.MSKM_JIGSHONM ")
// .append(" , MJ.SEND_UPPLNO ")
// .append(" , MJ.TA_JIGYOSHA_CNT AS TA_JIGYOSHA_CNT ")
// .append(" , MJ.MI_OUTPUT_CNT AS MI_OUTPUT_CNT ")
// .append(" , MJ.SEND_ZUMI_CNT AS SEND_ZUMI_CNT ")
// .append(" , MJ.KOPT_RRK_MDGC_TELNO ")
// .append(" , MJ.KOPT_RRK_MDGC_FAX_NO ")
// .append(" FROM (SELECT TU0041.MSKM_JIGSHO_NO AS MSKM_JIGSHO_NO ")
// .append(" , TU0041.MSKM_JIGSHONM AS MSKM_JIGSHONM ")
// .append(" , TU0041.SEND_UPPLNO AS SEND_UPPLNO ")
// .append(" , 0 AS TA_JIGYOSHA_CNT ")
// .append(" , NVL(TBL_MI_OUTPUT.MI_OUTPUT_CNT,0) AS MI_OUTPUT_CNT ")
// .append(" , NVL(TBL_SEND_ZUMI.SEND_ZUMI_CNT,0) AS SEND_ZUMI_CNT ")
// .append(" , TU0041.KOPT_RRK_MDGC_TELNO AS KOPT_RRK_MDGC_TELNO ")
// .append(" , TU0041.KOPT_RRK_MDGC_FAX_NO AS KOPT_RRK_MDGC_FAX_NO ")
// .append(" FROM TU_M_MSKM_JIGSHO TU0041 ")
// .append(" LEFT JOIN (SELECT CASE TU0011.BMP_MSKMSHO_TYPE_CD ")
// .append(" WHEN '1' THEN '000000000401' ")
// .append(" WHEN '2' THEN '000000000403' ")
// .append(" WHEN '3' THEN '000000000402' ")
// .append(" ELSE NULL ")
// .append(" END MSKM_JIGSHO_NO ")
// .append(" , COUNT(*) AS MI_OUTPUT_CNT ")
// .append(" FROM TU_T_BMP_KOJI TU0011 ")
// .append(" WHERE TU0011.BMP_KOJI_STAT = '021' ")
// .append(" AND TU0011.HOJIN_KOJIN_CD = ? ");
//
// if (JTUStrConst.KOJIN.equals(inMsg.getObject(ETU0041B014CBSMsg.KEY_HOJIN_KOJIN_CD)))
// {
// sql_Buff.append(" AND EXISTS(SELECT 1 ")
// .append(" FROM KK_T_SVC_KEI_UCWK KK0161 ")
// .append(" INNER JOIN KK_T_SVKEIUW_EOH_TEL KK0191 ")
// .append(" ON KK0161.SVC_KEI_UCWK_NO = KK0191.SVC_KEI_UCWK_NO ")
// .append(" AND KK0161.GENE_ADD_DTM = KK0191.GENE_ADD_DTM ")
// .append(" AND KK0191.DOBANITEN_JDG_CD IN ('0','1','2') ")
// .append(" WHERE KK0161.SVC_KEI_UCWK_NO = TU0011.SVC_KEI_UCWK_NO ")
// .append(" AND (KK0161.RSV_APLY_YMD || KK0161.GENE_ADD_DTM) = ( ")
// .append(" SELECT MAX(KK0161_GENE.RSV_APLY_YMD || KK0161_GENE.GENE_ADD_DTM) ")
// .append(" FROM KK_T_SVC_KEI_UCWK KK0161_GENE ")
// .append(" WHERE KK0161_GENE.SVC_KEI_UCWK_NO = KK0161.SVC_KEI_UCWK_NO ")
// .append(" AND KK0161_GENE.RSV_APLY_YMD <= ? ")
// .append(" AND KK0161_GENE.RSV_APLY_CD = '2' ")
// .append(" AND KK0161_GENE.MK_FLG = '0')) ");
// }
//
// sql_Buff.append(" AND TU0011.GENE_ADD_DTM = ( ")
// .append(" SELECT MAX(TU0011_GENE.GENE_ADD_DTM) ")
// .append(" FROM TU_T_BMP_KOJI TU0011_GENE ")
// .append(" WHERE TU0011_GENE.BMP_KOJI_NO = TU0011.BMP_KOJI_NO ")
// .append(" AND TU0011_GENE.MK_FLG = '0') ")
// .append(" GROUP BY TU0011.BMP_MSKMSHO_TYPE_CD) TBL_MI_OUTPUT ")
// .append(" ON TU0041.MSKM_JIGSHO_NO = TBL_MI_OUTPUT.MSKM_JIGSHO_NO ")
// .append(" LEFT JOIN (SELECT CASE TU0011.BMP_MSKMSHO_TYPE_CD ")
// .append(" WHEN '1' THEN '000000000401' ")
// .append(" WHEN '2' THEN '000000000403' ")
// .append(" WHEN '3' THEN '000000000402' ")
// .append(" ELSE NULL ")
// .append(" END MSKM_JIGSHO_NO ")
// .append(" , COUNT(*) AS SEND_ZUMI_CNT ")
// .append(" FROM TU_T_BMP_KOJI TU0011 ")
////OM-2015-0002945 MOD START
//// .append(" WHERE TU0011.NTT_RNKI_SEND_DTM LIKE ? ")
// // 性能の改善がみられるためバインド変数をやめる
// .append(" WHERE TU0011.NTT_RNKI_SEND_DTM LIKE '")
// .append(inMsg.getObject(ETU0041B014CBSMsg.KEY_STDARDYMD))
// .append("%' ")
////OM-2015-0002945 MOD END
// .append(" AND TU0011.HOJIN_KOJIN_CD = ? ")
// .append(" AND TU0011.GENE_ADD_DTM = ( ")
// .append(" SELECT MAX(TU0011_GENE.GENE_ADD_DTM) ")
// .append(" FROM TU_T_BMP_KOJI TU0011_GENE ")
// .append(" WHERE TU0011_GENE.BMP_KOJI_NO = TU0011.BMP_KOJI_NO ")
// .append(" AND TU0011_GENE.MK_FLG = '0') ")
// .append(" GROUP BY TU0011.BMP_MSKMSHO_TYPE_CD) TBL_SEND_ZUMI ")
// .append(" ON TU0041.MSKM_JIGSHO_NO = TBL_SEND_ZUMI.MSKM_JIGSHO_NO ")
// .append(" WHERE TU0041.MSKM_JIGSHO_NO IN ('000000000401','000000000402','000000000403') ")
// .append(" AND TU0041.MK_FLG = '0' ")
// .append(" UNION ALL ")
// .append(" SELECT TU0041.MSKM_JIGSHO_NO AS MSKM_JIGSHO_NO ")
// .append(" , TU0041.MSKM_JIGSHONM AS MSKM_JIGSHONM ")
// .append(" , TU0041.SEND_UPPLNO AS SEND_UPPLNO ")
// .append(" , (SELECT COUNT(*) AS TA_JIGYOSHA_CNT ")
// .append(" FROM TU_T_BMP_KOJI TU0011 ")
// .append(" WHERE TU0011.BMP_KOJI_STAT = '010' ")
// .append(" AND TU0011.BMP_MSKMSHO_TYPE_CD = '2' ")
// .append(" AND TU0011.HOJIN_KOJIN_CD = ? ");
//
// if (JTUStrConst.KOJIN.equals(inMsg.getObject(ETU0041B014CBSMsg.KEY_HOJIN_KOJIN_CD)))
// {
// sql_Buff.append(" AND EXISTS(SELECT 1 ")
// .append(" FROM KK_T_SVC_KEI_UCWK KK0161 ")
// .append(" INNER JOIN KK_T_SVKEIUW_EOH_TEL KK0191 ")
// .append(" ON KK0161.SVC_KEI_UCWK_NO = KK0191.SVC_KEI_UCWK_NO ")
// .append(" AND KK0161.GENE_ADD_DTM = KK0191.GENE_ADD_DTM ")
// .append(" AND KK0191.DOBANITEN_JDG_CD IN ('0','1','2') ")
// .append(" WHERE KK0161.SVC_KEI_UCWK_NO = TU0011.SVC_KEI_UCWK_NO ")
// .append(" AND (KK0161.RSV_APLY_YMD || KK0161.GENE_ADD_DTM) = ( ")
// .append(" SELECT MAX(KK0161_GENE.RSV_APLY_YMD || KK0161_GENE.GENE_ADD_DTM) ")
// .append(" FROM KK_T_SVC_KEI_UCWK KK0161_GENE ")
// .append(" WHERE KK0161_GENE.SVC_KEI_UCWK_NO = KK0161.SVC_KEI_UCWK_NO ")
// .append(" AND KK0161_GENE.RSV_APLY_YMD <= ? ")
// .append(" AND KK0161_GENE.RSV_APLY_CD = '2' ")
// .append(" AND KK0161_GENE.MK_FLG = '0')) ");
// }
//
// sql_Buff.append(" AND TU0011.GENE_ADD_DTM = ( ")
// .append(" SELECT MAX(TU0011_GENE.GENE_ADD_DTM) ")
// .append(" FROM TU_T_BMP_KOJI TU0011_GENE ")
// .append(" WHERE TU0011_GENE.BMP_KOJI_NO = TU0011.BMP_KOJI_NO ")
// .append(" AND TU0011_GENE.MK_FLG = '0')) AS TA_JIGYOSHA_CNT ")
// .append(" , (SELECT COUNT(*) AS MI_OUTPUT_CNT ")
// .append(" FROM TU_T_BMP_KOJI TU0011 ")
// .append(" WHERE TU0011.BMP_KOJI_STAT = '011' ")
// .append(" AND TU0011.BMP_MSKMSHO_TYPE_CD = '2' ")
// .append(" AND TU0011.HOJIN_KOJIN_CD = ? ")
// .append(" AND TU0011.TAJGS_RNKI_SEND_DTM IS NULL ");
//
// if (JTUStrConst.KOJIN.equals(inMsg.getObject(ETU0041B014CBSMsg.KEY_HOJIN_KOJIN_CD)))
// {
// sql_Buff.append(" AND EXISTS(SELECT 1 ")
// .append(" FROM KK_T_SVC_KEI_UCWK KK0161 ")
// .append(" INNER JOIN KK_T_SVKEIUW_EOH_TEL KK0191 ")
// .append(" ON KK0161.SVC_KEI_UCWK_NO = KK0191.SVC_KEI_UCWK_NO ")
// .append(" AND KK0161.GENE_ADD_DTM = KK0191.GENE_ADD_DTM ")
// .append(" AND KK0191.DOBANITEN_JDG_CD IN ('0','1','2') ")
// .append(" WHERE KK0161.SVC_KEI_UCWK_NO = TU0011.SVC_KEI_UCWK_NO ")
// .append(" AND (KK0161.RSV_APLY_YMD || KK0161.GENE_ADD_DTM) = ( ")
// .append(" SELECT MAX(KK0161_GENE.RSV_APLY_YMD || KK0161_GENE.GENE_ADD_DTM) ")
// .append(" FROM KK_T_SVC_KEI_UCWK KK0161_GENE ")
// .append(" WHERE KK0161_GENE.SVC_KEI_UCWK_NO = KK0161.SVC_KEI_UCWK_NO ")
// .append(" AND KK0161_GENE.RSV_APLY_YMD <= ? ")
// .append(" AND KK0161_GENE.RSV_APLY_CD = '2' ")
// .append(" AND KK0161_GENE.MK_FLG = '0')) ");
// }
//
// sql_Buff.append(" AND TU0011.GENE_ADD_DTM = ( ")
// .append(" SELECT MAX(TU0011_GENE.GENE_ADD_DTM) ")
// .append(" FROM TU_T_BMP_KOJI TU0011_GENE ")
// .append(" WHERE TU0011_GENE.BMP_KOJI_NO = TU0011.BMP_KOJI_NO ")
// .append(" AND TU0011_GENE.MK_FLG = '0')) AS MI_OUTPUT_CNT ")
// .append(" , (SELECT COUNT(*) AS SEND_ZUMI_CNT ")
// .append(" FROM TU_T_BMP_KOJI TU0011 ")
// .append(" WHERE TU0011.BMP_MSKMSHO_TYPE_CD = '2' ")
////OM-2015-0002945 MOD START
//// .append(" AND TU0011.TAJGS_RNKI_SEND_DTM LIKE ? ")
// // 性能の改善がみられるためバインド変数をやめる
// .append(" AND TU0011.TAJGS_RNKI_SEND_DTM LIKE '")
// .append(inMsg.getObject(ETU0041B014CBSMsg.KEY_STDARDYMD))
// .append("%' ")
////OM-2015-0002945 MOD END
// .append(" AND TU0011.HOJIN_KOJIN_CD = ? ")
// .append(" AND TU0011.GENE_ADD_DTM = ( ")
// .append(" SELECT MAX(TU0011_GENE.GENE_ADD_DTM) ")
// .append(" FROM TU_T_BMP_KOJI TU0011_GENE ")
// .append(" WHERE TU0011_GENE.BMP_KOJI_NO = TU0011.BMP_KOJI_NO ")
// .append(" AND TU0011_GENE.MK_FLG = '0')) AS SEND_ZUMI_CNT ")
// .append(" , TU0041.KOPT_RRK_MDGC_TELNO AS KOPT_RRK_MDGC_TELNO ")
// .append(" , TU0041.KOPT_RRK_MDGC_FAX_NO AS KOPT_RRK_MDGC_FAX_NO ")
// .append(" FROM TU_M_MSKM_JIGSHO TU0041 ")
// .append(" WHERE TU0041.MSKM_JIGSHO_NO = ('000000000404') ")
// .append(" AND TU0041.MK_FLG = '0') MJ ");
// // SQL文_個別部1
// if ( "1".equals( inMsg.getObject(ETU0041B014CBSMsg.FUNC_CODE).toString() ) ) {
// sql_Buff.append(" ORDER BY MJ.MSKM_JIGSHO_NO ");
// }
// SQL文_基本部1
StringBuffer sql_Buff = new StringBuffer();
sql_Buff.append(" SELECT SS.CD_DIV AS BMP_MSKMSHO_TYPE_CD ")
.append(" , SS.CD_DIV_NM AS DSP_BMP_MSKMSHO_TYPE_CD ")
.append(" , SS.TA_JIGYOSHA_CNT AS TA_JIGYOSHA_CNT ")
.append(" , SS.MI_OUTPUT_CNT AS MI_OUTPUT_CNT ")
.append(" , SS.SEND_ZUMI_CNT AS SEND_ZUMI_CNT ")
.append(" , SS.KOPT_RRK_MDGC_TELNO ")
.append(" FROM (SELECT ZM0171.CD_DIV ")
.append(" , ZM0171.CD_DIV_NM ")
.append(" , NVL(TBL_TA_JIGYOSHA.TA_JIGYOSHA_CNT,0) AS TA_JIGYOSHA_CNT ")
.append(" , NVL(TBL_MI_OUTPUT.MI_OUTPUT_CNT,0) AS MI_OUTPUT_CNT ")
.append(" , NVL(TBL_SEND_ZUMI.SEND_ZUMI_CNT,0) AS SEND_ZUMI_CNT ")
.append(" , (SELECT ZM0321.WORK_PARAM_SETTE_VALUE ")
.append(" FROM ZM_M_WORK_PARAM_KNRI ZM0321 ")
.append(" WHERE ZM0321.WORK_PARAM_ID = 'TU_BMP_TNT_TELNO' ")
.append(" AND ? BETWEEN ZM0321.WORK_PARAM_TSTAYMD AND ZM0321.WORK_PARAM_TENDYMD ")
.append(" AND ZM0321.MK_FLG = '0') AS KOPT_RRK_MDGC_TELNO ")
.append(" FROM ZM_M_CD_NM_KANRI ZM0171 ")
.append(" LEFT JOIN (SELECT CD_DIV ")
.append(" , COUNT(*) AS TA_JIGYOSHA_CNT ")
.append(" FROM (SELECT CASE TU0011.BMP_MSKMSHO_TYPE_CD ")
.append(" WHEN '3' THEN '3' ")
.append(" ELSE '1' ")
.append(" END CD_DIV ")
.append(" FROM TU_T_BMP_KOJI TU0011 ")
.append(" WHERE TU0011.BMP_KOJI_STAT = '010' ")
.append(" AND TU0011.HOJIN_KOJIN_CD = ? ");
if (JTUStrConst.KOJIN.equals(inMsg.getObject(ETU0041B014CBSMsg.KEY_HOJIN_KOJIN_CD)))
{
sql_Buff.append(" AND EXISTS(SELECT 1 ")
.append(" FROM KK_T_SVC_KEI_UCWK KK0161 ")
.append(" INNER JOIN KK_T_SVKEIUW_EOH_TEL KK0191 ")
.append(" ON KK0161.SVC_KEI_UCWK_NO = KK0191.SVC_KEI_UCWK_NO ")
.append(" AND KK0161.GENE_ADD_DTM = KK0191.GENE_ADD_DTM ")
.append(" WHERE KK0161.SVC_KEI_UCWK_NO = TU0011.SVC_KEI_UCWK_NO ")
.append(" AND (KK0161.RSV_APLY_YMD || KK0161.GENE_ADD_DTM) = ( ")
.append(" SELECT MAX(KK0161_GENE.RSV_APLY_YMD || KK0161_GENE.GENE_ADD_DTM) ")
.append(" FROM KK_T_SVC_KEI_UCWK KK0161_GENE ")
.append(" WHERE KK0161_GENE.SVC_KEI_UCWK_NO = KK0161.SVC_KEI_UCWK_NO ")
.append(" AND KK0161_GENE.RSV_APLY_YMD <= ? ")
.append(" AND KK0161_GENE.RSV_APLY_CD = '2' ")
.append(" AND KK0161_GENE.MK_FLG = '0')) ");
}
sql_Buff.append(" AND TU0011.GENE_ADD_DTM = ( ")
.append(" SELECT MAX(TU0011_GENE.GENE_ADD_DTM) ")
.append(" FROM TU_T_BMP_KOJI TU0011_GENE ")
.append(" WHERE TU0011_GENE.BMP_KOJI_NO = TU0011.BMP_KOJI_NO ")
.append(" AND TU0011_GENE.MK_FLG = '0')) ")
.append(" GROUP BY CD_DIV) TBL_TA_JIGYOSHA ")
.append(" ON ZM0171.CD_DIV = TBL_TA_JIGYOSHA.CD_DIV ")
.append(" LEFT JOIN (SELECT CD_DIV ")
.append(" , COUNT(*) AS MI_OUTPUT_CNT ")
.append(" FROM (SELECT CASE TU0011.BMP_MSKMSHO_TYPE_CD ")
.append(" WHEN '3' THEN '3' ")
.append(" ELSE '1' ")
.append(" END CD_DIV ")
.append(" FROM TU_T_BMP_KOJI TU0011 ")
.append(" WHERE TU0011.BMP_KOJI_STAT = '021' ")
.append(" AND TU0011.HOJIN_KOJIN_CD = ? ");
if (JTUStrConst.KOJIN.equals(inMsg.getObject(ETU0041B014CBSMsg.KEY_HOJIN_KOJIN_CD)))
{
sql_Buff.append(" AND EXISTS(SELECT 1 ")
.append(" FROM KK_T_SVC_KEI_UCWK KK0161 ")
.append(" INNER JOIN KK_T_SVKEIUW_EOH_TEL KK0191 ")
.append(" ON KK0161.SVC_KEI_UCWK_NO = KK0191.SVC_KEI_UCWK_NO ")
.append(" AND KK0161.GENE_ADD_DTM = KK0191.GENE_ADD_DTM ")
.append(" WHERE KK0161.SVC_KEI_UCWK_NO = TU0011.SVC_KEI_UCWK_NO ")
.append(" AND (KK0161.RSV_APLY_YMD || KK0161.GENE_ADD_DTM) = ( ")
.append(" SELECT MAX(KK0161_GENE.RSV_APLY_YMD || KK0161_GENE.GENE_ADD_DTM) ")
.append(" FROM KK_T_SVC_KEI_UCWK KK0161_GENE ")
.append(" WHERE KK0161_GENE.SVC_KEI_UCWK_NO = KK0161.SVC_KEI_UCWK_NO ")
.append(" AND KK0161_GENE.RSV_APLY_YMD <= ? ")
.append(" AND KK0161_GENE.RSV_APLY_CD = '2' ")
.append(" AND KK0161_GENE.MK_FLG = '0')) ");
}
sql_Buff.append(" AND TU0011.GENE_ADD_DTM = ( ")
.append(" SELECT MAX(TU0011_GENE.GENE_ADD_DTM) ")
.append(" FROM TU_T_BMP_KOJI TU0011_GENE ")
.append(" WHERE TU0011_GENE.BMP_KOJI_NO = TU0011.BMP_KOJI_NO ")
.append(" AND TU0011_GENE.MK_FLG = '0')) ")
.append(" GROUP BY CD_DIV) TBL_MI_OUTPUT ")
.append(" ON ZM0171.CD_DIV = TBL_MI_OUTPUT.CD_DIV ")
.append(" LEFT JOIN (SELECT CD_DIV ")
.append(" , COUNT(*) AS SEND_ZUMI_CNT ")
.append(" FROM (SELECT CASE TU0011.BMP_MSKMSHO_TYPE_CD ")
.append(" WHEN '3' THEN '3' ")
.append(" ELSE '1' ")
.append(" END CD_DIV ")
.append(" FROM TU_T_BMP_KOJI TU0011 ")
.append(" WHERE TU0011.NTT_RNKI_SEND_DTM LIKE '")
.append(inMsg.getObject(ETU0041B014CBSMsg.KEY_STDARDYMD))
.append("%' ")
.append(" AND TU0011.HOJIN_KOJIN_CD = ? ");
if (JTUStrConst.KOJIN.equals(inMsg.getObject(ETU0041B014CBSMsg.KEY_HOJIN_KOJIN_CD)))
{
sql_Buff.append(" AND EXISTS(SELECT 1 ")
.append(" FROM KK_T_SVC_KEI_UCWK KK0161 ")
.append(" INNER JOIN KK_T_SVKEIUW_EOH_TEL KK0191 ")
.append(" ON KK0161.SVC_KEI_UCWK_NO = KK0191.SVC_KEI_UCWK_NO ")
.append(" AND KK0161.GENE_ADD_DTM = KK0191.GENE_ADD_DTM ")
.append(" WHERE KK0161.SVC_KEI_UCWK_NO = TU0011.SVC_KEI_UCWK_NO ")
.append(" AND (KK0161.RSV_APLY_YMD || KK0161.GENE_ADD_DTM) = ( ")
.append(" SELECT MAX(KK0161_GENE.RSV_APLY_YMD || KK0161_GENE.GENE_ADD_DTM) ")
.append(" FROM KK_T_SVC_KEI_UCWK KK0161_GENE ")
.append(" WHERE KK0161_GENE.SVC_KEI_UCWK_NO = KK0161.SVC_KEI_UCWK_NO ")
.append(" AND KK0161_GENE.RSV_APLY_YMD <= ? ")
.append(" AND KK0161_GENE.RSV_APLY_CD = '2' ")
.append(" AND KK0161_GENE.MK_FLG = '0')) ");
}
sql_Buff.append(" AND TU0011.GENE_ADD_DTM = ( ")
.append(" SELECT MAX(TU0011_GENE.GENE_ADD_DTM) ")
.append(" FROM TU_T_BMP_KOJI TU0011_GENE ")
.append(" WHERE TU0011_GENE.BMP_KOJI_NO = TU0011.BMP_KOJI_NO ")
.append(" AND TU0011_GENE.MK_FLG = '0')) ")
.append(" GROUP BY CD_DIV) TBL_SEND_ZUMI ")
.append(" ON ZM0171.CD_DIV = TBL_SEND_ZUMI.CD_DIV ")
.append(" ")
.append(" WHERE ZM0171.CD_SBT_CD = 'CD01831' ")
.append(" AND ZM0171.MK_FLG = '0') SS ");
// SQL文_個別部1
if ( "1".equals( inMsg.getObject(ETU0041B014CBSMsg.FUNC_CODE).toString() ) ) {
sql_Buff.append(" ORDER BY SS.CD_DIV ");
}
// ANK-4494-00-00 MOD END
//prepareStatementにSQL文をセット
pstmt = con1.prepareStatement(sql_Buff.toString());
//ログ出力(SQL文の出力)
JSYejbLog.outlog(inContext, JSYejbLog.DBACCESS, this.getClass(), sql_Buff);
String opeDate = JTUModelCommon.getOpeDate(inMsg, null);
// PreparedStatementの指定されたパラメータカラムに 指定された値を設定
if ( "1".equals( inMsg.getString(ETU0041B014CBSMsg.FUNC_CODE) ) ) {
int index = 1;
// ANK-4494-00-00 MOD START
// // パラメータの設定(ＫＥＹ＿法人個人コードを指定)
// CAANJDBCUtil.setParam(pstmt, index++, inMsg.getObject(ETU0041B014CBSMsg.KEY_HOJIN_KOJIN_CD));
// // パラメータの設定(運用日付を指定)
// if (JTUStrConst.KOJIN.equals(inMsg.getObject(ETU0041B014CBSMsg.KEY_HOJIN_KOJIN_CD)))
// {
// CAANJDBCUtil.setParam(pstmt, index++, opeDate);
// }
////OM-2015-0002945 DEL START
//// // パラメータの設定(ＫＥＹ＿基準日 + "%"を指定)
//// CAANJDBCUtil.setParam(pstmt, index++, inMsg.getObject(ETU0041B014CBSMsg.KEY_STDARDYMD) + "%");
////OM-2015-0002945 DEL END
// // パラメータの設定(ＫＥＹ＿法人個人コードを指定)
// CAANJDBCUtil.setParam(pstmt, index++, inMsg.getObject(ETU0041B014CBSMsg.KEY_HOJIN_KOJIN_CD));
// // パラメータの設定(ＫＥＹ＿法人個人コードを指定)
// CAANJDBCUtil.setParam(pstmt, index++, inMsg.getObject(ETU0041B014CBSMsg.KEY_HOJIN_KOJIN_CD));
// // パラメータの設定(運用日付を指定)
// if (JTUStrConst.KOJIN.equals(inMsg.getObject(ETU0041B014CBSMsg.KEY_HOJIN_KOJIN_CD)))
// {
// CAANJDBCUtil.setParam(pstmt, index++, opeDate);
// }
// // パラメータの設定(ＫＥＹ＿法人個人コードを指定)
// CAANJDBCUtil.setParam(pstmt, index++, inMsg.getObject(ETU0041B014CBSMsg.KEY_HOJIN_KOJIN_CD));
// // パラメータの設定(運用日付を指定)
// if (JTUStrConst.KOJIN.equals(inMsg.getObject(ETU0041B014CBSMsg.KEY_HOJIN_KOJIN_CD)))
// {
// CAANJDBCUtil.setParam(pstmt, index++, opeDate);
// }
////OM-2015-0002945 DEL START
//// // パラメータの設定(ＫＥＹ＿基準日 + "%"を指定)
//// CAANJDBCUtil.setParam(pstmt, index++, inMsg.getObject(ETU0041B014CBSMsg.KEY_STDARDYMD) + "%");
////OM-2015-0002945 DEL END
// // パラメータの設定(ＫＥＹ＿法人個人コードを指定)
// CAANJDBCUtil.setParam(pstmt, index++, inMsg.getObject(ETU0041B014CBSMsg.KEY_HOJIN_KOJIN_CD));
// パラメータの設定(業務パラメータ：運用日付を指定)
CAANJDBCUtil.setParam(pstmt, index++, opeDate);
// パラメータの設定(ＫＥＹ＿法人個人コードを指定)
CAANJDBCUtil.setParam(pstmt, index++, inMsg.getObject(ETU0041B014CBSMsg.KEY_HOJIN_KOJIN_CD));
// パラメータの設定(運用日付を指定)
if (JTUStrConst.KOJIN.equals(inMsg.getObject(ETU0041B014CBSMsg.KEY_HOJIN_KOJIN_CD)))
{
CAANJDBCUtil.setParam(pstmt, index++, opeDate);
}
// パラメータの設定(ＫＥＹ＿法人個人コードを指定)
CAANJDBCUtil.setParam(pstmt, index++, inMsg.getObject(ETU0041B014CBSMsg.KEY_HOJIN_KOJIN_CD));
// パラメータの設定(運用日付を指定)
if (JTUStrConst.KOJIN.equals(inMsg.getObject(ETU0041B014CBSMsg.KEY_HOJIN_KOJIN_CD)))
{
CAANJDBCUtil.setParam(pstmt, index++, opeDate);
}
// パラメータの設定(ＫＥＹ＿法人個人コードを指定)
CAANJDBCUtil.setParam(pstmt, index++, inMsg.getObject(ETU0041B014CBSMsg.KEY_HOJIN_KOJIN_CD));
// パラメータの設定(運用日付を指定)
if (JTUStrConst.KOJIN.equals(inMsg.getObject(ETU0041B014CBSMsg.KEY_HOJIN_KOJIN_CD)))
{
CAANJDBCUtil.setParam(pstmt, index++, opeDate);
}
// ANK-4494-00-00 MOD END
}
// ResultSetの取得
rsltQuery = pstmt.executeQuery();
// ETU0041B014CBSMsgの明細にセットする項目
String[] msgKeyList1 = {
// ANK-4494-00-00 MOD START
// ETU0041B014CBSMsg1List.MSKM_JIGSHO_NO
// , ETU0041B014CBSMsg1List.MSKM_JIGSHONM
// , ETU0041B014CBSMsg1List.SEND_UPPLNO
// , ETU0041B014CBSMsg1List.TA_JIGYOSHA_CNT
// , ETU0041B014CBSMsg1List.MI_OUTPUT_CNT
// , ETU0041B014CBSMsg1List.SEND_ZUMI_CNT
// , ETU0041B014CBSMsg1List.KOPT_RRK_MDGC_TELNO
// , ETU0041B014CBSMsg1List.KOPT_RRK_MDGC_FAX_NO
ETU0041B014CBSMsg1List.BMP_MSKMSHO_TYPE_CD
, ETU0041B014CBSMsg1List.DSP_BMP_MSKMSHO_TYPE_CD
, ETU0041B014CBSMsg1List.TA_JIGYOSHA_CNT
, ETU0041B014CBSMsg1List.MI_OUTPUT_CNT
, ETU0041B014CBSMsg1List.SEND_ZUMI_CNT
, ETU0041B014CBSMsg1List.KOPT_RRK_MDGC_TELNO
// ANK-4494-00-00 MOD END
};
// 明細に処理結果(ResultSet)をマッピング
CAANMsg[] outMsg1 = mapMessageList(rsltQuery,msgKeyList1,"eo.ejb.cbs.cbsmsg.ETU0041B014CBSMsg1List",0);
// inMsg(CBSMsg)に明細をセット
inMsg.set("ETU0041B014CBSMsg1List", outMsg1);
} catch(SQLException e) {
inMsg.set(ETU0041B014CBSMsg.STATUS, StatusCodes.FIND_DB_ERR);
throw new CAANRuntimeException(e);
} finally {
// 資源の解放
try{
if(rsltQuery != null){
rsltQuery.close();
}
if(pstmt != null){
pstmt.close();
}
if(con1 != null){
closeConnection(con1);
}
}catch(SQLException e){
inMsg.set(ETU0041B014CBSMsg.STATUS, StatusCodes.FIND_DB_ERR);
throw new CAANRuntimeException(e);
}
}
}
}