/**
* タイムカード登録情報のなかから最大値を取得 <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 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;
}
/**
* 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;
}
/**
* <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;
}
/**
* 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;
}
/**
* <br>
* [機 能] ラベルの削除を行う <br>
* [解 説] <br>
* [備 考]
*
* @param con コネクション
* @param paramMdl パラメータ情報
* @throws SQLException SQL実行時例外
*/
public void deleteLabel(Connection con, Sml310ParamModel paramMdl) throws SQLException {
boolean commitFlg = false;
try {
// ラベルを削除する
SmlLabelDao slDao = new SmlLabelDao(con);
slDao.delete(paramMdl.getSmlEditLabelId());
// メール - ラベルを削除する
SmlJmeisLabelDao jDao = new SmlJmeisLabelDao(con);
jDao.delete(paramMdl.getSmlEditLabelId());
SmlSmeisLabelDao sDao = new SmlSmeisLabelDao(con);
sDao.delete(paramMdl.getSmlEditLabelId());
SmlWmeisLabelDao wDao = new SmlWmeisLabelDao(con);
wDao.delete(paramMdl.getSmlEditLabelId());
// メール情報表示順を削除する
SmlLabelSortDao mailSortDao = new SmlLabelSortDao(con);
mailSortDao.deleteMailSortOfLabel(paramMdl.getSmlEditLabelId());
// コミット実行
con.commit();
commitFlg = true;
} catch (SQLException e) {
log__.error("SQLException", e);
throw e;
} finally {
if (!commitFlg) {
JDBCUtil.rollback(con);
}
}
}
/**
* <br>
* [機 能] テンプレートを削除する <br>
* [解 説] <br>
* [備 考]
*
* @param paramMdl パラメータモデル
* @param reqMdl リクエストモデル
* @param con コネクション
* @return 削除したアンケート名
* @throws SQLException SQL実行例外
*/
public String doDelete(Enq230ParamModel paramMdl, RequestModel reqMdl, Connection con)
throws SQLException {
EnqCommonBiz enqBiz = new EnqCommonBiz(con);
boolean commitFlg = false;
String delEnqName = "";
// セッションユーザ情報を取得
BaseUserModel usModel = reqMdl.getSmodel();
int sessionUsrSid = usModel.getUsrsid();
try {
con.setAutoCommit(false);
for (String selectEnqSid : paramMdl.getEnq230selectEnqSid()) {
if (delEnqName.length() > 0) {
delEnqName += ",";
}
delEnqName += enqBiz.deleteEnquete(Long.parseLong(selectEnqSid), sessionUsrSid, con);
}
con.commit();
commitFlg = true;
} finally {
if (!commitFlg) {
JDBCUtil.rollback(con);
}
}
return delEnqName;
}
/**
* 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;
}
/**
* <br>
* [機 能] 更新処理 <br>
* [解 説] <br>
* [備 考]
*
* @param anp020knModel パラメータモデル
* @param con DBコネクション
* @param usrSid セッションユーザSID
* @throws Exception 実行例外
*/
public void doUpdate(Anp020knParamModel anp020knModel, Connection con, int usrSid)
throws Exception {
boolean commitFlg = false;
UDate now = new UDate();
try {
con.setAutoCommit(false);
AnpJdataModel bean = new AnpJdataModel();
bean.setAphSid(Integer.valueOf(anp020knModel.getAnpiSid()));
bean.setUsrSid(Integer.valueOf(anp020knModel.getUserSid()));
bean.setApdJokyoFlg(Integer.valueOf(anp020knModel.getAnp020JokyoFlg()));
bean.setApdPlaceFlg(Integer.valueOf(anp020knModel.getAnp020PlaceFlg()));
bean.setApdSyusyaFlg(Integer.valueOf(anp020knModel.getAnp020SyusyaFlg()));
bean.setApdComment(anp020knModel.getAnp020Comment());
bean.setApdRdate(now);
bean.setApdEuid(usrSid);
bean.setApdEdate(now);
AnpJdataDao dao = new AnpJdataDao(con);
dao.updateUserInput(bean);
commitFlg = true;
} catch (SQLException e) {
log__.error("SQLException", e);
throw e;
} finally {
if (commitFlg) {
con.commit();
} else {
JDBCUtil.rollback(con);
}
}
}
/**
* <br>
* [機 能] 添付ファイルダウンロードの処理 <br>
* [解 説] <br>
* [備 考]
*
* @param map アクションマッピング
* @param form アクションフォーム
* @param req リクエスト
* @param res レスポンス
* @param con コネクション
* @throws SQLException SQL実行例外
* @throws Exception 実行時例外
* @return ActionForward
*/
private ActionForward __doGetImageFile(
ActionMapping map,
Rsv010Form form,
HttpServletRequest req,
HttpServletResponse res,
Connection con)
throws SQLException, Exception {
// 施設SID
int rsdSid = form.getRsvSelectedSisetuSid();
// バイナリSID
Long binSid = form.getRsv010BinSid();
Rsv010Biz biz = new Rsv010Biz(getRequestModel(req), con);
RsvCommonBiz rsvBiz = new RsvCommonBiz();
// 閲覧権限ある且つ施設SIDとバイナリSIDの組み合わせが一致している
if (biz.isReadSisetsu(rsdSid) && rsvBiz.isCheckRsvImage(rsdSid, binSid, con)) {
CommonBiz cmnBiz = new CommonBiz();
CmnBinfModel cbMdl = null;
cbMdl = cmnBiz.getBinInfo(con, binSid, GroupSession.getResourceManager().getDomain(req));
if (cbMdl != null) {
JDBCUtil.closeConnectionAndNull(con);
// ファイルをダウンロードする
TempFileUtil.downloadInline(req, res, cbMdl, getAppRootPath(), Encoding.UTF_8);
}
}
return null;
}
/**
* グループ所属者全員の管理者区分を変更する
*
* @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 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>
* 全削除します
*
* @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;
}
/**
* Create Table
*
* @throws SQLException SQL実行例外
*/
public void createTable() throws SQLException {
PreparedStatement pstmt = null;
Connection con = null;
con = getCon();
try {
// SQL文
SqlBuffer sql = new SqlBuffer();
sql.addSql(" create table NTP_AN_MEMBER (");
sql.addSql(" NAN_SID NUMBER(10,0) not null,");
sql.addSql(" USR_SID NUMBER(10,0) not null,");
sql.addSql(" NAM_AUID NUMBER(10,0),");
sql.addSql(" NAM_ADATE varchar(23) not null,");
sql.addSql(" NAM_EUID NUMBER(10,0),");
sql.addSql(" NAM_EDATE varchar(23) not null,");
sql.addSql(" primary key (NAN_SID,USR_SID)");
sql.addSql(" )");
pstmt = con.prepareStatement(sql.toSqlString());
log__.info(sql.toLogString());
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;
}
/**
* Create Table
*
* @throws SQLException SQL実行例外
*/
public void createTable() throws SQLException {
PreparedStatement pstmt = null;
Connection con = null;
con = getCon();
try {
// SQL文
SqlBuffer sql = new SqlBuffer();
sql.addSql(" create table PTL_PORTLET_IMAGE (");
sql.addSql(" PLT_SID integer not null,");
sql.addSql(" PLI_SID bigint not null,");
sql.addSql(" BIN_SID bigint not null,");
sql.addSql(" PLI_NAME varchar(10) not null,");
sql.addSql(" primary key (PLT_SID,PLI_SID)");
sql.addSql(" )");
pstmt = con.prepareStatement(sql.toSqlString());
log__.info(sql.toLogString());
pstmt.executeUpdate();
} catch (SQLException e) {
throw e;
} finally {
JDBCUtil.closeStatement(pstmt);
}
}
/**
* <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);
}
}
/**
* ユーザーが所属するグループで<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;
}
/**
* 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;
}
/**
* 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;
}
/**
* <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;
}
/**
* 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;
}
/**
* 画像一覧を取得する。
*
* @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;
}
/**
* グループに所属するユーザの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;
}