public void deleteMemoDataByMemoDataCtcIdx(
CFSecurityAuthorization Authorization, long argTenantId, long argContactId) {
final String S_ProcName = "deleteMemoDataByMemoDataCtcIdx";
if (!schema.isTransactionOpen()) {
throw CFLib.getDefaultExceptionFactory()
.newUsageException(getClass(), S_ProcName, "Transaction not open");
}
ResultSet resultSet = null;
try {
Connection cnx = schema.getCnx();
String sql =
"exec sp_delete_memodata_by_memodatactcidx ?, ?, ?, ?, ?" + ", " + "?" + ", " + "?";
if (stmtDeleteByMemoDataCtcIdx == null) {
stmtDeleteByMemoDataCtcIdx = cnx.prepareStatement(sql);
}
int argIdx = 1;
stmtDeleteByMemoDataCtcIdx.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
stmtDeleteByMemoDataCtcIdx.setString(
argIdx++, (Authorization == null) ? "" : Authorization.getSecUserId().toString());
stmtDeleteByMemoDataCtcIdx.setString(
argIdx++, (Authorization == null) ? "" : Authorization.getSecSessionId().toString());
stmtDeleteByMemoDataCtcIdx.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
stmtDeleteByMemoDataCtcIdx.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecTenantId());
stmtDeleteByMemoDataCtcIdx.setLong(argIdx++, argTenantId);
stmtDeleteByMemoDataCtcIdx.setLong(argIdx++, argContactId);
Object stuff = null;
boolean moreResults = stmtDeleteByMemoDataCtcIdx.execute();
while (stuff == null) {
try {
moreResults = stmtDeleteByMemoDataCtcIdx.getMoreResults();
} catch (SQLException e) {
throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
}
if (moreResults) {
try {
stuff = stmtDeleteByMemoDataCtcIdx.getResultSet();
} catch (SQLException e) {
}
} else if (-1 == stmtDeleteByMemoDataCtcIdx.getUpdateCount()) {
break;
}
}
} catch (SQLException e) {
throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
} finally {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
}
resultSet = null;
}
}
}
protected CFCrmMemoDataBuff unpackMemoDataResultSetToBuff(ResultSet resultSet)
throws SQLException {
final String S_ProcName = "unpackMemoDataResultSetToBuff";
int idxcol = 1;
CFCrmMemoDataBuff buff = schema.getFactoryMemoData().newBuff();
{
String colString = resultSet.getString(idxcol);
if (resultSet.wasNull()) {
buff.setCreatedByUserId(null);
} else if ((colString == null) || (colString.length() <= 0)) {
buff.setCreatedByUserId(null);
} else {
buff.setCreatedByUserId(UUID.fromString(colString));
}
idxcol++;
colString = resultSet.getString(idxcol);
if (resultSet.wasNull()) {
buff.setCreatedAt(null);
} else if ((colString == null) || (colString.length() <= 0)) {
buff.setCreatedAt(null);
} else {
buff.setCreatedAt(CFCrmMSSqlSchema.convertTimestampString(colString));
}
idxcol++;
colString = resultSet.getString(idxcol);
if (resultSet.wasNull()) {
buff.setUpdatedByUserId(null);
} else if ((colString == null) || (colString.length() <= 0)) {
buff.setUpdatedByUserId(null);
} else {
buff.setUpdatedByUserId(UUID.fromString(colString));
}
idxcol++;
colString = resultSet.getString(idxcol);
if (resultSet.wasNull()) {
buff.setUpdatedAt(null);
} else if ((colString == null) || (colString.length() <= 0)) {
buff.setUpdatedAt(null);
} else {
buff.setUpdatedAt(CFCrmMSSqlSchema.convertTimestampString(colString));
}
idxcol++;
}
buff.setRequiredTenantId(resultSet.getLong(idxcol));
idxcol++;
buff.setRequiredMemoId(resultSet.getLong(idxcol));
idxcol++;
buff.setRequiredContactId(resultSet.getLong(idxcol));
idxcol++;
buff.setRequiredMemo(resultSet.getString(idxcol));
idxcol++;
buff.setRequiredRevision(resultSet.getInt(idxcol));
return (buff);
}
public CFCrmMemoDataBuff readBuff(CFSecurityAuthorization Authorization, CFCrmMemoDataPKey PKey) {
final String S_ProcName = "readBuff";
if (!schema.isTransactionOpen()) {
throw CFLib.getDefaultExceptionFactory()
.newUsageException(getClass(), S_ProcName, "Transaction not open");
}
ResultSet resultSet = null;
try {
Connection cnx = schema.getCnx();
long TenantId = PKey.getRequiredTenantId();
long MemoId = PKey.getRequiredMemoId();
String sql = "{ call sp_read_memodata( ?, ?, ?, ?, ?" + ", " + "?" + ", " + "?" + " ) }";
if (stmtReadBuffByPKey == null) {
stmtReadBuffByPKey = cnx.prepareStatement(sql);
}
int argIdx = 1;
stmtReadBuffByPKey.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
stmtReadBuffByPKey.setString(
argIdx++, (Authorization == null) ? "" : Authorization.getSecUserId().toString());
stmtReadBuffByPKey.setString(
argIdx++, (Authorization == null) ? "" : Authorization.getSecSessionId().toString());
stmtReadBuffByPKey.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
stmtReadBuffByPKey.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecTenantId());
stmtReadBuffByPKey.setLong(argIdx++, TenantId);
stmtReadBuffByPKey.setLong(argIdx++, MemoId);
resultSet = stmtReadBuffByPKey.executeQuery();
if ((resultSet != null) && resultSet.next()) {
CFCrmMemoDataBuff buff = unpackMemoDataResultSetToBuff(resultSet);
if (resultSet.next()) {
resultSet.last();
throw CFLib.getDefaultExceptionFactory()
.newRuntimeException(
getClass(),
S_ProcName,
"Did not expect multi-record response, " + resultSet.getRow() + " rows selected");
}
return (buff);
} else {
return (null);
}
} catch (SQLException e) {
throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
} finally {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
}
resultSet = null;
}
}
}
public CFCrmMemoDataBuff[] readAllBuff(CFSecurityAuthorization Authorization) {
final String S_ProcName = "readAllBuff";
if (!schema.isTransactionOpen()) {
throw CFLib.getDefaultExceptionFactory()
.newUsageException(getClass(), S_ProcName, "Transaction not open");
}
ResultSet resultSet = null;
try {
Connection cnx = schema.getCnx();
String sql = "{ call sp_read_memodata_all( ?, ?, ?, ?, ? ) }";
if (stmtReadAllBuff == null) {
stmtReadAllBuff = cnx.prepareStatement(sql);
}
int argIdx = 1;
stmtReadAllBuff.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
stmtReadAllBuff.setString(
argIdx++, (Authorization == null) ? "" : Authorization.getSecUserId().toString());
stmtReadAllBuff.setString(
argIdx++, (Authorization == null) ? "" : Authorization.getSecSessionId().toString());
stmtReadAllBuff.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
stmtReadAllBuff.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecTenantId());
resultSet = stmtReadAllBuff.executeQuery();
List<CFCrmMemoDataBuff> buffList = new LinkedList<CFCrmMemoDataBuff>();
if (resultSet != null) {
while (resultSet.next()) {
CFCrmMemoDataBuff buff = unpackMemoDataResultSetToBuff(resultSet);
buffList.add(buff);
}
}
int idx = 0;
CFCrmMemoDataBuff[] retBuff = new CFCrmMemoDataBuff[buffList.size()];
Iterator<CFCrmMemoDataBuff> iter = buffList.iterator();
while (iter.hasNext()) {
retBuff[idx++] = iter.next();
}
return (retBuff);
} catch (SQLException e) {
throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
} finally {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
}
resultSet = null;
}
}
}
public CFCrmMemoDataBuff[] readDerivedByMemoDataCtcIdx(
CFSecurityAuthorization Authorization, long TenantId, long ContactId) {
final String S_ProcName = "readDerivedByMemoDataCtcIdx";
if (!schema.isTransactionOpen()) {
throw CFLib.getDefaultExceptionFactory()
.newUsageException(getClass(), S_ProcName, "Transaction not open");
}
CFCrmMemoDataBuff[] buffList = readBuffByMemoDataCtcIdx(Authorization, TenantId, ContactId);
return (buffList);
}
public CFCrmMemoDataBuff[] readAllDerived(CFSecurityAuthorization Authorization) {
final String S_ProcName = "readAllDerived";
CFCrmMemoDataBuff[] buffArray;
if (!schema.isTransactionOpen()) {
throw CFLib.getDefaultExceptionFactory()
.newUsageException(getClass(), S_ProcName, "Transaction not open");
}
buffArray = readAllBuff(Authorization);
return (buffArray);
}
public CFCrmMemoDataBuff readDerivedByIdIdx(
CFSecurityAuthorization Authorization, long TenantId, long MemoId) {
final String S_ProcName = "CFCrmMSSqlMemoDataTable.readDerivedByIdIdx() ";
CFCrmMemoDataBuff buff;
if (!schema.isTransactionOpen()) {
throw CFLib.getDefaultExceptionFactory()
.newUsageException(getClass(), S_ProcName, "Transaction not open");
}
buff = readBuffByIdIdx(Authorization, TenantId, MemoId);
return (buff);
}
public CFCrmMemoDataBuff lockDerived(
CFSecurityAuthorization Authorization, CFCrmMemoDataPKey PKey) {
final String S_ProcName = "lockDerived";
if (!schema.isTransactionOpen()) {
throw CFLib.getDefaultExceptionFactory()
.newUsageException(getClass(), S_ProcName, "Transaction not open");
}
CFCrmMemoDataBuff buff;
buff = lockBuff(Authorization, PKey);
return (buff);
}
public String getSqlSelectMemoDataDistinctClassCode() {
if (S_sqlSelectMemoDataDistinctClassCode == null) {
S_sqlSelectMemoDataDistinctClassCode =
"SELECT "
+ "DISTINCT mmod.ClassCode "
+ "FROM "
+ schema.getLowerDbSchemaName()
+ "..MemoData AS mmod ";
}
return (S_sqlSelectMemoDataDistinctClassCode);
}
public String getSqlSelectMemoDataBuff() {
if (S_sqlSelectMemoDataBuff == null) {
S_sqlSelectMemoDataBuff =
"SELECT "
+ "mmod.tenantid, "
+ "mmod.memoid, "
+ "mmod.contactid, "
+ "mmod.memo, "
+ "mmod.revision "
+ "FROM "
+ schema.getLowerDbSchemaName()
+ "..MemoData AS mmod ";
}
return (S_sqlSelectMemoDataBuff);
}
public void deleteMemoData(CFSecurityAuthorization Authorization, CFCrmMemoDataBuff Buff) {
final String S_ProcName = "deleteMemoData";
try {
Connection cnx = schema.getCnx();
long TenantId = Buff.getRequiredTenantId();
long MemoId = Buff.getRequiredMemoId();
String sql = "exec sp_delete_memodata ?, ?, ?, ?, ?" + ", " + "?" + ", " + "?" + ", " + "?";
if (stmtDeleteByPKey == null) {
stmtDeleteByPKey = cnx.prepareStatement(sql);
}
int argIdx = 1;
stmtDeleteByPKey.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
stmtDeleteByPKey.setString(
argIdx++, (Authorization == null) ? "" : Authorization.getSecUserId().toString());
stmtDeleteByPKey.setString(
argIdx++, (Authorization == null) ? "" : Authorization.getSecSessionId().toString());
stmtDeleteByPKey.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
stmtDeleteByPKey.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecTenantId());
stmtDeleteByPKey.setLong(argIdx++, TenantId);
stmtDeleteByPKey.setLong(argIdx++, MemoId);
stmtDeleteByPKey.setInt(argIdx++, Buff.getRequiredRevision());
;
Object stuff = null;
boolean moreResults = stmtDeleteByPKey.execute();
while (stuff == null) {
try {
moreResults = stmtDeleteByPKey.getMoreResults();
} catch (SQLException e) {
throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
}
if (moreResults) {
try {
stuff = stmtDeleteByPKey.getResultSet();
} catch (SQLException e) {
}
} else if (-1 == stmtDeleteByPKey.getUpdateCount()) {
break;
}
}
} catch (SQLException e) {
throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
}
}
public void updateMemoData(CFSecurityAuthorization Authorization, CFCrmMemoDataBuff Buff) {
final String S_ProcName = "updateMemoData";
if ("MMOD".equals(Buff.getClassCode())
&& (!schema.isTenantUser(Authorization, Buff.getRequiredTenantId(), "UpdateMemoData"))) {
throw CFLib.getDefaultExceptionFactory()
.newRuntimeException(
getClass(),
S_ProcName,
"Permission denied -- User not part of TSecGroup UpdateMemoData");
}
try {
Connection cnx = schema.getCnx();
long TenantId = Buff.getRequiredTenantId();
long MemoId = Buff.getRequiredMemoId();
long ContactId = Buff.getRequiredContactId();
String Memo = Buff.getRequiredMemo();
int Revision = Buff.getRequiredRevision();
CFCrmMemoDataPKey pkey = schema.getFactoryMemoData().newPKey();
pkey.setRequiredTenantId(Buff.getRequiredTenantId());
pkey.setRequiredMemoId(Buff.getRequiredMemoId());
CFCrmMemoDataBuff readBuff = lockBuff(Authorization, pkey);
if (readBuff == null) {
throw CFLib.getDefaultExceptionFactory()
.newStaleCacheDetectedException(
getClass(),
S_ProcName,
"Attempted to update record which could not be locked/found",
schema.getLowerDbSchemaName() + "..memodata",
pkey);
}
int oldRevision = readBuff.getRequiredRevision();
if (oldRevision != Revision) {
throw CFLib.getDefaultExceptionFactory()
.newCollisionDetectedException(getClass(), S_ProcName, Buff);
}
int newRevision = Revision + 1;
String sql =
"UPDATE "
+ schema.getLowerDbSchemaName()
+ "..MemoData "
+ "SET "
+ "tenantid = ?"
+ ", "
+ "memoid = ?"
+ ", "
+ "contactid = ?"
+ ", "
+ "memo = ?"
+ ", "
+ "updatedby = ?, "
+ "updatedat = sysdatetime() "
+ ", revision = ? "
+ " WHERE "
+ "tenantid = ? "
+ "AND "
+ "memoid = ? "
+ "AND "
+ "revision = ? ";
if (stmtUpdateByPKey == null) {
stmtUpdateByPKey = cnx.prepareStatement(sql);
}
int argIdx = 1;
stmtUpdateByPKey.setLong(argIdx++, TenantId);
stmtUpdateByPKey.setLong(argIdx++, MemoId);
stmtUpdateByPKey.setLong(argIdx++, ContactId);
stmtUpdateByPKey.setString(argIdx++, Memo);
stmtUpdateByPKey.setString(argIdx++, Authorization.getSecUserId().toString());
stmtUpdateByPKey.setInt(argIdx++, newRevision);
stmtUpdateByPKey.setLong(argIdx++, TenantId);
stmtUpdateByPKey.setLong(argIdx++, MemoId);
stmtUpdateByPKey.setInt(argIdx++, Revision);
;
int rowsAffected = stmtUpdateByPKey.executeUpdate();
if (rowsAffected != 1) {
throw CFLib.getDefaultExceptionFactory()
.newRuntimeException(
getClass(),
S_ProcName,
"Expected 1 row to be affected by update, not " + rowsAffected);
}
Buff.setRequiredRevision(newRevision);
String sqlAuditUpdated =
"INSERT INTO "
+ schema.getLowerDbSchemaName()
+ "..MemoData_h( auditclusterid, "
+ " auditsessionid, "
+ " auditstamp"
+ ", "
+ "tenantid"
+ ", "
+ "memoid"
+ ", "
+ "contactid"
+ ", "
+ "memo"
+ ", "
+ " revision, "
+ " auditaction ) "
+ "SELECT ?, ?, sysdatetime()"
+ ", "
+ "mmod.tenantid"
+ ", "
+ "mmod.memoid"
+ ", "
+ "mmod.contactid"
+ ", "
+ "mmod.memo"
+ ", "
+ " mmod.revision, "
+ " 1 "
+ "FROM "
+ schema.getLowerDbSchemaName()
+ "..MemoData AS mmod "
+ " WHERE "
+ "mmod.tenantid = ? "
+ "AND mmod.memoid = ? ";
if (stmtAuditUpdatedByPKey == null) {
stmtAuditUpdatedByPKey = cnx.prepareStatement(sqlAuditUpdated);
}
argIdx = 1;
stmtAuditUpdatedByPKey.setLong(argIdx++, Authorization.getSecClusterId());
stmtAuditUpdatedByPKey.setString(argIdx++, Authorization.getSecSessionId().toString());
stmtAuditUpdatedByPKey.setLong(argIdx++, TenantId);
stmtAuditUpdatedByPKey.setLong(argIdx++, MemoId);
int rowsAudited = stmtAuditUpdatedByPKey.executeUpdate();
if (rowsAudited != 1) {
throw CFLib.getDefaultExceptionFactory()
.newRuntimeException(
getClass(),
S_ProcName,
"Expected 1 row to be affected by audit via insert-selected, not " + rowsAffected);
}
} catch (SQLException e) {
throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
}
}
public void createMemoData(CFSecurityAuthorization Authorization, CFCrmMemoDataBuff Buff) {
final String S_ProcName = "createMemoData";
if ("MMOD".equals(Buff.getClassCode())
&& (!schema.isTenantUser(Authorization, Buff.getRequiredTenantId(), "CreateMemoData"))) {
throw CFLib.getDefaultExceptionFactory()
.newRuntimeException(
getClass(),
S_ProcName,
"Permission denied -- User not part of TSecGroup CreateMemoData");
}
if (!schema.isTransactionOpen()) {
throw CFLib.getDefaultExceptionFactory()
.newUsageException(getClass(), S_ProcName, "Transaction not open");
}
try {
Connection cnx = schema.getCnx();
long TenantId = Buff.getRequiredTenantId();
long MemoId = Buff.getRequiredMemoId();
long ContactId = Buff.getRequiredContactId();
String Memo = Buff.getRequiredMemo();
int Revision = 1;
String sql =
"INSERT INTO "
+ schema.getLowerDbSchemaName()
+ "..MemoData( "
+ "forcesynclock, "
+ "createdby, "
+ "createdat, "
+ "updatedby, "
+ "updatedat, "
+ "tenantid, "
+ "memoid, "
+ "contactid, "
+ "memo"
+ ", revision )"
+ "VALUES ( "
+ "0, "
+ " ?, "
+ " getdate(), "
+ " ?, "
+ " getdate(), "
+ "?"
+ ", "
+ "?"
+ ", "
+ "?"
+ ", "
+ "?"
+ ", "
+ "1 )";
if (stmtCreateByPKey == null) {
stmtCreateByPKey = cnx.prepareStatement(sql);
}
int argIdx = 1;
stmtCreateByPKey.setString(argIdx++, Authorization.getSecUserId().toString());
stmtCreateByPKey.setString(argIdx++, Authorization.getSecUserId().toString());
stmtCreateByPKey.setLong(argIdx++, TenantId);
stmtCreateByPKey.setLong(argIdx++, MemoId);
stmtCreateByPKey.setLong(argIdx++, ContactId);
stmtCreateByPKey.setString(argIdx++, Memo);
int rowsAffected = stmtCreateByPKey.executeUpdate();
if (rowsAffected != 1) {
throw CFLib.getDefaultExceptionFactory()
.newRuntimeException(
getClass(),
S_ProcName,
"Expected 1 row to be affected by insert, not " + rowsAffected);
}
Buff.setRequiredRevision(Revision);
String sqlAuditCreated =
"INSERT INTO "
+ schema.getLowerDbSchemaName()
+ "..MemoData_h( auditclusterid, "
+ " auditsessionid, "
+ " auditstamp"
+ ", "
+ "tenantid"
+ ", "
+ "memoid"
+ ", "
+ "contactid"
+ ", "
+ "memo"
+ ", "
+ " revision, "
+ " auditaction ) "
+ "SELECT ?, ?, sysdatetime()"
+ ", "
+ "mmod.tenantid"
+ ", "
+ "mmod.memoid"
+ ", "
+ "mmod.contactid"
+ ", "
+ "mmod.memo"
+ ", "
+ " mmod.revision, "
+ " 0"
+ "FROM "
+ schema.getLowerDbSchemaName()
+ "..MemoData AS mmod "
+ " WHERE "
+ "mmod.tenantid = ? "
+ "AND mmod.memoid = ? ";
if (stmtAuditCreatedByPKey == null) {
stmtAuditCreatedByPKey = cnx.prepareStatement(sqlAuditCreated);
}
argIdx = 1;
stmtAuditCreatedByPKey.setLong(argIdx++, Authorization.getSecClusterId());
stmtAuditCreatedByPKey.setString(argIdx++, Authorization.getSecSessionId().toString());
stmtAuditCreatedByPKey.setLong(argIdx++, TenantId);
stmtAuditCreatedByPKey.setLong(argIdx++, MemoId);
int rowsAudited = stmtAuditCreatedByPKey.executeUpdate();
if (rowsAudited != 1) {
throw CFLib.getDefaultExceptionFactory()
.newRuntimeException(
getClass(),
S_ProcName,
"Expected 1 row to be affected by audit via insert-selected, not " + rowsAffected);
}
} catch (SQLException e) {
throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
}
}