Index: src/main/groovy/com/lemanscorp/mqconsumer/services/AttributeNameService.groovy =================================================================== diff -u -rbbf2e5405d63748b1584efc46c75fc36f48d22d7 -r176022c00ae6a69ff0e5449aa62eb9101a850d93 --- src/main/groovy/com/lemanscorp/mqconsumer/services/AttributeNameService.groovy (.../AttributeNameService.groovy) (revision bbf2e5405d63748b1584efc46c75fc36f48d22d7) +++ src/main/groovy/com/lemanscorp/mqconsumer/services/AttributeNameService.groovy (.../AttributeNameService.groovy) (revision 176022c00ae6a69ff0e5449aa62eb9101a850d93) @@ -1,78 +1,78 @@ -package com.lemanscorp.mqconsumer.services - -import com.lemanscorp.mqconsumer.domains.AttributeName -import groovy.util.logging.Slf4j -import org.springframework.beans.factory.annotation.Autowired -import org.springframework.beans.factory.annotation.Qualifier -import org.springframework.jdbc.core.RowMapper -import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate -import org.springframework.stereotype.Service - -import java.sql.ResultSet - -@Slf4j -@Service -class AttributeNameService { - - @Autowired - NamedParameterJdbcTemplate usDigiJdbcTemplate - - @Autowired - @Qualifier("europeDigiJdbcTemplate") - NamedParameterJdbcTemplate euDigiJdbcTemplate - - void createOrUpdate(Integer id) { - AttributeName attributeName = attributeNameById(id) - euDigiJdbcTemplate.update(ATTRIBUTE_NAME_UPSERT_SQL, attributeName.properties) - } - - AttributeName attributeNameById(Integer id) { - usDigiJdbcTemplate.queryForObject(ATTRIBUTE_NAME_SELECT_SQL, attributeName(id), attributeNameMapper) - } - - private RowMapper attributeNameMapper = { ResultSet rs, row -> - new AttributeName ( - id: (Integer) rs.getObject("attributeNameId"), - attributeName: (String) rs.getObject("attributeName"), - attributeDisplayName: (String) rs.getObject("attributeDisplayName"), - sourceId: (Integer) rs.getObject("sourceId"), - source: (Integer) rs.getObject("source"), - dateCreated: rs.getDate("dateCreated"), - createdBy: rs.getString("createdBy"), - lastUpdated: rs.getDate("lastUpdated"), - lastUpdatedBy: rs.getString("lastUpdatedBy"), - dateDeleted: rs.getDate("dateDeleted") ? new Date() : null, - deletedBy: rs.getString("deletedBy"), - version: (Integer) rs.getObject("version") - ) - - } - - private Map attributeName(Integer id) { [attributeNameId: id] } - - private static final String ATTRIBUTE_NAME_SELECT_SQL = "SELECT * FROM AttributeName WHERE attributeNameId = :attributeNameId" - - private static final String ATTRIBUTE_NAME_UPSERT_SQL = """ - SET Identity_Insert [AttributeName] ON; - MERGE AttributeName an - USING (SELECT :id AS id) source - ON an.attributeNameId = source.id - WHEN matched THEN - UPDATE SET - attributeName = :attributeName, - attributeDisplayName = :attributeDisplayName, - sourceId = :sourceId, - source = :source, - dateCreated = :dateCreated, - createdBy = :createdBy, - lastUpdated = :lastUpdated, - lastUpdatedBy = :lastUpdatedBy, - dateDeleted = :dateDeleted, - deletedBy = :deletedBy, - version = :version - WHEN NOT matched THEN - INSERT (attributeNameId, attributeName, attributeDisplayName, source, sourceId, dateCreated, createdBy, lastUpdated, lastUpdatedBy, dateDeleted, deletedBy, version) - VALUES(:id, :attributeName, :attributeDisplayName,:source, :sourceId, :dateCreated, :createdBy, :lastUpdated, :lastUpdatedBy, :dateDeleted, :deletedBy, :version); - SET Identity_Insert [AttributeName] OFF; - """ -} +package com.lemanscorp.mqconsumer.services + +import com.lemanscorp.mqconsumer.domains.AttributeName +import groovy.util.logging.Slf4j +import org.springframework.beans.factory.annotation.Autowired +import org.springframework.beans.factory.annotation.Qualifier +import org.springframework.jdbc.core.RowMapper +import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate +import org.springframework.stereotype.Service + +import java.sql.ResultSet + +@Slf4j +@Service +class AttributeNameService { + + @Autowired + NamedParameterJdbcTemplate usDigiJdbcTemplate + + @Autowired + @Qualifier("europeDigiJdbcTemplate") + NamedParameterJdbcTemplate euDigiJdbcTemplate + + void createOrUpdate(Integer id) { + AttributeName attributeName = attributeNameById(id) + euDigiJdbcTemplate.update(ATTRIBUTE_NAME_UPSERT_SQL, attributeName.properties) + } + + AttributeName attributeNameById(Integer id) { + usDigiJdbcTemplate.queryForObject(ATTRIBUTE_NAME_SELECT_SQL, attributeName(id), attributeNameMapper) + } + + private RowMapper attributeNameMapper = { ResultSet rs, row -> + new AttributeName ( + id: (Integer) rs.getObject("attributeNameId"), + attributeName: (String) rs.getObject("attributeName"), + attributeDisplayName: (String) rs.getObject("attributeDisplayName"), + sourceId: (Integer) rs.getObject("sourceId"), + source: (Integer) rs.getObject("source"), + dateCreated: rs.getTimestamp("dateCreated"), + createdBy: rs.getString("createdBy"), + lastUpdated: rs.getTimestamp("lastUpdated"), + lastUpdatedBy: rs.getString("lastUpdatedBy"), + dateDeleted: rs.getTimestamp("dateDeleted") ? new Date() : null, + deletedBy: rs.getString("deletedBy"), + version: (Integer) rs.getObject("version") + ) + + } + + private Map attributeName(Integer id) { [attributeNameId: id] } + + private static final String ATTRIBUTE_NAME_SELECT_SQL = "SELECT * FROM AttributeName WHERE attributeNameId = :attributeNameId" + + private static final String ATTRIBUTE_NAME_UPSERT_SQL = """ + SET Identity_Insert [AttributeName] ON; + MERGE AttributeName an + USING (SELECT :id AS id) source + ON an.attributeNameId = source.id + WHEN matched THEN + UPDATE SET + attributeName = :attributeName, + attributeDisplayName = :attributeDisplayName, + sourceId = :sourceId, + source = :source, + dateCreated = :dateCreated, + createdBy = :createdBy, + lastUpdated = :lastUpdated, + lastUpdatedBy = :lastUpdatedBy, + dateDeleted = :dateDeleted, + deletedBy = :deletedBy, + version = :version + WHEN NOT matched THEN + INSERT (attributeNameId, attributeName, attributeDisplayName, source, sourceId, dateCreated, createdBy, lastUpdated, lastUpdatedBy, dateDeleted, deletedBy, version) + VALUES(:id, :attributeName, :attributeDisplayName,:source, :sourceId, :dateCreated, :createdBy, :lastUpdated, :lastUpdatedBy, :dateDeleted, :deletedBy, :version); + SET Identity_Insert [AttributeName] OFF; + """ +} Index: src/main/groovy/com/lemanscorp/mqconsumer/services/AttributeValueService.groovy =================================================================== diff -u -rbbf2e5405d63748b1584efc46c75fc36f48d22d7 -r176022c00ae6a69ff0e5449aa62eb9101a850d93 --- src/main/groovy/com/lemanscorp/mqconsumer/services/AttributeValueService.groovy (.../AttributeValueService.groovy) (revision bbf2e5405d63748b1584efc46c75fc36f48d22d7) +++ src/main/groovy/com/lemanscorp/mqconsumer/services/AttributeValueService.groovy (.../AttributeValueService.groovy) (revision 176022c00ae6a69ff0e5449aa62eb9101a850d93) @@ -1,76 +1,76 @@ -package com.lemanscorp.mqconsumer.services - -import com.lemanscorp.mqconsumer.domains.AttributeValue -import groovy.util.logging.Slf4j -import org.springframework.beans.factory.annotation.Autowired -import org.springframework.beans.factory.annotation.Qualifier -import org.springframework.jdbc.core.RowMapper -import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate -import org.springframework.stereotype.Service - -import java.sql.ResultSet - -@Slf4j -@Service -class AttributeValueService { - - @Autowired - NamedParameterJdbcTemplate usDigiJdbcTemplate - - @Autowired - @Qualifier("europeDigiJdbcTemplate") - NamedParameterJdbcTemplate euDigiJdbcTemplate - - void createOrUpdate(Integer id) { - AttributeValue attributeValue = getAttributeValueById(id) - euDigiJdbcTemplate.update(ATTRIBUTE_VALUE_UPSERT_SQL, attributeValue.properties) - } - - private AttributeValue getAttributeValueById(Integer id) { - usDigiJdbcTemplate.queryForObject(ATTRIBUTE_VALUE_SELECT_SQL, attributeValue(id), attributeValueMapper) - } - - private RowMapper attributeValueMapper = { ResultSet rs, row -> - new AttributeValue ( - id: (Integer) rs.getObject("attributeValueId"), - attributeValue: (String) rs.getObject("attributeValue"), - sourceId: (Integer) rs.getObject("sourceId"), - source: (Integer) rs.getObject("source"), - dateCreated: rs.getDate("dateCreated"), - createdBy: rs.getString("createdBy"), - lastUpdated: rs.getDate("lastUpdated"), - lastUpdatedBy: rs.getString("lastUpdatedBy"), - dateDeleted: rs.getDate("dateDeleted") ? new Date() : null, - deletedBy: rs.getString("deletedBy"), - version: (Integer) rs.getObject("version") - ) - - } - - private Map attributeValue(Integer id) { [attributeValueId: id] } - - private static final String ATTRIBUTE_VALUE_SELECT_SQL = "SELECT * FROM AttributeValue WHERE attributeValueId = :attributeValueId" - - private static final String ATTRIBUTE_VALUE_UPSERT_SQL = """ - SET Identity_Insert [AttributeValue] ON; - MERGE AttributeValue an - USING (SELECT :id AS id) source - ON an.attributeValueId = source.id - WHEN matched THEN - UPDATE SET - attributeValue = :attributeValue, - sourceId = :sourceId, - source = :source, - dateCreated = :dateCreated, - createdBy = :createdBy, - lastUpdated = :lastUpdated, - lastUpdatedBy = :lastUpdatedBy, - dateDeleted = :dateDeleted, - deletedBy = :deletedBy, - version = :version - WHEN NOT matched THEN - INSERT (attributeValueId, attributeValue, source, sourceId, dateCreated, createdBy, lastUpdated, lastUpdatedBy, dateDeleted, deletedBy, version) - VALUES(:id, :attributeValue, :source, :sourceId, :dateCreated, :createdBy, :lastUpdated, :lastUpdatedBy, :dateDeleted, :deletedBy, :version); - SET Identity_Insert [AttributeValue] OFF; - """ -} +package com.lemanscorp.mqconsumer.services + +import com.lemanscorp.mqconsumer.domains.AttributeValue +import groovy.util.logging.Slf4j +import org.springframework.beans.factory.annotation.Autowired +import org.springframework.beans.factory.annotation.Qualifier +import org.springframework.jdbc.core.RowMapper +import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate +import org.springframework.stereotype.Service + +import java.sql.ResultSet + +@Slf4j +@Service +class AttributeValueService { + + @Autowired + NamedParameterJdbcTemplate usDigiJdbcTemplate + + @Autowired + @Qualifier("europeDigiJdbcTemplate") + NamedParameterJdbcTemplate euDigiJdbcTemplate + + void createOrUpdate(Integer id) { + AttributeValue attributeValue = getAttributeValueById(id) + euDigiJdbcTemplate.update(ATTRIBUTE_VALUE_UPSERT_SQL, attributeValue.properties) + } + + private AttributeValue getAttributeValueById(Integer id) { + usDigiJdbcTemplate.queryForObject(ATTRIBUTE_VALUE_SELECT_SQL, attributeValue(id), attributeValueMapper) + } + + private RowMapper attributeValueMapper = { ResultSet rs, row -> + new AttributeValue ( + id: (Integer) rs.getObject("attributeValueId"), + attributeValue: (String) rs.getObject("attributeValue"), + sourceId: (Integer) rs.getObject("sourceId"), + source: (Integer) rs.getObject("source"), + dateCreated: rs.getTimestamp("dateCreated"), + createdBy: rs.getString("createdBy"), + lastUpdated: rs.getTimestamp("lastUpdated"), + lastUpdatedBy: rs.getString("lastUpdatedBy"), + dateDeleted: rs.getTimestamp("dateDeleted") ? new Date() : null, + deletedBy: rs.getString("deletedBy"), + version: (Integer) rs.getObject("version") + ) + + } + + private Map attributeValue(Integer id) { [attributeValueId: id] } + + private static final String ATTRIBUTE_VALUE_SELECT_SQL = "SELECT * FROM AttributeValue WHERE attributeValueId = :attributeValueId" + + private static final String ATTRIBUTE_VALUE_UPSERT_SQL = """ + SET Identity_Insert [AttributeValue] ON; + MERGE AttributeValue an + USING (SELECT :id AS id) source + ON an.attributeValueId = source.id + WHEN matched THEN + UPDATE SET + attributeValue = :attributeValue, + sourceId = :sourceId, + source = :source, + dateCreated = :dateCreated, + createdBy = :createdBy, + lastUpdated = :lastUpdated, + lastUpdatedBy = :lastUpdatedBy, + dateDeleted = :dateDeleted, + deletedBy = :deletedBy, + version = :version + WHEN NOT matched THEN + INSERT (attributeValueId, attributeValue, source, sourceId, dateCreated, createdBy, lastUpdated, lastUpdatedBy, dateDeleted, deletedBy, version) + VALUES(:id, :attributeValue, :source, :sourceId, :dateCreated, :createdBy, :lastUpdated, :lastUpdatedBy, :dateDeleted, :deletedBy, :version); + SET Identity_Insert [AttributeValue] OFF; + """ +} Index: src/main/groovy/com/lemanscorp/mqconsumer/services/CategoryAttributeService.groovy =================================================================== diff -u -rbbf2e5405d63748b1584efc46c75fc36f48d22d7 -r176022c00ae6a69ff0e5449aa62eb9101a850d93 --- src/main/groovy/com/lemanscorp/mqconsumer/services/CategoryAttributeService.groovy (.../CategoryAttributeService.groovy) (revision bbf2e5405d63748b1584efc46c75fc36f48d22d7) +++ src/main/groovy/com/lemanscorp/mqconsumer/services/CategoryAttributeService.groovy (.../CategoryAttributeService.groovy) (revision 176022c00ae6a69ff0e5449aa62eb9101a850d93) @@ -1,136 +1,136 @@ -package com.lemanscorp.mqconsumer.services - -import java.sql.ResultSet - -import com.lemanscorp.mqconsumer.domains.CategoryAttribute -import groovy.util.logging.Slf4j -import org.springframework.beans.factory.annotation.Autowired -import org.springframework.beans.factory.annotation.Qualifier -import org.springframework.jdbc.core.RowMapper -import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource -import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate -import org.springframework.stereotype.Service -import org.springframework.transaction.support.TransactionTemplate - -@Slf4j -@Service -class CategoryAttributeService { - - @Autowired - NamedParameterJdbcTemplate usDigiJdbcTemplate - - @Autowired - @Qualifier("europeDigiJdbcTemplate") - NamedParameterJdbcTemplate euDigiJdbcTemplate - - @Autowired - @Qualifier("europeDigiTransactionTemplate") - TransactionTemplate euDigiTransactionTemplate - - void create(Integer id) { - CategoryAttribute categoryAttribute = getCategoryAttributeById(id) - euDigiTransactionTemplate.execute { - try { - euDigiJdbcTemplate.update(CATEGORY_ATTRIBUTE_UPSERT_SQL, categoryAttribute.properties) - euDigiJdbcTemplate.execute(CATEGORY_ATTRIBUTE_MOVE_SQL, [id: categoryAttribute.id, targetId: null, appSecurityUser: 'usToEuSyncProcess'], {}) - //TODO: Get the audit user as header from the actual service. - } catch (Exception e) { - log.error(e.message, e) - it.setRollbackOnly() - throw new Exception("Could not create CategoryAttribute") - } - } - } - - void update(Integer id) { - CategoryAttribute categoryAttribute = getCategoryAttributeById(id) - euDigiJdbcTemplate.update(CATEGORY_ATTRIBUTE_UPSERT_SQL, categoryAttribute.properties) - } - - void move(Integer id) { - CategoryAttribute categoryAttribute = getCategoryAttributeById(id) - List categoryAttributes = usDigiJdbcTemplate.query(CATEGORY_ATTRIBUTE_BY_CATEGORY_ID_SQL, [categoryId: categoryAttribute.categoryId], categoryAttributeMapper) - euDigiJdbcTemplate.batchUpdate(CATEGORY_ATTRIBUTE_UPSERT_SQL, categoryAttributes.collect { new BeanPropertySqlParameterSource(it)} as BeanPropertySqlParameterSource[]) - } - - private CategoryAttribute getCategoryAttributeById(Integer id) { - usDigiJdbcTemplate.queryForObject(CATEGORY_ATTRIBUTE_SELECT_SQL, categoryAttribute(id), categoryAttributeMapper) - } - - private RowMapper categoryAttributeMapper = { ResultSet rs, row -> - new CategoryAttribute ( - id: (Integer) rs.getObject("categoryAttributeId"), - categoryId: (Integer) rs.getObject("categoryId"), - attributeNameId: (Integer) rs.getObject("attributeNameId"), - sequence: (Integer) rs.getObject("sequence"), - source: (Integer) rs.getObject("source"), - sourceId: (Integer) rs.getObject("sourceId"), - isRequired: rs.getBoolean("isRequired"), - allowMultipleValues: rs.getBoolean("allowMultipleValues"), - isDropDown: rs.getBoolean("isDropDown"), - isHidden: rs.getBoolean("isHidden"), - isGroup: rs.getBoolean("isGroup"), - dateCreated: rs.getDate("dateCreated"), - createdBy: rs.getString("createdBy"), - lastUpdated: rs.getDate("lastUpdated"), - lastUpdatedBy: rs.getString("lastUpdatedBy"), - dateDeleted: rs.getDate("dateDeleted") ? new Date() : null, - deletedBy: rs.getString("deletedBy"), - version: (Integer) rs.getObject("version") - ) - } - - private Map categoryAttribute(Integer id) { [categoryAttributeId: id] } - - private static final String CATEGORY_ATTRIBUTE_SELECT_SQL = "SELECT * FROM CategoryAttribute WHERE categoryAttributeId = :categoryAttributeId" - - private static final String CATEGORY_ATTRIBUTE_BY_CATEGORY_ID_SQL = "SELECT * FROM CategoryAttribute WHERE categoryId = :categoryId AND dateDeleted IS NULL" - - private static final String CATEGORY_ATTRIBUTE_MOVE_SQL = '''EXEC dbo.spMoveCategoryAttribute - @sourceId = :id, - @targetId = :targetId, - @appSecurityUser = :appSecurityUser - ''' - - private static final String CATEGORY_ATTRIBUTE_UPSERT_SQL = """ - SET Identity_Insert [CategoryAttribute] ON; - MERGE CategoryAttribute ca - USING (SELECT :id AS id) source - ON ca.categoryAttributeId = source.id - WHEN matched THEN - UPDATE SET - categoryId = :categoryId, - attributeNameId = :attributeNameId, - sequence = :sequence, - source = :source, - sourceId = :sourceId, - isRequired = :isRequired, - allowMultipleValues = :allowMultipleValues, - dateCreated = :dateCreated, - createdBy = :createdBy, - lastUpdated = :lastUpdated, - lastUpdatedBy = :lastUpdatedBy, - dateDeleted = :dateDeleted, - deletedBy = :deletedBy, - version = :version - WHEN NOT matched THEN - INSERT (categoryAttributeId, categoryId, attributeNameId, sequence, source, sourceId, isRequired, allowMultipleValues, dateCreated, createdBy, lastUpdated, lastUpdatedBy, dateDeleted, deletedBy, version) - VALUES( - :id, - :categoryId, - :attributeNameId, - :sequence, - :source, - :sourceId, - :isRequired, - :allowMultipleValues, - :dateCreated, - :createdBy, - :lastUpdated, - :lastUpdatedBy, - :dateDeleted, - :deletedBy, - :version); - SET Identity_Insert [CategoryAttribute] OFF; - """ -} +package com.lemanscorp.mqconsumer.services + +import java.sql.ResultSet + +import com.lemanscorp.mqconsumer.domains.CategoryAttribute +import groovy.util.logging.Slf4j +import org.springframework.beans.factory.annotation.Autowired +import org.springframework.beans.factory.annotation.Qualifier +import org.springframework.jdbc.core.RowMapper +import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource +import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate +import org.springframework.stereotype.Service +import org.springframework.transaction.support.TransactionTemplate + +@Slf4j +@Service +class CategoryAttributeService { + + @Autowired + NamedParameterJdbcTemplate usDigiJdbcTemplate + + @Autowired + @Qualifier("europeDigiJdbcTemplate") + NamedParameterJdbcTemplate euDigiJdbcTemplate + + @Autowired + @Qualifier("europeDigiTransactionTemplate") + TransactionTemplate euDigiTransactionTemplate + + void create(Integer id) { + CategoryAttribute categoryAttribute = getCategoryAttributeById(id) + euDigiTransactionTemplate.execute { + try { + euDigiJdbcTemplate.update(CATEGORY_ATTRIBUTE_UPSERT_SQL, categoryAttribute.properties) + euDigiJdbcTemplate.execute(CATEGORY_ATTRIBUTE_MOVE_SQL, [id: categoryAttribute.id, targetId: null, appSecurityUser: 'usToEuSyncProcess'], {}) + //TODO: Get the audit user as header from the actual service. + } catch (Exception e) { + log.error(e.message, e) + it.setRollbackOnly() + throw new Exception("Could not create CategoryAttribute") + } + } + } + + void update(Integer id) { + CategoryAttribute categoryAttribute = getCategoryAttributeById(id) + euDigiJdbcTemplate.update(CATEGORY_ATTRIBUTE_UPSERT_SQL, categoryAttribute.properties) + } + + void move(Integer id) { + CategoryAttribute categoryAttribute = getCategoryAttributeById(id) + List categoryAttributes = usDigiJdbcTemplate.query(CATEGORY_ATTRIBUTE_BY_CATEGORY_ID_SQL, [categoryId: categoryAttribute.categoryId], categoryAttributeMapper) + euDigiJdbcTemplate.batchUpdate(CATEGORY_ATTRIBUTE_UPSERT_SQL, categoryAttributes.collect { new BeanPropertySqlParameterSource(it)} as BeanPropertySqlParameterSource[]) + } + + private CategoryAttribute getCategoryAttributeById(Integer id) { + usDigiJdbcTemplate.queryForObject(CATEGORY_ATTRIBUTE_SELECT_SQL, categoryAttribute(id), categoryAttributeMapper) + } + + private RowMapper categoryAttributeMapper = { ResultSet rs, row -> + new CategoryAttribute ( + id: (Integer) rs.getObject("categoryAttributeId"), + categoryId: (Integer) rs.getObject("categoryId"), + attributeNameId: (Integer) rs.getObject("attributeNameId"), + sequence: (Integer) rs.getObject("sequence"), + source: (Integer) rs.getObject("source"), + sourceId: (Integer) rs.getObject("sourceId"), + isRequired: rs.getBoolean("isRequired"), + allowMultipleValues: rs.getBoolean("allowMultipleValues"), + isDropDown: rs.getBoolean("isDropDown"), + isHidden: rs.getBoolean("isHidden"), + isGroup: rs.getBoolean("isGroup"), + dateCreated: rs.getTimestamp("dateCreated"), + createdBy: rs.getString("createdBy"), + lastUpdated: rs.getTimestamp("lastUpdated"), + lastUpdatedBy: rs.getString("lastUpdatedBy"), + dateDeleted: rs.getTimestamp("dateDeleted") ? new Date() : null, + deletedBy: rs.getString("deletedBy"), + version: (Integer) rs.getObject("version") + ) + } + + private Map categoryAttribute(Integer id) { [categoryAttributeId: id] } + + private static final String CATEGORY_ATTRIBUTE_SELECT_SQL = "SELECT * FROM CategoryAttribute WHERE categoryAttributeId = :categoryAttributeId" + + private static final String CATEGORY_ATTRIBUTE_BY_CATEGORY_ID_SQL = "SELECT * FROM CategoryAttribute WHERE categoryId = :categoryId AND dateDeleted IS NULL" + + private static final String CATEGORY_ATTRIBUTE_MOVE_SQL = '''EXEC dbo.spMoveCategoryAttribute + @sourceId = :id, + @targetId = :targetId, + @appSecurityUser = :appSecurityUser + ''' + + private static final String CATEGORY_ATTRIBUTE_UPSERT_SQL = """ + SET Identity_Insert [CategoryAttribute] ON; + MERGE CategoryAttribute ca + USING (SELECT :id AS id) source + ON ca.categoryAttributeId = source.id + WHEN matched THEN + UPDATE SET + categoryId = :categoryId, + attributeNameId = :attributeNameId, + sequence = :sequence, + source = :source, + sourceId = :sourceId, + isRequired = :isRequired, + allowMultipleValues = :allowMultipleValues, + dateCreated = :dateCreated, + createdBy = :createdBy, + lastUpdated = :lastUpdated, + lastUpdatedBy = :lastUpdatedBy, + dateDeleted = :dateDeleted, + deletedBy = :deletedBy, + version = :version + WHEN NOT matched THEN + INSERT (categoryAttributeId, categoryId, attributeNameId, sequence, source, sourceId, isRequired, allowMultipleValues, dateCreated, createdBy, lastUpdated, lastUpdatedBy, dateDeleted, deletedBy, version) + VALUES( + :id, + :categoryId, + :attributeNameId, + :sequence, + :source, + :sourceId, + :isRequired, + :allowMultipleValues, + :dateCreated, + :createdBy, + :lastUpdated, + :lastUpdatedBy, + :dateDeleted, + :deletedBy, + :version); + SET Identity_Insert [CategoryAttribute] OFF; + """ +} Index: src/main/groovy/com/lemanscorp/mqconsumer/services/CategoryAttributeValueService.groovy =================================================================== diff -u -rbbf2e5405d63748b1584efc46c75fc36f48d22d7 -r176022c00ae6a69ff0e5449aa62eb9101a850d93 --- src/main/groovy/com/lemanscorp/mqconsumer/services/CategoryAttributeValueService.groovy (.../CategoryAttributeValueService.groovy) (revision bbf2e5405d63748b1584efc46c75fc36f48d22d7) +++ src/main/groovy/com/lemanscorp/mqconsumer/services/CategoryAttributeValueService.groovy (.../CategoryAttributeValueService.groovy) (revision 176022c00ae6a69ff0e5449aa62eb9101a850d93) @@ -1,122 +1,122 @@ -package com.lemanscorp.mqconsumer.services - -import java.sql.ResultSet - -import com.lemanscorp.mqconsumer.domains.CategoryAttributeValue -import groovy.util.logging.Slf4j -import org.springframework.beans.factory.annotation.Autowired -import org.springframework.beans.factory.annotation.Qualifier -import org.springframework.jdbc.core.RowMapper -import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource -import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate -import org.springframework.stereotype.Service -import org.springframework.transaction.support.TransactionTemplate - -@Slf4j -@Service -class CategoryAttributeValueService { - - @Autowired - NamedParameterJdbcTemplate usDigiJdbcTemplate - - @Autowired - @Qualifier("europeDigiJdbcTemplate") - NamedParameterJdbcTemplate euDigiJdbcTemplate - - @Autowired - @Qualifier("europeDigiTransactionTemplate") - TransactionTemplate euDigiTransactionTemplate - - void create(Integer id) { - CategoryAttributeValue categoryAttributeValue = getCategoryAttributeValueById(id) - euDigiTransactionTemplate.execute { - try { - euDigiJdbcTemplate.update(CATEGORY_ATTRIBUTE_VALUE_UPSERT_SQL, categoryAttributeValue.properties) - euDigiJdbcTemplate.execute(CATEGORY_ATTRIBUTE_VALUE_MOVE_SQL, [id: categoryAttributeValue.id, targetId: null, appSecurityUser: 'usToEuSyncProcess'], {}) - //TODO: Get the audit user as header from the actual service. - } catch (Exception e) { - log.error(e.message, e) - it.setRollbackOnly() - throw new Exception("Could not create CategoryAttributeValue") - } - } - } - - void update(Integer id) { - CategoryAttributeValue categoryAttributeValue = getCategoryAttributeValueById(id) - euDigiJdbcTemplate.update(CATEGORY_ATTRIBUTE_VALUE_UPSERT_SQL, categoryAttributeValue.properties) - } - - void move(Integer id) { - CategoryAttributeValue categoryAttributeValue = getCategoryAttributeValueById(id) - List categoryAttributeValues = usDigiJdbcTemplate.query( - CATEGORY_ATTRIBUTE_VALUE_BY_CATEGORY_ATTRIBUTE_SQL, - [categoryAttributeId: categoryAttributeValue.categoryAttributeId], - categoryAttributeValueMapper - ) - euDigiJdbcTemplate.batchUpdate(CATEGORY_ATTRIBUTE_VALUE_UPSERT_SQL, categoryAttributeValues.collect { new BeanPropertySqlParameterSource(it)} as BeanPropertySqlParameterSource[]) - } - - private CategoryAttributeValue getCategoryAttributeValueById(Integer id) { - usDigiJdbcTemplate.queryForObject(CATEGORY_ATTRIBUTE_VALUE_SELECT_SQL, categoryAttributeValue(id) ,categoryAttributeValueMapper) - } - - private RowMapper categoryAttributeValueMapper = { ResultSet rs, row -> - new CategoryAttributeValue( - id: (Integer) rs.getObject("categoryAttributeValueId"), - categoryAttributeId: (Integer) rs.getObject("categoryAttributeId"), - attributeValueId: (Integer) rs.getObject("attributeValueId"), - sequence: (Integer) rs.getObject("sequence"), - source: (Integer) rs.getObject("source"), - sourceId: (Integer) rs.getObject("sourceId"), - dateCreated: rs.getDate("dateCreated"), - createdBy: rs.getString("createdBy"), - lastUpdated: rs.getDate("lastUpdated"), - lastUpdatedBy: rs.getString("lastUpdatedBy"), - dateDeleted: rs.getDate("dateDeleted") ? new Date() : null, - deletedBy: rs.getString("deletedBy"), - version: (Integer) rs.getObject("version") - ) - } - - private Map categoryAttributeValue(Integer id) { - [categoryAttributeValueId: id] - } - - private static final String CATEGORY_ATTRIBUTE_VALUE_SELECT_SQL = "SELECT * FROM categoryAttributeValue WHERE categoryAttributeValueId = :categoryAttributeValueId" - - private static final String CATEGORY_ATTRIBUTE_VALUE_BY_CATEGORY_ATTRIBUTE_SQL = "SELECT * FROM categoryAttributeValue WHERE categoryAttributeId = :categoryAttributeId AND dateDeleted IS NULL" - - private static final String CATEGORY_ATTRIBUTE_VALUE_MOVE_SQL = '''EXEC dbo.spMoveCategoryAttributeValue - @sourceId = :id, - @targetId = :targetId, - @appSecurityUser = :appSecurityUser - ''' - - private static final String CATEGORY_ATTRIBUTE_VALUE_UPSERT_SQL = """ - SET Identity_Insert [CategoryAttributeValue] ON; - MERGE CategoryAttributeValue cav - USING (SELECT :id AS id) source - ON cav.categoryAttributeValueId = source.id - WHEN matched THEN - UPDATE SET - categoryAttributeId = :categoryAttributeId, - attributeValueId = :attributeValueId, - sequence = :sequence, - source = :source, - sourceId = :sourceId, - dateCreated = :dateCreated, - createdBy = :createdBy, - lastUpdated = :lastUpdated, - lastUpdatedBy = :lastUpdatedBy, - dateDeleted = :dateDeleted, - deletedBy = :deletedBy, - version = :version - WHEN NOT matched THEN - INSERT (categoryAttributeValueId,categoryAttributeId, attributeValueId, sequence, source, sourceId, - dateCreated, createdBy, lastUpdated, lastUpdatedBy, dateDeleted, deletedBy, version) - VALUES(:id, :categoryAttributeId, :attributeValueId, :sequence, :source, :sourceId, - :dateCreated, :createdBy, :lastUpdated, :lastUpdatedBy, :dateDeleted, :deletedBy, :version); - SET Identity_Insert [CategoryAttributeValue] OFF; - """ -} +package com.lemanscorp.mqconsumer.services + +import java.sql.ResultSet + +import com.lemanscorp.mqconsumer.domains.CategoryAttributeValue +import groovy.util.logging.Slf4j +import org.springframework.beans.factory.annotation.Autowired +import org.springframework.beans.factory.annotation.Qualifier +import org.springframework.jdbc.core.RowMapper +import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource +import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate +import org.springframework.stereotype.Service +import org.springframework.transaction.support.TransactionTemplate + +@Slf4j +@Service +class CategoryAttributeValueService { + + @Autowired + NamedParameterJdbcTemplate usDigiJdbcTemplate + + @Autowired + @Qualifier("europeDigiJdbcTemplate") + NamedParameterJdbcTemplate euDigiJdbcTemplate + + @Autowired + @Qualifier("europeDigiTransactionTemplate") + TransactionTemplate euDigiTransactionTemplate + + void create(Integer id) { + CategoryAttributeValue categoryAttributeValue = getCategoryAttributeValueById(id) + euDigiTransactionTemplate.execute { + try { + euDigiJdbcTemplate.update(CATEGORY_ATTRIBUTE_VALUE_UPSERT_SQL, categoryAttributeValue.properties) + euDigiJdbcTemplate.execute(CATEGORY_ATTRIBUTE_VALUE_MOVE_SQL, [id: categoryAttributeValue.id, targetId: null, appSecurityUser: 'usToEuSyncProcess'], {}) + //TODO: Get the audit user as header from the actual service. + } catch (Exception e) { + log.error(e.message, e) + it.setRollbackOnly() + throw new Exception("Could not create CategoryAttributeValue") + } + } + } + + void update(Integer id) { + CategoryAttributeValue categoryAttributeValue = getCategoryAttributeValueById(id) + euDigiJdbcTemplate.update(CATEGORY_ATTRIBUTE_VALUE_UPSERT_SQL, categoryAttributeValue.properties) + } + + void move(Integer id) { + CategoryAttributeValue categoryAttributeValue = getCategoryAttributeValueById(id) + List categoryAttributeValues = usDigiJdbcTemplate.query( + CATEGORY_ATTRIBUTE_VALUE_BY_CATEGORY_ATTRIBUTE_SQL, + [categoryAttributeId: categoryAttributeValue.categoryAttributeId], + categoryAttributeValueMapper + ) + euDigiJdbcTemplate.batchUpdate(CATEGORY_ATTRIBUTE_VALUE_UPSERT_SQL, categoryAttributeValues.collect { new BeanPropertySqlParameterSource(it)} as BeanPropertySqlParameterSource[]) + } + + private CategoryAttributeValue getCategoryAttributeValueById(Integer id) { + usDigiJdbcTemplate.queryForObject(CATEGORY_ATTRIBUTE_VALUE_SELECT_SQL, categoryAttributeValue(id) ,categoryAttributeValueMapper) + } + + private RowMapper categoryAttributeValueMapper = { ResultSet rs, row -> + new CategoryAttributeValue( + id: (Integer) rs.getObject("categoryAttributeValueId"), + categoryAttributeId: (Integer) rs.getObject("categoryAttributeId"), + attributeValueId: (Integer) rs.getObject("attributeValueId"), + sequence: (Integer) rs.getObject("sequence"), + source: (Integer) rs.getObject("source"), + sourceId: (Integer) rs.getObject("sourceId"), + dateCreated: rs.getTimestamp("dateCreated"), + createdBy: rs.getString("createdBy"), + lastUpdated: rs.getTimestamp("lastUpdated"), + lastUpdatedBy: rs.getString("lastUpdatedBy"), + dateDeleted: rs.getTimestamp("dateDeleted") ? new Date() : null, + deletedBy: rs.getString("deletedBy"), + version: (Integer) rs.getObject("version") + ) + } + + private Map categoryAttributeValue(Integer id) { + [categoryAttributeValueId: id] + } + + private static final String CATEGORY_ATTRIBUTE_VALUE_SELECT_SQL = "SELECT * FROM categoryAttributeValue WHERE categoryAttributeValueId = :categoryAttributeValueId" + + private static final String CATEGORY_ATTRIBUTE_VALUE_BY_CATEGORY_ATTRIBUTE_SQL = "SELECT * FROM categoryAttributeValue WHERE categoryAttributeId = :categoryAttributeId AND dateDeleted IS NULL" + + private static final String CATEGORY_ATTRIBUTE_VALUE_MOVE_SQL = '''EXEC dbo.spMoveCategoryAttributeValue + @sourceId = :id, + @targetId = :targetId, + @appSecurityUser = :appSecurityUser + ''' + + private static final String CATEGORY_ATTRIBUTE_VALUE_UPSERT_SQL = """ + SET Identity_Insert [CategoryAttributeValue] ON; + MERGE CategoryAttributeValue cav + USING (SELECT :id AS id) source + ON cav.categoryAttributeValueId = source.id + WHEN matched THEN + UPDATE SET + categoryAttributeId = :categoryAttributeId, + attributeValueId = :attributeValueId, + sequence = :sequence, + source = :source, + sourceId = :sourceId, + dateCreated = :dateCreated, + createdBy = :createdBy, + lastUpdated = :lastUpdated, + lastUpdatedBy = :lastUpdatedBy, + dateDeleted = :dateDeleted, + deletedBy = :deletedBy, + version = :version + WHEN NOT matched THEN + INSERT (categoryAttributeValueId,categoryAttributeId, attributeValueId, sequence, source, sourceId, + dateCreated, createdBy, lastUpdated, lastUpdatedBy, dateDeleted, deletedBy, version) + VALUES(:id, :categoryAttributeId, :attributeValueId, :sequence, :source, :sourceId, + :dateCreated, :createdBy, :lastUpdated, :lastUpdatedBy, :dateDeleted, :deletedBy, :version); + SET Identity_Insert [CategoryAttributeValue] OFF; + """ +} Index: src/main/groovy/com/lemanscorp/mqconsumer/services/CategoryService.groovy =================================================================== diff -u -rb5233bc4254a324d783ba04f720ed46335cb364c -r176022c00ae6a69ff0e5449aa62eb9101a850d93 --- src/main/groovy/com/lemanscorp/mqconsumer/services/CategoryService.groovy (.../CategoryService.groovy) (revision b5233bc4254a324d783ba04f720ed46335cb364c) +++ src/main/groovy/com/lemanscorp/mqconsumer/services/CategoryService.groovy (.../CategoryService.groovy) (revision 176022c00ae6a69ff0e5449aa62eb9101a850d93) @@ -73,7 +73,7 @@ sequence: (Integer) rs.getObject("sequence"), source: (Integer) rs.getObject("source"), sourceId: (Integer) rs.getObject("sourceId"), - effectiveDate: rs.getDate("effectiveDate"), + effectiveDate: rs.getTimestamp("effectiveDate"), dateCreated: rs.getTimestamp("dateCreated"), createdBy: rs.getString("createdBy"), lastUpdated: rs.getTimestamp("lastUpdated"), Index: src/main/groovy/com/lemanscorp/mqconsumer/services/CategorySubComCodeService.groovy =================================================================== diff -u -rbbf2e5405d63748b1584efc46c75fc36f48d22d7 -r176022c00ae6a69ff0e5449aa62eb9101a850d93 --- src/main/groovy/com/lemanscorp/mqconsumer/services/CategorySubComCodeService.groovy (.../CategorySubComCodeService.groovy) (revision bbf2e5405d63748b1584efc46c75fc36f48d22d7) +++ src/main/groovy/com/lemanscorp/mqconsumer/services/CategorySubComCodeService.groovy (.../CategorySubComCodeService.groovy) (revision 176022c00ae6a69ff0e5449aa62eb9101a850d93) @@ -1,75 +1,75 @@ -package com.lemanscorp.mqconsumer.services - -import java.sql.ResultSet - -import com.lemanscorp.mqconsumer.domains.CategorySubComCode -import groovy.util.logging.Slf4j -import org.springframework.beans.factory.annotation.Autowired -import org.springframework.beans.factory.annotation.Qualifier -import org.springframework.jdbc.core.RowMapper -import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate -import org.springframework.stereotype.Service - -@Slf4j -@Service -class CategorySubComCodeService { - - @Autowired - NamedParameterJdbcTemplate usDigiJdbcTemplate - - @Autowired - @Qualifier("europeDigiJdbcTemplate") - NamedParameterJdbcTemplate euDigiJdbcTemplate - - def createOrUpdate(Integer id) { - CategorySubComCode categorySubComCode = getCategorySubComCodeById(id) - euDigiJdbcTemplate.update(CATEGORY_SUB_COM_CODE_UPSERT_SQL, categorySubComCode.properties) - } - - private CategorySubComCode getCategorySubComCodeById(Integer categorySubComCodeId) { - usDigiJdbcTemplate.queryForObject(CATEGORY_SUB_COM_CODE_SELECT_SQL, categorySubComCode(categorySubComCodeId), categorySubComCodeMapper) - } - - private RowMapper categorySubComCodeMapper = { ResultSet rs, row -> - new CategorySubComCode( - id: (Integer) rs.getObject("categorySubComCodeId"), - categoryId: (Integer) rs.getObject("categoryId"), - subComCodeId: (Integer) rs.getObject("subComCodeId"), - dateCreated: rs.getDate("dateCreated"), - createdBy: rs.getString("createdBy"), - lastUpdated: rs.getDate("lastUpdated"), - lastUpdatedBy: rs.getString("lastUpdatedBy"), - dateDeleted: rs.getDate("dateDeleted") ? new Date() : null, - deletedBy: rs.getString("deletedBy"), - version: (Integer) rs.getObject("version") - ) - } - - private static final String CATEGORY_SUB_COM_CODE_UPSERT_SQL = """ - SET Identity_Insert [categorySubComCode] ON; - MERGE categorySubComCode cc - USING (SELECT :id AS id) source - ON cc.categorySubComCodeId = source.id - WHEN matched THEN - UPDATE SET - categoryId = :categoryId, - subComCodeId = :subComCodeId, - dateCreated = :dateCreated, - createdBy = :createdBy, - lastUpdated = :lastUpdated, - lastUpdatedBy = :lastUpdatedBy, - dateDeleted = :dateDeleted, - deletedBy = :deletedBy, - version = :version - WHEN NOT matched THEN - INSERT (categorySubComCodeId, categoryId, subComCodeId, dateCreated, createdBy, lastUpdated, lastUpdatedBy, dateDeleted, deletedBy, version) - VALUES(:id, :categoryId, :subComCodeId, :dateCreated, :createdBy, :lastUpdated, :lastUpdatedBy, :dateDeleted, :deletedBy, :version); - SET Identity_Insert [categorySubComCode] ON; - """ - - private Map categorySubComCode(Integer id) { - [categorySubComCodeId: id] - } - - private static final String CATEGORY_SUB_COM_CODE_SELECT_SQL = "SELECT * FROM CategorySubComCode WHERE categorySubComCodeId = :categorySubComCodeId" -} +package com.lemanscorp.mqconsumer.services + +import java.sql.ResultSet + +import com.lemanscorp.mqconsumer.domains.CategorySubComCode +import groovy.util.logging.Slf4j +import org.springframework.beans.factory.annotation.Autowired +import org.springframework.beans.factory.annotation.Qualifier +import org.springframework.jdbc.core.RowMapper +import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate +import org.springframework.stereotype.Service + +@Slf4j +@Service +class CategorySubComCodeService { + + @Autowired + NamedParameterJdbcTemplate usDigiJdbcTemplate + + @Autowired + @Qualifier("europeDigiJdbcTemplate") + NamedParameterJdbcTemplate euDigiJdbcTemplate + + def createOrUpdate(Integer id) { + CategorySubComCode categorySubComCode = getCategorySubComCodeById(id) + euDigiJdbcTemplate.update(CATEGORY_SUB_COM_CODE_UPSERT_SQL, categorySubComCode.properties) + } + + private CategorySubComCode getCategorySubComCodeById(Integer categorySubComCodeId) { + usDigiJdbcTemplate.queryForObject(CATEGORY_SUB_COM_CODE_SELECT_SQL, categorySubComCode(categorySubComCodeId), categorySubComCodeMapper) + } + + private RowMapper categorySubComCodeMapper = { ResultSet rs, row -> + new CategorySubComCode( + id: (Integer) rs.getObject("categorySubComCodeId"), + categoryId: (Integer) rs.getObject("categoryId"), + subComCodeId: (Integer) rs.getObject("subComCodeId"), + dateCreated: rs.getTimestamp("dateCreated"), + createdBy: rs.getString("createdBy"), + lastUpdated: rs.getTimestamp("lastUpdated"), + lastUpdatedBy: rs.getString("lastUpdatedBy"), + dateDeleted: rs.getTimestamp("dateDeleted") ? new Date() : null, + deletedBy: rs.getString("deletedBy"), + version: (Integer) rs.getObject("version") + ) + } + + private static final String CATEGORY_SUB_COM_CODE_UPSERT_SQL = """ + SET Identity_Insert [categorySubComCode] ON; + MERGE categorySubComCode cc + USING (SELECT :id AS id) source + ON cc.categorySubComCodeId = source.id + WHEN matched THEN + UPDATE SET + categoryId = :categoryId, + subComCodeId = :subComCodeId, + dateCreated = :dateCreated, + createdBy = :createdBy, + lastUpdated = :lastUpdated, + lastUpdatedBy = :lastUpdatedBy, + dateDeleted = :dateDeleted, + deletedBy = :deletedBy, + version = :version + WHEN NOT matched THEN + INSERT (categorySubComCodeId, categoryId, subComCodeId, dateCreated, createdBy, lastUpdated, lastUpdatedBy, dateDeleted, deletedBy, version) + VALUES(:id, :categoryId, :subComCodeId, :dateCreated, :createdBy, :lastUpdated, :lastUpdatedBy, :dateDeleted, :deletedBy, :version); + SET Identity_Insert [categorySubComCode] ON; + """ + + private Map categorySubComCode(Integer id) { + [categorySubComCodeId: id] + } + + private static final String CATEGORY_SUB_COM_CODE_SELECT_SQL = "SELECT * FROM CategorySubComCode WHERE categorySubComCodeId = :categorySubComCodeId" +}