/**
* 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;
}
/**
* 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 SML_BAN_DEST_CONF (");
sql.addSql(" SBC_SID NUMBER(10,0) not null,");
sql.addSql(" SBC_NAME varchar(50) not null,");
sql.addSql(" SBC_BIKO varchar(1000),");
sql.addSql(" SBC_AUID NUMBER(10,0) not null,");
sql.addSql(" SBC_ADATE varchar(23) not null,");
sql.addSql(" SBC_EUID NUMBER(10,0) not null,");
sql.addSql(" SBC_EDATE varchar(23) not null,");
sql.addSql(" primary key (SBC_SID)");
sql.addSql(" )");
pstmt = con.prepareStatement(sql.toSqlString());
log__.info(sql.toLogString());
pstmt.executeUpdate();
} catch (SQLException e) {
throw e;
} finally {
JDBCUtil.closeStatement(pstmt);
}
}
/**
* 指定ユーザーが所属するグループを<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 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>
* [備 考]
*
* @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 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;
}
/**
* <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(" WML_ADM_CONF");
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>
* 登録情報が無い場合はシステム日付から年を取得
*
* @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;
}
/**
* Drop Table
*
* @throws SQLException SQL実行例外
*/
public void dropTable() throws SQLException {
PreparedStatement pstmt = null;
Connection con = null;
con = getCon();
try {
// SQL文
SqlBuffer sql = new SqlBuffer();
sql.addSql("drop table WML_ADM_CONF");
pstmt = con.prepareStatement(sql.toSqlString());
log__.info(sql.toLogString());
pstmt.executeUpdate();
} catch (SQLException e) {
throw e;
} finally {
JDBCUtil.closeStatement(pstmt);
}
}
/**
* <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 labSid LAB_SID
* @return delete count
* @throws SQLException SQL実行例外
*/
public int delete(int labSid) throws SQLException {
PreparedStatement pstmt = null;
int count = 0;
Connection con = null;
con = getCon();
try {
CmnLabelUsrModel model = selectOneLabel(labSid);
ArrayList<Integer> list = selectLabelSort(model.getLabSort(), model.getLucSid());
// SQL文
SqlBuffer sql = new SqlBuffer();
sql.addSql(" delete");
sql.addSql(" from");
sql.addSql(" CMN_LABEL_USR");
sql.addSql(" where ");
sql.addSql(" LAB_SID=?");
pstmt = con.prepareStatement(sql.toSqlString());
sql.addIntValue(labSid);
log__.info(sql.toLogString());
sql.setParameter(pstmt);
count = pstmt.executeUpdate();
for (int i = 0; i < list.size(); i++) {
sortArrange(list.get(i));
}
} catch (SQLException e) {
throw e;
} finally {
JDBCUtil.closeStatement(pstmt);
}
return count;
}
/**
* Insert CMN_BELONGM Data Bindding JavaBean
*
* @param bean CMN_BELONGM Data Bindding JavaBean
* @throws SQLException SQL実行例外
*/
public void insert(CmnBelongmModel 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_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);
}
}
/**
* 指定グループに所属するユーザリストを返却します
*
* @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;
}
/**
* 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 CMN_BELONGM All Data
*
* @return List in CMN_BELONGMModel
* @throws SQLException SQL実行例外
*/
public List<CmnBelongmModel> select() 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");
pstmt = con.prepareStatement(sql.toSqlString());
log__.info(sql.toLogString());
rs = pstmt.executeQuery();
while (rs.next()) {
ret.add(__getCmnBelongmFromRs(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;
}
/**
* グループ所属者全員の管理者区分を変更する
*
* @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);
}
}
/**
* <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;
}
/**
* 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 bean CIR_USER Data Bindding JavaBean
* @throws SQLException SQL実行例外
*/
public void updateAdmInitSet(CirInitModel bean) throws SQLException {
PreparedStatement pstmt = null;
Connection con = null;
con = getCon();
try {
// SQL文
SqlBuffer sql = new SqlBuffer();
sql.addSql(" update");
sql.addSql(" CIR_INIT");
sql.addSql(" set ");
sql.addSql(" CIN_INITSET_KEN = ?,");
sql.addSql(" CIN_MEMO_KBN = ?,");
sql.addSql(" CIN_MEMO_DAY = ?,");
sql.addSql(" CIN_KOU_KBN = ?,");
sql.addSql(" CIN_EUID = ?,");
sql.addSql(" CIN_EDATE = ?");
pstmt = con.prepareStatement(sql.toSqlString());
sql.addIntValue(bean.getCinInitSetKen());
sql.addIntValue(bean.getCinMemoKbn());
sql.addIntValue(bean.getCinMemoDay());
sql.addIntValue(bean.getCinKouKbn());
sql.addIntValue(bean.getCinEuid());
sql.addDateValue(bean.getCinEdate());
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;
}
/**
* アカウント設定を更新する
*
* @param bean WML_ADM_CONF Data Bindding JavaBean
* @return 更新件数
* @throws SQLException SQL実行例外
*/
public int updateAdmConf(WmlAdmConfModel bean) throws SQLException {
PreparedStatement pstmt = null;
int count = 0;
Connection con = null;
con = getCon();
boolean receiveUpFlg = bean.getWadAutoreceive() == GSConstWebmail.MAIL_AUTO_RSV_ON;
try {
// SQL文
SqlBuffer sql = new SqlBuffer();
sql.addSql(" update");
sql.addSql(" WML_ADM_CONF");
sql.addSql(" set ");
sql.addSql(" WAD_ACNT_MAKE=?,");
sql.addSql(" WAD_ACCT_SENDFORMAT=?,");
sql.addSql(" WAD_ACCT_LOG_REGIST=?,");
sql.addSql(" WAD_PERMIT_KBN=?,");
if (receiveUpFlg) {
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=?");
pstmt = con.prepareStatement(sql.toSqlString());
sql.addIntValue(bean.getWadAcntMake());
sql.addIntValue(bean.getWadAcctSendformat());
sql.addIntValue(bean.getWadAcctLogRegist());
sql.addIntValue(bean.getWadPermitKbn());
if (receiveUpFlg) {
sql.addIntValue(bean.getWadAutoReceiveTime());
}
sql.addIntValue(bean.getWadDisk());
sql.addIntValue(bean.getWadDiskSize());
sql.addIntValue(bean.getWadDiskComp());
sql.addIntValue(bean.getWadDelreceive());
sql.addIntValue(bean.getWadAutoreceive());
sql.addStrValue(bean.getWadSendHost());
sql.addIntValue(bean.getWadSendPort());
sql.addIntValue(bean.getWadSendSsl());
sql.addStrValue(bean.getWadReceiveHost());
sql.addIntValue(bean.getWadReceivePort());
sql.addIntValue(bean.getWadReceiveSsl());
sql.addIntValue(bean.getWadCheckAddress());
sql.addIntValue(bean.getWadCheckFile());
sql.addIntValue(bean.getWadCompressFile());
sql.addIntValue(bean.getWadTimesent());
sql.addIntValue(bean.getWadSendLimit());
sql.addIntValue(bean.getWadSendLimitSize());
sql.addIntValue(bean.getWadFwlimit());
sql.addIntValue(bean.getWadBcc());
sql.addIntValue(bean.getWadBccTh());
sql.addIntValue(bean.getWadWarnDisk());
sql.addIntValue(bean.getWadWarnDiskTh());
sql.addIntValue(bean.getWadSettingServer());
sql.addIntValue(bean.getWadProxyUser());
sql.addIntValue(bean.getWadCompressFileDef());
sql.addIntValue(bean.getWadTimesentDef());
log__.info(sql.toLogString());
sql.setParameter(pstmt);
count = pstmt.executeUpdate();
} catch (SQLException e) {
throw e;
} finally {
JDBCUtil.closeStatement(pstmt);
}
return count;
}
/**
* 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;
}
/**
* <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;
}
/**
* Select CMN_BELONGM
*
* @param usid ユーザSID
* @param gsid グループSID
* @return CMN_BELONGMModel
* @throws SQLException SQL実行例外
*/
public CmnBelongmModel select(int usid, int gsid) throws SQLException {
PreparedStatement pstmt = null;
ResultSet rs = null;
Connection con = null;
CmnBelongmModel ret = null;
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(" 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();
if (rs.next()) {
ret = __getCmnBelongmFromRs(rs);
}
} catch (SQLException e) {
throw e;
} finally {
JDBCUtil.closeResultSet(rs);
JDBCUtil.closeStatement(pstmt);
}
return ret;
}
/**
* グループ所属ユーザーの追加・変更を行います。
*
* @param bean CMN_BELONGM Data Bindding JavaBean
* @param users 更新対象ユーザー配列
* @throws SQLException SQL実行例外
*/
public void insertBelongUser(CmnBelongmModel bean, String[] users) throws SQLException {
PreparedStatement pstmt = null;
Connection con = null;
con = getCon();
// ユーザーリスト作成
StringBuilder bufUsers = new StringBuilder();
// 1件目
bufUsers.append(users[0]);
for (int i = 1; i < users.length; i++) {
// 2件目移行
bufUsers.append(",");
bufUsers.append(users[i]);
}
try {
// 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(" select");
sql.addSql(" ?,");
sql.addSql(" USR_SID,");
sql.addSql(" ?,");
sql.addSql(" ?,");
sql.addSql(" ?,");
sql.addSql(" ?,");
sql.addSql(" ?,");
sql.addSql(" ?");
sql.addSql(" from");
sql.addSql(" CMN_USRM");
sql.addSql(" where");
sql.addSql(" USR_SID in (" + bufUsers.toString() + ")");
sql.addSql(" and");
sql.addSql(" not exists (");
sql.addSql(" select 1 from CMN_BELONGM");
sql.addSql(" where GRP_SID = ?");
sql.addSql(" and");
sql.addSql(" CMN_USRM.USR_SID = CMN_BELONGM.USR_SID");
sql.addSql(" )");
pstmt = con.prepareStatement(sql.toSqlString());
sql.addIntValue(bean.getGrpSid());
sql.addIntValue(bean.getBegAuid());
sql.addDateValue(bean.getBegAdate());
sql.addIntValue(bean.getBegEuid());
sql.addDateValue(bean.getBegEdate());
sql.addIntValue(bean.getBegDefgrp());
sql.addIntValue(GSConst.USER_NOT_ADMIN);
sql.addIntValue(bean.getGrpSid());
log__.info(sql.toLogString());
sql.setParameter(pstmt);
pstmt.executeUpdate();
pstmt = null;
// SQL文
sql = new SqlBuffer();
sql.addSql(" delete");
sql.addSql(" from");
sql.addSql(" CMN_BELONGM");
sql.addSql(" where");
sql.addSql(" GRP_SID=?");
sql.addSql(" and");
sql.addSql(" USR_SID not in (" + bufUsers.toString() + ")");
pstmt = con.prepareStatement(sql.toSqlString());
sql.addIntValue(bean.getGrpSid());
log__.info(sql.toLogString());
sql.setParameter(pstmt);
pstmt.executeUpdate();
} catch (SQLException e) {
throw e;
} finally {
JDBCUtil.closeStatement(pstmt);
}
}
/**
* グループに所属するユーザの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 grpKbn グループ区分
* @param gsid グループSID
* @param users 更新対象ユーザー配列
* @throws SQLException SQL実行例外
*/
public void updataBelongUserGrpKbn(int grpKbn, int gsid, String[] users) throws SQLException {
PreparedStatement pstmt = null;
Connection con = null;
con = getCon();
// ユーザーリスト作成
StringBuilder bufUsers = new StringBuilder();
// 1件目
bufUsers.append(users[0]);
for (int i = 1; i < users.length; i++) {
// 2件目移行
bufUsers.append(",");
bufUsers.append(users[i]);
}
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 = ?");
sql.addSql(" and");
sql.addSql(" USR_SID in (" + bufUsers.toString() + ")");
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);
}
}
/**
* 初期値 設定値を新規登録する。
*
* @param bean CIR_USER Data Bindding JavaBean
* @throws SQLException SQL実行例外
*/
public void insertAdmInitSet(CirInitModel 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(" CIR_INIT(");
sql.addSql(" CIN_INITSET_KEN,");
sql.addSql(" CIN_MEMO_KBN,");
sql.addSql(" CIN_MEMO_DAY,");
sql.addSql(" CIN_KOU_KBN,");
sql.addSql(" CIN_AUID,");
sql.addSql(" CIN_ADATE,");
sql.addSql(" CIN_EUID,");
sql.addSql(" CIN_EDATE,");
sql.addSql(" CIN_ACNT_MAKE,");
sql.addSql(" CIN_AUTO_DEL_KBN,");
sql.addSql(" CIN_ACNT_USER");
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(" ?,");
sql.addSql(" ?");
sql.addSql(" )");
pstmt = con.prepareStatement(sql.toSqlString());
sql.addIntValue(bean.getCinInitSetKen());
sql.addIntValue(bean.getCinMemoKbn());
sql.addIntValue(bean.getCinMemoDay());
sql.addIntValue(bean.getCinKouKbn());
sql.addIntValue(bean.getCinAuid());
sql.addDateValue(bean.getCinAdate());
sql.addIntValue(bean.getCinEuid());
sql.addDateValue(bean.getCinEdate());
sql.addIntValue(bean.getCinAcntMake());
sql.addIntValue(bean.getCinAutoDelKbn());
sql.addIntValue(bean.getCinAcntUser());
log__.info(sql.toLogString());
sql.setParameter(pstmt);
pstmt.executeUpdate();
} catch (SQLException e) {
throw e;
} finally {
JDBCUtil.closeStatement(pstmt);
}
}
/**
* 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 WML_ADM_CONF (");
sql.addSql(" WAD_ACNT_MAKE NUMBER(10,0) not null,");
sql.addSql(" WAD_ACCT_SENDFORMAT NUMBER(10,0) not null,");
sql.addSql(" WAD_ACCT_LOG_REGIST NUMBER(10,0) not null,");
sql.addSql(" WAD_PERMIT_KBN NUMBER(10,0) not null,");
sql.addSql(" WAD_AUTO_RECEIVE_TIME NUMBER(10,0) not null,");
sql.addSql(" WAD_DISK NUMBER(10,0) not null,");
sql.addSql(" WAD_DISK_SIZE NUMBER(10,0),");
sql.addSql(" WAD_DISK_COMP NUMBER(10,0),");
sql.addSql(" WAD_DELRECEIVE NUMBER(10,0) not null,");
sql.addSql(" WAD_AUTORECEIVE NUMBER(10,0) not null,");
sql.addSql(" WAD_SEND_HOST varchar(100),");
sql.addSql(" WAD_SEND_PORT NUMBER(10,0),");
sql.addSql(" WAD_SEND_SSL NUMBER(10,0),");
sql.addSql(" WAD_RECEIVE_HOST varchar(100),");
sql.addSql(" WAD_RECEIVE_PORT NUMBER(10,0),");
sql.addSql(" WAD_RECEIVE_SSL NUMBER(10,0),");
sql.addSql(" WAD_CHECK_ADDRESS NUMBER(10,0) not null,");
sql.addSql(" WAD_CHECK_FILE NUMBER(10,0) not null,");
sql.addSql(" WAD_COMPRESS_FILE NUMBER(10,0) not null,");
sql.addSql(" WAD_TIMESENT NUMBER(10,0) not null,");
sql.addSql(" WAD_SEND_LIMIT NUMBER(10,0) not null,");
sql.addSql(" WAD_SEND_LIMIT_SIZE NUMBER(10,0),");
sql.addSql(" WAD_FWLIMIT NUMBER(10,0) not null,");
sql.addSql(" WAD_BCC NUMBER(10,0) not null,");
sql.addSql(" WAD_BCC_TH NUMBER(10,0),");
sql.addSql(" WAD_WARN_DISK NUMBER(10,0) not null,");
sql.addSql(" WAD_WARN_DISK_TH NUMBER(10,0),");
sql.addSql(" WAD_SETTING_SERVER NUMBER(10,0) not null,");
sql.addSql(" WAD_PROXY_USER NUMBER(10,0) not null,");
sql.addSql(" WAD_COMPRESS_FILE_DEF NUMBER(10,0),");
sql.addSql(" WAD_TIMESENT_DEF NUMBER(10,0)");
sql.addSql(" )");
pstmt = con.prepareStatement(sql.toSqlString());
log__.info(sql.toLogString());
pstmt.executeUpdate();
} catch (SQLException e) {
throw e;
} finally {
JDBCUtil.closeStatement(pstmt);
}
}
