/**
* タイムカード登録情報のなかから最大値を取得 <br>
* 登録情報が無い場合はシステム日付から年を取得
*
* @return int 最大値
* @throws SQLException SQL実行例外
*/
public int getMaxYear() throws SQLException {
PreparedStatement pstmt = null;
ResultSet rs = null;
Connection con = null;
UDate sysDate = new UDate();
int ret = sysDate.getYear();
con = getCon();
try {
// SQL文
SqlBuffer sql = new SqlBuffer();
sql.addSql(" select ");
sql.addSql(" max(TCD_DATE) as MAXDATE");
sql.addSql(" from ");
sql.addSql(" TCD_TCDATA");
pstmt = con.prepareStatement(sql.toSqlString());
log__.info(sql.toLogString());
rs = pstmt.executeQuery();
if (rs.next() && rs.getTimestamp("MAXDATE") != null) {
UDate maxDate = UDate.getInstanceTimestamp(rs.getTimestamp("MAXDATE"));
ret = maxDate.getYear();
}
} catch (SQLException e) {
throw e;
} finally {
JDBCUtil.closeResultSet(rs);
JDBCUtil.closeStatement(pstmt);
}
return ret;
}
/**
* Select PTL_PORTLET_IMAGE All Data
*
* @return List in PTL_PORTLET_IMAGEModel
* @throws SQLException SQL実行例外
*/
public List<PtlPortletImageModel> select() throws SQLException {
PreparedStatement pstmt = null;
ResultSet rs = null;
Connection con = null;
ArrayList<PtlPortletImageModel> ret = new ArrayList<PtlPortletImageModel>();
con = getCon();
try {
// SQL文
SqlBuffer sql = new SqlBuffer();
sql.addSql(" select ");
sql.addSql(" PLT_SID,");
sql.addSql(" PLI_SID,");
sql.addSql(" BIN_SID,");
sql.addSql(" PLI_NAME");
sql.addSql(" from ");
sql.addSql(" PTL_PORTLET_IMAGE");
pstmt = con.prepareStatement(sql.toSqlString());
log__.info(sql.toLogString());
rs = pstmt.executeQuery();
while (rs.next()) {
ret.add(__getPtlPortletImageFromRs(rs));
}
} catch (SQLException e) {
throw e;
} finally {
JDBCUtil.closeResultSet(rs);
JDBCUtil.closeStatement(pstmt);
}
return ret;
}
/**
* Select NTP_AN_MEMBER All Data
*
* @return List in NTP_AN_MEMBERModel
* @throws SQLException SQL実行例外
*/
public List<NtpAnMemberModel> select() throws SQLException {
PreparedStatement pstmt = null;
ResultSet rs = null;
Connection con = null;
List<NtpAnMemberModel> ret = new ArrayList<NtpAnMemberModel>();
con = getCon();
try {
// SQL文
SqlBuffer sql = new SqlBuffer();
sql.addSql(" select ");
sql.addSql(" NAN_SID,");
sql.addSql(" USR_SID,");
sql.addSql(" NAM_AUID,");
sql.addSql(" NAM_ADATE,");
sql.addSql(" NAM_EUID,");
sql.addSql(" NAM_EDATE");
sql.addSql(" from ");
sql.addSql(" NTP_AN_MEMBER");
pstmt = con.prepareStatement(sql.toSqlString());
log__.info(sql.toLogString());
rs = pstmt.executeQuery();
while (rs.next()) {
ret.add(__getNtpAnShohinFromRs(rs));
}
} catch (SQLException e) {
throw e;
} finally {
JDBCUtil.closeResultSet(rs);
JDBCUtil.closeStatement(pstmt);
}
return ret;
}
/**
* <br>
* [機 能] レコード件数を取得する <br>
* [解 説] <br>
* [備 考]
*
* @return 件数
* @throws SQLException SQL実行例外
*/
public int selectCount() throws SQLException {
PreparedStatement pstmt = null;
ResultSet rs = null;
Connection con = null;
int count = 0;
con = getCon();
try {
// SQL文
SqlBuffer sql = new SqlBuffer();
sql.addSql(" select ");
sql.addSql(" count(*) as CNT");
sql.addSql(" from ");
sql.addSql(" CIR_INIT");
pstmt = con.prepareStatement(sql.toSqlString());
log__.info(sql.toLogString());
rs = pstmt.executeQuery();
if (rs.next()) {
count = rs.getInt("CNT");
}
} catch (SQLException e) {
throw e;
} finally {
JDBCUtil.closeResultSet(rs);
JDBCUtil.closeStatement(pstmt);
}
return count;
}
/**
* Select TCD_TCDATA
*
* @param bean TCD_TCDATA Model
* @return TCD_TCDATAModel
* @throws SQLException SQL実行例外
*/
public TcdTcdataModel select(TcdTcdataModel bean) throws SQLException {
PreparedStatement pstmt = null;
ResultSet rs = null;
Connection con = null;
TcdTcdataModel ret = null;
con = getCon();
try {
// SQL文
SqlBuffer sql = new SqlBuffer();
sql.addSql(" select");
sql.addSql(" USR_SID,");
sql.addSql(" TCD_DATE,");
sql.addSql(" TCD_INTIME,");
sql.addSql(" TCD_OUTTIME,");
sql.addSql(" TCD_STRIKE_INTIME,");
sql.addSql(" TCD_STRIKE_OUTTIME,");
sql.addSql(" TCD_BIKO,");
sql.addSql(" TCD_STATUS,");
sql.addSql(" TCD_HOLKBN,");
sql.addSql(" TCD_HOLOTHER,");
sql.addSql(" TCD_HOLCNT,");
sql.addSql(" TCD_CHKKBN,");
sql.addSql(" TCD_SOUKBN,");
sql.addSql(" TCD_LOCK_FLG,");
sql.addSql(" TCD_AUID,");
sql.addSql(" TCD_ADATE,");
sql.addSql(" TCD_EUID,");
sql.addSql(" TCD_EDATE,");
// 休日関連を追加
sql.addSql(" HOL_NAME,");
sql.addSql(" HOL_KBN");
sql.addSql(" from ");
sql.addSql(" TCD_TCDATA left join CMN_HOLIDAY");
sql.addSql(" on TCD_TCDATA.TCD_DATE = CMN_HOLIDAY.HOL_DATE");
sql.addSql(" where ");
sql.addSql(" USR_SID=?");
sql.addSql(" and");
sql.addSql(" TCD_DATE=?");
pstmt = con.prepareStatement(sql.toSqlString());
sql.addIntValue(bean.getUsrSid());
sql.addDateValue(bean.getTcdDate());
log__.info(sql.toLogString());
sql.setParameter(pstmt);
rs = pstmt.executeQuery();
if (rs.next()) {
ret = __getTcdTcdataFromRs(rs);
}
} catch (SQLException e) {
throw e;
} finally {
JDBCUtil.closeResultSet(rs);
JDBCUtil.closeStatement(pstmt);
}
return ret;
}
/**
* <br>
* [機 能] 指定されたユーザの指定URLブックマーク情報を取得する。 <br>
* [解 説] <br>
* [備 考]
*
* @param bmuSid URLSID
* @param usrSid ユーザSID
* @return BmkBookmarkModel
* @throws SQLException SQL実行例外
*/
public BmkBookmarkModel getUsrBookmark(int bmuSid, int usrSid) throws SQLException {
PreparedStatement pstmt = null;
ResultSet rs = null;
Connection con = null;
BmkBookmarkModel ret = null;
con = getCon();
try {
// SQL文
SqlBuffer sql = new SqlBuffer();
sql.addSql(" select");
sql.addSql(" BMK_SID,");
sql.addSql(" BMK_KBN,");
sql.addSql(" USR_SID,");
sql.addSql(" GRP_SID,");
sql.addSql(" BMU_SID,");
sql.addSql(" BMK_TITLE,");
sql.addSql(" BMK_CMT,");
sql.addSql(" BMK_SCORE,");
sql.addSql(" BMK_PUBLIC,");
sql.addSql(" BMK_MAIN,");
sql.addSql(" BMK_SORT,");
sql.addSql(" BMK_AUID,");
sql.addSql(" BMK_ADATE,");
sql.addSql(" BMK_EUID,");
sql.addSql(" BMK_EDATE");
sql.addSql(" from");
sql.addSql(" BMK_BOOKMARK");
sql.addSql(" where");
sql.addSql(" USR_SID=?");
sql.addSql(" and");
sql.addSql(" BMU_SID=?");
sql.addIntValue(usrSid);
sql.addIntValue(bmuSid);
pstmt = con.prepareStatement(sql.toSqlString());
log__.info(sql.toLogString());
sql.setParameter(pstmt);
rs = pstmt.executeQuery();
if (rs.next()) {
ret = __getBmkBookmarkFromRs(rs);
}
} catch (SQLException e) {
throw e;
} finally {
JDBCUtil.closeResultSet(rs);
JDBCUtil.closeStatement(pstmt);
}
return ret;
}
/**
* Select TCD_TCDATA All Data
*
* @param usrSid ユーザSID
* @return List in TCD_TCDATAModel
* @throws SQLException SQL実行例外
*/
public List<TcdTcdataModel> select(int usrSid) throws SQLException {
PreparedStatement pstmt = null;
ResultSet rs = null;
Connection con = null;
ArrayList<TcdTcdataModel> ret = new ArrayList<TcdTcdataModel>();
con = getCon();
try {
// SQL文
SqlBuffer sql = new SqlBuffer();
sql.addSql(" select ");
sql.addSql(" USR_SID,");
sql.addSql(" TCD_DATE,");
sql.addSql(" TCD_INTIME,");
sql.addSql(" TCD_OUTTIME,");
sql.addSql(" TCD_STRIKE_INTIME,");
sql.addSql(" TCD_STRIKE_OUTTIME,");
sql.addSql(" TCD_BIKO,");
sql.addSql(" TCD_STATUS,");
sql.addSql(" TCD_HOLKBN,");
sql.addSql(" TCD_HOLOTHER,");
sql.addSql(" TCD_HOLCNT,");
sql.addSql(" TCD_CHKKBN,");
sql.addSql(" TCD_SOUKBN,");
sql.addSql(" TCD_LOCK_FLG,");
sql.addSql(" TCD_AUID,");
sql.addSql(" TCD_ADATE,");
sql.addSql(" TCD_EUID,");
sql.addSql(" TCD_EDATE");
sql.addSql(" from ");
sql.addSql(" TCD_TCDATA");
sql.addSql(" where ");
sql.addSql(" USR_SID=?");
pstmt = con.prepareStatement(sql.toSqlString());
sql.addIntValue(usrSid);
sql.setParameter(pstmt);
log__.info(sql.toLogString());
rs = pstmt.executeQuery();
while (rs.next()) {
ret.add(__getTcdTcdataFromRs(rs));
}
} catch (SQLException e) {
throw e;
} finally {
JDBCUtil.closeResultSet(rs);
JDBCUtil.closeStatement(pstmt);
}
return ret;
}
/**
* ユーザーが所属するグループで<br>
* デフォルトグループのグループSIDを返します。
*
* @param usid ユーザーSID
* @return List in CMN_BELONGMModel
* @throws SQLException SQL実行例外
*/
public int selectUserBelongGroupDef(int usid) throws SQLException {
PreparedStatement pstmt = null;
ResultSet rs = null;
Connection con = null;
int ret = 0;
con = getCon();
try {
// SQL文
SqlBuffer sql = new SqlBuffer();
sql.addSql(" select");
sql.addSql(" GRP_SID,");
sql.addSql(" USR_SID,");
sql.addSql(" BEG_AUID,");
sql.addSql(" BEG_ADATE,");
sql.addSql(" BEG_EUID,");
sql.addSql(" BEG_EDATE,");
sql.addSql(" BEG_DEFGRP,");
sql.addSql(" BEG_GRPKBN");
sql.addSql(" from");
sql.addSql(" CMN_BELONGM");
sql.addSql(" where ");
sql.addSql(" USR_SID=?");
sql.addSql(" AND ");
sql.addSql(" BEG_DEFGRP=1");
pstmt = con.prepareStatement(sql.toSqlString());
sql.addIntValue(usid);
log__.info(sql.toLogString());
sql.setParameter(pstmt);
rs = pstmt.executeQuery();
if (rs.next()) {
CmnBelongmModel model = __getCmnBelongmFromRs(rs);
ret = model.getGrpSid();
log__.debug("BEG_DEFGRP=1 :" + ret);
}
} catch (SQLException e) {
throw e;
} finally {
JDBCUtil.closeResultSet(rs);
JDBCUtil.closeStatement(pstmt);
}
return ret;
}
/**
* Select NTP_ADM_CONF All Data
*
* @return List in NTP_ADM_CONFModel
* @throws SQLException SQL実行例外
*/
public NtpAdmConfModel select() throws SQLException {
PreparedStatement pstmt = null;
ResultSet rs = null;
Connection con = null;
NtpAdmConfModel ret = null;
con = getCon();
try {
// SQL文
SqlBuffer sql = new SqlBuffer();
sql.addSql(" select ");
sql.addSql(" NAC_CRANGE,");
sql.addSql(" NAC_ATDEL_FLG,");
sql.addSql(" NAC_ATDEL_Y,");
sql.addSql(" NAC_ATDEL_M,");
sql.addSql(" NAC_HOUR_DIV,");
sql.addSql(" NAC_SML_KBN,");
sql.addSql(" NAC_SML_NOTICE_KBN,");
sql.addSql(" NAC_SML_NOTICE_GRP,");
sql.addSql(" NAC_CSML_KBN,");
sql.addSql(" NAC_CSML_NOTICE_KBN,");
sql.addSql(" NAC_GSML_KBN,");
sql.addSql(" NAC_GSML_NOTICE_KBN,");
sql.addSql(" NAC_AUID,");
sql.addSql(" NAC_ADATE,");
sql.addSql(" NAC_EUID,");
sql.addSql(" NAC_EDATE");
sql.addSql(" from ");
sql.addSql(" NTP_ADM_CONF");
pstmt = con.prepareStatement(sql.toSqlString());
log__.info(sql.toLogString());
rs = pstmt.executeQuery();
while (rs.next()) {
ret = __getNtpAdminConfiFromRs(rs);
}
} catch (SQLException e) {
throw e;
} finally {
JDBCUtil.closeResultSet(rs);
JDBCUtil.closeStatement(pstmt);
}
return ret;
}
/**
* CMN_BELONGMの存在チェックを行います
*
* @param usid ユーザSID
* @param gsid グループSID
* @return boolean true: 存在 false: 非存在
* @throws SQLException SQL実行例外
*/
public boolean isExist(int gsid, int usid) throws SQLException {
PreparedStatement pstmt = null;
ResultSet rs = null;
Connection con = null;
boolean ret = false;
con = getCon();
try {
// SQL文
SqlBuffer sql = new SqlBuffer();
sql.addSql(" select");
sql.addSql(" count (*) as cnt");
sql.addSql(" from");
sql.addSql(" CMN_BELONGM");
sql.addSql(" where ");
sql.addSql(" GRP_SID=?");
sql.addSql(" and");
sql.addSql(" USR_SID=?");
pstmt = con.prepareStatement(sql.toSqlString());
sql.addIntValue(gsid);
sql.addIntValue(usid);
log__.info(sql.toLogString());
sql.setParameter(pstmt);
rs = pstmt.executeQuery();
int count = 0;
if (rs.next()) {
count = rs.getInt("cnt");
}
if (count > 0) {
ret = true;
}
log__.debug("===>getInt :" + rs.getInt("cnt"));
} catch (SQLException e) {
throw e;
} finally {
JDBCUtil.closeResultSet(rs);
JDBCUtil.closeStatement(pstmt);
}
return ret;
}
/**
* <br>
* [機 能] カテゴリー下のラベル情報一覧を取得 <br>
* [解 説] <br>
* [備 考]
*
* @param sid 選択カテゴリSID
* @return ラベル情報一覧
* @throws SQLException SQL実行例外
*/
public ArrayList<CmnLabelUsrModel> select(int sid) throws SQLException {
PreparedStatement pstmt = null;
ResultSet rs = null;
ArrayList<CmnLabelUsrModel> ret = new ArrayList<CmnLabelUsrModel>();
Connection con = null;
con = getCon();
try {
// SQL文
SqlBuffer sql = new SqlBuffer();
sql.addSql(" select ");
sql.addSql(" LAB_SID,");
sql.addSql(" LAB_NAME,");
sql.addSql(" LUC_SID,");
sql.addSql(" LAB_BIKO,");
sql.addSql(" LAB_ADATE,");
sql.addSql(" LAB_AUID,");
sql.addSql(" LAB_EDATE,");
sql.addSql(" LAB_EUID,");
sql.addSql(" LAB_SORT");
sql.addSql(" from ");
sql.addSql(" CMN_LABEL_USR");
sql.addSql(" where ");
sql.addSql(" LUC_SID=?");
sql.addSql(" order by");
sql.addSql(" LAB_SORT");
sql.addIntValue(sid);
pstmt = con.prepareStatement(sql.toSqlString());
sql.setParameter(pstmt);
log__.info(sql.toLogString());
rs = pstmt.executeQuery();
while (rs.next()) {
ret.add(__getCmnLabelUsrFormRs(rs));
}
} catch (SQLException e) {
throw e;
} finally {
JDBCUtil.closeResultSet(rs);
JDBCUtil.closeStatement(pstmt);
}
return ret;
}
/**
* 画像一覧を取得する。
*
* @param pltSid PLT_SID
* @return PTL_PORTLET_IMAGEModel
* @throws SQLException SQL実行例外
*/
public List<PtlPortletImageModel> getImageList(int pltSid) throws SQLException {
PreparedStatement pstmt = null;
ResultSet rs = null;
Connection con = null;
List<PtlPortletImageModel> ret = new ArrayList<PtlPortletImageModel>();
con = getCon();
try {
// SQL文
SqlBuffer sql = new SqlBuffer();
sql.addSql(" select");
sql.addSql(" PTL_PORTLET_IMAGE.PLT_SID,");
sql.addSql(" PTL_PORTLET_IMAGE.PLI_SID,");
sql.addSql(" PTL_PORTLET_IMAGE.BIN_SID,");
sql.addSql(" PTL_PORTLET_IMAGE.PLI_NAME");
sql.addSql(" from");
sql.addSql(" PTL_PORTLET_IMAGE,");
sql.addSql(" CMN_BINF");
sql.addSql(" where ");
sql.addSql(" PTL_PORTLET_IMAGE.BIN_SID=CMN_BINF.BIN_SID");
sql.addSql(" and");
sql.addSql(" PTL_PORTLET_IMAGE.PLT_SID=?");
sql.addSql(" and");
sql.addSql(" CMN_BINF.BIN_JKBN=?");
pstmt = con.prepareStatement(sql.toSqlString());
sql.addIntValue(pltSid);
sql.addIntValue(GSConst.JTKBN_TOROKU);
log__.info(sql.toLogString());
sql.setParameter(pstmt);
rs = pstmt.executeQuery();
while (rs.next()) {
ret.add(__getPtlPortletImageFromRs(rs));
}
} catch (SQLException e) {
throw e;
} finally {
JDBCUtil.closeResultSet(rs);
JDBCUtil.closeStatement(pstmt);
}
return ret;
}
/**
* Select BMK_BOOKMARK All Data
*
* @return List in BMK_BOOKMARKModel
* @throws SQLException SQL実行例外
*/
public List<BmkBookmarkModel> select() throws SQLException {
PreparedStatement pstmt = null;
ResultSet rs = null;
Connection con = null;
ArrayList<BmkBookmarkModel> ret = new ArrayList<BmkBookmarkModel>();
con = getCon();
try {
// SQL文
SqlBuffer sql = new SqlBuffer();
sql.addSql(" select ");
sql.addSql(" BMK_SID,");
sql.addSql(" BMK_KBN,");
sql.addSql(" USR_SID,");
sql.addSql(" GRP_SID,");
sql.addSql(" BMU_SID,");
sql.addSql(" BMK_TITLE,");
sql.addSql(" BMK_CMT,");
sql.addSql(" BMK_SCORE,");
sql.addSql(" BMK_PUBLIC,");
sql.addSql(" BMK_MAIN,");
sql.addSql(" BMK_SORT,");
sql.addSql(" BMK_AUID,");
sql.addSql(" BMK_ADATE,");
sql.addSql(" BMK_EUID,");
sql.addSql(" BMK_EDATE");
sql.addSql(" from ");
sql.addSql(" BMK_BOOKMARK");
pstmt = con.prepareStatement(sql.toSqlString());
log__.info(sql.toLogString());
rs = pstmt.executeQuery();
while (rs.next()) {
ret.add(__getBmkBookmarkFromRs(rs));
}
} catch (SQLException e) {
throw e;
} finally {
JDBCUtil.closeResultSet(rs);
JDBCUtil.closeStatement(pstmt);
}
return ret;
}
/**
* <br>
* [機 能]管理者の設定による初期値取得する。。 <br>
* [解 説] <br>
* [備 考]
*
* @throws SQLException SQL実行例外
* @return ret CirInitModel
* @author JTS
*/
public CirInitModel select() throws SQLException {
PreparedStatement pstmt = null;
ResultSet rs = null;
Connection con = null;
con = getCon();
CirInitModel ret = null;
try {
SqlBuffer sql = new SqlBuffer();
sql.addSql("select");
sql.addSql(" CIN_INITSET_KEN,");
sql.addSql(" CIN_MEMO_KBN,");
sql.addSql(" CIN_MEMO_DAY,");
sql.addSql(" CIN_KOU_KBN,");
sql.addSql(" CIN_ACNT_MAKE,");
sql.addSql(" CIN_AUTO_DEL_KBN,");
sql.addSql(" CIN_ACNT_USER,");
sql.addSql(" CIN_AUID,");
sql.addSql(" CIN_ADATE,");
sql.addSql(" CIN_EUID,");
sql.addSql(" CIN_EDATE ");
sql.addSql(" from");
sql.addSql(" CIR_INIT");
log__.info(sql.toLogString());
pstmt = con.prepareStatement(sql.toSqlString());
sql.setParameter(pstmt);
rs = pstmt.executeQuery();
while (rs.next()) {
ret = __getCirInitFromRs(rs);
}
} catch (SQLException e) {
throw e;
} finally {
JDBCUtil.closeResultSet(rs);
JDBCUtil.closeStatement(pstmt);
}
return ret;
}
/**
* ユーザーが所属するグループのモデルリストを返却します
*
* @param usid ユーザーSID
* @return List in CMN_BELONGMModel
* @throws SQLException SQL実行例外
*/
public List<CmnBelongmModel> selectUserBelongGroup(int usid) throws SQLException {
PreparedStatement pstmt = null;
ResultSet rs = null;
Connection con = null;
ArrayList<CmnBelongmModel> ret = new ArrayList<CmnBelongmModel>();
con = getCon();
try {
// SQL文
SqlBuffer sql = new SqlBuffer();
sql.addSql(" select");
sql.addSql(" GRP_SID,");
sql.addSql(" USR_SID,");
sql.addSql(" BEG_AUID,");
sql.addSql(" BEG_ADATE,");
sql.addSql(" BEG_EUID,");
sql.addSql(" BEG_EDATE,");
sql.addSql(" BEG_DEFGRP,");
sql.addSql(" BEG_GRPKBN");
sql.addSql(" from");
sql.addSql(" CMN_BELONGM");
sql.addSql(" where ");
sql.addSql(" USR_SID=?");
pstmt = con.prepareStatement(sql.toSqlString());
sql.addIntValue(usid);
log__.info(sql.toLogString());
sql.setParameter(pstmt);
rs = pstmt.executeQuery();
while (rs.next()) {
ret.add(__getCmnBelongmFromRs(rs));
}
} catch (SQLException e) {
throw e;
} finally {
JDBCUtil.closeResultSet(rs);
JDBCUtil.closeStatement(pstmt);
}
return ret;
}
/**
* Select NTP_AN_MEMBER
*
* @param nanSid NAN_SID
* @return NTP_AN_MEMBERModel
* @throws SQLException SQL実行例外
*/
public String[] select(int nanSid) throws SQLException {
PreparedStatement pstmt = null;
ResultSet rs = null;
Connection con = null;
ArrayList<String> ret = new ArrayList<String>();
con = getCon();
try {
// SQL文
SqlBuffer sql = new SqlBuffer();
sql.addSql(" select");
sql.addSql(" distinct USR_SID");
sql.addSql(" from");
sql.addSql(" NTP_AN_MEMBER");
sql.addSql(" where ");
sql.addSql(" NAN_SID=?");
sql.addSql(" union");
sql.addSql(" select");
sql.addSql(" distinct USR_SID");
sql.addSql(" from");
sql.addSql(" NTP_AN_MEMBER_HISTORY");
sql.addSql(" where ");
sql.addSql(" NAN_SID=?");
pstmt = con.prepareStatement(sql.toSqlString());
sql.addIntValue(nanSid);
sql.addIntValue(nanSid);
log__.info(sql.toLogString());
sql.setParameter(pstmt);
rs = pstmt.executeQuery();
while (rs.next()) {
ret.add(Integer.toString(rs.getInt("USR_SID")));
}
} catch (SQLException e) {
throw e;
} finally {
JDBCUtil.closeResultSet(rs);
JDBCUtil.closeStatement(pstmt);
}
return ret.toArray(new String[ret.size()]);
}
/**
* グループに所属するユーザのSIDを取得する
*
* @param gsids グループSID
* @return CMN_BELONGMModel
* @throws SQLException SQL実行例外
*/
public List<String> select(String[] gsids) throws SQLException {
PreparedStatement pstmt = null;
ResultSet rs = null;
Connection con = null;
List<String> ret = new ArrayList<String>();
con = getCon();
try {
// SQL文
SqlBuffer sql = new SqlBuffer();
sql.addSql(" select");
sql.addSql(" distinct USR_SID");
sql.addSql(" from");
sql.addSql(" CMN_BELONGM");
if (gsids != null && gsids.length > 0) {
sql.addSql(" where ");
sql.addSql(" GRP_SID in (");
for (int i = 0; i < gsids.length; i++) {
if (i > 0) {
sql.addSql(" ,");
}
sql.addSql(gsids[i]);
}
sql.addSql(")");
}
pstmt = con.prepareStatement(sql.toSqlString());
log__.info(sql.toLogString());
sql.setParameter(pstmt);
rs = pstmt.executeQuery();
while (rs.next()) {
ret.add(String.valueOf(rs.getInt("USR_SID")));
}
} catch (SQLException e) {
throw e;
} finally {
JDBCUtil.closeResultSet(rs);
JDBCUtil.closeStatement(pstmt);
}
return ret;
}
/**
* Select NTP_AN_MEMBER
*
* @param nanSid NAN_SID
* @param nhnSid USR_SID
* @return NTP_AN_MEMBERModel
* @throws SQLException SQL実行例外
*/
public NtpAnMemberModel select(int nanSid, int nhnSid) throws SQLException {
PreparedStatement pstmt = null;
ResultSet rs = null;
Connection con = null;
NtpAnMemberModel ret = null;
con = getCon();
try {
// SQL文
SqlBuffer sql = new SqlBuffer();
sql.addSql(" select");
sql.addSql(" NAN_SID,");
sql.addSql(" USR_SID,");
sql.addSql(" NAM_AUID,");
sql.addSql(" NAM_ADATE,");
sql.addSql(" NAM_EUID,");
sql.addSql(" NAM_EDATE");
sql.addSql(" from");
sql.addSql(" NTP_AN_MEMBER");
sql.addSql(" where ");
sql.addSql(" NAN_SID=?");
sql.addSql(" and");
sql.addSql(" USR_SID=?");
pstmt = con.prepareStatement(sql.toSqlString());
sql.addIntValue(nanSid);
sql.addIntValue(nhnSid);
log__.info(sql.toLogString());
sql.setParameter(pstmt);
rs = pstmt.executeQuery();
if (rs.next()) {
ret = __getNtpAnShohinFromRs(rs);
}
} catch (SQLException e) {
throw e;
} finally {
JDBCUtil.closeResultSet(rs);
JDBCUtil.closeStatement(pstmt);
}
return ret;
}
/**
* <br>
* [機 能] 指定SIDから送信制限設定名を取得 <br>
* [解 説] <br>
* [備 考]
*
* @param sbcSids 取得対象SID
* @return 検索結果
* @throws SQLException SQL実行時例外
*/
public List<String> getSbcNameList(String[] sbcSids) throws SQLException {
PreparedStatement pstmt = null;
ResultSet rs = null;
Connection con = null;
ArrayList<String> ret = new ArrayList<String>();
con = getCon();
if (sbcSids == null || sbcSids.length == 0) {
return ret;
}
try {
// SQL文
SqlBuffer sql = new SqlBuffer();
sql.addSql(" select ");
sql.addSql(" SBC_SID,");
sql.addSql(" SBC_NAME");
sql.addSql(" from ");
sql.addSql(" SML_BAN_DEST_CONF");
sql.addSql(" where ");
sql.addSql(" SBC_SID in (");
for (int i = 0; i < sbcSids.length; i++) {
String sid = sbcSids[i];
if (i > 0) {
sql.addSql(" , ");
}
sql.addSql(sid);
}
sql.addSql(" )");
pstmt = con.prepareStatement(sql.toSqlString());
log__.info(sql.toLogString());
rs = pstmt.executeQuery();
while (rs.next()) {
ret.add(rs.getString("SBC_NAME"));
}
} catch (SQLException e) {
throw e;
} finally {
JDBCUtil.closeResultSet(rs);
JDBCUtil.closeStatement(pstmt);
}
return ret;
}
/**
* <br>
* [機 能] 指定したアンケートの対象人数を取得する <br>
* [解 説] <br>
* [備 考]
*
* @param emnSid アンケートSID
* @return 対象者件数
* @throws SQLException SQL実行時例外
*/
public int[] getAnswerCount(long emnSid) throws SQLException {
PreparedStatement pstmt = null;
ResultSet rs = null;
Connection con = null;
int[] result = new int[3];
con = getCon();
try {
// SQL文
SqlBuffer sql = new SqlBuffer();
sql.addSql(" select ");
sql.addSql(" count(EMN_SID) as ALLCNT,");
sql.addSql(" count(case when EAM_STS_KBN = ? then 1 else null end) as ANSCNT,");
sql.addSql(" count(case when EAM_STS_KBN = ? then 1 else null end) as NOANSCNT");
sql.addSql(" from");
sql.addSql(" ENQ_ANS_MAIN");
sql.addSql(" where");
sql.addSql(" ENQ_ANS_MAIN.EMN_SID = ?");
sql.addIntValue(GSConstEnquete.EAM_STS_KBN_YES);
sql.addIntValue(GSConstEnquete.EAM_STS_KBN_NO);
sql.addLongValue(emnSid);
log__.info(sql.toLogString());
pstmt = con.prepareStatement(sql.toSqlString());
sql.setParameter(pstmt);
rs = pstmt.executeQuery();
if (rs.next()) {
result[0] = rs.getInt("ALLCNT");
result[1] = rs.getInt("ANSCNT");
result[2] = rs.getInt("NOANSCNT");
}
} catch (SQLException e) {
throw e;
} finally {
JDBCUtil.closeResultSet(rs);
JDBCUtil.closeStatement(pstmt);
}
return result;
}
/**
* Select PTL_PORTLET_IMAGE
*
* @param pltSid PLT_SID
* @param pliSid PLI_SID
* @return PTL_PORTLET_IMAGEModel
* @throws SQLException SQL実行例外
*/
public PtlPortletImageModel select(int pltSid, long pliSid) throws SQLException {
PreparedStatement pstmt = null;
ResultSet rs = null;
Connection con = null;
PtlPortletImageModel ret = null;
con = getCon();
try {
// SQL文
SqlBuffer sql = new SqlBuffer();
sql.addSql(" select");
sql.addSql(" PLT_SID,");
sql.addSql(" PLI_SID,");
sql.addSql(" BIN_SID,");
sql.addSql(" PLI_NAME");
sql.addSql(" from");
sql.addSql(" PTL_PORTLET_IMAGE");
sql.addSql(" where ");
sql.addSql(" PLT_SID=?");
sql.addSql(" and");
sql.addSql(" PLI_SID=?");
pstmt = con.prepareStatement(sql.toSqlString());
sql.addIntValue(pltSid);
sql.addLongValue(pliSid);
log__.info(sql.toLogString());
sql.setParameter(pstmt);
rs = pstmt.executeQuery();
if (rs.next()) {
ret = __getPtlPortletImageFromRs(rs);
}
} catch (SQLException e) {
throw e;
} finally {
JDBCUtil.closeResultSet(rs);
JDBCUtil.closeStatement(pstmt);
}
return ret;
}
/**
* Select SML_BAN_DEST_CONF
*
* @param sbcSid SBC_SID
* @return SML_BAN_DEST_CONFModel
* @throws SQLException SQL実行例外
*/
public SmlBanDestConfModel select(int sbcSid) throws SQLException {
PreparedStatement pstmt = null;
ResultSet rs = null;
Connection con = null;
SmlBanDestConfModel ret = null;
con = getCon();
try {
// SQL文
SqlBuffer sql = new SqlBuffer();
sql.addSql(" select");
sql.addSql(" SBC_SID,");
sql.addSql(" SBC_NAME,");
sql.addSql(" SBC_BIKO,");
sql.addSql(" SBC_AUID,");
sql.addSql(" SBC_ADATE,");
sql.addSql(" SBC_EUID,");
sql.addSql(" SBC_EDATE");
sql.addSql(" from");
sql.addSql(" SML_BAN_DEST_CONF");
sql.addSql(" where ");
sql.addSql(" SBC_SID=?");
pstmt = con.prepareStatement(sql.toSqlString());
sql.addIntValue(sbcSid);
log__.info(sql.toLogString());
sql.setParameter(pstmt);
rs = pstmt.executeQuery();
if (rs.next()) {
ret = __getSmlBanDestConfFromRs(rs);
}
} catch (SQLException e) {
throw e;
} finally {
JDBCUtil.closeResultSet(rs);
JDBCUtil.closeStatement(pstmt);
}
return ret;
}
/**
* <br>
* [機 能] 指定ソート順以上のソート順のラベルSID取得 <br>
* [解 説] <br>
* [備 考]
*
* @param sortNum ソート順
* @param lucSid カテゴリSID
* @return ラベルSID
* @throws SQLException SQL実行例外
*/
public ArrayList<Integer> selectLabelSort(int sortNum, int lucSid) throws SQLException {
PreparedStatement pstmt = null;
ResultSet rs = null;
ArrayList<Integer> list = new ArrayList<Integer>();
Connection con = null;
con = getCon();
try {
// SQL文
SqlBuffer sql = new SqlBuffer();
sql.addSql(" select ");
sql.addSql(" LAB_SID");
sql.addSql(" from ");
sql.addSql(" CMN_LABEL_USR");
sql.addSql(" where");
sql.addSql(" LUC_SID = ?");
sql.addSql(" and");
sql.addSql(" LAB_SORT > ?");
sql.addIntValue(lucSid);
sql.addIntValue(sortNum);
log__.info(sql.toLogString());
pstmt = con.prepareStatement(sql.toSqlString());
sql.setParameter(pstmt);
rs = pstmt.executeQuery();
while (rs.next()) {
list.add(rs.getInt("LAB_SID"));
}
} catch (SQLException e) {
throw e;
} finally {
JDBCUtil.closeResultSet(rs);
JDBCUtil.closeStatement(pstmt);
}
return list;
}
/**
* <br>
* [機 能] 検索結果の件数取得 <br>
* [解 説] <br>
* [備 考]
*
* @param searchMdl 検索パラメータ
* @return 検索結果件数
* @throws SQLException SQL実行時例外
*/
public int recordCount(Sml380SearchModel searchMdl) throws SQLException {
PreparedStatement pstmt = null;
ResultSet rs = null;
Connection con = null;
int ret = 0;
con = getCon();
try {
// SQL文
SqlBuffer sql = new SqlBuffer();
sql.addSql(" select ");
sql.addSql(" count(SBC_SID) as CNT");
sql.addSql(" from ");
sql.addSql(" SML_BAN_DEST_CONF");
if (!StringUtil.isNullZeroString(searchMdl.getKeyword())) {
sql.addSql(" where ");
sql.addSql(" SBC_NAME ");
String value =
" like '%"
+ JDBCUtil.encFullStringLike(searchMdl.getKeyword())
+ "%' ESCAPE '"
+ JDBCUtil.def_esc
+ "'";
sql.addSql(value);
}
pstmt = con.prepareStatement(sql.toSqlString());
log__.info(sql.toLogString());
rs = pstmt.executeQuery();
if (rs.next()) {
ret = rs.getInt("CNT");
}
} catch (SQLException e) {
throw e;
} finally {
JDBCUtil.closeResultSet(rs);
JDBCUtil.closeStatement(pstmt);
}
return ret;
}
/**
* <br>
* [機 能] カテゴリ内のソート順の最大値を取得する <br>
* [解 説] <br>
* [備 考]
*
* @param sid カテゴリSID
* @return int ソート順の最大値
* @throws SQLException SQL実行例外
*/
public int getSortMax(int sid) throws SQLException {
PreparedStatement pstmt = null;
ResultSet rs = null;
Connection con = getCon();
int ret = 0;
try {
// SQL文
SqlBuffer sql = new SqlBuffer();
sql.addSql(" select");
sql.addSql(" max(LAB_SORT) as MAX");
sql.addSql(" from");
sql.addSql(" CMN_LABEL_USR");
sql.addSql(" where");
sql.addSql(" LUC_SID=?");
sql.addIntValue(sid);
log__.info(sql.toLogString());
pstmt = con.prepareStatement(sql.toSqlString());
sql.setParameter(pstmt);
rs = pstmt.executeQuery();
if (rs.next()) {
ret = rs.getInt("MAX");
} else {
ret = 0;
}
} catch (SQLException e) {
throw e;
} finally {
JDBCUtil.closeResultSet(rs);
JDBCUtil.closeStatement(pstmt);
}
return ret;
}
/**
* <br>
* [機 能] カテゴリ内のラベルソート順を整理する <br>
* [解 説] <br>
* [備 考]
*
* @param sid ラベルSID
* @throws SQLException SQL実行例外
*/
public void sortArrange(int sid) throws SQLException {
PreparedStatement pstmt = null;
ResultSet rs = null;
Connection con = getCon();
try {
// SQL文
SqlBuffer sql = new SqlBuffer();
sql.addSql(" update");
sql.addSql(" CMN_LABEL_USR");
sql.addSql(" set");
sql.addSql(" LAB_SORT=");
sql.addSql(" (select LAB_SORT ");
sql.addSql(" from");
sql.addSql(" CMN_LABEL_USR ");
sql.addSql(" where ");
sql.addSql(" LAB_SID=?)-1");
sql.addSql(" where");
sql.addSql(" LAB_SID=?");
sql.addIntValue(sid);
sql.addIntValue(sid);
log__.info(sql.toLogString());
pstmt = con.prepareStatement(sql.toSqlString());
sql.setParameter(pstmt);
pstmt.executeUpdate();
} catch (SQLException e) {
throw e;
} finally {
JDBCUtil.closeResultSet(rs);
JDBCUtil.closeStatement(pstmt);
}
}
/**
* 指定グループに所属するユーザリストを返却します
*
* @param gsid グループSID
* @return List in CMN_BELONGMModel
* @throws SQLException SQL実行例外
*/
public ArrayList<Integer> selectBelongUserSid(int gsid) throws SQLException {
PreparedStatement pstmt = null;
ResultSet rs = null;
Connection con = null;
ArrayList<Integer> ret = new ArrayList<Integer>();
con = getCon();
try {
// SQL文
SqlBuffer sql = new SqlBuffer();
sql.addSql(" select");
sql.addSql(" USR_SID");
sql.addSql(" from");
sql.addSql(" CMN_BELONGM");
sql.addSql(" where ");
sql.addSql(" GRP_SID=?");
pstmt = con.prepareStatement(sql.toSqlString());
sql.addIntValue(gsid);
log__.info(sql.toLogString());
sql.setParameter(pstmt);
rs = pstmt.executeQuery();
while (rs.next()) {
ret.add(new Integer(rs.getInt("USR_SID")));
}
} catch (SQLException e) {
throw e;
} finally {
JDBCUtil.closeResultSet(rs);
JDBCUtil.closeStatement(pstmt);
}
return ret;
}
/**
* Select SML_BAN_DEST_CONF All Data
*
* @return List in SML_BAN_DEST_CONFModel
* @throws SQLException SQL実行例外
*/
public List<SmlBanDestConfModel> select() throws SQLException {
PreparedStatement pstmt = null;
ResultSet rs = null;
Connection con = null;
ArrayList<SmlBanDestConfModel> ret = new ArrayList<SmlBanDestConfModel>();
con = getCon();
try {
// SQL文
SqlBuffer sql = new SqlBuffer();
sql.addSql(" select ");
sql.addSql(" SBC_SID,");
sql.addSql(" SBC_NAME,");
sql.addSql(" SBC_BIKO,");
sql.addSql(" SBC_AUID,");
sql.addSql(" SBC_ADATE,");
sql.addSql(" SBC_EUID,");
sql.addSql(" SBC_EDATE");
sql.addSql(" from ");
sql.addSql(" SML_BAN_DEST_CONF");
pstmt = con.prepareStatement(sql.toSqlString());
log__.info(sql.toLogString());
rs = pstmt.executeQuery();
while (rs.next()) {
ret.add(__getSmlBanDestConfFromRs(rs));
}
} catch (SQLException e) {
throw e;
} finally {
JDBCUtil.closeResultSet(rs);
JDBCUtil.closeStatement(pstmt);
}
return ret;
}
/**
* <br>
* [機 能] 一覧取得 <br>
* [解 説] <br>
* [備 考]
*
* @param searchMdl 検索パラメータ
* @return 検索結果
* @throws SQLException SQL実行時例外
*/
public List<Sml380DataModel> searchSbc(Sml380SearchModel searchMdl) throws SQLException {
PreparedStatement pstmt = null;
ResultSet rs = null;
Connection con = null;
ArrayList<Sml380DataModel> ret = new ArrayList<Sml380DataModel>();
con = getCon();
try {
// SQL文
SqlBuffer sql = new SqlBuffer();
sql.addSql(" select ");
sql.addSql(" SBC_SID,");
sql.addSql(" SBC_NAME,");
sql.addSql(" SBC_BIKO,");
sql.addSql(" SBC_AUID,");
sql.addSql(" SBC_ADATE,");
sql.addSql(" SBC_EUID,");
sql.addSql(" SBC_EDATE");
sql.addSql(" from ");
sql.addSql(" SML_BAN_DEST_CONF");
if (!StringUtil.isNullZeroString(searchMdl.getKeyword())) {
sql.addSql(" where ");
sql.addSql(" SBC_NAME ");
String value =
" like '%"
+ JDBCUtil.encFullStringLike(searchMdl.getKeyword())
+ "%' ESCAPE '"
+ JDBCUtil.def_esc
+ "'";
sql.addSql(value);
}
sql.addSql(" order by ");
sql.addSql(" SML_BAN_DEST_CONF.SBC_NAME ");
if (searchMdl.getOrder() == GSConstSmail.ORDER_KEY_ASC) {
sql.addSql(" asc ");
} else {
sql.addSql(" desc ");
}
pstmt =
con.prepareStatement(
sql.toSqlString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
sql.setParameter(pstmt);
log__.info(sql.toLogString());
rs = pstmt.executeQuery();
int start = searchMdl.getStart();
int maxCount = searchMdl.getLimit();
if (start > 1) {
rs.absolute(start - 1);
}
for (int i = 0; rs.next() && i < maxCount; i++) {
Sml380DataModel bean = new Sml380DataModel();
bean.setSbcSid(rs.getInt("SBC_SID"));
bean.setDspSbcName(rs.getString("SBC_NAME"));
bean.setDspSbcBiko(StringUtilHtml.transToHTmlPlusAmparsant(rs.getString("SBC_BIKO")));
ret.add(bean);
}
} catch (SQLException e) {
throw e;
} finally {
JDBCUtil.closeResultSet(rs);
JDBCUtil.closeStatement(pstmt);
}
return ret;
}
/**
* Select WML_ADM_CONF
*
* @return WML_ADM_CONFModel
* @throws SQLException SQL実行例外
*/
public WmlAdmConfModel selectAdmData() throws SQLException {
PreparedStatement pstmt = null;
ResultSet rs = null;
Connection con = null;
WmlAdmConfModel ret = new WmlAdmConfModel();
con = getCon();
try {
// SQL文
SqlBuffer sql = new SqlBuffer();
sql.addSql(" select ");
sql.addSql(" WAD_ACNT_MAKE,");
sql.addSql(" WAD_ACCT_SENDFORMAT,");
sql.addSql(" WAD_ACCT_LOG_REGIST,");
sql.addSql(" WAD_PERMIT_KBN,");
sql.addSql(" WAD_AUTO_RECEIVE_TIME,");
sql.addSql(" WAD_DISK,");
sql.addSql(" WAD_DISK_SIZE,");
sql.addSql(" WAD_DISK_COMP,");
sql.addSql(" WAD_DELRECEIVE,");
sql.addSql(" WAD_AUTORECEIVE,");
sql.addSql(" WAD_SEND_HOST,");
sql.addSql(" WAD_SEND_PORT,");
sql.addSql(" WAD_SEND_SSL,");
sql.addSql(" WAD_RECEIVE_HOST,");
sql.addSql(" WAD_RECEIVE_PORT,");
sql.addSql(" WAD_RECEIVE_SSL,");
sql.addSql(" WAD_CHECK_ADDRESS,");
sql.addSql(" WAD_CHECK_FILE,");
sql.addSql(" WAD_COMPRESS_FILE,");
sql.addSql(" WAD_TIMESENT,");
sql.addSql(" WAD_SEND_LIMIT,");
sql.addSql(" WAD_SEND_LIMIT_SIZE,");
sql.addSql(" WAD_FWLIMIT,");
sql.addSql(" WAD_BCC,");
sql.addSql(" WAD_BCC_TH,");
sql.addSql(" WAD_WARN_DISK,");
sql.addSql(" WAD_WARN_DISK_TH,");
sql.addSql(" WAD_SETTING_SERVER,");
sql.addSql(" WAD_PROXY_USER,");
sql.addSql(" WAD_COMPRESS_FILE_DEF,");
sql.addSql(" WAD_TIMESENT_DEF");
sql.addSql(" from ");
sql.addSql(" WML_ADM_CONF");
pstmt = con.prepareStatement(sql.toSqlString());
log__.info(sql.toLogString());
rs = pstmt.executeQuery();
if (rs.next()) {
ret = __getWmlAdmConfFromRs(rs);
}
} catch (SQLException e) {
throw e;
} finally {
JDBCUtil.closeResultSet(rs);
JDBCUtil.closeStatement(pstmt);
}
return ret;
}
