/**
* <br>
* [機 能] 表示順序を入れ替える <br>
* [解 説] <br>
* [備 考]
*
* @param motoSid 入れ替え元ラベルSID
* @param motoSort 入れ替え元ソート順
* @param sakiSid 入れ替え先ラベルSID
* @param sakiSort 入れ替え先ソート順
* @param con コネクション
* @throws SQLException SQL実行例外
*/
public void updateSort(int motoSid, int motoSort, int sakiSid, int sakiSort, Connection con)
throws SQLException {
PreparedStatement pstmt = null;
try {
// SQL文
SqlBuffer sql = new SqlBuffer();
sql.addSql(" update");
sql.addSql(" CMN_LABEL_USR");
sql.addSql(" set LAB_SORT = case when LAB_SID = ? then" + " ?");
sql.addSql(" when LAB_SID = ? then" + " ?");
sql.addSql(" else LAB_SORT end");
sql.addIntValue(motoSid);
sql.addIntValue(sakiSort);
sql.addIntValue(sakiSid);
sql.addIntValue(motoSort);
log__.info(sql.toLogString());
pstmt = con.prepareStatement(sql.toSqlString());
sql.setParameter(pstmt);
pstmt.executeUpdate();
} catch (SQLException e) {
throw e;
} finally {
JDBCUtil.closeStatement(pstmt);
}
}
/**
* Update CIR_INIT Data Bindding JavaBean
*
* @param bean CIR_INIT Data Bindding JavaBean
* @return 更新件数
* @throws SQLException SQL実行例外
*/
public int updateActSetting(CirInitModel bean) throws SQLException {
PreparedStatement pstmt = null;
int count = 0;
Connection con = null;
con = getCon();
try {
// SQL文
SqlBuffer sql = new SqlBuffer();
sql.addSql(" update");
sql.addSql(" CIR_INIT");
sql.addSql(" set ");
sql.addSql(" CIN_ACNT_MAKE=?,");
sql.addSql(" CIN_AUTO_DEL_KBN=?,");
sql.addSql(" CIN_ACNT_USER=?");
pstmt = con.prepareStatement(sql.toSqlString());
sql.addIntValue(bean.getCinAcntMake());
sql.addIntValue(bean.getCinAutoDelKbn());
sql.addIntValue(bean.getCinAcntUser());
log__.info(sql.toLogString());
sql.setParameter(pstmt);
count = pstmt.executeUpdate();
} catch (SQLException e) {
throw e;
} finally {
JDBCUtil.closeStatement(pstmt);
}
return count;
}
/**
* <br>
* [機 能]データ件数を取得。 <br>
* [解 説] <br>
* [備 考]
*
* @throws SQLException SQL実行例外
* @return ret CirInitModel
* @author JTS
*/
public int getCount() throws SQLException {
PreparedStatement pstmt = null;
ResultSet rs = null;
Connection con = null;
con = getCon();
int count = 0;
try {
SqlBuffer sql = new SqlBuffer();
sql.addSql("select");
sql.addSql(" count(*)");
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()) {
count = rs.getInt(1);
}
} 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;
}
/**
* Delete BMK_BOOKMARK
*
* @param grpSid GRP_SID
* @param usrSid USR_SID
* @return int
* @throws SQLException SQL実行例外
*/
public int delete(int grpSid, int usrSid) throws SQLException {
PreparedStatement pstmt = null;
int count = 0;
Connection con = null;
con = getCon();
try {
// SQL文
SqlBuffer sql = new SqlBuffer();
sql.addSql(" delete");
sql.addSql(" from");
sql.addSql(" BMK_BOOKMARK");
sql.addSql(" where ");
if (grpSid != -1) {
sql.addSql(" GRP_SID=?");
sql.addIntValue(grpSid);
} else {
sql.addSql(" USR_SID=?");
sql.addIntValue(usrSid);
}
pstmt = con.prepareStatement(sql.toSqlString());
log__.info(sql.toLogString());
sql.setParameter(pstmt);
count = pstmt.executeUpdate();
} catch (SQLException e) {
throw e;
} finally {
JDBCUtil.closeStatement(pstmt);
}
return count;
}
/**
* Delete TCD_TCDATA
*
* @param bean TCD_TCDATA Model
* @return int 削除件数
* @throws SQLException SQL実行例外
*/
public int delete(TcdTcdataModel bean) throws SQLException {
PreparedStatement pstmt = null;
int count = 0;
Connection con = null;
con = getCon();
try {
// SQL文
SqlBuffer sql = new SqlBuffer();
sql.addSql(" delete");
sql.addSql(" from");
sql.addSql(" TCD_TCDATA");
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);
count = pstmt.executeUpdate();
} catch (SQLException e) {
throw e;
} finally {
JDBCUtil.closeStatement(pstmt);
}
return count;
}
/**
* グループ所属者全員の管理者区分を変更する
*
* @param gsid グループSID
* @param grpKbn グループ区分
* @throws SQLException SQL実行例外
*/
public void updataAllBelongUserGrpKbn(int gsid, int grpKbn) throws SQLException {
PreparedStatement pstmt = null;
Connection con = null;
con = getCon();
try {
// SQL文
SqlBuffer sql = new SqlBuffer();
sql.addSql(" update ");
sql.addSql(" CMN_BELONGM");
sql.addSql(" set");
sql.addSql(" BEG_GRPKBN = ?");
sql.addSql(" where");
sql.addSql(" GRP_SID = ?");
pstmt = con.prepareStatement(sql.toSqlString());
sql.addIntValue(grpKbn);
sql.addIntValue(gsid);
log__.info(sql.toLogString());
sql.setParameter(pstmt);
pstmt.executeUpdate();
} catch (SQLException e) {
throw e;
} finally {
JDBCUtil.closeStatement(pstmt);
}
}
/**
* Update NTP_AN_MEMBER Data Bindding JavaBean
*
* @param bean NTP_AN_MEMBER Data Bindding JavaBean
* @return 更新件数
* @throws SQLException SQL実行例外
*/
public int update(NtpAnMemberModel bean) throws SQLException {
PreparedStatement pstmt = null;
int count = 0;
Connection con = null;
con = getCon();
try {
// SQL文
SqlBuffer sql = new SqlBuffer();
sql.addSql(" update");
sql.addSql(" NTP_AN_MEMBER");
sql.addSql(" set ");
sql.addSql(" NAN_SID=?,");
sql.addSql(" USR_SID=?,");
sql.addSql(" NAM_EUID=?,");
sql.addSql(" NAM_EDATE=?");
pstmt = con.prepareStatement(sql.toSqlString());
sql.addIntValue(bean.getNanSid());
sql.addIntValue(bean.getUsrSid());
sql.addIntValue(bean.getNamEuid());
sql.addDateValue(bean.getNamEdate());
log__.info(sql.toLogString());
sql.setParameter(pstmt);
count = pstmt.executeUpdate();
} catch (SQLException e) {
throw e;
} finally {
JDBCUtil.closeStatement(pstmt);
}
return count;
}
/**
* 指定ユーザーが所属するグループを<br>
* 全削除します
*
* @param uSid ユーザーSID
* @return 削除件数
* @throws SQLException SQL実行例外
*/
public int deleteUserBelongGroup(int uSid) throws SQLException {
PreparedStatement pstmt = null;
int count = 0;
Connection con = null;
con = getCon();
try {
// SQL文
SqlBuffer sql = new SqlBuffer();
sql.addSql(" delete");
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);
count = pstmt.executeUpdate();
} catch (SQLException e) {
throw e;
} finally {
JDBCUtil.closeStatement(pstmt);
}
return count;
}
/**
* Update NTP_ADM_CONF Data Bindding JavaBean
*
* @param bean NTP_ADM_CONF Data Bindding JavaBean
* @return 更新件数
* @throws SQLException SQL実行例外
*/
public int update(NtpAdmConfModel bean) throws SQLException {
PreparedStatement pstmt = null;
int count = 0;
Connection con = null;
con = getCon();
try {
// SQL文
SqlBuffer sql = new SqlBuffer();
sql.addSql(" update");
sql.addSql(" NTP_ADM_CONF");
sql.addSql(" set ");
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=?");
pstmt = con.prepareStatement(sql.toSqlString());
sql.addIntValue(bean.getNacCrange());
sql.addIntValue(bean.getNacAtdelFlg());
sql.addIntValue(bean.getNacAtdelY());
sql.addIntValue(bean.getNacAtdelM());
sql.addIntValue(bean.getNacHourDiv());
sql.addIntValue(bean.getNacSmlKbn());
sql.addIntValue(bean.getNacSmlNoticeKbn());
sql.addIntValue(bean.getNacSmlNoticeGrp());
sql.addIntValue(bean.getNacCsmlKbn());
sql.addIntValue(bean.getNacCsmlNoticeKbn());
sql.addIntValue(bean.getNacGsmlKbn());
sql.addIntValue(bean.getNacGsmlNoticeKbn());
sql.addIntValue(bean.getNacAuid());
sql.addDateValue(bean.getNacAdate());
sql.addIntValue(bean.getNacEuid());
sql.addDateValue(bean.getNacEdate());
log__.info(sql.toLogString());
sql.setParameter(pstmt);
count = pstmt.executeUpdate();
} catch (SQLException e) {
throw e;
} finally {
JDBCUtil.closeStatement(pstmt);
}
return count;
}
/**
* Insert CMN_BELONGM Data Bindding JavaBean
*
* @param addLists ArrayList<CMN_BELONGM>
* @throws SQLException SQL実行例外
*/
public void insert(ArrayList<CmnBelongmModel> addLists) throws SQLException {
PreparedStatement pstmt = null;
Connection con = null;
con = getCon();
try {
for (CmnBelongmModel bean : addLists) {
// SQL文
SqlBuffer sql = new SqlBuffer();
sql.addSql(" insert ");
sql.addSql(" into ");
sql.addSql(" CMN_BELONGM(");
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(" )");
sql.addSql(" values");
sql.addSql(" (");
sql.addSql(" ?,");
sql.addSql(" ?,");
sql.addSql(" ?,");
sql.addSql(" ?,");
sql.addSql(" ?,");
sql.addSql(" ?,");
sql.addSql(" ?,");
sql.addSql(" ?");
sql.addSql(" )");
pstmt = con.prepareStatement(sql.toSqlString());
sql.addIntValue(bean.getGrpSid());
sql.addIntValue(bean.getUsrSid());
sql.addIntValue(bean.getBegAuid());
sql.addDateValue(bean.getBegAdate());
sql.addIntValue(bean.getBegEuid());
sql.addDateValue(bean.getBegEdate());
sql.addIntValue(bean.getBegDefgrp());
sql.addIntValue(bean.getBegGrpkbn());
log__.info(sql.toLogString());
sql.setParameter(pstmt);
pstmt.executeUpdate();
}
} catch (SQLException e) {
throw e;
} finally {
JDBCUtil.closeStatement(pstmt);
}
}
/**
* Insert CMN_LABEL_USR Data Bindding JavaBean
*
* @param bean CMN_LABEL_USR Data Bindding JavaBean
* @throws SQLException SQL実行例外
*/
public void insert(CmnLabelUsrModel bean) throws SQLException {
PreparedStatement pstmt = null;
Connection con = null;
con = getCon();
try {
// SQL文
SqlBuffer sql = new SqlBuffer();
sql.addSql(" insert ");
sql.addSql(" into ");
sql.addSql(" CMN_LABEL_USR(");
sql.addSql(" LAB_SID,");
sql.addSql(" LUC_SID,");
sql.addSql(" LAB_NAME,");
sql.addSql(" LAB_BIKO,");
sql.addSql(" LAB_AUID,");
sql.addSql(" LAB_ADATE,");
sql.addSql(" LAB_EUID,");
sql.addSql(" LAB_EDATE,");
sql.addSql(" LAB_SORT");
sql.addSql(" )");
sql.addSql(" values");
sql.addSql(" (");
sql.addSql(" ?,");
sql.addSql(" ?,");
sql.addSql(" ?,");
sql.addSql(" ?,");
sql.addSql(" ?,");
sql.addSql(" ?,");
sql.addSql(" ?,");
sql.addSql(" ?,");
sql.addSql(" ?");
sql.addSql(" )");
pstmt = con.prepareStatement(sql.toSqlString());
sql.addIntValue(bean.getLabSid());
sql.addIntValue(bean.getLucSid());
sql.addStrValue(bean.getLabName());
sql.addStrValue(bean.getLabBiko());
sql.addIntValue(bean.getLabAuid());
sql.addDateValue(bean.getLabAdate());
sql.addIntValue(bean.getLabEuid());
sql.addDateValue(bean.getLabEdate());
sql.addIntValue(bean.getLabSort());
log__.info(sql.toLogString());
sql.setParameter(pstmt);
pstmt.executeUpdate();
} catch (SQLException e) {
throw e;
} finally {
JDBCUtil.closeStatement(pstmt);
}
}
/**
* <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;
}
/**
* Insert SML_BAN_DEST_CONF Data Bindding JavaBean
*
* @param bean SML_BAN_DEST_CONF Data Bindding JavaBean
* @throws SQLException SQL実行例外
*/
public void insert(SmlBanDestConfModel bean) throws SQLException {
PreparedStatement pstmt = null;
Connection con = null;
con = getCon();
try {
// SQL文
SqlBuffer sql = new SqlBuffer();
sql.addSql(" insert ");
sql.addSql(" into ");
sql.addSql(" SML_BAN_DEST_CONF(");
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(" )");
sql.addSql(" values");
sql.addSql(" (");
sql.addSql(" ?,");
sql.addSql(" ?,");
sql.addSql(" ?,");
sql.addSql(" ?,");
sql.addSql(" ?,");
sql.addSql(" ?,");
sql.addSql(" ?");
sql.addSql(" )");
pstmt = con.prepareStatement(sql.toSqlString());
sql.addIntValue(bean.getSbcSid());
sql.addStrValue(bean.getSbcName());
sql.addStrValue(bean.getSbcBiko());
sql.addIntValue(bean.getSbcAuid());
sql.addDateValue(bean.getSbcAdate());
sql.addIntValue(bean.getSbcEuid());
sql.addDateValue(bean.getSbcEdate());
log__.info(sql.toLogString());
sql.setParameter(pstmt);
pstmt.executeUpdate();
} catch (SQLException e) {
throw e;
} finally {
JDBCUtil.closeStatement(pstmt);
}
}
/**
* ユーザーが所属するグループで<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;
}
/**
* Insert NTP_AN_MEMBER Data Bindding JavaBean
*
* @param bean NTP_AN_MEMBER Data Bindding JavaBean
* @throws SQLException SQL実行例外
*/
public void insert(NtpAnMemberModel bean) throws SQLException {
PreparedStatement pstmt = null;
Connection con = null;
con = getCon();
try {
// SQL文
SqlBuffer sql = new SqlBuffer();
sql.addSql(" insert ");
sql.addSql(" into ");
sql.addSql(" NTP_AN_MEMBER(");
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(" )");
sql.addSql(" values");
sql.addSql(" (");
sql.addSql(" ?,");
sql.addSql(" ?,");
sql.addSql(" ?,");
sql.addSql(" ?,");
sql.addSql(" ?,");
sql.addSql(" ?");
sql.addSql(" )");
pstmt = con.prepareStatement(sql.toSqlString());
sql.addIntValue(bean.getNanSid());
sql.addIntValue(bean.getUsrSid());
sql.addIntValue(bean.getNamAuid());
sql.addDateValue(bean.getNamAdate());
sql.addIntValue(bean.getNamEuid());
sql.addDateValue(bean.getNamEdate());
log__.info(sql.toLogString());
sql.setParameter(pstmt);
pstmt.executeUpdate();
} catch (SQLException e) {
throw e;
} finally {
JDBCUtil.closeStatement(pstmt);
}
}
/**
* 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;
}
/**
* Update BMK_BOOKMARK Data Bindding JavaBean
*
* @param bean BMK_BOOKMARK Data Bindding JavaBean
* @return 更新件数
* @throws SQLException SQL実行例外
*/
public int update(BmkBookmarkModel bean) throws SQLException {
PreparedStatement pstmt = null;
int count = 0;
Connection con = null;
con = getCon();
try {
// SQL文
SqlBuffer sql = new SqlBuffer();
sql.addSql(" update");
sql.addSql(" BMK_BOOKMARK");
sql.addSql(" set ");
sql.addSql(" BMK_TITLE=?,");
sql.addSql(" BMK_CMT=?,");
sql.addSql(" BMK_SCORE=?,");
sql.addSql(" BMK_PUBLIC=?,");
sql.addSql(" BMK_MAIN=?,");
sql.addSql(" BMK_EUID=?,");
sql.addSql(" BMK_EDATE=?");
sql.addSql(" where ");
sql.addSql(" BMK_SID=?");
pstmt = con.prepareStatement(sql.toSqlString());
sql.addStrValue(bean.getBmkTitle());
sql.addStrValue(bean.getBmkCmt());
sql.addIntValue(bean.getBmkScore());
sql.addIntValue(bean.getBmkPublic());
sql.addIntValue(bean.getBmkMain());
sql.addIntValue(bean.getBmkEuid());
sql.addDateValue(bean.getBmkEdate());
// where
sql.addIntValue(bean.getBmkSid());
log__.info(sql.toLogString());
sql.setParameter(pstmt);
count = pstmt.executeUpdate();
} catch (SQLException e) {
throw e;
} finally {
JDBCUtil.closeStatement(pstmt);
}
return count;
}
/**
* <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 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;
}
/**
* グループに所属するユーザの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;
}
/**
* ユーザーが所属するグループのモデルリストを返却します
*
* @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;
}
/**
* Update CMN_BELONGM Data Bindding JavaBean
*
* @param bean CMN_BELONGM Data Bindding JavaBean
* @return 更新件数
* @throws SQLException SQL実行例外
*/
public int update(CmnBelongmModel bean) throws SQLException {
PreparedStatement pstmt = null;
int count = 0;
Connection con = null;
con = getCon();
try {
// SQL文
SqlBuffer sql = new SqlBuffer();
sql.addSql(" update");
sql.addSql(" CMN_BELONGM");
sql.addSql(" set ");
sql.addSql(" BEG_AUID=?,");
sql.addSql(" BEG_ADATE=?,");
sql.addSql(" BEG_EUID=?,");
sql.addSql(" BEG_EDATE=?,");
sql.addSql(" BEG_DEFGRP=?");
sql.addSql(" where ");
sql.addSql(" GRP_SID=?");
sql.addSql(" and");
sql.addSql(" USR_SID=?");
pstmt = con.prepareStatement(sql.toSqlString());
sql.addIntValue(bean.getBegAuid());
sql.addDateValue(bean.getBegAdate());
sql.addIntValue(bean.getBegEuid());
sql.addDateValue(bean.getBegEdate());
sql.addIntValue(bean.getBegDefgrp());
// where
sql.addIntValue(bean.getGrpSid());
sql.addIntValue(bean.getUsrSid());
log__.info(sql.toLogString());
sql.setParameter(pstmt);
count = pstmt.executeUpdate();
} catch (SQLException e) {
throw e;
} finally {
JDBCUtil.closeStatement(pstmt);
}
return count;
}
/**
* 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()]);
}
/**
* 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>
* [機 能] 指定したアンケートの対象人数を取得する <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;
}
/**
* Update SML_BAN_DEST_CONF Data Bindding JavaBean
*
* @param bean SML_BAN_DEST_CONF Data Bindding JavaBean
* @return 更新件数
* @throws SQLException SQL実行例外
*/
public int update(SmlBanDestConfModel bean) throws SQLException {
PreparedStatement pstmt = null;
int count = 0;
Connection con = null;
con = getCon();
try {
// SQL文
SqlBuffer sql = new SqlBuffer();
sql.addSql(" update");
sql.addSql(" SML_BAN_DEST_CONF");
sql.addSql(" set ");
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(" where ");
sql.addSql(" SBC_SID=?");
pstmt = con.prepareStatement(sql.toSqlString());
sql.addStrValue(bean.getSbcName());
sql.addStrValue(bean.getSbcBiko());
sql.addIntValue(bean.getSbcAuid());
sql.addDateValue(bean.getSbcAdate());
sql.addIntValue(bean.getSbcEuid());
sql.addDateValue(bean.getSbcEdate());
// where
sql.addIntValue(bean.getSbcSid());
log__.info(sql.toLogString());
sql.setParameter(pstmt);
count = pstmt.executeUpdate();
} catch (SQLException e) {
throw e;
} finally {
JDBCUtil.closeStatement(pstmt);
}
return count;
}
/**
* 画像を論理削除する。
*
* @param pltSid ポートレットSID
* @return int 更新件数
* @throws SQLException SQL実行例外
*/
public int updateImgFlg(int pltSid) throws SQLException {
PreparedStatement pstmt = null;
int count = 0;
Connection con = null;
con = getCon();
if (pltSid == 0) {
return count;
}
try {
// SQL文
SqlBuffer sql = new SqlBuffer();
sql.addSql(" update");
sql.addSql(" CMN_BINF");
sql.addSql(" set");
sql.addSql(" BIN_JKBN = ?");
sql.addSql(" where");
sql.addSql(" CMN_BINF.BIN_SID in (");
sql.addSql(" select");
sql.addSql(" PTL_PORTLET_IMAGE.BIN_SID");
sql.addSql(" from");
sql.addSql(" PTL_PORTLET_IMAGE");
sql.addSql(" where");
sql.addSql(" PTL_PORTLET_IMAGE.PLT_SID = ?");
sql.addSql(" )");
sql.addIntValue(GSConst.JTKBN_DELETE);
sql.addIntValue(pltSid);
log__.info(sql.toLogString());
pstmt = con.prepareStatement(sql.toSqlString());
sql.setParameter(pstmt);
count = pstmt.executeUpdate();
} catch (SQLException e) {
throw e;
} finally {
JDBCUtil.closeStatement(pstmt);
}
return count;
}
