Java tutorial
/* * Copyright 2005 Walt Disney Company. All rights reserved. */ package com.disney.opa.dao.impl; import java.sql.PreparedStatement; import java.sql.SQLException; import java.text.MessageFormat; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Comparator; import java.util.Date; import java.util.HashMap; import java.util.HashSet; import java.util.LinkedHashMap; import java.util.List; import java.util.Locale; import java.util.Map; import java.util.Set; import java.util.TreeMap; import javax.sql.DataSource; import org.apache.commons.lang.StringUtils; import org.apache.log4j.Logger; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.dao.DataAccessException; import org.springframework.jdbc.core.PreparedStatementCallback; import org.springframework.jdbc.core.namedparam.MapSqlParameterSource; import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate; import org.springframework.stereotype.Repository; import com.disney.opa.dao.CompanyDao; import com.disney.opa.dao.PermissionSetDao; import com.disney.opa.dao.ProductDao; import com.disney.opa.dao.ProductGroupDao; import com.disney.opa.dom.attribute.Attribute; import com.disney.opa.dom.attribute.AttributeConstants; import com.disney.opa.dom.attribute.AttributeDefinition; import com.disney.opa.dom.attribute.AttributeDefinitionSet; import com.disney.opa.dom.option.Option; import com.disney.opa.dom.option.PAUserOption; import com.disney.opa.dom.option.UserOption; import com.disney.opa.dom.permission.PermissionDefinition; import com.disney.opa.dom.product.BulkTask; import com.disney.opa.dom.product.ChildImageAssignment; import com.disney.opa.dom.product.HardStopByPassByAttribute; import com.disney.opa.dom.product.InactiveProduct; import com.disney.opa.dom.product.MassOp; import com.disney.opa.dom.product.MassOpDetail; import com.disney.opa.dom.product.PAPIEDocProduct; import com.disney.opa.dom.product.PAProductPreviewInfo; import com.disney.opa.dom.product.Product; import com.disney.opa.dom.product.ProductForSpi; import com.disney.opa.dom.product.ProductGroup; import com.disney.opa.dom.product.ProductHeader; import com.disney.opa.dom.product.ProductPreviewInfo; import com.disney.opa.dom.product.ProductState; import com.disney.opa.dom.product.ProductSummary; import com.disney.opa.dom.product.RequiredAttributeDependency; import com.disney.opa.dom.product.ReviewResult; import com.disney.opa.dom.product.StepVisit; import com.disney.opa.dom.product.StyleGuide; import com.disney.opa.dom.product.TemplateHardStop; import com.disney.opa.dom.user.Company; import com.disney.opa.entity.ChildCount; import com.disney.opa.entity.ParameterSQL; import com.disney.opa.exception.PADataAccessException; import com.disney.opa.service.ProductService; import com.disney.opa.util.JDBCUtil; import com.disney.opa.util.MessageUtil; import com.disney.opa.util.OpStatus; import com.disney.opa.util.ProductAttributeProcessor; import com.disney.opa.util.resultsetprocessor.RowMappers; /** * @author jzyang * @author Marcos Serrano serrm042 * @since January 17, 2008 */ @Repository("ProductDao") public class ProductDaoImpl implements ProductDao { @Autowired DataSource dataSource; @Autowired RowMappers rowMappers; @Autowired ProductService productService; @Autowired PermissionSetDao permissionSetDao; @Autowired CompanyDao companyDao; @Autowired ProductGroupDao productGroupDao; @Autowired MessageUtil messageUtil; private NamedParameterJdbcTemplate namedJdbcTemplate = null; final Logger log = Logger.getLogger(this.getClass().getName()); // For attribute set definitions of a template private static final String SP_FIRSTSTEP_WITHOUT_OPTION = "spSelTemplateAttributes {0}, 0, {1}, {2}"; private static final String SP_FIRSTSTEP_WITH_OPTION = "spSelTemplateAttributes {0}, 1, {1}, {2}"; // Creating a new product and its attribute values private static final String SP_INSERT_PRODUCT = "spInsProduct {0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}"; private static final String SP_INSERT_PRODUCT_ATTRIBUTEVALUE_INTEGER = "spInsProductAttributeInteger {0}, {1}, {2}, {3}"; private static final String SP_INSERT_PRODUCT_ATTRIBUTEVALUE_DOUBLE = "spInsProductAttributeNumber {0}, {1}, {2}, {3}"; private static final String SP_INSERT_PRODUCT_ATTRIBUTEVALUE_STRING = "spInsProductAttributeText {0}, {1}, {2}, {3}"; private static final String SP_INSERT_PRODUCT_ATTRIBUTEVALUE_DATE = "spInsProductAttributeDate {0}, {1}, {2}, {3}"; private static final String SP_INSERT_PRODUCT_ATTRIBUTEVALUE_OPTION = "spInsProductAttributeOption {0}, {1}, {2}, {3}, {4}"; private static final String SP_INSERT_PRODUCT_ATTRIBUTEVALUE_STRING_AND_INTEGER = "spInsProductAttributeTextAndInteger {0}, {1}, {2}, {3}, {4}"; private static final String SP_INSERT_PRODUCT_CHILD = "spInsProductChild {0}, {1}, {2}"; // Getting a product private static final String SP_GET_PRODUCT = "spSelProduct {0}, null, {1}"; // Getting list of products for SPI based on Project ID private static final String SP_GET_PRODUCT_FOR_SPI = "spSelProductsForSPIProjectNumber {0}"; private static final String SP_GET_PRODUCT_ATTRIBUTES = "spSelProductAttributeValues {0}, {1}"; // {0}=productID, {1}=languageID // Get a product w/ id and textkeys private static final String SP_GET_PRODUCT_READ_ONLY = "spSelProductReadOnly {0}, {1}, {2}"; // productId, pageTypeId, userTypeId // Updating a product private static final String SP_UPDATE_PRODUCT = "spUpdProduct {0},{1},{2},{3},{4},{5},{6},{7},{8}"; private static final String SP_DELETE_ATTRIBUTE_VALUES = "spDelProductAttributes {0}, {1}"; private static final String SP_UPDATE_PRODUCT_STATUS = "spUpdProductStatus {0},{1}"; //Cancelling a task private static final String SP_UPDATE_PRODUCT_REVIEW = "spUpdProductReview {0}, {1}, {2}, {3}, 1"; // {0}=productStateId, {1}=reviewerId, {2}=activateReviewerUserId, {3}=reviewTypeID, {4}=isComplete // Activate, Deactivate a product private static final String SP_ACTIVATE_PRODUCT = "spActivateProduct {0}, {1}"; // Delete product and Child if exits private static final String SP_DELETE_PRODUCT = "spDelProduct {0}"; // ProductState private static final String SP_GET_PRODUCT_STATE = "spSelProductState {0}"; private static final String SP_UPDATE_PRODUCT_STATE = "spUpdProductState {0}, {1}"; private static final String SP_SUBMIT_PRODUCT = "spInsProductState {0}, {1}, {2}, {3}, {4}, {5}, {6}"; private static final String SP_DELEGATE_PRODUCT = "spUpdProductStatePerformer {0}, {1}, {2}"; private static final String SP_GET_PRODUCT_STATES = "spSelProductStates {0}"; /* {0} = @productID */ // Review private static final String SP_GET_REVIEW_OPTIONS = "spSelReviewOptions {0}"; /* {0}=@csvIDs, {1}=@idType*/ private static final String SP_GET_REVIEW_OPTIONS_BY_CSV = "spSelReviewOptionsByCSV {0}, {1}"; private static final String SP_ASSIGN_REVIEWER = "spInsProductReviewer {0}, {1}, 1, {2}"; private static final String SP_SAVE_REVIEWER = "spInsProductReviewer {0}, {1}, 0"; private static final String SP_GET_PRODUCT_REVIEWS = "spSelProductReviews null, {0}, null, null"; private static final String SP_GET_PRODUCT_REVIEWS_BY_PERMISSION = "spSelProductReviews null, {0}, null, null,{1}"; private static final String SP_GET_MOST_RECENT_REVIEWERS = "spSelMostRecentReviewersForProduct {0}"; private static final String SP_DEL_UNASSIGNED_REVIEWER = "spDelProductReviewers {0}, {1}"; private static final String SP_DEL_REVIEWERS = "spDelProductReviewers {0}, {1}"; private static final String SP_SUBMIT_TO_REVIEWERS = "spUpdProductReviewerAssignment {0}, null, 1"; private static final String SP_DELEGATE_REVIEWER = "spUpdProductReview {0},{1},{2}"; private static final String SP_REVIEW_PRODUCT = "spUpdProductReview {0}, {1}, {2}, {3}, 1"; private static final String SP_CREATE_PRODUCT_SUMMARY = "spCreateProductSummary {0}"; private static final String SP_CREATE_PRODUCT_SUMMARY_WITH_PARENT = "spCreateProductSummary {0},{1}"; private static final String SP_SEL_REQUIRED_ATTRIBUTE_IDS = "spSelTemplateRequiredAttributes {0},{1}"; private static final String SP_SEL_PRODUCTSTATUS_OPTION = "spSelProductStatus"; private static final String SP_SEL_STEP_VISITS = "spSelProductWorkflowStepVisits {0},{1},{2},{3}"; private static final String SP_COPY_PRODUCT = "spInsCopiedProduct {0},{1},{2},{3},{4},{5},{6},{7}"; private static final String SP_COPY_COMPONENT = "spCopyComponent {0},{1}"; private static final String SP_GET_CHILD_SUMMARIES = "spSelProductSummary null, {0}, {1}, {2}"; private static final String SP_GET_CHILD_PRODUCTS = "spSelProduct null, {0}, 1, {1}"; private static final String SP_GET_CHILD_PRODUCTS_ATTRIBUTES = "spSelProductAttributeValues null, {0}, {1}, {2}"; // {0}=languageID, {1}=parentID, {2}=castAsTemplateID private static final String SP_GET_CHILD_COUNT = "spSelProductChildCounts {0}"; private static final String SP_GET_RANGE_IMAGE_ASSIGNMENTS = "spSelRangeItemImages {0}"; // {0}=parent product ID private static final String SP_DEL_PRODUCT_FROM_USER_FOLDER = "spDelUserFolderProductItem {0},{1}"; // {0}=userID, {1}=productID private static final String SP_CREATE_RANGE_CHILDREN = "spCreateBatchChildProductsForParent {0},{1},{2},{3}"; // {0}=parent ID, {1}=child template ID, {2}=user ID, {3}=number of children private static final String SP_CREATE_ASSORTMENT_CHILDREN = "spCreateBatchChildProductsForAssortmentParent {0},{1},{2},{3},{4}"; // {0}=parent ID, {1}=child template ID, {2}=user ID, {3}=userTypeID, {4}=number of children private static final String SP_CREATE_LINE_LIST_CHILDREN = "spCreateBatchChildProductsForLineListParent {0},{1},{2},{3},{4}"; // {0}=parent ID, {1}=child template ID, {2}=user ID, {3}=userTypeID, {4}=number of children /* @since OPA 6.3; 07/05/2012 */ private static final String SP_SEL_PRODUCT_SUMMARIES = "spSelProductSummaries {0},{1}"; /* {0} = @csvIDs; {1} = @languageID */ // Bug 5234 changes private static final String SP_SEL_SELECTED_REVIEWERS = "spSelSelectedReviewers {0}"; // {0}=productID private static final String SP_INS_SELECTED_REVIEWERS = "spInsSelectedReviewer {0},{1}"; // {0}=productID, {1}=userID private static final String SP_DEL_SELECTED_REVIEWERS = "spDelSelectedReviewers {0}, {1}"; // {0}=productID, {1}=reviewerID private static final String SP_PUBLISH_RANGE_ITEMS = "spPublishRangeItems {0}"; private static final String SP_SEL_COLLECTION = "spSelCollection {0}"; private static final String SP_RESUBMIT_TO_REVIEWER = "spUpdProductReviewResubmit {0}, {1}, {2}"; private static final String SP_SEL_DEPENDENT_REQUIRED_ATTRIBUTE = "spSelRequiredAttributeTrigger {0}, {1}"; private static final String SP_SEL_STYLE_GUIDE = "spSelStyleGuide {0},{1},{2}"; private static final String SP_SEL_PRODUCT_STATUS_AFTER = "spSelProductStatusAfter {0}"; private static final String SP_SEL_DEFAULT_DISNEY_APPROVER = "spSelDefaultDisneyContact {0},{1},{2}"; // Operations on a bunch of products in admin section private static final String SP_INS_MASS_OPS = "spInsMassOps {0},{1},{2},{3},{4},{5},{6}"; private static final String SP_UPD_MASS_OPS = "spUpdMassOps {0},{1},{2},{3}"; private static final String SP_GET_MASS_OPS = "spSelMassOps {0},{1},{2},{3},{4},{5}"; private static final String SP_INS_MASS_OPS_DETAIL = "spInsMassOpsDetail {0},{1},{2}"; private static final String SP_UPD_MASS_OPS_DETAIL = "spUpdMassOpsDetail {0},{1},{2},{3}"; private static final String SP_GET_MASS_OPS_DETAIL = "spSelMassOpsDetail {0},{1}"; private static final String SP_CHECK_PRODS_IN_PROCESS = "spCheckProductsInProcess {0}"; // Reassign products private static final String SP_REASSIGN_PRODUCT_ASSOCIATE = "spReassignAssociateProduct {0},{1},{2},{3}"; private static final String SP_REASSIGN_PRODUCT_LICENSEE = "spReassignLicenseeProduct {0},{1},{2},{3}"; private static final String SP_GET_ALL_STEPS_FOR_ASSOCIATE = "spSelAllStepsForAssociate {0}"; private static final String SP_GET_ALL_STEPS_FOR_LICENSEE = "spSelAllStepsForLicensee {0}"; private static final String SP_GET_ALL_STEPS_FOR_AGENT = "spSelAllStepsForAgent {0}"; private static final String SP_GET_ARRIVAL_DATE = "spSelArrivalDate {0}, {1}"; // Removes the product view status (resets the product to unread) private static final String SP_DEL_USER_PRODUCT_VIEW_STATUS = "spDelUserProductViewStatus {0}, {1}, {2}"; //private static final String SP_DELEGATE_ALL = "spUpdProductStateDelegateAll {0}, {1}"; private static final String SP_SEL_SUBCATEGORY_ACRONYM = "spSelSubcategoryAcronym {0}, {1}, {2}"; private static final String SP_CHECK_UPCEAN_FOR_LICENSEE_EXISTS = "spCheckUPCEANExists {0}, {1}, {2}, {3}"; private static final String SP_CHECK_ATTRIBUTE_EXCEPTION_EXISTS = "spCheckAttributeException {0}, {1}"; private static final int USERGROUP_PORTAL_PUBLISHER = 172; private static final String SP_GET_ASSORTMENT_PARENT_TEMPLATE = "spGetAssortmentParentTemplate {0}"; private static final String SP_CONVERT_PRODUCT_TO_ASSORTMENT = "spConvertProductToAssortment {0}, {1}, {2}"; private static final String SP_GET_AGED_PRODUCTS = "spSelAgedProducts"; private static final String SP_UNCANCEL_PRODUCT = "spUndoBulkAdminCancelProduct {0}, {1}, {2}"; private static final String SP_UNSUSPEND_PRODUCT = "spUndoBulkAdminSuspendProduct {0}"; private static final String SP_CHECK_CAN_SHOW_PARENT = "spCheckCanShowParent {0}, {1}, {2}"; //FHBP Component Types private static final String SP_SEL_PRODUCTTEMPLATE_COMPONENTS_OPTION = "spSelChildComponentsTypes"; private static final String SP_SEL_PRODUCT_HARD_STOPS = "spSelProductHardStops {0}, {1}, {2}"; private static final String SP_COPY_TO_STEP_ID = "spSelCopyStepFromType {0}, {1}"; //OPA 5.11 // Retrieves Product Facility IDs private static final String SP_GET_PRODUCT_FACILITY_IDS = "spSelProductFacilityIds {0}"; // Update ProductSummary & productState table when Associate creates a product on behalf of licensee user. private static final String SP_UPDATE_ON_BEHALF_LICENSEE_USER = "spUpdOnBehalfLicenseeUser {0}, {1}"; // Copy completed products private static final String SP_INSERT_COPY_COMPLETED_PRODUCT = "spInsCopyCompletedProducts {0},{1},{2}"; private static final String SP_GET_ORIGINAL_PRODUCT_ID = "spSelCopyCompletedProducts {0}"; private static final String SP_SEL_POST_LAUNCH_ITEMS_EMAIL = "spSelPostLaunchItems"; private static final String SP_SEL_FACILITY_EMAIL_CONTENT = "spSelFacilityEmailContent"; private static final String SP_SEL_AUTOSUSPEND_EMAIL_CONTENT = "spSelAutoSuspendEmailContent"; private static final String SP_SEL_HARD_STOP_BY_PASS_BY_ATTRIBUTE_VALUE = "spSelHardStopByPassByAttributeValue {0}, {1}, {2}"; private static final String SP_FIND_PARENT_TEMPLATE_ID = "spFindParentTemplateId {0}"; private static final String SP_SEL_PRODUCT_IDS_FOR_LRT_VALIDATION = "spSelProductForLRTValidation {0}"; private static final String SP_SEL_INACTIVE_PRODUCT = "spSelInactiveProductAutoSuspend {0}"; //PI Integration 2012 private static final String SP_SEL_PI_COMPONENT_MATRIX = "spSelPIComponentMatrix {0}"; private static final String SP_SEL_PI_COMPONENTS_FOR_EDOCS = "spSelPIComponentsToTransmit"; //OPA 7.3 MR - FNS Component Matrix private static final String SP_SEL_FNS_COMPONENT_MATRIX = "spSelFNSComponentMatrix {0}"; //OPA 7.3.x Mini-Release for Lucasfilm product restriction: private static final String SP_SEL_PRODUCT_ASSOCIATION = "spSelProductAssociation {0}, {1}"; //OPA 7.3.x Mini-Release for Lucasfilm product restriction: private static final String SP_SEL_PRODUCT_RESTRICTION = "spSelProductRestriction {0}"; /* Updates Product Users without updating other product details: * {0} Product ID * {1} licenseeID * {2} associateID * {3} leadIDString */ private static final String SP_UPD_PRODUCT_USERS = "spUpdProductUsers {0}, {1}, {2}, {3}"; /* Getting product(s). To clarify the difference between spSelProduct and * spSelProducts I named the constants with "RETRIEVE" because * SP_GET_CHILD_PRODUCTS already existed. I wanted to remain consistent. * * spSelProducts {0} -- @ProductIDs * null -- @ParentProductIDs * {1] -- @Active */ private static final String SP_RETRIEVE_PRODUCTS = "spSelProducts {0}, null, {1}"; /* Getting child product(s). To clarify the difference between spSelProduct * and spSelProducts I named the constants with "RETRIEVE" because * SP_GET_CHILD_PRODUCTS already existed. I wanted to remain consistent. * * spSelProducts null -- @ProductIDs * {0} -- @ParentProductIDs * {1] -- @Active */ private static final String SP_RETRIEVE_CHILD_PRODUCTS = "spSelProducts null, {0}, {1}"; /* Retrieves Attribute Set Definitions for the templates of specified * products. * * spSelProductTemplateAttributes {0} -- @ProductIDs * {1} -- @AttributeIDs * 0 -- @IncludeOptions (not included) * {2} -- @LimitByCurrentProductStatus * {3} -- @LanguageID */ private static final String SP_PRODUCT_TEMPLATE_ATTRS_WITHOUT_OPTION = "spSelProductTemplateAttributes {0}, {1}, 0, {2}, {3}"; /* Retrieves Attribute Set Definitions for the templates of specified * products. * * spSelProductTemplateAttributes {0} -- @ProductIDs * {1} -- @AttributeIDs * 1 -- @IncludeOptions (included) * {2} -- @LimitByCurrentProductStatus * {3} -- @LanguageID */ private static final String SP_PRODUCT_TEMPLATE_ATTRS_WITH_OPTION = "spSelProductTemplateAttributes {0}, {1}, 1, {2}, {3}"; /* Retrieves Attributes (values) for multiple products * spSelProductsAttributeValues {0} -- @ProductIDs * {1} -- @ParentProductIDs * {2} -- @AttributeIDs * {3} -- @LanguageID */ private static final String SP_RETRIEVE_PRODUCT_ATTRIBUTES = "spSelProductsAttributeValues {0}, {1}, {2}, {3}"; private static final String SP_SEL_PRODUCT_PREVIEW_INFO = "spSelProductPreview {0}, {1}"; public AttributeDefinitionSet createProductSql(int productTemplateID, int languageID, boolean populateAvailableOptions, int productStatusID) throws PADataAccessException { String productStatusIdStr = "null"; if (productStatusID == -1) { productStatusIdStr = String.valueOf(productStatusID); } String sqlWithParameters = (populateAvailableOptions) ? SP_FIRSTSTEP_WITH_OPTION : SP_FIRSTSTEP_WITHOUT_OPTION; String sql = JDBCUtil.query(sqlWithParameters, new Object[] { String.valueOf(productTemplateID), String.valueOf(productStatusIdStr), String.valueOf(languageID) }); MapSqlParameterSource parmSource = new MapSqlParameterSource(); List<AttributeDefinitionSet> list = namedJdbcTemplate.query(sql, parmSource, rowMappers.processResultSetRowMapper); AttributeDefinitionSet set = null; if (list != null && list.size() > 0) { set = list.get(0); } return set; } public Product createProduct(int productTemplateID, int languageID, boolean populateAvailableOptions) throws PADataAccessException { if (log.isDebugEnabled()) { log.debug("Method createProduct(" + productTemplateID + ", " + languageID + ", " + populateAvailableOptions + ") ... begin"); } Product product = null; /* String sqlWithParameters = (populateAvailableOptions) ? SP_FIRSTSTEP_WITH_OPTION : SP_FIRSTSTEP_WITHOUT_OPTION; String sql = JDBCUtil.query(sqlWithParameters, new Object[]{ String.valueOf(productTemplateID), "null", String.valueOf(languageID)}); MapSqlParameterSource parmSource = new MapSqlParameterSource(); List<AttributeDefinitionSet> list = namedJdbcTemplate.query(sql, parmSource, rowMappers.processResultSetRowMapper); AttributeDefinitionSet set = null; if(list != null && list.size() > 0) { set = list.get(0); } */ AttributeDefinitionSet set = productService.createProductSql(productTemplateID, languageID, populateAvailableOptions, -1); if (set != null) { product = new Product(); product.setProductTemplateID(productTemplateID); product.setAttributeDefinitionSet(set); AttributeDefinition[] definitions = set.getAllAttributeDefinitions(); if (definitions != null && definitions.length > 0) { for (int i = 0; i < definitions.length; i++) { AttributeDefinition oneAttributeDefinition = definitions[i]; product.initAttribute(oneAttributeDefinition); } } } if (log.isDebugEnabled()) { log.debug("Method createProduct(" + productTemplateID + ", " + languageID + ", " + populateAvailableOptions + ") ... end"); } return product; } public Product createProduct(int productTemplateID, int languageID, boolean populateAvailableOptions, int productStatusID) throws PADataAccessException { if (log.isDebugEnabled()) { log.debug("Method createProduct(" + productTemplateID + ", " + languageID + ", " + populateAvailableOptions + ", " + productStatusID + ") ... begin"); } Product product = null; /* String sqlWithParameters = (populateAvailableOptions) ? SP_FIRSTSTEP_WITH_OPTION : SP_FIRSTSTEP_WITHOUT_OPTION; String sql = JDBCUtil.query(sqlWithParameters, new Object[]{ String.valueOf(productTemplateID), String.valueOf(productStatusID), String.valueOf(languageID)}); MapSqlParameterSource parmSource = new MapSqlParameterSource(); List<AttributeDefinitionSet> list = namedJdbcTemplate.query(sql, parmSource, rowMappers.processResultSetRowMapper); AttributeDefinitionSet set = null; if(list != null && list.size() > 0) { set = list.get(0); } */ AttributeDefinitionSet set = productService.createProductSql(productTemplateID, languageID, populateAvailableOptions, productStatusID); if (set != null) { product = new Product(); product.setProductTemplateID(productTemplateID); product.setAttributeDefinitionSet(set); AttributeDefinition[] definitions = set.getAllAttributeDefinitions(); if (definitions != null && definitions.length > 0) { for (int i = 0; i < definitions.length; i++) { AttributeDefinition oneAttributeDefinition = definitions[i]; product.initAttribute(oneAttributeDefinition); } } } if (log.isDebugEnabled()) { log.debug("Method createProduct(" + productTemplateID + ", " + languageID + ", " + populateAvailableOptions + ", " + productStatusID + ") ... end"); } return product; } /** * * @param productIDs * @param languageID * @param populateAvailableOptions * @return * @throws PADataAccessException */ private Map<Integer, AttributeDefinitionSet> getAttributeDefinitions(String productIDs, String attributeIDs, int languageID, boolean populateAvailableOptions) throws PADataAccessException { boolean limitByCurrentProductStatus = true; return getAttributeDefinitions(productIDs, attributeIDs, languageID, populateAvailableOptions, limitByCurrentProductStatus); } /** * Returns the {@link AttributeDefinitionSet} objects for a specified list * of products. * <p> * The reason a map is returned is because this is used by * {@link #get(String, String, int, boolean, int)} and we need to match * {@link AttributeDefinitionSet} instances to the applicable Product * object rather than sending in and passing back out the Product object. * * @param productIDs A comma separated String of Product IDs. * @param attributeIDs * A comma-separated String of attribute IDs. If <code>null</code> * all attribute definitions are returned. If and empty string no * attribute definitions are returned. Otherwise specified * attribute definitions are returned. * @param languageID * A language ID (to get translations for options). * @param populateAvailableOptions * A boolean to indicate if available options for an attribute should * be populated. * <ul> * <li>If you need to present the product for editing, * set populateAvailableOptions to <code>true</code>.</li> * <li>If you need to present the product for viewing, * set populateAvailableOptions to <code>false</code>.</li> * </ul> * @param limitByCurrentProductStatus If <code>true</code> the attributes * are limited to those that are visible by the specified product * status. * @return {@link Map} of Product IDs (as {@link Integer}) to * {@link AttributeDefinitionSet} object. * @throws PADataAccessException */ private Map<Integer, AttributeDefinitionSet> getAttributeDefinitions(String productIDs, String attributeIDs, int languageID, boolean populateAvailableOptions, boolean limitByCurrentProductStatus) throws PADataAccessException { if (log.isDebugEnabled()) { log.debug("Method addDefinitions(" + productIDs + ", " + attributeIDs + ", " + languageID + ", " + populateAvailableOptions + ", " + limitByCurrentProductStatus + ") ... begin"); } String sqlWithParameters = (populateAvailableOptions) ? SP_PRODUCT_TEMPLATE_ATTRS_WITH_OPTION : SP_PRODUCT_TEMPLATE_ATTRS_WITHOUT_OPTION; String limitByStatus = (limitByCurrentProductStatus) ? "1" : "0"; /** 1. if attributeIDs is null, return all attributes (pass "null"); * 2. if attributeIDs is empty string, return no attributes (pass ''); * 3. if attributeIDs is not empty string, return only those attributes * (pass '52,200,...'). */ String attributeIDsString = null; if (attributeIDs != null && attributeIDs.trim().length() > 0) { attributeIDsString = JDBCUtil.sqlEscapeString(attributeIDs); } else if (attributeIDs != null && attributeIDs.trim().length() == 0) { attributeIDsString = JDBCUtil.sqlEscapeString(""); } else { attributeIDsString = "null"; } AttributeDefinitionSet[] attributeDefinitionSet = null; Map<Integer, AttributeDefinitionSet> adsMap = new HashMap<Integer, AttributeDefinitionSet>(); String sql = JDBCUtil.query(sqlWithParameters, new Object[] { JDBCUtil.sqlEscapeString(productIDs), attributeIDsString, limitByStatus, String.valueOf(languageID) }); MapSqlParameterSource parmSource = new MapSqlParameterSource(); List<Map<Integer, AttributeDefinitionSet>> list = namedJdbcTemplate.query(sql, parmSource, rowMappers.attributeDefinitionMapRowMapper); if (list != null && list.size() > 0) { adsMap = list.get(0); } if (log.isDebugEnabled()) { log.debug("Method addDefinitions(" + productIDs + ", " + attributeIDs + ", " + languageID + ", " + populateAvailableOptions + ", " + limitByCurrentProductStatus + ") ... end"); } return adsMap; } /** * Populates the Attributes for a a specific set of Products. * @param productSet * {@link Set} of {@link Product} objects. This is needed because the * {@link Product} objects passed in are expected to contain the * Attribute Definitions. See * {@link #getAttributeDefinitions(String, String, int, boolean, boolean)} * @param productIDs A comma separated String of Product IDs. * @param attributeIDs * A comma-separated String of attribute IDs. If <code>null</code> * all attribute definitions are returned. If and empty string no * attribute definitions are returned. Otherwise specified * attribute definitions are returned. * @param languageID * A language ID (to get translations for options). * @throws PADataAccessException */ private void populateAttributeValues(Set<Product> productSet, String productIDs, String attributeIDs, int languageID) throws PADataAccessException { /** 1. if attributeIDs is null, return all attributes (pass "null"); * 2. if attributeIDs is empty string, return no attributes (pass ''); * 3. if attributeIDs is not empty string, return only those attributes * (pass '52,200,...'). */ String attributeIDsString = null; if (attributeIDs != null && attributeIDs.trim().length() > 0) { attributeIDsString = JDBCUtil.sqlEscapeString(attributeIDs); } else if (attributeIDs != null && attributeIDs.trim().length() == 0) { attributeIDsString = JDBCUtil.sqlEscapeString(""); } else { attributeIDsString = "null"; } Product[] products = null; String sql = JDBCUtil.query(SP_RETRIEVE_PRODUCT_ATTRIBUTES, new Object[] { JDBCUtil.sqlEscapeString(productIDs), "null", attributeIDsString, String.valueOf(languageID) }); MapSqlParameterSource parmSource = new MapSqlParameterSource(); List<Product> list = namedJdbcTemplate.query(sql, parmSource, rowMappers.productRowMapper); if (list != null && list.size() > 0) { products = new Product[list.size()]; list.toArray(products); } } /** * Retrieves multiple products and the attributes for those products. * @param productIDs A comma-separated String of product IDs. * @param attributeIDs A comma-separated String of attribute IDs. To * return all attributes for the product pass a <code>null</code> to * this method. To get the product data without attribute data pass * and empty string (""). To return a subset of applicable attributes * pass a value akin to "1,4,10,...". * @param languageID The identifier for the user's language. This is * used for attribute values to be translated. * @param populateAvailableOptions Determines whether or not to return * applicable options for attribute definitions. * @param active If <code>1</code> return only active products. * @return An array of {@link Product} objects. If * <code>attributeIDs</code> is <tt>null</tt> all attribute definitions * and attribute values are returned. If <code>attributeIDs</code> is * an empty string, the Product data is returned, but without attribute * data. If <code>attributeIDs</code> is a populated csv (like * "1,4,10") only those attribute definitions and attribute values * are returned. */ public Product[] get(String productIDs, String attributeIDs, int languageID, boolean populateAvailableOptions, int active) throws PADataAccessException { if (log.isDebugEnabled()) { log.debug("Method get(" + ((productIDs != null) ? productIDs : "null") + ", " + ((attributeIDs != null) ? attributeIDs : "null") + ", " + languageID + ", " + populateAvailableOptions + ", " + active + ") ... begin"); } boolean getAttributes = false; /** If attributeIDs is NULL or attributeIDs not an empty string, get * attribute definitions. If attributeIDs is an empty string * spare the call - no attributes to return. */ getAttributes = ((attributeIDs == null) || (attributeIDs != null && attributeIDs.trim().length() > 0)); String activeString = null; if (active != JDBCUtil.IGNORED) { activeString = String.valueOf(active); } if (log.isDebugEnabled()) { log.debug("Retrieving the set of products"); } Set<Product> productSet = new HashSet<Product>(); String sql = JDBCUtil.query(SP_RETRIEVE_PRODUCTS, new Object[] { JDBCUtil.sqlEscapeString(productIDs), activeString }); MapSqlParameterSource parmSource = new MapSqlParameterSource(); List<Set<Product>> list = namedJdbcTemplate.query(sql, parmSource, rowMappers.productSetRowMapper); if (list != null && list.size() > 0) { productSet = list.get(0); } if (productSet != null && productSet.size() > 0) { if (log.isDebugEnabled()) { log.debug("Retrieving the attribute defintions for products"); } //Get Attribute Definitions Map<Integer, AttributeDefinitionSet> definitions = null; if (getAttributes) { definitions = this.getAttributeDefinitions(productIDs, attributeIDs, languageID, populateAvailableOptions); } // END: IF getAttributes if (log.isDebugEnabled()) { log.debug("Initialize each attribute defintion for " + "each product"); } // Initialize each attribute (definition) for each product for (Product product : productSet) { Integer productID = product.getID(); // need a casting variable Product p = (Product) product; // initialize all attributes if (definitions != null) { AttributeDefinitionSet ads = definitions.get(productID); if (ads != null) { p.setAttributeDefinitionSet((AttributeDefinitionSet) ads); AttributeDefinition[] defs = ads.getAllAttributeDefinitions(); for (AttributeDefinition def : defs) { p.initAttribute(def); } //END: LOOP AttributeDefinition array } //END: AttributeDefinitionSet is not null } //END: definitions is not null } //END: LOOP productSet // Populate Attribute Values if (getAttributes) { this.populateAttributeValues(productSet, productIDs, attributeIDs, languageID); } // END: IF getAttributes // No need to set Dirty flag because attributes are not being // queried for. if (getAttributes) { /** * Set dirty flag to false for each attribute so future update * will check if an attribute is dirty to determine if an * update is necessary. */ for (Product product : productSet) { Attribute[] attributes = product.getAllAttributes(); if (attributes != null) { for (Attribute attribute : attributes) { Attribute paAttr = (Attribute) attribute; if (paAttr != null) { paAttr.setDirty(false); } } //END: LOOP attributes } //END: attribute is not null } //END: LOOP productSet } //END: IF getAttributes } Product[] results = null; if (productSet != null && productSet.size() > 0) { results = new Product[productSet.size()]; productSet.toArray(results); } if (log.isDebugEnabled()) { log.debug("Method get(" + ((productIDs != null) ? productIDs : "null") + ", " + ((attributeIDs != null) ? attributeIDs : "null") + ", " + languageID + ", " + populateAvailableOptions + ", " + active + ") ... end"); } return results; } /* // TODO - This method is using JDBC and should re-write later. This method is called from only one class // PDFPrintDetailsAction.java public PAProductReadOnly getReadOnly(int productID, int pageTypeId, int userTypeId, int languageID) throws PADataAccessException { if (log.isDebugEnabled()) log.debug("getReadOnly(" +productID+ ", " +userTypeId+ ", " +pageTypeId+ ", " +languageID+ ") ... begin"); String sql = SP_GET_PRODUCT_READ_ONLY; Object[] parameters = new Object[] { String.valueOf(productID), pageTypeId, userTypeId }; if (parameters != null && parameters.length > 0) { sql = MessageFormat.format(SP_GET_PRODUCT_READ_ONLY, parameters); } Hashtable<Integer,ResultSetProcessor> processorSet = new Hashtable<Integer,ResultSetProcessor>(3); PAProductReadOnlyProcessor processor = new PAProductReadOnlyProcessor(); processorSet.put(1, processor); PAProductReadOnlyProcessorModule moduleProcessor = new PAProductReadOnlyProcessorModule(); processorSet.put(2, moduleProcessor); PAProductReadOnlyProcessorAttributes attributesProcessor = new PAProductReadOnlyProcessorAttributes(); processorSet.put(3, attributesProcessor); PAProductReadOnlyProcessorAttributes rangeLiteParentProcessor = new PAProductReadOnlyProcessorAttributes(); processorSet.put(4, rangeLiteParentProcessor); JDBCUtil.query(sql, processorSet); PAProductReadOnly product = processor.getProduct(); product.setModuleList(moduleProcessor.getModuleList()); product.setAttributeMap(attributesProcessor.getAttributeMap()); Map<Integer,List<PAAttributeReadOnly>> map = rangeLiteParentProcessor.getAttributeMap(); if (map != null && !map.isEmpty()) { for (PAAttributeReadOnly attribute : map.get(new Integer(-1))) { if (attribute.getId() == AttributeConstants.ATT_UPC_EXCEPTION) { product.setUpcException(attribute.getOptionTextKey()); } if (attribute.getId() == AttributeConstants.ATT_BUSINESS_JUSTIFICATION) { product.setBusinessJustification(attribute.getTextValue()); } } } if (log.isDebugEnabled()) log.debug("getReadOnly(" +productID+ ", " +userTypeId+ ", " +pageTypeId+ ", " +languageID+ ") ... end"); return product; } */ @SuppressWarnings("unchecked") public int add(Product product) throws PADataAccessException { if (product == null) { throw new PADataAccessException("Product object is null."); } int productID = 0; if (log.isDebugEnabled()) { log.debug("Method add(" + product + ") ... begin"); } // Insert product header List parameterSQLlist = new ArrayList(); int identityPosition = 0; String insertProductSQL = buildInsertProductSQL(product); Attribute[] attributes = product.getAllAttributes(); if (attributes != null && attributes.length > 0) { for (int i = 0; i < attributes.length; i++) { Attribute attribute = attributes[i]; AttributeDefinition definition = (AttributeDefinition) (attribute.getAttributeDefinition()); int dataTypeID = attribute.getAttributeDefinition().getDataTypeID(); if (dataTypeID == AttributeConstants.DATATYPE_IS_STRING) { String[] values = attribute.getStringValues(); if (values != null) { for (int j = 0; j < values.length; j++) { Object[] parameters = new Object[] { ParameterSQL.IDENTITY_PLACEHOLDER, String.valueOf(definition.getID()), String.valueOf(j), JDBCUtil.sqlEscapeString(values[j]) }; parameterSQLlist.add(new ParameterSQL(SP_INSERT_PRODUCT_ATTRIBUTEVALUE_STRING, parameters, identityPosition)); } } } else if (dataTypeID == AttributeConstants.DATATYPE_IS_INTEGER) { Integer[] values = attribute.getIntegerValues(); if (values != null) { if (definition.getID() == AttributeConstants.ATT_PORTFOLIO) { final Map<Integer, String> portfolioMap = this .getStyleGuides(product.getProductTemplateID(), null, null); for (int j = 0; j < values.length; j++) { Object[] parameters = new Object[] { ParameterSQL.IDENTITY_PLACEHOLDER, String.valueOf(definition.getID()), String.valueOf(j), JDBCUtil.sqlEscapeString(portfolioMap.get(values[j].intValue())), String.valueOf(values[j].intValue()) }; parameterSQLlist .add(new ParameterSQL(SP_INSERT_PRODUCT_ATTRIBUTEVALUE_STRING_AND_INTEGER, parameters, identityPosition)); } } else if (definition.getID() == AttributeConstants.ATT_ON_BEHALF_COMPANY) { Company[] companyArray = companyDao .getCompanyAssociation(product.getLicenseeCompanyID(), 0); if (companyArray != null && values != null && values.length == 1) { for (int j = 0; j < values.length; j++) { for (int k = 0; k < companyArray.length; k++) { if (values[j] == companyArray[k].getID()) { Object[] parameters = new Object[] { ParameterSQL.IDENTITY_PLACEHOLDER, String.valueOf(definition.getID()), String.valueOf(j), JDBCUtil.sqlEscapeString(companyArray[k].getName()), String.valueOf(values[j].intValue()) }; parameterSQLlist.add(new ParameterSQL( SP_INSERT_PRODUCT_ATTRIBUTEVALUE_STRING_AND_INTEGER, parameters, identityPosition)); } } } } } else { for (int j = 0; j < values.length; j++) { Object[] parameters = new Object[] { ParameterSQL.IDENTITY_PLACEHOLDER, String.valueOf(definition.getID()), String.valueOf(j), String.valueOf(values[j].intValue()) }; parameterSQLlist.add(new ParameterSQL(SP_INSERT_PRODUCT_ATTRIBUTEVALUE_INTEGER, parameters, identityPosition)); } } } } else if (dataTypeID == AttributeConstants.DATATYPE_IS_DOUBLE) { Double[] values = attribute.getDoubleValues(); if (values != null) { for (int j = 0; j < values.length; j++) { Object[] parameters = new Object[] { ParameterSQL.IDENTITY_PLACEHOLDER, String.valueOf(definition.getID()), String.valueOf(j), String.valueOf(values[j].doubleValue()) }; parameterSQLlist.add(new ParameterSQL(SP_INSERT_PRODUCT_ATTRIBUTEVALUE_DOUBLE, parameters, identityPosition)); } } } else if (dataTypeID == AttributeConstants.DATATYPE_IS_DATE || dataTypeID == AttributeConstants.DATATYPE_IS_MMYYYY) { Date[] values = attribute.getDateValues(); if (values != null) { for (int j = 0; j < values.length; j++) { Object[] parameters = new Object[] { ParameterSQL.IDENTITY_PLACEHOLDER, String.valueOf(definition.getID()), String.valueOf(j), JDBCUtil.sqlEscapeDate(values[j]) }; parameterSQLlist.add(new ParameterSQL(SP_INSERT_PRODUCT_ATTRIBUTEVALUE_DATE, parameters, identityPosition)); } } } else if (dataTypeID == AttributeConstants.DATATYPE_IS_OPTION) { Option[] values = attribute.getOptionValues(); if (values != null) { for (int j = 0; j < values.length; j++) { Object[] parameters = new Object[] { ParameterSQL.IDENTITY_PLACEHOLDER, String.valueOf(definition.getID()), String.valueOf(j), String.valueOf(definition.getOptionSourceID()), String.valueOf(values[j].getID()) }; parameterSQLlist.add(new ParameterSQL(SP_INSERT_PRODUCT_ATTRIBUTEVALUE_OPTION, parameters, identityPosition)); } } } } } // Insert parent-child relation if any if (product.getParentID() != 0) { identityPosition = 1; Object[] parameters = new Object[] { String.valueOf(product.getParentID()), ParameterSQL.IDENTITY_PLACEHOLDER, String.valueOf(product.getCreatorID()) }; parameterSQLlist.add(new ParameterSQL(SP_INSERT_PRODUCT_CHILD, parameters, identityPosition)); } // AK 04/27/2012 [6.1] - Add ProductGroups for this product // How do you safeguard against ParameterSQL being empty if we ever turn off the build statement? ParameterSQL prodGroupSql = buildInsertProductGroupsSQL(ParameterSQL.IDENTITY_PLACEHOLDER, JDBCUtil.IGNORED, true); parameterSQLlist.add(prodGroupSql); // Create a Product Summary identityPosition = 0; Object[] parameters = new Object[] { ParameterSQL.IDENTITY_PLACEHOLDER }; parameterSQLlist.add(new ParameterSQL(SP_CREATE_PRODUCT_SUMMARY, parameters, identityPosition)); ParameterSQL[] array = new ParameterSQL[parameterSQLlist.size()]; parameterSQLlist.toArray(array); productID = JDBCUtil.executeInsertWithoutIdentity(insertProductSQL, array, dataSource); if (log.isDebugEnabled()) { log.debug("New product id = " + productID); log.debug("Method add(" + product + ") ... end"); } return productID; } private String buildInsertProductSQL(Product product) { return MessageFormat.format(SP_INSERT_PRODUCT, new Object[] { String.valueOf(product.getProductTemplateID()), JDBCUtil.sqlEscapeString(product.getName()), JDBCUtil.sqlEscapeString(product.getDescription()), String.valueOf(product.getLicenseeCompanyID()), String.valueOf(product.getLicenseeUserID()), String.valueOf(product.getAssociateUserID()), String.valueOf(product.getTechnicalLeadUserID()), String.valueOf(product.getCreatorID()), null }); } private String buildUpdateProductSQL(Product product) { return MessageFormat.format(SP_UPDATE_PRODUCT, new Object[] { String.valueOf(product.getID()), String.valueOf(product.getProductTemplateID()), JDBCUtil.sqlEscapeString(product.getName()), JDBCUtil.sqlEscapeString(product.getDescription()), String.valueOf(product.getLicenseeCompanyID()), String.valueOf(product.getLicenseeUserID()), String.valueOf(product.getAssociateUserID()), //OPA 5.11 - null this out if 0 or -1 (product.getTechnicalLeadUserID() <= 0) ? "null" : String.valueOf(product.getTechnicalLeadUserID()), String.valueOf(product.getCreatorID()) }); } public static List<Product> childProductsByType(Product[] childProducts, int childProductType) { List<Product> products = null; if (childProducts != null && childProducts.length > 0) { products = new ArrayList<Product>(); for (int i = 0; i < childProducts.length; i++) { Product product = childProducts[i]; if (product.isActive() && product.getProductType() == childProductType) { products.add(product); } } } return products; } public void update(Product product) throws PADataAccessException { if (product == null) { throw new PADataAccessException("Product object is null."); } //OPA 6.2, check if the parent template has template based permission 100 to allow the packaging prduct to have its own associate //if it does, then we need to update the associate id for child packaging component once update is called on parent since //updating parent product also updates child product associate id to parent associate id and that is not a desired situation final PermissionDefinition[] permissions = permissionSetDao.getPermissionDefinitions( product.getProductTemplateID(), permissionSetDao.ALLOW_PACKAGING_COMPONENT_FOR_ASSOCIATE); boolean doNotUpdateAssociateId = (permissions != null && permissions.length > 0) ? true : false; List<Product> undoAssociateIdProducts = null; //OPA 7.0: Undo Associate User Id for packaging and PI components if (doNotUpdateAssociateId) { Product[] childProducts = this.getChildProducts(product.getID(), 1); undoAssociateIdProducts = childProductsByType(childProducts, Product.PRODUCT_TYPE_PACKAGE_COMPONENT); List<Product> undoPIProductIds = childProductsByType(childProducts, Product.PRODUCT_TYPE_PI_COMPONENT); if (undoPIProductIds != null) { undoAssociateIdProducts.addAll(undoPIProductIds); } } else { Product[] childProducts = this.getChildProducts(product.getID(), 1); undoAssociateIdProducts = childProductsByType(childProducts, Product.PRODUCT_TYPE_PI_COMPONENT); } int productID = product.getID(); String productName = product.getName(); if (log.isDebugEnabled()) { log.debug("Method update(" + product + ") with id = " + productID + " ... begin"); } List<String> sqlList = new ArrayList<String>(); //Add subcategory prefix and property suffix for NA Stationery Products PermissionDefinition[] permission = permissionSetDao.getPermissionDefinitions( product.getProductTemplateID(), permissionSetDao.PRODUCT_NAMING_CONVENTION); if (product.getName() != "" && permission != null && permission.length > 0) { productName = getRevisedProductName(product); product.setName(productName); } // Update product header String updateProductSQL = buildUpdateProductSQL(product); sqlList.add(updateProductSQL); Attribute[] attributes = product.getAllAttributes(); if (attributes != null && attributes.length > 0) { for (int i = 0; i < attributes.length; i++) { Attribute attribute = (Attribute) attributes[i]; AttributeDefinition definition = (AttributeDefinition) (attribute.getAttributeDefinition()); if (attribute.isDirty()) { // First, delete existing attribute values String deleteAttributeSQL = MessageFormat.format(SP_DELETE_ATTRIBUTE_VALUES, new Object[] { String.valueOf(productID), String.valueOf(definition.getID()) }); sqlList.add(deleteAttributeSQL); int dataTypeID = attribute.getAttributeDefinition().getDataTypeID(); // Then insert new values if (dataTypeID == AttributeConstants.DATATYPE_IS_STRING) { String[] values = attribute.getStringValues(); if (values != null) { for (int j = 0; j < values.length; j++) { String insertStringSQL = MessageFormat.format( SP_INSERT_PRODUCT_ATTRIBUTEVALUE_STRING, new Object[] { String.valueOf(productID), String.valueOf(definition.getID()), String.valueOf(j), JDBCUtil.sqlEscapeString(values[j]) }); sqlList.add(insertStringSQL); } } } else if (dataTypeID == AttributeConstants.DATATYPE_IS_INTEGER) { Integer[] values = attribute.getIntegerValues(); // Portfolio attribute, store both TextValue and Integer Value; if (values != null) { if (definition.getID() == AttributeConstants.ATT_PORTFOLIO) { final Map<Integer, String> portfolioMap = this .getStyleGuides(product.getProductTemplateID(), null, null); for (int j = 0; j < values.length; j++) { String insertIntegerSQL = MessageFormat.format( SP_INSERT_PRODUCT_ATTRIBUTEVALUE_STRING_AND_INTEGER, new Object[] { String.valueOf(productID), String.valueOf(definition.getID()), String.valueOf(j), JDBCUtil.sqlEscapeString( portfolioMap.get(values[j].intValue())), String.valueOf(values[j].intValue()) }); sqlList.add(insertIntegerSQL); } } else if (definition.getID() == AttributeConstants.ATT_ON_BEHALF_COMPANY) { Company[] companyArray = companyDao .getCompanyAssociation(product.getLicenseeCompanyID(), 0); if (companyArray != null && values != null && values.length == 1) { for (int j = 0; j < values.length; j++) { for (int k = 0; k < companyArray.length; k++) { if (values[j] == companyArray[k].getID()) { String insertIntegerSQL = MessageFormat.format( SP_INSERT_PRODUCT_ATTRIBUTEVALUE_STRING_AND_INTEGER, new Object[] { String.valueOf(productID), String.valueOf(definition.getID()), String.valueOf(j), JDBCUtil.sqlEscapeString(companyArray[k].getName()), String.valueOf(values[j].intValue()) }); sqlList.add(insertIntegerSQL); } } } } } else { for (int j = 0; j < values.length; j++) { String insertIntegerSQL = MessageFormat.format( SP_INSERT_PRODUCT_ATTRIBUTEVALUE_INTEGER, new Object[] { String.valueOf(productID), String.valueOf(definition.getID()), String.valueOf(j), String.valueOf(values[j].intValue()) }); sqlList.add(insertIntegerSQL); } } } } else if (dataTypeID == AttributeConstants.DATATYPE_IS_DOUBLE) { Double[] values = attribute.getDoubleValues(); if (values != null) { for (int j = 0; j < values.length; j++) { String insertDoubleSQL = MessageFormat.format( SP_INSERT_PRODUCT_ATTRIBUTEVALUE_DOUBLE, new Object[] { String.valueOf(productID), String.valueOf(definition.getID()), String.valueOf(j), String.valueOf(values[j].doubleValue()) }); sqlList.add(insertDoubleSQL); } } } else if (dataTypeID == AttributeConstants.DATATYPE_IS_DATE || dataTypeID == AttributeConstants.DATATYPE_IS_MMYYYY) { Date[] values = attribute.getDateValues(); if (values != null) { for (int j = 0; j < values.length; j++) { String insertDateSQL = MessageFormat.format(SP_INSERT_PRODUCT_ATTRIBUTEVALUE_DATE, new Object[] { String.valueOf(productID), String.valueOf(definition.getID()), String.valueOf(j), JDBCUtil.sqlEscapeDate(values[j]) }); sqlList.add(insertDateSQL); } } } else if (dataTypeID == AttributeConstants.DATATYPE_IS_OPTION) { Option[] values = attribute.getOptionValues(); if (values != null) { for (int j = 0; j < values.length; j++) { String insertOptionSQL = MessageFormat.format( SP_INSERT_PRODUCT_ATTRIBUTEVALUE_OPTION, new Object[] { String.valueOf(productID), String.valueOf(definition.getID()), String.valueOf(j), String.valueOf(definition.getOptionSourceID()), String.valueOf(values[j].getID()) }); sqlList.add(insertOptionSQL); } } } } } } // AK 04/27/2012 [6.1] - update/insert ProductGroups for Product String insertProductGroupsSQL = this.buildInsertProductGroupsSQL(productID, JDBCUtil.IGNORED, true); sqlList.add(insertProductGroupsSQL); //OPA 7.3: keep this call to add all the other sql statements //JDBCUtil.executeUpdateDeleteInsert(sqlList); for (String sql : sqlList) { namedJdbcTemplate.execute(sql, new PreparedStatementCallback() { @Override public Object doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException { return ps.executeUpdate(); } }); } String summarySQL = MessageFormat.format(SP_CREATE_PRODUCT_SUMMARY, new Object[] { String.valueOf(productID) }); namedJdbcTemplate.execute(summarySQL, new PreparedStatementCallback() { @Override public Object doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException { return ps.executeUpdate(); } }); //since the associate Id was updated when the parent product was updated (ex, packaging component //need to have own associate id), the associate id needs to be reverted back (see above for more comments, OPA 6.2) if (undoAssociateIdProducts != null && undoAssociateIdProducts.size() > 0) { for (Product tempProduct : undoAssociateIdProducts) { updateUsers(tempProduct, JDBCUtil.IGNORED, tempProduct.getAssociateUserID(), JDBCUtil.IGNORED); } } if (log.isDebugEnabled()) { log.debug("Method update(" + product + ") with id = " + product.getID() + " ... end"); } } public void attachNewComponent(int parentProductID, int productID, int userId) throws PADataAccessException { if (log.isDebugEnabled()) { log.debug("Method attachNewComponent(" + parentProductID + ", " + productID + ", " + userId + ") ... begin"); } List sqlList = new ArrayList(); String sql = MessageFormat.format(SP_INSERT_PRODUCT_CHILD, new Object[] { String.valueOf(parentProductID), String.valueOf(productID), String.valueOf(userId) }); sqlList.add(sql); if (log.isDebugEnabled()) { log.debug("create the product summary for " + String.valueOf(productID)); } //AK 04/27/2012 [6.1] - update/insert ProductGroups for Product. // should not need to do this for parent product. String insertProductGroupsSQL = this.buildInsertProductGroupsSQL(productID, JDBCUtil.IGNORED, true); sqlList.add(insertProductGroupsSQL); // Product Summary String summarySQL = MessageFormat.format(SP_CREATE_PRODUCT_SUMMARY_WITH_PARENT, new Object[] { String.valueOf(productID), String.valueOf(parentProductID) }); sqlList.add(summarySQL); JDBCUtil.executeUpdateDeleteInsert(sqlList, dataSource); if (log.isDebugEnabled()) { log.debug("Method attachNewComponent(" + parentProductID + ", " + productID + ", " + userId + ") ... end"); } } @SuppressWarnings("unchecked") public void submitProductNewComponent(int productID, int workflowID, int stepID, int ownerUserID, int performerUserID, int userTypeID, int reviewTypeID) throws PADataAccessException { if (log.isDebugEnabled()) { log.debug("Method submitProduct(" + productID + ", " + workflowID + ", " + stepID + ", " + ownerUserID + ", " + performerUserID + ", " + reviewTypeID + ") ... begin"); } @SuppressWarnings("rawtypes") List sqlList = new ArrayList(); String reviewTypeIDString = null; if (reviewTypeID != JDBCUtil.IGNORED) { reviewTypeIDString = String.valueOf(reviewTypeID); } String ownerIDString = null; String performerIDString = null; if (ownerUserID != 0) { ownerIDString = String.valueOf(ownerUserID); } if (performerUserID != 0) { performerIDString = String.valueOf(performerUserID); } String sql = MessageFormat.format(SP_SUBMIT_PRODUCT, new Object[] { String.valueOf(productID), String.valueOf(workflowID), String.valueOf(stepID), ownerIDString, performerIDString, String.valueOf(userTypeID), reviewTypeIDString }); sqlList.add(sql); // AK 04/27/2012 [6.1] - update/insert ProductGroups for Product/Component String insertProductGroupsSQL = this.buildInsertProductGroupsSQL(productID, JDBCUtil.IGNORED, true); sqlList.add(insertProductGroupsSQL); // Product Summary String summarySQL = MessageFormat.format(SP_CREATE_PRODUCT_SUMMARY, new Object[] { String.valueOf(productID) }); sqlList.add(summarySQL); JDBCUtil.executeUpdateDeleteInsert(sqlList, dataSource); if (log.isDebugEnabled()) { log.debug("Method submitProductNewComponent(" + productID + ", " + workflowID + ", " + stepID + ", " + ownerUserID + ", " + performerUserID + ", " + reviewTypeID + ") ... end"); } } @SuppressWarnings("unchecked") public void submitProduct(int productID, int workflowID, int stepID, int ownerUserID, int performerUserID, int userTypeID, int reviewTypeID) throws PADataAccessException { if (log.isDebugEnabled()) { log.debug("Method submitProduct(" + productID + ", " + workflowID + ", " + stepID + ", " + ownerUserID + ", " + performerUserID + ", " + userTypeID + ", " + reviewTypeID + ") ... begin"); } @SuppressWarnings("rawtypes") List sqlList = new ArrayList(); String reviewTypeIDString = null; if (reviewTypeID != JDBCUtil.IGNORED) { reviewTypeIDString = String.valueOf(reviewTypeID); } String ownerIDString = null; String performerIDString = null; if (ownerUserID != 0) { ownerIDString = String.valueOf(ownerUserID); } if (performerUserID != 0) { performerIDString = String.valueOf(performerUserID); } String sql = MessageFormat.format(SP_SUBMIT_PRODUCT, new Object[] { String.valueOf(productID), String.valueOf(workflowID), String.valueOf(stepID), ownerIDString, performerIDString, String.valueOf(userTypeID), reviewTypeIDString }); sqlList.add(sql); // Remove the product from a receiver's folder. String removeProductFromFolder = MessageFormat.format(SP_DEL_PRODUCT_FROM_USER_FOLDER, new Object[] { String.valueOf(performerUserID), String.valueOf(productID) }); sqlList.add(removeProductFromFolder); // AK 04/27/2012 [6.1] - update/insert ProductGroups for Product String insertProductGroupSQL = this.buildInsertProductGroupsSQL(productID, performerUserID, true); sqlList.add(insertProductGroupSQL); // Product Summary String summarySQL = MessageFormat.format(SP_CREATE_PRODUCT_SUMMARY, new Object[] { String.valueOf(productID) }); sqlList.add(summarySQL); JDBCUtil.executeUpdateDeleteInsert(sqlList, dataSource); if (log.isDebugEnabled()) { log.debug("Method submitProduct(" + productID + ", " + workflowID + ", " + stepID + ", " + ownerUserID + ", " + performerUserID + ", " + userTypeID + ", " + reviewTypeID + ") ... end"); } } public void submitProduct(int productID, int workflowID, int stepID, int userID, int userTypeID, int reviewTypeID) throws PADataAccessException { if (log.isDebugEnabled()) { log.debug("Method submitProduct(" + productID + ", " + workflowID + ", " + stepID + ", " + userID + ", " + userTypeID + ", " + reviewTypeID + ") ... begin"); } submitProduct(productID, workflowID, stepID, userID, userID, userTypeID, reviewTypeID); if (log.isDebugEnabled()) { log.debug("Method submitProduct(" + productID + ", " + workflowID + ", " + stepID + ", " + userID + ", " + userTypeID + ", " + reviewTypeID + ") ... end"); } } public void deleteProductAttribute(int productId, int productAttributeId) throws PADataAccessException { if (log.isDebugEnabled()) { log.debug("Method deleteProductAttribute(" + productId + productAttributeId + ") ... begin"); } String sql = MessageFormat.format(SP_DELETE_ATTRIBUTE_VALUES, new Object[] { String.valueOf(productId), String.valueOf(productAttributeId) }); JDBCUtil.executeUpdateDeleteInsert(sql, dataSource); if (log.isDebugEnabled()) { log.debug("Method deleteProductAttribute(" + productId + productAttributeId + ") ... end"); } } public void delegate(int productStateID, int userID, int currentPerformerUserId) throws PADataAccessException { if (log.isDebugEnabled()) { log.debug("Method delegate(" + productStateID + ", " + userID + ", " + currentPerformerUserId + ") ... begin"); } String sql = MessageFormat.format(SP_DELEGATE_PRODUCT, new Object[] { String.valueOf(productStateID), String.valueOf(userID), String.valueOf(currentPerformerUserId) }); JDBCUtil.executeUpdateDeleteInsert(sql, dataSource); if (log.isDebugEnabled()) { log.debug("Method delegate(" + productStateID + ", " + userID + ", " + currentPerformerUserId + ") ... end"); } } public void setActiveStatus(int productID, boolean isActive) throws PADataAccessException { if (log.isDebugEnabled()) { log.debug("Method setActiveStatus(" + productID + "," + isActive + ") ... begin"); } List sqlList = new ArrayList(); String sql = MessageFormat.format(SP_ACTIVATE_PRODUCT, new Object[] { String.valueOf(productID), String.valueOf(isActive == true ? 1 : 0) }); sqlList.add(sql); //No need to reset ProductGroups for product String summarySQL = MessageFormat.format(SP_CREATE_PRODUCT_SUMMARY, new Object[] { String.valueOf(productID) }); sqlList.add(summarySQL); JDBCUtil.executeUpdateDeleteInsert(sqlList, dataSource); if (log.isDebugEnabled()) { log.debug("Method setActiveStatus(" + productID + "," + isActive + ") ... end"); } } public void deleteProduct(int productID) throws PADataAccessException { if (log.isDebugEnabled()) { log.debug("Method deleteProduct(" + productID + ") ... begin"); } String sql = MessageFormat.format(SP_DELETE_PRODUCT, new Object[] { String.valueOf(productID) }); JDBCUtil.executeUpdateDeleteInsert(sql, dataSource); if (log.isDebugEnabled()) { log.debug("Method deleteProduct(" + productID + ") ... end"); } } public void updateProductStatus(int productID, int productStatusID) throws PADataAccessException { if (log.isDebugEnabled()) { log.debug("Method updateProductStatus(" + productID + "," + productStatusID + ") ... begin"); } List sqlList = new ArrayList(); String sql = MessageFormat.format(SP_UPDATE_PRODUCT_STATUS, new Object[] { String.valueOf(productID), String.valueOf(productStatusID) }); sqlList.add(sql); //No need to reset ProductGroups for product String summarySQL = MessageFormat.format(SP_CREATE_PRODUCT_SUMMARY, new Object[] { String.valueOf(productID) }); sqlList.add(summarySQL); JDBCUtil.executeUpdateDeleteInsert(sqlList, dataSource); if (log.isDebugEnabled()) { log.debug("Method updateProductStatus(" + productID + "," + productStatusID + ") ... end"); } } public void delegateReviewer(int productStateID, int delegatorReviewerID, int delegateeReviewerID) throws PADataAccessException { if (log.isDebugEnabled()) { log.debug("Method delegateReviewer(" + productStateID + "," + delegatorReviewerID + "," + delegateeReviewerID + ") ... begin"); } String sql = MessageFormat.format(SP_DELEGATE_REVIEWER, new Object[] { String.valueOf(productStateID), String.valueOf(delegatorReviewerID), String.valueOf(delegateeReviewerID) }); JDBCUtil.executeUpdateDeleteInsert(sql, dataSource); if (log.isDebugEnabled()) { log.debug("Method delegateReviewer(" + productStateID + "," + delegatorReviewerID + "," + delegateeReviewerID + ") ... end"); } } /* public void submitToReviewers(int productID, int productStateID) throws PADataAccessException { if(log.isDebugEnabled()) { log.debug("Method submitToReviewers(" + productID + ", " + productStateID + ") ... begin"); } List sqlList = new ArrayList(); String sql = MessageFormat.format(SP_SUBMIT_TO_REVIEWERS, new Object[] {String.valueOf(productStateID)}); sqlList.add(sql); // Get current reviewers and remove product from their folder ReviewResult[] reviewResults = this.getReviewResults(productStateID); if(reviewResults != null) { for(int i = 0; i < reviewResults.length; i++) { UserOption user = reviewResults[i].getReviewPerformer(); if(user != null) { int userID = user.getUserID(); String removeProductFromFolder = MessageFormat.format(SP_DEL_PRODUCT_FROM_USER_FOLDER, new Object[]{String.valueOf(userID), String.valueOf(productID)}); sqlList.add(removeProductFromFolder); } } } String summarySQL = MessageFormat.format(SP_CREATE_PRODUCT_SUMMARY, new Object[]{String.valueOf(productID)}); sqlList.add(summarySQL); JDBCUtil.executeUpdateDeleteInsert(sqlList,namedJdbcTemplate); if(log.isDebugEnabled()) { log.debug("Method submitToReviewers(" + productID + ", " + productStateID + ") ... end"); } } public void setReviewers(int productStateID, int[] userIDs) throws PADataAccessException { if(userIDs == null || userIDs.length == 0) { return; } if(log.isDebugEnabled()) { log.debug("Method setReviewers(" + productStateID + ", " + userIDs + ") ... begin"); } List list = new ArrayList(); // get Current Reviewers ReviewResult[] results = this.getReviewResults(productStateID); HashMap assignedReviewerIDs = new HashMap(); HashMap newReviewerIDs = new HashMap(); for(int i = 0; i < userIDs.length; i++) { newReviewerIDs.put(new Integer(userIDs[i]), null); } if(results != null) { for(int i = 0; i < results.length; i++) { int oldReviewerID = results[i].getAssignedReviewer().getUserID(); Integer key = new Integer(oldReviewerID); assignedReviewerIDs.put(key, null); // remove the previous reviewer if he is not in the new reviewers list and product has not been submitted to him yet. if(! newReviewerIDs.containsKey(key) && results[i].canBeReviewed() == false) { String removeReviewerSQL = MessageFormat.format(SP_DEL_UNASSIGNED_REVIEWER, new Object[]{String.valueOf(productStateID), String.valueOf(oldReviewerID)}); list.add(removeReviewerSQL); } } } for(int i = 0; i < userIDs.length; i++) { Integer key = new Integer(userIDs[i]); if(assignedReviewerIDs.containsKey(key)) { if(log.isDebugEnabled()) { log.debug("User " + userIDs[i] + " is already set as a reviewer. No need to add it again."); } } else { // new reviewer String sql = MessageFormat.format(SP_ASSIGN_REVIEWER, new Object[] {String.valueOf(productStateID), String.valueOf(userIDs[i])}); list.add(sql); } } JDBCUtil.executeUpdateDeleteInsert(list); if(log.isDebugEnabled()) { log.debug("Method setReviewers(" + productStateID + ", " + userIDs + ") ... end"); } } */ public UserOption[] getSelectedReviewers(int productID) throws PADataAccessException { if (log.isDebugEnabled()) { log.debug("Method getSelectedReviewers(" + productID + ") ... begin"); } PAUserOption[] array = null; String sql = JDBCUtil.query(SP_SEL_SELECTED_REVIEWERS, new Object[] { String.valueOf(productID) }); MapSqlParameterSource parmSource = new MapSqlParameterSource(); List<UserOption> list = namedJdbcTemplate.query(sql, parmSource, rowMappers.userOptionRowMapper); if (list != null && list.size() > 0) { array = new PAUserOption[list.size()]; list.toArray(array); } if (log.isDebugEnabled()) { log.debug("Method getSelectedReviewers(" + productID + ") ... end"); } return array; } public void removeReviewers(final int productID, final int productStateID, final int[] reviewerIDs) throws PADataAccessException { if (log.isDebugEnabled()) { log.debug("Method removeReviewers(" + productStateID + ", " + reviewerIDs + ") ... begin"); } String sql = null; String deleteSQL = null; if (reviewerIDs == null) { deleteSQL = MessageFormat.format(SP_DEL_SELECTED_REVIEWERS, new Object[] { String.valueOf(productID), "null" }); JDBCUtil.executeUpdateDeleteInsert(deleteSQL, dataSource); sql = MessageFormat.format(SP_DEL_REVIEWERS, new Object[] { String.valueOf(productStateID), "null" }); JDBCUtil.executeUpdateDeleteInsert(sql, dataSource); } else { for (int i = 0; i < reviewerIDs.length; i++) { deleteSQL = MessageFormat.format(SP_DEL_SELECTED_REVIEWERS, new Object[] { String.valueOf(productID), String.valueOf(reviewerIDs[i]) }); JDBCUtil.executeUpdateDeleteInsert(deleteSQL, dataSource); sql = MessageFormat.format(SP_DEL_REVIEWERS, new Object[] { String.valueOf(productStateID), String.valueOf(reviewerIDs[i]) }); JDBCUtil.executeUpdateDeleteInsert(sql, dataSource); } } //No need to reset ProductGroups for product final String summarySQL = MessageFormat.format(SP_CREATE_PRODUCT_SUMMARY, new Object[] { String.valueOf(productID) }); JDBCUtil.executeUpdateDeleteInsert(summarySQL, dataSource); if (log.isDebugEnabled()) { log.debug("Method removeReviewers(" + productStateID + ", " + reviewerIDs + ") ... end"); } } public void review(int productID, int productStateID, int assignedReviewerID, int reviewPerformerID, int reviewOptionID) throws PADataAccessException { if (log.isDebugEnabled()) { log.debug("Method review(" + productID + ", " + productStateID + ", " + assignedReviewerID + ", " + reviewOptionID + ") ... begin"); } /* OPA 6.0 - ESPN -- AK 02/16/12: As of OPA 6.0 ESPN Reviewers do not vote on product being reviewed (they only comment). OPA 6.0 - ESPN -- AK 02/24/12: I was asked to put the vote back for reviewers, but I can leave this code in place because it does not break anything. */ String reviewOptionIDString = null; if (reviewOptionID != JDBCUtil.IGNORED) { reviewOptionIDString = String.valueOf(reviewOptionID); } List sqlList = new ArrayList(); String sql = MessageFormat.format(SP_REVIEW_PRODUCT, new Object[] { String.valueOf(productStateID), String.valueOf(assignedReviewerID), String.valueOf(reviewPerformerID), reviewOptionIDString }); sqlList.add(sql); //No need to reset ProductGroups for product String summarySQL = MessageFormat.format(SP_CREATE_PRODUCT_SUMMARY, new Object[] { String.valueOf(productID) }); sqlList.add(summarySQL); JDBCUtil.executeUpdateDeleteInsert(sqlList, dataSource); if (log.isDebugEnabled()) { log.debug("Method review(" + productID + ", " + productStateID + ", " + assignedReviewerID + ", " + reviewOptionID + ") ... end"); } } public ReviewResult[] getReviewResults(int productStateID, int permissionID) throws PADataAccessException { if (log.isDebugEnabled()) { log.debug("Method getReviewResults(" + productStateID + "," + permissionID + ") ... begin"); } ReviewResult[] array = null; String sql = JDBCUtil.query(SP_GET_PRODUCT_REVIEWS_BY_PERMISSION, new Object[] { String.valueOf(productStateID), String.valueOf(permissionID) }); MapSqlParameterSource parmSource = new MapSqlParameterSource(); List<ReviewResult> list = namedJdbcTemplate.query(sql, parmSource, rowMappers.reviewResultRowMapper); if (list != null && list.size() > 0) { array = new ReviewResult[list.size()]; list.toArray(array); } if (log.isDebugEnabled()) { log.debug("Method getReviewResults(" + productStateID + "," + permissionID + ") ... end"); } return array; } // Tseng, Denys - 2008/01/17 - Added templateId so that the dispositions // can be different from template to template public Option[] getReviewOptions(int templateId) throws PADataAccessException { if (log.isDebugEnabled()) { log.debug("Method getReviewOptions() ... begin"); } Option[] array = null; String sql = JDBCUtil.query(SP_GET_REVIEW_OPTIONS, new Object[] { String.valueOf(templateId) }); MapSqlParameterSource parmSource = new MapSqlParameterSource(); List<Option> list = namedJdbcTemplate.query(sql, parmSource, rowMappers.optionResultSetRowMapper); if (list != null && list.size() > 0) { array = new Option[list.size()]; list.toArray(array); } if (log.isDebugEnabled()) { log.debug("Method getReviewOptions() ... end"); } return array; } public Option[] getReviewOptions(String csv, int csvType) throws PADataAccessException { if (log.isDebugEnabled()) { log.debug("Method getReviewOptions(csv, idType) ... begin"); } String csvTypeString = null; if (csvType != JDBCUtil.IGNORED) { csvTypeString = String.valueOf(csvType); } Option[] array = null; String sql = JDBCUtil.query(SP_GET_REVIEW_OPTIONS_BY_CSV, new Object[] { JDBCUtil.sqlEscapeString(csv), csvTypeString }); MapSqlParameterSource parmSource = new MapSqlParameterSource(); List<Option> list = namedJdbcTemplate.query(sql, parmSource, rowMappers.optionResultSetRowMapper); if (list != null && list.size() > 0) { array = new Option[list.size()]; list.toArray(array); } if (log.isDebugEnabled()) { log.debug("Method getReviewOptions(csv, idType) ... end"); } return array; } public ProductState getProductState(int productID) throws PADataAccessException { if (log.isDebugEnabled()) { log.debug("Method getProductState(" + productID + ") ... begin"); } ProductState state = null; String sql = JDBCUtil.query(SP_GET_PRODUCT_STATE, new Object[] { String.valueOf(productID) }); MapSqlParameterSource parmSource = new MapSqlParameterSource(); List<ProductState> list = namedJdbcTemplate.query(sql, parmSource, rowMappers.productStateRowMapper); if (list != null && list.size() > 0) { state = (ProductState) list.get(0); } if (log.isDebugEnabled()) { log.debug("Method getProductState(" + productID + ") ... end"); } return state; } // defaults to get only active products public Product get(int productID, int languageID, boolean populateAvailableOptions) throws PADataAccessException { return get(productID, languageID, populateAvailableOptions, 1); } public List<ProductForSpi> getProductsForProjectId(int projectId) throws PADataAccessException { String sql = JDBCUtil.query(SP_GET_PRODUCT_FOR_SPI, new Object[] { String.valueOf(projectId) }); MapSqlParameterSource parmSource = new MapSqlParameterSource(); List<ProductForSpi> list = namedJdbcTemplate.query(sql, parmSource, rowMappers.productForSpiRowMapper); return list; } public Product get(int productID, int languageID, boolean populateAvailableOptions, int active) throws PADataAccessException { Product product = null; if (log.isDebugEnabled()) { log.debug("Method get(" + productID + ", " + languageID + ", " + populateAvailableOptions + ") ... begin"); } String activeString = null; if (active != JDBCUtil.IGNORED) { activeString = String.valueOf(active); } ProductHeader header = null; String sql = JDBCUtil.query(SP_GET_PRODUCT, new Object[] { String.valueOf(productID), String.valueOf(activeString) }); MapSqlParameterSource parmSource = new MapSqlParameterSource(); List<ProductHeader> list = namedJdbcTemplate.query(sql, parmSource, rowMappers.productHeaderRowMapper); if (list != null && list.size() > 0) { ProductHeader[] array = new ProductHeader[list.size()]; list.toArray(array); header = array[0]; } if (header != null) { int productTemplateID = header.getProductTemplateID(); int productStatusID = header.getProductStatusID(); if (productTemplateID != 0) { // Get AttributeSet definitions Product p = createProduct(productTemplateID, languageID, populateAvailableOptions, productStatusID); if (p != null) { product = (Product) p; product.setID(productID); product.setAssociateUserID(header.getAssociateID()); product.setTechnicalLeadUserID(header.getTechnicalLeadID()); product.setProductStatusID(header.getProductStatusID()); product.setCreatorID(header.getCreatorID()); product.setLicenseeCompanyID(header.getLicenseeCompanyID()); product.setLicenseeCompanyExternalRefID(header.getLicenseeCompanyExternalRefID()); product.setLicenseeUserID(header.getLicenseeID()); product.setProductTemplateID(productTemplateID); product.setLastUpdateDate(header.getUpdateDate()); product.setActive(header.isActive()); product.setParentID(header.getParentID()); // Rex adding creationDate product.setCreationDate(header.getCreationDate()); // Get Attribute Values ProductAttributeProcessor processor = new ProductAttributeProcessor(p); sql = JDBCUtil.query(SP_GET_PRODUCT_ATTRIBUTES, new Object[] { String.valueOf(productID), String.valueOf(languageID) }); parmSource = new MapSqlParameterSource(); List<Attribute> AttributeList = namedJdbcTemplate.query(sql, parmSource, rowMappers.attributeResultSetExtractor); /* for (Attribute a : product.getAllAttributes()) { for (Attribute productAttribute : AttributeList) { if (a.getAttributeId() == productAttribute.getAttributeId()) { if (null != a.getValues()) { productAttribute.set(a.getValues()); } else { productAttribute.add(a.getValue()); } productAttribute.setDirty(false); } } } */ for (Attribute a : AttributeList) { for (Attribute productAttribute : product.getAllAttributes()) { if (a.getAttributeId() == productAttribute.getAttributeId()) { if (null != a.getValues()) { productAttribute.set(a.getValues()); } else { productAttribute.add(a.getValue()); } productAttribute.setDirty(false); } } } } } } // Set dirty flag to false for each attribute // so future update will check if an attribute is dirty to determine if an update is necessary. if (product != null) { Attribute[] attributes = product.getAllAttributes(); if (attributes != null && attributes.length > 0) { for (int i = 0; i < attributes.length; i++) { Attribute a = (Attribute) attributes[i]; a.setDirty(false); } } } if (log.isDebugEnabled()) { log.debug( "Method get(" + productID + ", " + languageID + ", " + populateAvailableOptions + ") ... end"); } return product; } public ReviewResult[] getReviewResults(int productStateID) throws PADataAccessException { if (log.isDebugEnabled()) { log.debug("Method getReviewResults(" + productStateID + ") ... begin"); } ReviewResult[] array = null; String sql = JDBCUtil.query(SP_GET_PRODUCT_REVIEWS, new Object[] { String.valueOf(productStateID) }); MapSqlParameterSource parmSource = new MapSqlParameterSource(); List<ReviewResult> list = namedJdbcTemplate.query(sql, parmSource, rowMappers.reviewResultRowMapper); if (list != null && list.size() > 0) { array = new ReviewResult[list.size()]; list.toArray(array); } if (log.isDebugEnabled()) { log.debug("Method getReviewResults(" + productStateID + ") ... end"); } return array; } /** * Retrieves all of a product's product states joined to the previous/prior * product state of data. Each product state object contains data * pertaining to the previous/prior product state. This is consistent with * the way OPA retrieves product state data. * @param productID a <code>Product</code> identifier. * @return an array of <code>ProductState</code> objects. * @throws PADataAccessException */ @SuppressWarnings("unchecked") public ProductState[] getProductStates(int productID) throws PADataAccessException { if (log.isDebugEnabled()) { log.debug("Method getProductStates(" + productID + ") ... begin"); } ProductState[] states = null; String sql = JDBCUtil.query(SP_GET_PRODUCT_STATES, new Object[] { String.valueOf(productID) }); MapSqlParameterSource parmSource = new MapSqlParameterSource(); List<ProductState> list = namedJdbcTemplate.query(sql, parmSource, rowMappers.productStateRowMapper); if (list != null && list.size() > 0) { states = new ProductState[list.size()]; list.toArray(states); } if (log.isDebugEnabled()) { log.debug("Method getProductStates(" + productID + ") ... end"); } return states; } public void updateProductState(int productId, int newStepId) throws PADataAccessException { if (log.isDebugEnabled()) { log.debug("Method updateProductState(" + productId + ", " + newStepId + ") ... begin"); } String sqlUpdateProductState = MessageFormat.format(SP_UPDATE_PRODUCT_STATE, new Object[] { String.valueOf(productId), String.valueOf(newStepId) }); JDBCUtil.executeUpdateDeleteInsert(sqlUpdateProductState, dataSource); if (log.isDebugEnabled()) { log.debug("Method updateProductState(" + productId + ", " + newStepId + ") ... end"); } } public int[] getRequiredAttributeIDs(int productTemplateID, int productStatusID) throws PADataAccessException { if (log.isDebugEnabled()) { log.debug( "Method getRequiredAttributeIDs(" + productTemplateID + ", " + productStatusID + ") ... begin"); } int[] ids = null; String sql = JDBCUtil.query(SP_SEL_REQUIRED_ATTRIBUTE_IDS, new Object[] { String.valueOf(productTemplateID), String.valueOf(productStatusID) }); MapSqlParameterSource parmSource = new MapSqlParameterSource(); List<Integer> list = namedJdbcTemplate.query(sql, parmSource, rowMappers.integerRowMapper); if (list != null && list.size() > 0) { Integer[] array = new Integer[list.size()]; list.toArray(array); ids = new int[list.size()]; for (int i = 0; i < ids.length; i++) { ids[i] = array[i].intValue(); } } if (log.isDebugEnabled()) { log.debug("Method getRequiredAttributeIDs(" + productTemplateID + ", " + productStatusID + ") ... end"); } return ids; } public RequiredAttributeDependency getDependentRequiredAttributeIDs(int productTemplateID, int productStatusID) throws PADataAccessException { if (log.isDebugEnabled()) { log.debug("Method getDependentRequiredAttributeIDs(" + productTemplateID + ", " + productStatusID + ") ... begin"); } String sql = JDBCUtil.query(SP_SEL_DEPENDENT_REQUIRED_ATTRIBUTE, new Object[] { String.valueOf(productTemplateID), String.valueOf(productStatusID) }); MapSqlParameterSource parmSource = new MapSqlParameterSource(); List<RequiredAttributeDependency> list = namedJdbcTemplate.query(sql, parmSource, rowMappers.dependentRequiredAttributeRowMapper); RequiredAttributeDependency result = new RequiredAttributeDependency(); if (list != null && list.size() > 0) { result = list.get(0); } if (log.isDebugEnabled()) { log.debug("Method getDependentRequiredAttributeIDs(" + productTemplateID + ", " + productStatusID + ") ... end"); } return result; } public Option[] getProductStatusOptions() throws PADataAccessException { if (log.isDebugEnabled()) { log.debug("Method getProductStatusOptions() ... begin"); } Option[] array = null; String sql = JDBCUtil.query(SP_SEL_PRODUCTSTATUS_OPTION); MapSqlParameterSource parmSource = new MapSqlParameterSource(); List<Option> list = namedJdbcTemplate.query(sql, parmSource, rowMappers.optionResultSetRowMapper); if (list != null && list.size() > 0) { array = new Option[list.size()]; list.toArray(array); } if (log.isDebugEnabled()) { log.debug("Method getProductStatusOptions() ... end"); } return array; } public int[] getProductStatusIDsAfter(int currentProductStatusID) throws PADataAccessException { if (log.isDebugEnabled()) { log.debug("Method getProductStatusIDsAfter(" + currentProductStatusID + ") ... begin"); } int[] ids = null; String sql = JDBCUtil.query(SP_SEL_PRODUCT_STATUS_AFTER, new Object[] { String.valueOf(currentProductStatusID) }); MapSqlParameterSource parmSource = new MapSqlParameterSource(); List<Integer> list = namedJdbcTemplate.query(sql, parmSource, rowMappers.integerRowMapper); if (list != null && list.size() > 0) { Integer[] array = new Integer[list.size()]; list.toArray(array); ids = new int[list.size()]; for (int i = 0; i < ids.length; i++) { ids[i] = array[i].intValue(); } } if (log.isDebugEnabled()) { log.debug("Method getProductStatusIDsAfter(" + currentProductStatusID + ") ... end"); } return ids; } public StepVisit[] getStepVisitsCount(int productID, int workflowID, int stepID, int userID) throws PADataAccessException { if (log.isDebugEnabled()) { log.debug("Method getStepVisitsCount(" + productID + ", " + workflowID + ", " + stepID + ", " + userID + ") ... begin"); } StepVisit[] array = null; String stepIDString = null; if (stepID != JDBCUtil.IGNORED) { stepIDString = String.valueOf(stepID); } String sql = JDBCUtil.query(SP_SEL_STEP_VISITS, new Object[] { String.valueOf(productID), String.valueOf(workflowID), stepIDString, String.valueOf(userID) }); MapSqlParameterSource parmSource = new MapSqlParameterSource(); List<StepVisit> list = namedJdbcTemplate.query(sql, parmSource, rowMappers.userVisitRowMapper); if (list != null && list.size() > 0) { array = new StepVisit[list.size()]; list.toArray(array); } if (log.isDebugEnabled()) { log.debug("Method getStepVisitsCount(" + productID + ", " + workflowID + ", " + stepID + ", " + userID + ") ... end"); } return array; } public int copyProduct(int originalProductID, String newProductName, int productTemplateID, int workflowID, int stepID, int userID, int userTypeID, int langID, Locale locale, boolean includeComments) throws PADataAccessException { if (log.isDebugEnabled()) { log.debug("Method copyProduct(" + originalProductID + ", " + newProductName + ", " + productTemplateID + ", " + workflowID + ", " + stepID + ", " + userID + ", " + userTypeID + ", " + includeComments + ") ... begin"); } String productIDString = String.valueOf(originalProductID); String productName = JDBCUtil.sqlEscapeString(newProductName); String templateIDString = null; if (productTemplateID != JDBCUtil.IGNORED) { templateIDString = String.valueOf(productTemplateID); } String workflowIDString = null; if (workflowID != JDBCUtil.IGNORED) { workflowIDString = String.valueOf(workflowID); } String stepIDString = null; if (stepID != JDBCUtil.IGNORED) { stepIDString = String.valueOf(stepID); } String userIDString = String.valueOf(userID); String userTypeString = String.valueOf(userTypeID); String includeCommentsString = (includeComments) ? "1" : "0"; //TODO: 11/16/2011 - the product naming convention should be removed from the copyProduct() //method and the name should be passed to this method instead of this current implementation. //This allows the decoupling of the name so when copying children products a retrieval of the //parent product just to get the name would not be necessary. PermissionDefinition[] permission = permissionSetDao.getPermissionDefinitions(productTemplateID, permissionSetDao.PRODUCT_NAMING_CONVENTION); if (newProductName != "" && permission != null && permission.length > 0) { // need to retrieve product to get prefix and suffix Product product = this.get(originalProductID, 1, true); newProductName = getRevisedProductName(newProductName, product, locale); productName = JDBCUtil.sqlEscapeString(newProductName); } String sql = JDBCUtil.query(SP_COPY_PRODUCT, new Object[] { productIDString, productName, templateIDString, workflowIDString, stepIDString, userIDString, userTypeString, includeCommentsString }); int newProductID = 0; MapSqlParameterSource parmSource = new MapSqlParameterSource(); List<Integer> results = namedJdbcTemplate.query(sql, parmSource, rowMappers.integerRowMapper); if (results != null && results.size() > 0) { newProductID = results.get(0); } // AK 04/27/2012 [6.1] - update/insert ProductGroups for Product try { List sqlList = new ArrayList(); String insertProductGroupsSQL = this.buildInsertProductGroupsSQL(newProductID, JDBCUtil.IGNORED, true); sqlList.add(insertProductGroupsSQL); final String summarySQL = MessageFormat.format(SP_CREATE_PRODUCT_SUMMARY, new Object[] { String.valueOf(newProductID) }); sqlList.add(summarySQL); JDBCUtil.executeUpdateDeleteInsert(sqlList, dataSource); } catch (Exception e) { log.error("Exception trying to create Product Groups for " + "Product ID: " + newProductID, e); } if (log.isDebugEnabled()) { log.debug("New copied product id = " + newProductID); log.debug("Method copyProduct(" + originalProductID + ", " + newProductName + ", " + productTemplateID + ", " + workflowID + ", " + stepID + ", " + userID + ", " + userTypeID + ", " + includeComments + ") ... end"); } return newProductID; } /** * OPA 7.0: Get all Children */ public Product[] getChildProducts(int parentID, int languageID) throws PADataAccessException { return this.getChildProducts(parentID, languageID, -1, -1); } /** * OPA 7.0: Get all Range Children */ public Product[] getRangeChildProducts(int parentID, int languageID) throws PADataAccessException { return this.getChildProducts(parentID, languageID, -1, 1); } /** * OPA 7.0: Get all components exclude range children */ public Product[] getNonRangeChildProducts(int parentID, int languageID) throws PADataAccessException { return this.getChildProducts(parentID, languageID, -1, 0); } /** * OPA 7.0: Get all Children */ public Product[] getChildProducts(int parentID, int languageID, int castAsTemplateID) throws PADataAccessException { return this.getChildProducts(parentID, languageID, castAsTemplateID, -1); } /** * OPA 7.0: Get all Range Children */ public Product[] getRangeChildProducts(int parentID, int languageID, int castAsTemplateID) throws PADataAccessException { return this.getChildProducts(parentID, languageID, castAsTemplateID, 1); } /** * OPA 7.0: Get all components exclude range children */ public Product[] getNonRangeChildProducts(int parentID, int languageID, int castAsTemplateID) throws PADataAccessException { return this.getChildProducts(parentID, languageID, castAsTemplateID, 0); } private Product[] getChildProducts(int parentID, int languageID, int castAsTemplateID, int onlyRangeChildrens) throws PADataAccessException { if (log.isDebugEnabled()) { log.debug("Method getChildProducts(" + parentID + ", " + languageID + ", " + castAsTemplateID + ", " + onlyRangeChildrens + ") ... begin"); } int childTemplateID = 0; Product[] array = null; String onlyRangeChildrensString = null; if (onlyRangeChildrens != JDBCUtil.IGNORED) { onlyRangeChildrensString = String.valueOf(onlyRangeChildrens); } // Step 1: Get all child headers /* ProductHeaderProcessor headerProcessor = new ProductHeaderProcessor(); JDBCUtil.query(SP_GET_CHILD_PRODUCTS, new Object[] {String.valueOf(parentID), onlyRangeChildrensString}, headerProcessor); List list = headerProcessor.getList(); */ String sql = JDBCUtil.query(SP_GET_CHILD_PRODUCTS, new Object[] { String.valueOf(parentID), onlyRangeChildrensString }); MapSqlParameterSource parmSource = new MapSqlParameterSource(); List<ProductHeader> list = namedJdbcTemplate.query(sql, parmSource, rowMappers.productHeaderRowMapper); ProductHeader[] headers = null; if (list != null && list.size() > 0) { headers = new ProductHeader[list.size()]; list.toArray(headers); } if (headers != null && headers.length > 0) { LinkedHashMap map = new LinkedHashMap(); for (int i = 0; i < headers.length; i++) { //Step 2: Load Attribute Set for each child template id (updated for FHB 2011 - not all child products have the same template) childTemplateID = headers[i].getProductTemplateID(); if (castAsTemplateID != JDBCUtil.IGNORED) { childTemplateID = castAsTemplateID; } int childProductStatusID = headers[i].getProductStatusID(); boolean populateAvailableOptions = false; /* String sqlWithParameters = (populateAvailableOptions) ? SP_FIRSTSTEP_WITH_OPTION : SP_FIRSTSTEP_WITHOUT_OPTION; SetResultSetProcessor processor = new SetResultSetProcessor(populateAvailableOptions); JDBCUtil.query(sqlWithParameters, new Object[]{ String.valueOf(childTemplateID), String.valueOf(childProductStatusID), String.valueOf(languageID)}, processor); AttributeDefinitionSet set = processor.getPAAttributeDefinitionSet(); */ String sqlWithParameters = (populateAvailableOptions) ? SP_FIRSTSTEP_WITH_OPTION : SP_FIRSTSTEP_WITHOUT_OPTION; String sqlChild = JDBCUtil.query(sqlWithParameters, new Object[] { String.valueOf(childTemplateID), String.valueOf(childProductStatusID), String.valueOf(languageID) }); MapSqlParameterSource parmSourceChild = new MapSqlParameterSource(); List<AttributeDefinitionSet> listChildren = namedJdbcTemplate.query(sqlChild, parmSourceChild, rowMappers.processResultSetRowMapper); AttributeDefinitionSet set = null; if (listChildren != null && listChildren.size() > 0) { set = listChildren.get(0); } //Step 3: Build products with headers, no attribute value yet ProductHeader header = headers[i]; Product product = new Product(); product.setID(header.getProductID()); product.setAssociateUserID(header.getAssociateID()); product.setTechnicalLeadUserID(header.getTechnicalLeadID()); product.setProductStatusID(header.getProductStatusID()); product.setCreatorID(header.getCreatorID()); product.setLicenseeCompanyID(header.getLicenseeCompanyID()); product.setLicenseeUserID(header.getLicenseeID()); product.setProductTemplateID(header.getProductTemplateID()); product.setLastUpdateDate(header.getUpdateDate()); product.setActive(header.isActive()); product.setParentID(header.getParentID()); product.setAttributeDefinitionSet(set); AttributeDefinition[] definitions = set.getAllAttributeDefinitions(); if (definitions != null && definitions.length > 0) { for (int j = 0; j < definitions.length; j++) { product.initAttribute(definitions[j]); } } map.put(new Integer(product.getID()), product); } // Step 4: Populate attribute values for each product // TODO setup all the child attributes String processedTemplateIds = ""; for (int i = 0; i < headers.length; i++) { String templateId = String.valueOf(headers[i].getProductTemplateID()); if (processedTemplateIds.indexOf(templateId) < 0) { /* ChildrenProductAttributeProcessor attributeValuesProcessor = new ChildrenProductAttributeProcessor(map); JDBCUtil.query(SP_GET_CHILD_PRODUCTS_ATTRIBUTES, new Object[]{ String.valueOf(languageID), String.valueOf(parentID), String.valueOf(headers[i].getProductTemplateID())}, attributeValuesProcessor); */ sql = JDBCUtil.query(SP_GET_CHILD_PRODUCTS_ATTRIBUTES, new Object[] { String.valueOf(languageID), String.valueOf(parentID), String.valueOf(headers[i].getProductTemplateID()) }); parmSource = new MapSqlParameterSource(); List<Attribute> AttributeList = namedJdbcTemplate.query(sql, parmSource, rowMappers.attributeResultSetExtractor); Product currentProduct = (Product) map.get(Integer.valueOf(headers[i].getProductID())); for (Attribute a : AttributeList) { for (Attribute productAttribute : currentProduct.getAllAttributes()) { if (a.getAttributeId() == productAttribute.getAttributeId()) { if (null != a.getValues()) { productAttribute.set(a.getValues()); } else { productAttribute.add(a.getValue()); } productAttribute.setDirty(false); } } } processedTemplateIds += templateId + ";"; } } array = new Product[list.size()]; map.values().toArray(array); // Set dirty flag to false for each attribute // so future update will check if an attribute is dirty to determine if an update is necessary. for (int i = 0; i < array.length; i++) { if (array[i] != null) { Attribute[] attributes = array[i].getAllAttributes(); if (attributes != null && attributes.length > 0) { for (int j = 0; j < attributes.length; j++) { Attribute a = (Attribute) attributes[j]; a.setDirty(false); } } } } } if (log.isDebugEnabled()) { log.debug("Method getChildProducts(" + parentID + ", " + languageID + ", " + castAsTemplateID + ", " + onlyRangeChildrens + ") ... end"); } return array; } /** * OPA 7.0: Get all children */ public ProductSummary[] getChildProductSummaries(int parentID, int languageID) throws PADataAccessException { return this.getChildProductSummaries(parentID, languageID, -1); } /** * OPA 7.0: Get all range children */ public ProductSummary[] getRangeChildProductSummaries(int parentID, int languageID) throws PADataAccessException { return this.getChildProductSummaries(parentID, languageID, 1); } /** * OPA 7.0: Get all components exclude range children */ public ProductSummary[] getNonRangeChildProductSummaries(int parentID, int languageID) throws PADataAccessException { return this.getChildProductSummaries(parentID, languageID, 0); } private ProductSummary[] getChildProductSummaries(int parentID, int languageID, int onlyRangeChildrens) throws PADataAccessException { if (log.isDebugEnabled()) { log.debug("Method getChildProductSummaries(" + parentID + ", " + languageID + ", " + onlyRangeChildrens + ") ... begin"); } String onlyRangeChildrensString = null; if (onlyRangeChildrens != JDBCUtil.IGNORED) { onlyRangeChildrensString = String.valueOf(onlyRangeChildrens); } ProductSummary[] array = null; String sqlChild = JDBCUtil.query(SP_GET_CHILD_SUMMARIES, new Object[] { String.valueOf(languageID), String.valueOf(parentID), onlyRangeChildrensString }); MapSqlParameterSource parmSourceChild = new MapSqlParameterSource(); List<ProductSummary> listChildren = namedJdbcTemplate.query(sqlChild, parmSourceChild, rowMappers.productSummaryRowMapper); if (listChildren != null && listChildren.size() > 0) { array = new ProductSummary[listChildren.size()]; listChildren.toArray(array); } if (log.isDebugEnabled()) { log.debug("Method getChildProductSummaries(" + parentID + ", " + languageID + ", " + onlyRangeChildrens + ") ... end"); } return array; } public ChildCount getChildCount(int parentID) throws PADataAccessException { if (log.isDebugEnabled()) { log.debug("Method getChildCount(" + parentID + ") ... begin"); } String sqlChild = JDBCUtil.query(SP_GET_CHILD_COUNT, new Object[] { String.valueOf(parentID) }); MapSqlParameterSource parmSourceChild = new MapSqlParameterSource(); List<ChildCount> listChildren = namedJdbcTemplate.query(sqlChild, parmSourceChild, rowMappers.childCountRowMapper); ChildCount childCount = new ChildCount(); if (listChildren != null && listChildren.size() > 0) { childCount = listChildren.get(0); } if (log.isDebugEnabled()) { log.debug("Method getChildCount(" + parentID + ") ... end"); } return childCount; } public ChildImageAssignment[] getChildImageAssignments(int parentID) throws PADataAccessException { if (log.isDebugEnabled()) { log.debug("Method getChildImageAssignments(" + parentID + ") ... begin"); } ChildImageAssignment[] array = null; String sql = JDBCUtil.query(SP_GET_RANGE_IMAGE_ASSIGNMENTS, new Object[] { String.valueOf(parentID) }); MapSqlParameterSource parmSourceChild = new MapSqlParameterSource(); List<ChildImageAssignment> list = namedJdbcTemplate.query(sql, parmSourceChild, rowMappers.childImageAssignmentRowMapper); if (list != null && list.size() > 0) { array = new ChildImageAssignment[list.size()]; list.toArray(array); } if (log.isDebugEnabled()) { log.debug("Method getChildImageAssignments(" + parentID + ") ... end"); } return array; } public List createRangeChildren(int parentID, int childTemplateID, int userID, int numberOfChildren) throws PADataAccessException { if (log.isDebugEnabled()) { log.debug("Method createRangeChildren(" + parentID + ", " + childTemplateID + ", " + userID + ", " + numberOfChildren + ") ... begin"); } String sql = MessageFormat.format(SP_CREATE_RANGE_CHILDREN, new Object[] { String.valueOf(parentID), String.valueOf(childTemplateID), String.valueOf(userID), String.valueOf(numberOfChildren) }); // Anthony Kaufman 2011-06-29: Previous Version //createChildren(sql); // OLDER CODE (BEFORE 2011-06-29) //JDBCUtil.executeUpdateDeleteInsert(sql); // Anthony Kaufman 2011-06-29: Modified to return the list of created child product ids JDBCUtil.query(sql); MapSqlParameterSource parmSourceChild = new MapSqlParameterSource(); List<String> list = namedJdbcTemplate.query(sql, parmSourceChild, rowMappers.stringRowMapper); String[] names = null; if (list != null && list.size() > 0) { names = new String[list.size()]; list.toArray(names); } //AK 04/27/2012 [6.1] - update/insert ProductGroups for Range Children try { //OPA 7.0: Get only Range children Product[] children = getRangeChildProducts(parentID, 1); if (children != null) { List sqlList = new ArrayList(); for (Product product : children) { int productID = product.getID(); String insertProductGroupsSQL = this.buildInsertProductGroupsSQL(productID, JDBCUtil.IGNORED, true); sqlList.add(insertProductGroupsSQL); // AK 04/27/2012 [6.1] - update Product Summary for Assortment Children final String summarySQL = MessageFormat.format(SP_CREATE_PRODUCT_SUMMARY, new Object[] { String.valueOf(productID) }); sqlList.add(summarySQL); } if (sqlList != null && !sqlList.isEmpty()) { JDBCUtil.executeUpdateDeleteInsert(sqlList, dataSource); } } } catch (Exception e) { log.error("Exception creating Product Groups for Range children", e); } if (log.isDebugEnabled()) { log.debug("Method createRangeChildren(" + parentID + ", " + childTemplateID + ", " + userID + ", " + numberOfChildren + ") ... end"); } return list; } public void createChildren(String sql) throws PADataAccessException { if (log.isDebugEnabled()) { log.debug("Method createChildren(" + sql + ") ... begin"); } //String sql = MessageFormat.format(SP_CREATE_ASSORTMENT_CHILDREN, new Object[]{String.valueOf(parentID), String.valueOf(childTemplateID), String.valueOf(userID), String.valueOf(numberOfChildren)}); JDBCUtil.executeUpdateDeleteInsert(sql, dataSource); if (log.isDebugEnabled()) { log.debug("Method createChildren(" + sql + ") ... begin"); } } public void createLineListChildren(int parentID, int childTemplateID, int userID, int userTypeID, int numberOfChildren) throws PADataAccessException { if (log.isDebugEnabled()) { log.debug("Method createLineListChildren(" + parentID + ", " + childTemplateID + ", " + userID + ", " + userTypeID + ", " + numberOfChildren + ") ... begin"); } String sql = MessageFormat.format(SP_CREATE_LINE_LIST_CHILDREN, new Object[] { String.valueOf(parentID), String.valueOf(childTemplateID), String.valueOf(userID), String.valueOf(userTypeID), String.valueOf(numberOfChildren) }); createChildren(sql); // update/insert ProductGroups for Line List Children try { Product[] children = getChildProducts(parentID, 1); if (children != null) { List sqlList = new ArrayList(); for (Product product : children) { int productID = product.getID(); String insertProductGroupsSQL = this.buildInsertProductGroupsSQL(productID, JDBCUtil.IGNORED, true); sqlList.add(insertProductGroupsSQL); // update Product Summary for Line List Children final String summarySQL = MessageFormat.format(SP_CREATE_PRODUCT_SUMMARY, new Object[] { String.valueOf(productID) }); sqlList.add(summarySQL); } if (sqlList != null && !sqlList.isEmpty()) { JDBCUtil.executeUpdateDeleteInsert(sqlList, dataSource); } } } catch (Exception e) { log.error("Exception creating Product Groups for " + "Line List children", e); } if (log.isDebugEnabled()) { log.debug("Method createLineListChildren(" + parentID + ", " + childTemplateID + ", " + userID + ", " + userTypeID + ", " + numberOfChildren + ") ... begin"); } } public void createAssortmentChildren(int parentID, int childTemplateID, int userID, int userTypeID, int numberOfChildren) throws PADataAccessException { if (log.isDebugEnabled()) { log.debug("Method createAssortmentChildren(" + parentID + ", " + childTemplateID + ", " + userID + ", " + userTypeID + ", " + numberOfChildren + ") ... begin"); } String sql = MessageFormat.format(SP_CREATE_ASSORTMENT_CHILDREN, new Object[] { String.valueOf(parentID), String.valueOf(childTemplateID), String.valueOf(userID), String.valueOf(userTypeID), String.valueOf(numberOfChildren) }); createChildren(sql); // AK 04/27/2012 [6.1] - update/insert ProductGroups for Assortment Children try { Product[] children = getChildProducts(parentID, 1); if (children != null) { List sqlList = new ArrayList(); for (Product product : children) { int productID = product.getID(); String insertProductGroupsSQL = this.buildInsertProductGroupsSQL(productID, JDBCUtil.IGNORED, true); sqlList.add(insertProductGroupsSQL); // AK 04/27/2012 [6.1] - update Product Summary for Assortment Children final String summarySQL = MessageFormat.format(SP_CREATE_PRODUCT_SUMMARY, new Object[] { String.valueOf(productID) }); sqlList.add(summarySQL); } if (sqlList != null && !sqlList.isEmpty()) { JDBCUtil.executeUpdateDeleteInsert(sqlList, dataSource); } } } catch (Exception e) { log.error("Exception creating Product Groups for " + "Assortment children", e); } if (log.isDebugEnabled()) { log.debug("Method createAssortmentChildren(" + parentID + ", " + childTemplateID + ", " + userID + ", " + userTypeID + ", " + numberOfChildren + ") ... begin"); } } private void saveReviewers(final int productID, final int[] reviewerIDs) throws PADataAccessException { if (log.isDebugEnabled()) { log.debug("Method saveReviewers(" + productID + ", " + reviewerIDs + ") ... begin"); } final List sqlList = new ArrayList(); if (reviewerIDs != null && reviewerIDs.length > 0) { for (int i = 0; i < reviewerIDs.length; i++) { final String deleteSQL = MessageFormat.format(SP_DEL_SELECTED_REVIEWERS, new Object[] { String.valueOf(productID), String.valueOf(reviewerIDs[i]) }); sqlList.add(deleteSQL); final String insertSQL = MessageFormat.format(SP_INS_SELECTED_REVIEWERS, new Object[] { String.valueOf(productID), String.valueOf(reviewerIDs[i]) }); sqlList.add(insertSQL); } } JDBCUtil.executeUpdateDeleteInsert(sqlList, dataSource); if (log.isDebugEnabled()) { log.debug("Method saveReviewers(" + productID + ", " + reviewerIDs + ") ... end"); } } private void processReviewers(final int productID, final int productStateID, final int[] reviewerIDs, final int actionType, Date[] requestByDates) throws PADataAccessException { if (log.isDebugEnabled()) { log.debug("Method processReviewers(" + productID + ", " + productStateID + ", " + reviewerIDs + ") ... begin"); } // First save these reviewers this.saveReviewers(productID, reviewerIDs); final List list = new ArrayList(); // get Current Reviewers final ReviewResult[] results = this.getReviewResults(productStateID); final HashMap assignedReviewerIDs = new HashMap(); final HashMap newReviewerIDs = new HashMap(); for (int i = 0; i < reviewerIDs.length; i++) { newReviewerIDs.put(new Integer(reviewerIDs[i]), null); } if (results != null) { Integer key1; Integer key2; for (int i = 0; i < results.length; i++) { //generate a list of userids that is an assigned or active reviewer final int oldReviewerID = results[i].getAssignedReviewer().getUserID(); final int oldActiveReviewerID = results[i].getReviewPerformer().getUserID(); key1 = new Integer(oldReviewerID); key2 = new Integer(oldActiveReviewerID); assignedReviewerIDs.put(key1, null); assignedReviewerIDs.put(key2, null); // remove the previous reviewer if he is not in the new reviewers list and product has not been submitted to him yet. // if(! newReviewerIDs.containsKey(key) && results[i].canBeReviewed() == false) if (!newReviewerIDs.containsKey(key1) && results[i].hasBeenReviewed() == false) { //String removeReviewerSQL = MessageFormat.format(SP_DEL_UNASSIGNED_REVIEWER, new Object[]{String.valueOf(productStateID), String.valueOf(oldReviewerID)}); ///list.add(removeReviewerSQL); } } } for (int i = 0; i < reviewerIDs.length; i++) { final Integer key = new Integer(reviewerIDs[i]); //if userid is already an assigned or active reviewer, do not assign if (assignedReviewerIDs.containsKey(key)) { if (log.isDebugEnabled()) { log.debug("User " + reviewerIDs[i] + " is already set as a reviewer. No need to add it again."); } } else { if (log.isDebugEnabled()) { log.debug("Action type : " + actionType + " " + (actionType == 0 ? " Save Reviewers - " : " Assign Reviewers - ") + "(" + productID + ", " + productStateID + ", " + reviewerIDs + ")"); } // new reviewer String sql = null; if (actionType == 0) { sql = MessageFormat.format(SP_SAVE_REVIEWER, new Object[] { String.valueOf(productStateID), String.valueOf(reviewerIDs[i]) }); } else { if (actionType == 1) { sql = MessageFormat.format(SP_ASSIGN_REVIEWER, new Object[] { String.valueOf(productStateID), String.valueOf(reviewerIDs[i]), JDBCUtil.sqlEscapeDate(requestByDates[0]) }); } else { sql = MessageFormat.format(SP_ASSIGN_REVIEWER, new Object[] { String.valueOf(productStateID), String.valueOf(reviewerIDs[i]), JDBCUtil.sqlEscapeDate(requestByDates[i]) }); } } list.add(sql); } } //No need to reset ProductGroups for products final String summarySQL = MessageFormat.format(SP_CREATE_PRODUCT_SUMMARY, new Object[] { String.valueOf(productID) }); list.add(summarySQL); JDBCUtil.executeUpdateDeleteInsert(list, dataSource); if (log.isDebugEnabled()) { log.debug("Method processReviewers(" + productID + ", " + productStateID + ", " + reviewerIDs + ") ... end"); } } public void saveSelectedReviewers(final int productID, final int productStateID, final int[] reviewerIDs) throws PADataAccessException { processReviewers(productID, productStateID, reviewerIDs, 0, null); //save operation don't have request by date (null) } public void submitToReviewers(final int productID, final int productStateID, final int[] reviewerIDs, Date[] requestByDates) throws PADataAccessException { processReviewers(productID, productStateID, reviewerIDs, requestByDates.length, requestByDates); // q3/q4 requirement. 7/29/08 } public void resubmitToReviewer(int productID, int productStateID, int assignedReviewerID, Date requestByDate) throws PADataAccessException { if (log.isDebugEnabled()) { log.debug("Method resubmitToReviewer(" + productID + ", " + productStateID + ", " + assignedReviewerID + ") ... begin"); } List sqlList = new ArrayList(); String sql = MessageFormat.format(SP_RESUBMIT_TO_REVIEWER, new Object[] { String.valueOf(productStateID), String.valueOf(assignedReviewerID), JDBCUtil.sqlEscapeDate(requestByDate) }); sqlList.add(sql); //No need to reset ProductGroups for product String summarySQL = MessageFormat.format(SP_CREATE_PRODUCT_SUMMARY, new Object[] { String.valueOf(productID) }); sqlList.add(summarySQL); JDBCUtil.executeUpdateDeleteInsert(sqlList, dataSource); if (log.isDebugEnabled()) { log.debug("Method resubmitToReviewer(" + productID + ", " + productStateID + ", " + assignedReviewerID + ") ... end"); } } public void publishRangeItems(int parentProductID) throws PADataAccessException { if (log.isDebugEnabled()) { log.debug("Method publishRangeItems(" + parentProductID + ") ... begin"); } String sql = MessageFormat.format(SP_PUBLISH_RANGE_ITEMS, new Object[] { String.valueOf(parentProductID) }); JDBCUtil.executeUpdateDeleteInsert(sql, dataSource); if (log.isDebugEnabled()) { log.debug("Method publishRangeItems(" + parentProductID + ") ... end"); } } public String[] getCollectionNames(int licenseeCompanyID) throws PADataAccessException { if (log.isDebugEnabled()) { log.debug("Method getCollectionNames(" + licenseeCompanyID + ") ... begin"); } String companyIDString = null; if (licenseeCompanyID != JDBCUtil.IGNORED) { companyIDString = String.valueOf(licenseeCompanyID); } String sql = JDBCUtil.query(SP_SEL_COLLECTION, new Object[] { companyIDString }); MapSqlParameterSource parmSourceChild = new MapSqlParameterSource(); List<String> list = namedJdbcTemplate.query(sql, parmSourceChild, rowMappers.stringRowMapper); String[] names = null; if (list != null && list.size() > 0) { names = new String[list.size()]; list.toArray(names); } if (log.isDebugEnabled()) { log.debug("Method getCollectionNames(" + licenseeCompanyID + ") ... end"); } return names; } public Map<Integer, String> getStyleGuides(int templateID, Date fromPublishDate, Date toPublishDate) throws PADataAccessException { if (log.isDebugEnabled()) log.debug("Method getStyleGuides(" + templateID + ", " + fromPublishDate + ", " + toPublishDate + ") ... begin"); String templateIDString = null; if (templateID != JDBCUtil.IGNORED) { templateIDString = String.valueOf(templateID); } /*StyleGuideProcessor processor = new StyleGuideProcessor(); JDBCUtil.query(SP_SEL_STYLE_GUIDE, new Object[]{ templateIDString, JDBCUtil.sqlEscapeDate(fromPublishDate), JDBCUtil.sqlEscapeDate(toPublishDate)}, processor); return processor.getMap(); */ String sql = JDBCUtil.query(SP_SEL_STYLE_GUIDE, new Object[] { templateIDString, JDBCUtil.sqlEscapeDate(fromPublishDate), JDBCUtil.sqlEscapeDate(toPublishDate) }); MapSqlParameterSource parmSource = new MapSqlParameterSource(); List<StyleGuide> list = namedJdbcTemplate.query(sql, parmSource, rowMappers.styleGudeRowMapper); Map<Integer, String> returnMap = new HashMap<Integer, String>(); for (StyleGuide sg : list) { returnMap.put(sg.getId(), sg.getStyleGuideName()); } return returnMap; } public int getDefaultDisneyApprover(int licenseeCountryID, int categoryID, int stepID) throws PADataAccessException { if (log.isDebugEnabled()) { log.debug("Method getDefaultDisneyApprover(" + licenseeCountryID + ", " + categoryID + ", " + stepID + ") ... begin"); } String sql = JDBCUtil.query(SP_SEL_DEFAULT_DISNEY_APPROVER, new Object[] { String.valueOf(licenseeCountryID), String.valueOf(categoryID), String.valueOf(stepID) }); int userID = 0; MapSqlParameterSource parmSource = new MapSqlParameterSource(); List<Integer> list = namedJdbcTemplate.query(sql, parmSource, rowMappers.integerRowMapper); if (list != null && list.size() > 0) { userID = list.get(0); } if (log.isDebugEnabled()) { log.debug("Method getDefaultDisneyApprover(" + licenseeCountryID + ", " + categoryID + ", " + stepID + ") ... end"); } return userID; } public int createMassOp(int opTypeID, int fromUserID, int toUserID, int adminUserID, String comments, int sleepTime, int sleepAfter) throws PADataAccessException { if (log.isDebugEnabled()) { log.debug("Method createMassOp(" + opTypeID + ", " + fromUserID + ", " + toUserID + ", " + adminUserID + ") ... begin"); } String sql = MessageFormat.format(SP_INS_MASS_OPS, new Object[] { String.valueOf(opTypeID), String.valueOf(fromUserID), String.valueOf(toUserID), String.valueOf(adminUserID), JDBCUtil.sqlEscapeString(comments), String.valueOf(sleepTime), String.valueOf(sleepAfter) }); int opID = JDBCUtil.executeInsertWithoutIdentity(sql, null, dataSource); if (log.isDebugEnabled()) { log.debug("Method createMassOp(" + opTypeID + ", " + fromUserID + ", " + toUserID + ", " + adminUserID + ") ... end"); } return opID; } public void updateMassOp(int opID, Date endDate, int numProcessed, int numPassed) throws PADataAccessException { if (log.isDebugEnabled()) { log.debug("Method updateMassOp(" + opID + ", " + endDate + ") ... begin"); } String sql = MessageFormat.format(SP_UPD_MASS_OPS, new Object[] { String.valueOf(opID), JDBCUtil.sqlEscapeDate(endDate), String.valueOf(numProcessed), String.valueOf(numPassed) }); JDBCUtil.executeUpdateDeleteInsert(sql, dataSource); if (log.isDebugEnabled()) { log.debug("Method updateMassOp(" + opID + ", " + endDate + ") ... end"); } } public MassOp[] getMassOps(Date startDate, Date endDate, int opTypeId, int opId, int productId, int getRunningJobs) throws PADataAccessException { if (log.isDebugEnabled()) { log.debug("Method getMassOps(" + startDate + ", " + endDate + ") ... begin"); } MassOp[] array = null; String sql = JDBCUtil.query(SP_GET_MASS_OPS, new Object[] { JDBCUtil.sqlEscapeDate(startDate), JDBCUtil.sqlEscapeDate(endDate), String.valueOf(opTypeId), String.valueOf(opId), String.valueOf(productId), String.valueOf(getRunningJobs) }); MapSqlParameterSource parmSourceChild = new MapSqlParameterSource(); List<MassOp> list = namedJdbcTemplate.query(sql, parmSourceChild, rowMappers.massOpRowMapper); if (list != null && list.size() > 0) { array = new MassOp[list.size()]; list.toArray(array); } if (log.isDebugEnabled()) { log.debug("Method getMassOps(" + startDate + ", " + endDate + ") ... end"); } return array; } public void createMassOpDetail(int opID, String prodIds, String descriptions) throws PADataAccessException { if (log.isDebugEnabled()) { log.debug("Method createMassOpDetail(" + opID + ", " + prodIds + ") ... begin"); } // Need not use Unicode string because prod-ids are purely digits, and descriptions are audit history ascii strings String sql = MessageFormat.format(SP_INS_MASS_OPS_DETAIL, new Object[] { String.valueOf(opID), JDBCUtil.sqlEscapeString(prodIds, false), JDBCUtil.sqlEscapeString(descriptions, false) }); JDBCUtil.executeUpdateDeleteInsert(sql, dataSource); if (log.isDebugEnabled()) { log.debug("Method createMassOpDetail(" + opID + ", " + prodIds + ") ... end"); } } public void updateMassOpDetail(int opID, int productID, int statusId, String description) throws PADataAccessException { if (log.isDebugEnabled()) { log.debug("Method updateMassOpDetail(" + opID + ", " + productID + ", " + statusId + ") ... begin"); } String sql = MessageFormat.format(SP_UPD_MASS_OPS_DETAIL, new Object[] { String.valueOf(opID), String.valueOf(productID), String.valueOf(statusId), JDBCUtil.sqlEscapeString(description, false) }); JDBCUtil.executeUpdateDeleteInsert(sql, dataSource); if (log.isDebugEnabled()) { log.debug("Method updateMassOpDetail(" + opID + ", " + productID + ", " + statusId + ") ... end"); } } public MassOpDetail[] getMassOpDetails(int opID, int statusId) throws PADataAccessException { if (log.isDebugEnabled()) { log.debug("Method getMassOpDetails(" + opID + ") ... begin"); } MassOpDetail[] array = null; String sql = JDBCUtil.query(SP_GET_MASS_OPS_DETAIL, new Object[] { String.valueOf(opID), String.valueOf(statusId) }); MapSqlParameterSource parmSourceChild = new MapSqlParameterSource(); List<MassOpDetail> list = namedJdbcTemplate.query(sql, parmSourceChild, rowMappers.massOpDetailRowMapper); if (list != null && list.size() > 0) { array = new MassOpDetail[list.size()]; list.toArray(array); } if (log.isDebugEnabled()) { log.debug("Method getMassOpDetails(" + opID + ") ... end"); } return array; } // This is a very generic method and should be moved to a Utility class: AL 4/27/2007 public static String substringWithDelimiter(String str, String delim, int max) { String substr = null; if (str == null || str.length() < max) { substr = str; } else { substr = str.substring(0, max); int idx = substr.lastIndexOf(delim); if (idx != -1) substr = substr.substring(0, idx); } return substr; } public Boolean[] getCheckProductsInProcess(String productIdList) throws PADataAccessException { if (log.isDebugEnabled()) { log.debug("Method getCheckProductInProcess(" + productIdList + ") ... begin"); } Boolean[] array = null; List<Boolean> list = new ArrayList(); String pIdList = null; List<Boolean> tmpList = null; while (productIdList != null) { pIdList = substringWithDelimiter(productIdList, ",", ParameterSQL.MAX_SQL_ASCII_STRING_LENGTH); productIdList = (productIdList.length() > pIdList.length()) ? productIdList.substring(pIdList.length() + 1) : null; String sql = JDBCUtil.query(SP_CHECK_PRODS_IN_PROCESS, new Object[] { JDBCUtil.sqlEscapeString(pIdList, false) }); MapSqlParameterSource parmSourceChild = new MapSqlParameterSource(); tmpList = namedJdbcTemplate.query(sql, parmSourceChild, rowMappers.booleanRowMapper); if (tmpList != null && tmpList.size() > 0) list.addAll(tmpList); } if (list != null && list.size() > 0) { array = new Boolean[list.size()]; list.toArray(array); } if (log.isDebugEnabled()) { log.debug("Method getCheckProductInProcess(" + productIdList + ") ... end"); } return array; } public OpStatus reassignProductAssociate(int fromUserID, int toUserID, int productID, String comments) throws PADataAccessException { if (log.isDebugEnabled()) { log.debug("Method reassignProductAssociate(" + fromUserID + ", " + toUserID + ", " + productID + ") ... begin"); } if (null == comments || comments.length() == 0) { comments = "null"; } OpStatus opStatus = null; String sql = JDBCUtil.query(SP_REASSIGN_PRODUCT_ASSOCIATE, new Object[] { String.valueOf(fromUserID), String.valueOf(toUserID), String.valueOf(productID), JDBCUtil.sqlEscapeString(comments) }); MapSqlParameterSource parmSourceChild = new MapSqlParameterSource(); List<OpStatus> list = namedJdbcTemplate.query(sql, parmSourceChild, rowMappers.opStatusRowMapper); if (list != null && list.size() > 0) { opStatus = (OpStatus) list.get(0); // assuming that one product is being reassigned at a time } if (log.isDebugEnabled()) { log.debug("Method reassignProductAssociate(" + fromUserID + ", " + toUserID + ", " + productID + ") ... end"); } return opStatus; } public OpStatus reassignProductLicensee(int fromUserID, int toUserID, int productID, String comments) throws PADataAccessException { if (log.isDebugEnabled()) { log.debug("Method reassignProductLicensee(" + fromUserID + ", " + toUserID + ", " + productID + ") ... begin"); } OpStatus opStatus = null; String sql = JDBCUtil.query(SP_REASSIGN_PRODUCT_LICENSEE, new Object[] { String.valueOf(fromUserID), String.valueOf(toUserID), String.valueOf(productID), JDBCUtil.sqlEscapeString(comments) }); MapSqlParameterSource parmSourceChild = new MapSqlParameterSource(); List<OpStatus> list = namedJdbcTemplate.query(sql, parmSourceChild, rowMappers.opStatusRowMapper); if (list != null && list.size() > 0) { opStatus = (OpStatus) list.get(0); // assuming that one product is being reassigned at a time } if (log.isDebugEnabled()) { log.debug("Method reassignProductLicensee(" + fromUserID + ", " + toUserID + ", " + productID + ") ... end"); } return opStatus; } public void updateCollectionName(int[] productIDs, String collectionName) throws PADataAccessException { throw new RuntimeException("Not yet implemented"); } public void removeCollectionName(int[] productIDs) throws PADataAccessException { throw new RuntimeException("Not yet implemented"); } private class ProductStateCreationDateComparator implements Comparator<ProductState> { public int compare(ProductState ps1, ProductState ps2) { /** * product states and their creation dates should never be null, * but this is mostly precautionary. */ try { return ps1.getCurrentCreationDate().compareTo(ps2.getCurrentCreationDate()); } catch (Exception e) { try { if (ps1.getCurrentCreationDate() != null && ps2.getCurrentCreationDate() == null) { return 1; } else if (ps1.getCurrentCreationDate() == null && ps2.getCurrentCreationDate() != null) { return -1; } return 0; } catch (Exception e1) { return 0; } } } } public Option[] getAllStepsForLicensee(int productId, int currentStepId) { /*Option[] options = null; try { options = OptionUtil.loadOptions(SP_GET_ALL_STEPS_FOR_LICENSEE, new Object[]{String.valueOf(productId)}); */ MapSqlParameterSource parmSource = new MapSqlParameterSource(); String qString = JDBCUtil.query(SP_GET_ALL_STEPS_FOR_LICENSEE, new Object[] { String.valueOf(productId) }); List<Option> results = namedJdbcTemplate.query(qString, parmSource, rowMappers.optionRowMapper); final Option[] array = results.toArray(new Option[results.size()]); //} catch (PADataAccessException p) {} return array; } public Option[] getAllStepsForAgent(int productId, int currentStepId) { /*Option[] options = null; try { options = OptionUtil.loadOptions(SP_GET_ALL_STEPS_FOR_AGENT, new Object[]{String.valueOf(productId)}); } catch (PADataAccessException p) {} return options;*/ MapSqlParameterSource parmSource = new MapSqlParameterSource(); String qString = JDBCUtil.query(SP_GET_ALL_STEPS_FOR_AGENT, new Object[] { String.valueOf(productId) }); List<Option> results = namedJdbcTemplate.query(qString, parmSource, rowMappers.optionRowMapper); final Option[] array = results.toArray(new Option[results.size()]); return array; } public Option[] getAllStepsForAssociate(int productId) { /*Option[] options = null; try { options = OptionUtil.loadOptions(SP_GET_ALL_STEPS_FOR_ASSOCIATE, new Object[]{String.valueOf(productId)}); } catch(PADataAccessException p) { log.debug(p.getMessage()); } return options;*/ MapSqlParameterSource parmSource = new MapSqlParameterSource(); String qString = JDBCUtil.query(SP_GET_ALL_STEPS_FOR_ASSOCIATE, new Object[] { String.valueOf(productId) }); List<Option> results = namedJdbcTemplate.query(qString, parmSource, rowMappers.optionRowMapper); final Option[] array = results.toArray(new Option[results.size()]); return array; } public Date getArrivalDateAsDate(int productId, int userId) { SimpleDateFormat sdf = new SimpleDateFormat("MMM dd yyyy hh:mmaa"); try { return sdf.parse(getArrivalDate(productId, userId)); } catch (Exception e) { log.error(e); return null; } } public String getArrivalDate(int productId, int userId) { String arrivalDate = null; try { String sql = JDBCUtil.query(SP_GET_ARRIVAL_DATE, new Object[] { String.valueOf(productId), String.valueOf(userId) }); MapSqlParameterSource parmSource = new MapSqlParameterSource(); List<String> list = namedJdbcTemplate.query(sql, parmSource, rowMappers.stringRowMapper); if (list != null && list.size() > 0) { arrivalDate = list.get(0); } } catch (Exception p) { log.debug(p.getMessage()); } return arrivalDate; } /** * Removes the product view status. * @author Tseng, Denys - 2008/02/10 *//* public void removeProductViewStatus(int productId, int stateId, int userId) { try { String sql = MessageFormat.format(SP_DEL_USER_PRODUCT_VIEW_STATUS, new Object[] { String.valueOf(productId), String.valueOf(stateId), String.valueOf(userId)}); JDBCUtil.executeUpdateDeleteInsert(sql); } catch (PADataAccessException p) { log.debug("Unable to delete the specified UserProductViewStatus record", p); } } (non-Javadoc) * @see com.disney.dcp.lic.pa.product.ProductManager#delegateAll(int, int, int) //public void delegateAll(int productId, int userId) throws PADataAccessException { // if(log.isDebugEnabled()) { // log.debug("Method delegateAll(" + productId + ", " + userId + ") ... begin"); // } // String sql = MessageFormat.format(SP_DELEGATE_ALL, // new Object[] { String.valueOf(productId), // String.valueOf(userId)}); // JDBCUtil.executeUpdateDeleteInsert(sql); // if(log.isDebugEnabled()) { // log.debug("Method delegateAll(" + productId + ", " + userId + ") ... begin"); // } //} /** * Appends prefix and suffix from a source product to the new product name. * This is used when copying products. * <p> * Take a look at CopyProductAction#copyProduct. * <p> * Format Example: PAR-Product XYZ-Cars; Mickey Mouse; Toy Story * * @param newProductName The name of the new product * @param sourceProduct The source product to get the prefix and suffix * from. * @return returns new product name with prefix and suffix. * @see #getRevisedProductName(Product) */ public String getRevisedProductName(String newProductName, Product sourceProduct, Locale locale) { String prefix = ""; String suffix = ""; final int TAXONOMY_OPTION_SOURCE_ID = 2; if (locale == null) { locale = Locale.US; } //get prefix (TODO: This needs to be moved to some form of utility * class). Attribute subcategory = sourceProduct.getAttribute(AttributeConstants.ATT_SUBCATEGORY); if (subcategory != null) { List<String> acronymList = new ArrayList<String>(); Option[] opts = subcategory.getOptionValues(); for (Option opt : opts) { String textKey = getProductNamePrefixKey(opt.getID(), TAXONOMY_OPTION_SOURCE_ID, sourceProduct.getProductTemplateID()); String value = messageUtil.getTextTranslation(locale, textKey); acronymList.add(value); } prefix = StringUtils.join(acronymList, Product.MULTI_PREFIX_NAME_DELIMITER); } //get suffix (TODO: This needs to be moved to some form of utility * class). Attribute property = sourceProduct.getAttribute(AttributeConstants.ATT_PROPERTY); if (property != null) { Option[] opts = property.getOptionValues(); if (opts != null) { for (Option opt : opts) { String value = messageUtil.getTextTranslation(locale, opt.getDisplayKey()); if (value != null && !value.equals("")) { suffix += value + "; "; } } } } log.debug("productName = " + newProductName); log.debug("prefix = " + prefix); log.debug("suffix = " + suffix); //TODO: 11/11/11 - the longer properties are truncated so this condition //needs to account for the abbreviated property name with ellipses if (suffix.length() >= TRUNCATE_LENGTH) { suffix = suffix.substring(0, TRUNCATE_LENGTH); } if (newProductName.indexOf(suffix) >= 0) { if (newProductName.indexOf(prefix) == -1) { //prefix does not exist //add prefix only to original product name newProductName = (prefix + Product.PRODUCT_NAME_DELIMITER + newProductName); } } else { if (newProductName.indexOf(prefix) > -1) { // prefix exist //add suffix only to original product name newProductName = (Product.PRODUCT_NAME_DELIMITER + newProductName + Product.PRODUCT_NAME_DELIMITER + suffix); } else { // both prefix and suffix does not exist //add prefix and suffix to original product name newProductName = (prefix + Product.PRODUCT_NAME_DELIMITER + newProductName + Product.PRODUCT_NAME_DELIMITER + suffix); } } //truncate name to 1st 250 characters if greater than 255 characters //required b/c Name column max length is 255 characters if (newProductName.length() > Product.PRODUCT_NAME_CHARACTER_MAX) { newProductName = newProductName.substring(0, Product.PRODUCT_NAME_CHARACTER_MAX); } log.debug("Updated Product length [" + newProductName.length() + "] Name = " + newProductName); return newProductName; } /** * Retrieves product name prefix and suffix and appends to original * product name. * EX: PAR-Product XYZ-Cars; Mickey Mouse; Toy Story * * @param product * @return returns product name with prefix and suffix */ public String getRevisedProductName(Product product) { String productName = product.getName(); String prefix = ""; String suffix = ""; boolean isPrefixRequired = false; // Rex null checking this, should not happen // 8.0 Can't have null product names. if (null == product.getName()) { return ""; } //if transient name suffix is blank, prefix is not needed (only populated via SaveProduct) //prevents prefix duplicates during "Send to Disney" process if (product.getNameSuffix() != null && product.getNameSuffix() != "") { isPrefixRequired = true; } else { isPrefixRequired = false; } if (isPrefixRequired) { prefix = getProductNamePrefix(product); } suffix = getProductNameSufffix(product); log.debug("productName = " + productName); log.debug("prefix = " + prefix); log.debug("suffix = " + suffix); if (productName.endsWith(suffix)) { //add prefix only to original product name productName = prefix + productName; //productName = productName.substring(0, Product.PRODUCT_NAME_DELIMITER); } else { //add prefix and suffix to original product name productName = prefix + productName + suffix; } //truncate name to 1st 250 characters if greater than 255 characters //required b/c Name column max length is 255 characters if (productName.length() > Product.PRODUCT_NAME_CHARACTER_MAX) { productName = productName.substring(0, Product.PRODUCT_NAME_CHARACTER_MAX); } log.debug("Updated Product length [" + productName.length() + "] Name = " + productName); return productName; } /** * Retrieves textkey from the SubcategoryAcronyms table for subcategory. * * @param int optionId * @param int optionSourceId * @param int templateId * @return textkey of the subcategory */ public String getProductNamePrefixKey(int optionId, int optionSourceId, int templateId) { String textkey = ""; try { Object[] params = new Object[] { String.valueOf(optionId), String.valueOf(optionSourceId), String.valueOf(templateId) }; //use sproc to retrieve acronym String sql = JDBCUtil.query(SP_SEL_SUBCATEGORY_ACRONYM, params); MapSqlParameterSource parmSource = new MapSqlParameterSource(); List<String> list = namedJdbcTemplate.query(sql, parmSource, rowMappers.stringRowMapper); if (list != null && list.size() > 0) { textkey = list.get(0); } } catch (Exception e) { //if exception is caught, return empty string log.error("Exception encountered while retrieving Subcategory acronym for optionId [" + optionId + "] optionSourceId [" + optionSourceId + "]", e); } return textkey; } public String getProductNamePrefix(Product product) { String prefix = ""; if (product.getNamePrefix() != null && product.getNamePrefix() != "") { prefix = product.getNamePrefix() + Product.PRODUCT_NAME_DELIMITER; } return prefix; } /** * Retrieves property names for a given product. * EX: -Cars; Mickey Mouse; Toy Story * * @param product * @return property names as suffixes */ public String getProductNameSufffix(Product product) { String suffix = ""; if (product.getNameSuffix() != null && product.getNameSuffix() != "") { suffix = Product.PRODUCT_NAME_DELIMITER + product.getNameSuffix(); } return suffix; } public boolean checkDuplicateUpcEanExistsForLicensee(int licenseeId, String upc, int type, int productId) throws PADataAccessException { int result = 0; log.debug("licenseeId = " + licenseeId + " upcEan = " + upc + " type = " + type + " productId = " + productId); try { String sql = JDBCUtil.query(SP_CHECK_UPCEAN_FOR_LICENSEE_EXISTS, new Object[] { String.valueOf(licenseeId), JDBCUtil.sqlEscapeString(upc), String.valueOf(type), String.valueOf(productId) }); MapSqlParameterSource parmSource = new MapSqlParameterSource(); List<Integer> list = namedJdbcTemplate.query(sql, parmSource, rowMappers.integerRowMapper); if (list != null && list.size() > 0) { result = list.get(0); } } catch (Exception p) { log.debug(p.getMessage()); } log.debug("checkDuplicateUpcEanExistsForLicensee has obtained a value of : " + result); return result > 0 ? true : false; } public boolean checkAttributeExceptionExists(int attributeId, String value) throws PADataAccessException { int result = 0; log.debug("attributeId = " + attributeId + " value = " + value); try { String sql = JDBCUtil.query(SP_CHECK_ATTRIBUTE_EXCEPTION_EXISTS, new Object[] { String.valueOf(attributeId), JDBCUtil.sqlEscapeString(value) }); MapSqlParameterSource parmSource = new MapSqlParameterSource(); List<Integer> list = namedJdbcTemplate.query(sql, parmSource, rowMappers.integerRowMapper); if (list != null && list.size() > 0) { result = list.get(0); } } catch (Exception p) { log.debug(p.getMessage()); } return result > 0 ? true : false; } /** * Retrieves the assortment parent template id based on * a given template id. * * @param childTemplateId * @return Assortment Parent Template ID. */ public int getAssortmentParentTemplate(int childTemplateId) { int parentTemplateId = 0; try { Object[] params = new Object[] { String.valueOf(childTemplateId) }; String sql = JDBCUtil.query(SP_GET_ASSORTMENT_PARENT_TEMPLATE, params); log.debug("parentTemplateId for childTemplateId " + parentTemplateId + " = " + childTemplateId); MapSqlParameterSource parmSource = new MapSqlParameterSource(); List<Integer> list = namedJdbcTemplate.query(sql, parmSource, rowMappers.integerRowMapper); if (list != null && list.size() > 0) { parentTemplateId = list.get(0); } } catch (Exception pex) { log.error("Exception encountered while retrieving parentTemplateId for " + "childTemplateId: " + childTemplateId, pex); } return parentTemplateId; } public int convertProductToAssortment(int productId, int childTemplateId, int userID) throws PADataAccessException { int newProductId = 0; if (log.isDebugEnabled()) { log.debug("Method ConvertProductToAssortment(" + productId + ", " + childTemplateId + ", " + userID + ") ... begin"); } Object[] params = new Object[] { String.valueOf(productId), String.valueOf(childTemplateId), String.valueOf(userID) }; String sql = JDBCUtil.query(SP_CONVERT_PRODUCT_TO_ASSORTMENT, params); MapSqlParameterSource parmSource = new MapSqlParameterSource(); List<Integer> list = namedJdbcTemplate.query(sql, parmSource, rowMappers.integerRowMapper); if (list != null && list.size() > 0) { newProductId = list.get(0); } // AK 04/27/2012 [6.1] - update/insert ProductGroups for Product try { List sqlList = new ArrayList(); String insertProductGroupsSQL = this.buildInsertProductGroupsSQL(newProductId, JDBCUtil.IGNORED, true); sqlList.add(insertProductGroupsSQL); final String summarySQL = MessageFormat.format(SP_CREATE_PRODUCT_SUMMARY, new Object[] { String.valueOf(newProductId) }); sqlList.add(summarySQL); JDBCUtil.executeUpdateDeleteInsert(sqlList, dataSource); } catch (Exception e) { log.error("Exception trying to create Product Groups for " + "Product ID: " + newProductId, e); } if (log.isDebugEnabled()) { log.debug("Method ConvertProductToAssortment(" + productId + ", " + childTemplateId + ", " + userID + ") ... begin"); } return newProductId; } /* public Map getAgedProducts() throws PADataAccessException { if(log.isDebugEnabled()) { log.debug("Method getAgedProducts() ... begin"); } Hashtable processorSet = new Hashtable(2); List ageAlerts = null; AgeAlertResultSetProcessor ageAlertResultSetProcessor = new AgeAlertResultSetProcessor(); processorSet.put(new Integer(1), ageAlertResultSetProcessor); EmailContent processorEmail = new EmailContent(); processorSet.put(new Integer(2), processorEmail); String sql = SP_GET_AGED_PRODUCTS; JDBCUtil.query(sql, processorSet); if(log.isDebugEnabled()) { log.debug("Method getAgedProducts() ... end"); } ageAlerts = ageAlertResultSetProcessor.getPAAgeAlerts(); Map map = new TreeMap(); if (ageAlerts!=null) { map.put("ageProducts", ageAlerts); } if (processorEmail!=null) { map.put("emailTranslations", processorEmail.getTable()); } return map; } */ public void uncancelProduct(int productID, int userID) throws PADataAccessException { if (log.isDebugEnabled()) { log.debug("Method uncancelProduct(" + productID + "," + userID + ") ... begin"); log.debug("... calling uncancelProduct(productID,null,userID)..."); } uncancelProduct(productID, null, userID); if (log.isDebugEnabled()) { log.debug("Method uncancelProduct(" + productID + "," + userID + ") ... end"); } } public void uncancelProduct(int productID, String userComments, int userID) throws PADataAccessException { if (log.isDebugEnabled()) { log.debug("Method uncancelProduct(" + productID + ",<userComments>," + userID + ") ... begin"); } List sqlList = new ArrayList(); String sql = MessageFormat.format(SP_UNCANCEL_PRODUCT, new Object[] { String.valueOf(productID), userComments, String.valueOf(userID) }); sqlList.add(sql); JDBCUtil.executeUpdateDeleteInsert(sqlList, dataSource); JDBCUtil.query(SP_UNCANCEL_PRODUCT, new Object[] { String.valueOf(productID), JDBCUtil.sqlEscapeString(userComments), String.valueOf(userID) }); String returnMessage = null; MapSqlParameterSource parmSource = new MapSqlParameterSource(); List<BulkTask> list = namedJdbcTemplate.query(sql, parmSource, rowMappers.bulkTasksRowMapper); if (log.isDebugEnabled()) { log.debug("Method uncancelProduct(" + productID + ",<userComments>," + userID + ") ... end"); } } public void unsuspendProduct(int productID) throws PADataAccessException { if (log.isDebugEnabled()) { log.debug("Method unsuspendProduct(" + productID + ") ... begin"); } List sqlList = new ArrayList(); String sql = MessageFormat.format(SP_UNSUSPEND_PRODUCT, new Object[] { String.valueOf(productID) }); sqlList.add(sql); JDBCUtil.executeUpdateDeleteInsert(sqlList, dataSource); JDBCUtil.query(SP_UNSUSPEND_PRODUCT, new Object[] { String.valueOf(productID) }); String returnMessage = null; MapSqlParameterSource parmSource = new MapSqlParameterSource(); List<BulkTask> list = namedJdbcTemplate.query(sql, parmSource, rowMappers.bulkTasksRowMapper); if (log.isDebugEnabled()) { log.debug("Method unsuspendProduct(" + productID + ") ... end"); } } public boolean canShowParentProduct(int productId, int userId, boolean isMarvel) throws PADataAccessException { if (log.isDebugEnabled()) log.debug("Method canShowParentProduct() productId [" + productId + "]; userId [" + userId + "]... begin"); int count = 0; boolean retVal; String sql = JDBCUtil.query(SP_CHECK_CAN_SHOW_PARENT, new Object[] { String.valueOf(productId), String.valueOf(userId), String.valueOf(isMarvel ? 1 : 0) }); MapSqlParameterSource parmSource = new MapSqlParameterSource(); List<Integer> list = namedJdbcTemplate.query(sql, parmSource, rowMappers.integerRowMapper); if (list != null && list.size() > 0) { count = list.get(0); } if (count > 0) { retVal = true; } else { retVal = false; } if (log.isDebugEnabled()) log.debug("Method canShowParentProduct() ... end"); return retVal; } public Option[] getFHBPProductComponentOptions(int optionSetId, int optionSourceId) throws PADataAccessException { if (log.isDebugEnabled()) { log.debug("Method getFHBPProductComponentOptions(..) ... begin"); } Option[] array = null; Object[] params = new Object[] { String.valueOf(optionSetId), String.valueOf(optionSourceId) }; //use sproc to retrieve acronym String sql = JDBCUtil.query(SP_SEL_PRODUCTTEMPLATE_COMPONENTS_OPTION, params); MapSqlParameterSource parmSourceChild = new MapSqlParameterSource(); List<MassOpDetail> list = namedJdbcTemplate.query(sql, parmSourceChild, rowMappers.massOpDetailRowMapper); if (list != null && list.size() > 0) { array = new Option[list.size()]; list.toArray(array); } if (log.isDebugEnabled()) { log.debug("Method getFHBPProductComponentOptions(..) ... end"); } return array; } public void copyComponent(int originalProductID, int newProductId) throws PADataAccessException { if (log.isDebugEnabled()) { log.debug("Method PAProductManager.copyProduct(" + originalProductID + " , " + newProductId + ") ... begin"); } String productIDString = String.valueOf(originalProductID); String newProductIdString = String.valueOf(newProductId); String sql = MessageFormat.format(SP_COPY_COMPONENT, new Object[] { productIDString, newProductIdString }); JDBCUtil.executeUpdateDeleteInsert(sql, dataSource); // AK 04/27/2012 [6.1] - update/insert ProductGroups for Product try { List sqlList = new ArrayList(); String insertProductGroupsSQL = this.buildInsertProductGroupsSQL(newProductId, JDBCUtil.IGNORED, true); sqlList.add(insertProductGroupsSQL); final String summarySQL = MessageFormat.format(SP_CREATE_PRODUCT_SUMMARY, new Object[] { String.valueOf(newProductId) }); sqlList.add(summarySQL); JDBCUtil.executeUpdateDeleteInsert(sqlList, dataSource); } catch (Exception e) { log.error("Exception trying to create Product Groups for " + "Product ID: " + newProductId, e); } if (log.isDebugEnabled()) { log.debug("Method PAProductManager.copyProduct(" + originalProductID + " , " + newProductId + ") ... begin"); } } public List<HardStopByPassByAttribute> getHardStopByPassByAttributeValue(int parentTemplateId, int attributeId, int optionId) throws PADataAccessException { Product product = null; if (log.isDebugEnabled()) { log.debug("Method getHardStopByPassByAttributeValue(" + parentTemplateId + "," + attributeId + " , " + optionId + ") ... begin"); } String sql = JDBCUtil.query(SP_SEL_HARD_STOP_BY_PASS_BY_ATTRIBUTE_VALUE, new Object[] { String.valueOf(parentTemplateId), String.valueOf(attributeId), String.valueOf(optionId) }); MapSqlParameterSource parmSourceChild = new MapSqlParameterSource(); List<HardStopByPassByAttribute> list = namedJdbcTemplate.query(sql, parmSourceChild, rowMappers.hardStopByPassByAttributeRowMapper); if (log.isDebugEnabled()) { log.debug("Method getHardStopByPassByAttributeValue(" + parentTemplateId + "," + attributeId + " , " + optionId + ") ... end"); } return list; } /** * OPA 7.0: Find product hard stops for approved next step * @param productID * @param userTypeID * @param nextStepID * @return * @throws PADataAccessException */ public List getProductHardStops(int productID, int userTypeID, int nextStepID) throws PADataAccessException { Product product = null; if (log.isDebugEnabled()) { log.debug("Method getProductHardStops(" + productID + ", " + userTypeID + ", " + nextStepID + ") ... begin"); } String sql = JDBCUtil.query(SP_SEL_PRODUCT_HARD_STOPS, new Object[] { String.valueOf(productID), String.valueOf(userTypeID), String.valueOf(nextStepID) }); MapSqlParameterSource parmSourceChild = new MapSqlParameterSource(); List<TemplateHardStop> list = namedJdbcTemplate.query(sql, parmSourceChild, rowMappers.productHardStopRowMapper); if (log.isDebugEnabled()) { log.debug("Method getProductHardStops(" + productID + ", " + userTypeID + ", " + nextStepID + ") ... end"); } return list; } /** * Retrieves product Facility Ids. * @param productID * @return * @throws PADataAccessException */ public List<Integer> getProductFacilityIds(int productID) throws PADataAccessException { if (log.isDebugEnabled()) { log.debug("Method getProductFacilityIds(" + productID + ") ... begin"); } String sql = JDBCUtil.query(SP_GET_PRODUCT_FACILITY_IDS, new Object[] { String.valueOf(productID) }); MapSqlParameterSource parmSource = new MapSqlParameterSource(); List<Integer> list = namedJdbcTemplate.query(sql, parmSource, rowMappers.integerRowMapper); if (log.isDebugEnabled()) { log.debug("Method getProductFacilityIds(" + productID + ") ... end"); } return list; } /** * Updates ProductSummary & ProductState on Behalf Licensee User when Associate creates the product. * @param productId * @param ownerUserId * @throws PADataAccessException */ public void updateOnBehalfLicenseeUser(int productId, int ownerUserId) throws PADataAccessException { if (log.isDebugEnabled()) { log.debug("Method updateOnBehalfLicenseeUser(" + productId + ", " + ownerUserId + ") ... begin"); } String sql = MessageFormat.format(SP_UPDATE_ON_BEHALF_LICENSEE_USER, new Object[] { String.valueOf(productId), String.valueOf(ownerUserId) }); JDBCUtil.executeUpdateDeleteInsert(sql, dataSource); if (log.isDebugEnabled()) { log.debug("Method updateOnBehalfLicenseeUser(" + productId + ", " + ownerUserId + ") ... end"); } } /** * Creates a record to keep the relationship between the original Product Id and its copy product. * @param copyCompletedProductId * @param originalProductId * @param copyParentProductId * @throws PADataAccessException */ public void copyCompletedProduct(int copyCompletedProductId, int originalProductId, int copyParentProductId) throws PADataAccessException { if (log.isDebugEnabled()) { log.debug("Method copyCompletedProduct(" + copyCompletedProductId + ", " + originalProductId + ") ... begin"); } String sql = MessageFormat.format(SP_INSERT_COPY_COMPLETED_PRODUCT, new Object[] { String.valueOf(copyCompletedProductId), String.valueOf(originalProductId), String.valueOf(copyParentProductId) }); JDBCUtil.executeUpdateDeleteInsert(sql, dataSource); // AK 04/27/2012 [6.1] - update/insert ProductGroups for Product try { List sqlList = new ArrayList(); String insertProductGroupsSQL = this.buildInsertProductGroupsSQL(copyCompletedProductId, JDBCUtil.IGNORED, true); sqlList.add(insertProductGroupsSQL); final String summarySQL = MessageFormat.format(SP_CREATE_PRODUCT_SUMMARY, new Object[] { String.valueOf(copyCompletedProductId) }); sqlList.add(summarySQL); JDBCUtil.executeUpdateDeleteInsert(sqlList, dataSource); } catch (Exception e) { log.error("Exception trying to create Product Groups for " + "Product ID: " + copyCompletedProductId, e); } if (log.isDebugEnabled()) { log.debug("Method copyCompletedProduct(" + copyCompletedProductId + ", " + originalProductId + ") ... end"); } } /** * Returns the original product Id based upon the copyCompletedProductId passed * @param copyCompletedProductId * @return * @throws PADataAccessException */ public int getOriginalProductId(int copyCompletedProductId) throws PADataAccessException { if (log.isDebugEnabled()) { log.debug("Method getOriginalProductId(" + copyCompletedProductId + ") ... begin"); } int originalProductId = 0; String sql = JDBCUtil.query(SP_GET_ORIGINAL_PRODUCT_ID, new Object[] { String.valueOf(copyCompletedProductId) }); MapSqlParameterSource parmSource = new MapSqlParameterSource(); List<Integer> list = namedJdbcTemplate.query(sql, parmSource, rowMappers.originalProductRowMapper); if (list != null && list.size() > 0) { originalProductId = list.get(0); } if (log.isDebugEnabled()) { log.debug("Method getOriginalProductId(" + copyCompletedProductId + ") ... end"); } return originalProductId; } /** * Returns the email body for Facility - All Languages * @return * @throws PADataAccessException */ public Map<String, String> getFacilityEmailBody() throws PADataAccessException { if (log.isDebugEnabled()) { log.debug("Method getFacilityEmailBody() ... begin"); } String sql = JDBCUtil.query(SP_SEL_FACILITY_EMAIL_CONTENT); TreeMap<String, String> table = null; MapSqlParameterSource parmSource = new MapSqlParameterSource(); List<TreeMap<String, String>> list = namedJdbcTemplate.query(sql, parmSource, rowMappers.facilityEmailRowMapper); if (list != null && list.size() > 0) { table = list.get(0); } if (log.isDebugEnabled()) { log.debug("Method getFacilityEmailBody() ... end"); } return table; } /** * Returns the email body for Autosuspend Inactive Products - All Languages * @return * @throws PADataAccessException */ public Map<String, String> getAutoSuspendInactiveProductEmailBody() throws PADataAccessException { if (log.isDebugEnabled()) { log.debug("Method getFacilityEmailBody() ... begin"); } String sql = JDBCUtil.query(SP_SEL_AUTOSUSPEND_EMAIL_CONTENT); TreeMap<String, String> table = null; MapSqlParameterSource parmSource = new MapSqlParameterSource(); List<TreeMap<String, String>> list = namedJdbcTemplate.query(sql, parmSource, rowMappers.autoSuspendInactiveProductRowMapper); if (list != null && list.size() > 0) { table = list.get(0); } if (log.isDebugEnabled()) { log.debug("Method getFacilityEmailBody() ... end"); } return table; } /* public TreeMap getPostLaunchReviewItems() throws PADataAccessException { if(log.isDebugEnabled()) { log.debug("Method getPostLaunchReview() ... begin"); } PostLaunchReviewProcessor processor = new PostLaunchReviewProcessor(); Hashtable processorSet = new Hashtable(2); processorSet.put(new Integer(1), processor); PostLaunchEmailContent processorEmail = new PostLaunchEmailContent(); processorSet.put(new Integer(2), processorEmail); String sql = MessageFormat.format(SP_SEL_POST_LAUNCH_ITEMS_EMAIL, new String[]{}); JDBCUtil.query(sql, processorSet); if(log.isDebugEnabled()) { log.debug("Method getListRetrievedItems() ... begin"); } TreeMap returnList = new TreeMap(); returnList.put(ProductManagerDaoImpl.POST_LAUNCH_ITEM_EMAIL_INFO, processor.getTable()); returnList.put(ProductManagerDaoImpl.POST_LAUNCH_ITEM_EMAIL_CONTENT, processorEmail.getTable()); return returnList; } private class PostLaunchReviewProcessor implements ResultSetProcessor { private TreeMap table = new TreeMap(); public TreeMap getTable() { return this.table; } public void processResultSet(ResultSet rs) throws SQLException { PostLaunchItemInfo info = null; while (rs.next()) { int licenseeId = rs.getInt("licenseeId"); int associateId = rs.getInt("associateId"); int disneyRefNumber = rs.getInt("disneyRefNumber"); info = new PostLaunchItemInfo(); info.setDisneyRefNumber(rs.getInt("disneyRefNumber")); info.setLicenseeId(rs.getInt("licenseeId")); info.setLicenseeName(rs.getString("licenseeName")); info.setAssociateId(rs.getInt("licenseeId")); info.setAssociateName(rs.getString("associateName")); info.setAssociateEmailAddress(rs.getString("associateEmailAddress")); info.setProductName(rs.getString("productName")); info.setPostLaunchDate(rs.getDate("dateValue")); info.setLanguageId(rs.getInt("languageID")); table.put(new String(associateId + "-" + licenseeId + "-" + disneyRefNumber), info); } } public CachedData getCacheableData() throws ObjectNotCacheableException { throw new ObjectNotCacheableException ("This object may not be cached."); } public void setCachedData(CachedData data) throws ObjectNotCacheableException { throw new ObjectNotCacheableException ("This object may not be cached."); } } private class PostLaunchEmailContent implements ResultSetProcessor { private TreeMap table = new TreeMap(); public TreeMap getTable() { return this.table; } public void processResultSet(ResultSet rs) throws SQLException { while (rs.next()) { table.put(rs.getString("textKey")+rs.getString("languageId"), rs.getString("textTranslation")); } } public CachedData getCacheableData() throws ObjectNotCacheableException { throw new ObjectNotCacheableException ("This object may not be cached."); } public void setCachedData(CachedData data) throws ObjectNotCacheableException { throw new ObjectNotCacheableException ("This object may not be cached."); } } */ /** * Returns the parent template ID or the grand parent template ID based upon the child product Id or grand child product Id * @param productId * @return * @throws PADataAccessException */ public int findParentTemplateId(int productId) throws PADataAccessException { if (log.isDebugEnabled()) { log.debug("Method findParentTemplateId(" + productId + ") ... begin"); } String sql = JDBCUtil.query(SP_FIND_PARENT_TEMPLATE_ID, new Object[] { String.valueOf(productId) }); MapSqlParameterSource parmSource = new MapSqlParameterSource(); List<Integer> list = namedJdbcTemplate.query(sql, parmSource, rowMappers.findParentTemplateRowMapper); int parentTemplateId = 0; if (list != null && list.size() > 0) { parentTemplateId = list.get(0); } if (log.isDebugEnabled()) { log.debug("Method findParentTemplateId(" + productId + ") ... begin"); } return parentTemplateId; } public int getCopyToStepId(int templateId, int userTypeId) throws PADataAccessException { String sql = JDBCUtil.query(SP_COPY_TO_STEP_ID, new Object[] { String.valueOf(templateId), String.valueOf(userTypeId) }); MapSqlParameterSource parmSource = new MapSqlParameterSource(); List<Integer> list = namedJdbcTemplate.query(sql, parmSource, rowMappers.integerRowMapper); int result = (list != null && list.size() > 0) ? ((Integer) list.get(0)).intValue() : -1; //-1 will be ignored when used in subsequent procs return result; } public boolean isValidProductGroup(int productGroupID) throws PADataAccessException { return productGroupDao.isValidProductGroup(productGroupID); } public Set<ProductGroup> getProductGroups(int productID) throws PADataAccessException { return productGroupDao.getProductGroups(productID); } public int insertProductGroup(int productID, int productGroupID, int userID, boolean suppressResult) throws PADataAccessException { return productGroupDao.insertProductGroup(productID, productGroupID, userID, suppressResult); } public int insertProductGroups(int productID, int userID, boolean suppressResult) throws PADataAccessException { return productGroupDao.insertProductGroups(productID, userID, suppressResult); } public int deleteProductGroup(int productID, int productGroupID, boolean suppressResult) throws PADataAccessException { return productGroupDao.deleteProductGroup(productID, productGroupID, suppressResult); } public int deleteProductGroups(int productID, boolean suppressResult) throws PADataAccessException { return productGroupDao.deleteProductGroups(productID, suppressResult); } public String buildGetProductGroupsSQL(int productID) throws PADataAccessException { return productGroupDao.buildGetProductGroupsSQL(productID); } public String buildInsertProductGroupSQL(int productID, int productGroupID, int userID, boolean suppressResult) throws PADataAccessException { return productGroupDao.buildInsertProductGroupSQL(productID, productGroupID, userID, suppressResult); } public String buildInsertProductGroupsSQL(int productID, int userID, boolean suppressResult) throws PADataAccessException { return productGroupDao.buildInsertProductGroupsSQL(productID, userID, suppressResult); } public ParameterSQL buildInsertProductGroupsSQL(String identityPlaceholder, int userID, boolean suppressResult) throws PADataAccessException { return productGroupDao.buildInsertProductGroupsSQL(identityPlaceholder, userID, suppressResult); } public String buildDeleteProductGroupSQL(int productID, int productGroupID, boolean suppressResult) throws PADataAccessException { return productGroupDao.buildDeleteProductGroupSQL(productID, productGroupID, suppressResult); } public String buildDeleteProductGroupsSQL(int productID, boolean suppressResult) throws PADataAccessException { return productGroupDao.buildDeleteProductGroupsSQL(productID, suppressResult); } public ParameterSQL buildDeleteProductGroupsSQL(String identityPlaceholder, int userID, boolean suppressResult) throws PADataAccessException { return productGroupDao.buildDeleteProductGroupsSQL(identityPlaceholder, userID, suppressResult); } public List<Integer> getProductIdsForLRTValidation(String licenseeIds) throws PADataAccessException { if (log.isDebugEnabled()) { log.debug("Method getProductIdsForLRTValidation() ... begin"); } //TODO: 10/24/12 - WTF is this???? licenseeIds = "'" + licenseeIds; licenseeIds = licenseeIds + "'"; /* IntegerProcessor intProcessor = new IntegerProcessor(); //OPA 6.5 - if licensee ids is null then pass NULL to sproc if(licenseeIds == null) { JDBCUtil.query(SP_SEL_PRODUCT_IDS_FOR_LRT_VALIDATION, null, intProcessor); } else { JDBCUtil.query(SP_SEL_PRODUCT_IDS_FOR_LRT_VALIDATION, new Object[] {String.valueOf(licenseeIds)}, intProcessor); } */ List<Integer> results = null; if (licenseeIds == null) { String qString = JDBCUtil.query(SP_SEL_PRODUCT_IDS_FOR_LRT_VALIDATION, new Object[] {}); MapSqlParameterSource parmSource = new MapSqlParameterSource(); results = namedJdbcTemplate.query(qString, parmSource, rowMappers.integerRowMapper); } else { String qString = JDBCUtil.query(SP_SEL_PRODUCT_IDS_FOR_LRT_VALIDATION, new Object[] { licenseeIds }); MapSqlParameterSource parmSource = new MapSqlParameterSource(); results = namedJdbcTemplate.query(qString, parmSource, rowMappers.integerRowMapper); } if (log.isDebugEnabled()) { if (null != results) { log.debug("Found " + results.size() + " products for LRT validation"); log.debug("Method getProductIdsForLRTValidation() ... end"); } } return results; } public List<Integer> getAllMigrationProducts() throws PADataAccessException { if (log.isDebugEnabled()) { log.debug("Method getProductIdsForLRTValidation() ... begin"); } List<Integer> results = null; String qString = JDBCUtil.query("SELECT productid FROM dbo.migration_revalidatedproducts", new Object[] {}); MapSqlParameterSource parmSource = new MapSqlParameterSource(); results = namedJdbcTemplate.query(qString, parmSource, rowMappers.integerRowMapper); return results; } //(non-Javadoc) //* @see com.disney.dcp.lic.pa.product.ProductManager#updateUsers(com.disney.dcp.lic.pa.product.Product, int, int, int) public void updateUsers(Product product, int licenseeID, int associateID, int leadID) throws PADataAccessException { if (product == null) { throw new PADataAccessException("Product object is null."); } if (licenseeID < 0 && associateID < 0 && leadID < 0) { throw new PADataAccessException("Nothing to update on Product."); } if (log.isDebugEnabled()) { log.debug("Method updateUsers(" + product.getID() + ", " + licenseeID + ", " + associateID + ", " + leadID + ") ... begin"); } String productIDString = String.valueOf(product.getID()); String licenseeIDString = null; if (licenseeID != JDBCUtil.IGNORED) { licenseeIDString = String.valueOf(licenseeID); } String associateIDString = null; if (associateID != JDBCUtil.IGNORED) { associateIDString = String.valueOf(associateID); } String leadIDString = null; if (leadID != JDBCUtil.IGNORED) { leadIDString = String.valueOf(leadID); } List<String> sqlList = new ArrayList<String>(); String updProductUsers = MessageFormat.format(SP_UPD_PRODUCT_USERS, new Object[] { productIDString, licenseeIDString, associateIDString, leadIDString }); sqlList.add(updProductUsers); String summarySQL = MessageFormat.format(SP_CREATE_PRODUCT_SUMMARY, new Object[] { productIDString }); sqlList.add(summarySQL); //JDBCUtil.executeUpdateDeleteInsert(sqlList); for (String sql : sqlList) { namedJdbcTemplate.execute(sql, new PreparedStatementCallback() { @Override public Object doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException { return ps.executeUpdate(); } }); } if (log.isDebugEnabled()) { log.debug("Method updateUsers(" + product.getID() + ", " + licenseeID + ", " + associateID + ", " + leadIDString + ") ... end"); } } /** * * @param csv * @param languageID * @return * @throws PADataAccessException */ public ProductSummary[] getProductSummaries(String csv, int languageID) throws PADataAccessException { if (log.isDebugEnabled()) { log.debug("Method getProductSummaries(csv, languageID) ... begin"); } //This is needed because Constants.java is in PRODAPP not PRODAPP_API final int LANGUAGE_ENGLISH = 1; String langID = String.valueOf(((languageID != JDBCUtil.IGNORED) ? languageID : LANGUAGE_ENGLISH)); ProductSummary[] array = null; String sql = JDBCUtil.query(SP_SEL_PRODUCT_SUMMARIES, new Object[] { JDBCUtil.sqlEscapeString(csv), langID }); MapSqlParameterSource parmSourceChild = new MapSqlParameterSource(); List<MassOpDetail> list = namedJdbcTemplate.query(sql, parmSourceChild, rowMappers.massOpDetailRowMapper); if (list != null && list.size() > 0) { array = new ProductSummary[list.size()]; list.toArray(array); } if (log.isDebugEnabled()) { log.debug("Method getProductSummaries(csv, languageID) ... end"); } return array; } public Map<Integer, InactiveProduct> getInactiveProducts(int regionId) throws PADataAccessException { if (log.isDebugEnabled()) { log.debug("Method getInactiveProducts() ... begin"); } String sql = JDBCUtil.query(SP_SEL_INACTIVE_PRODUCT, new Object[] { String.valueOf(regionId) }); Map<Integer, InactiveProduct> table = null; MapSqlParameterSource parmSource = new MapSqlParameterSource(); List<Map<Integer, InactiveProduct>> list = namedJdbcTemplate.query(sql, parmSource, rowMappers.inactiveProductRowMapper); if (list != null && list.size() > 0) { table = list.get(0); } if (log.isDebugEnabled()) { log.debug("Method getProductIdsForLRTValidation() ... end"); } return table; } /** * Retrieve simple 1x4 matrix of data regarding the PI component for a given parent product id. This * method serves as a check on multiple levels to indicate whether a component should be created and, * if it is to be created, to indicate whether compliance documents is required. <b>All parameters * are required.</b> * * @param parentProductId * @throws PADataAccessException * @return List<Integer> The 1x2 matrix of integers indicating: (1,1) = whether PI component creation required AND (1,2) = whether PI compliance documentation is required */ public List<Integer> getPIComponentMatrix(int parentProductId) throws PADataAccessException { String sql = JDBCUtil.query(SP_SEL_PI_COMPONENT_MATRIX, new Object[] { String.valueOf(parentProductId) }); MapSqlParameterSource parmSource = new MapSqlParameterSource(); List<Integer> list = namedJdbcTemplate.query(sql, parmSource, rowMappers.integerRowMapper); int parentTemplateId = 0; if (list != null && list.size() > 0) { parentTemplateId = list.get(0); } return list; } /** * Retrieve list of approved PI components that have not already been sent; include associated information to * include in the XML transmission to eDocs. * * @return List<PIEDocProduct> * @throws PADataAccessException */ public List<PAPIEDocProduct> getPIComponentsForTransmission() throws PADataAccessException { String sql = JDBCUtil.query(SP_SEL_PI_COMPONENTS_FOR_EDOCS); MapSqlParameterSource parmSource = new MapSqlParameterSource(); List<PAPIEDocProduct> list = namedJdbcTemplate.query(sql, parmSource, rowMappers.pIEDocProductRowMapper); return list; } public ProductPreviewInfo getProductPreviewInfo(int productId, int languageId) throws PADataAccessException { ProductPreviewInfo productPreview = null; String sql = JDBCUtil.query(SP_SEL_PRODUCT_PREVIEW_INFO, new Object[] { String.valueOf(productId), String.valueOf(languageId) }); MapSqlParameterSource parmSource = new MapSqlParameterSource(); List<PAProductPreviewInfo> productPreviewInfoList = namedJdbcTemplate.query(sql, parmSource, rowMappers.productPreviewInfoRowMapper); if (productPreviewInfoList != null && productPreviewInfoList.size() > 0) { productPreview = productPreviewInfoList.get(0); } return productPreview; } //OPA 7.3 MR - FNS Component Matrix /** * Get the Lead user id from the FNSComponentMatrix based on the template, step and attribute value * * @param productId * @return int Lead User Id * @throws PADataAccessException */ public int getFNSComponentMatrixLeadUserId(int productId) throws PADataAccessException { int leadUserId = -1; //default system admin user (procedure should always return user id) String sql = JDBCUtil.query(SP_SEL_FNS_COMPONENT_MATRIX, new Object[] { String.valueOf(productId) }); MapSqlParameterSource parmSource = new MapSqlParameterSource(); List<Integer> list = namedJdbcTemplate.query(sql, parmSource, rowMappers.integerRowMapper); int parentTemplateId = 0; if (list != null && list.size() > 0) { leadUserId = list.get(0); } //return default system user because the procedure should always return a value return leadUserId; } //OPA 7.3.x Lucasfilm Product Restriction Mini-Release public int getProductAssociation(int productId, int userId) throws PADataAccessException { String sql = JDBCUtil.query(SP_SEL_PRODUCT_ASSOCIATION, new Object[] { String.valueOf(productId), String.valueOf(userId) }); //this should always return a 1 or 0 MapSqlParameterSource parmSource = new MapSqlParameterSource(); List<Integer> list = namedJdbcTemplate.query(sql, parmSource, rowMappers.integerRowMapper); int parentTemplateId = 0; if (list != null && list.size() > 0) { return list.get(0).intValue(); } return 0; } //OPA 7.3.x Lucasfilm Product Restriction Mini-Release public int getProductRestiction(int productId) throws PADataAccessException { String sql = JDBCUtil.query(SP_SEL_PRODUCT_RESTRICTION, new Object[] { String.valueOf(productId) }); //this should always return a 1 or 0 MapSqlParameterSource parmSource = new MapSqlParameterSource(); List<Integer> list = namedJdbcTemplate.query(sql, parmSource, rowMappers.integerRowMapper); if (list != null && list.size() > 0) { return list.get(0).intValue(); } return 0; } public void setDataSource(DataSource dataSource) { namedJdbcTemplate = new NamedParameterJdbcTemplate(dataSource); } //TODO issue #4 on AK's email for 10/1 : should we be deleting or updating the ProductReview table. //current implementation is deleting. //check the web code and see if there is anything related to updateProductReviews //we want to inactive not DELETE //spUPDProductReviews //spSELProductReviews...make sure this is amended to NOT pick up inactive product reviews. //there is a table called productSelectedReviewers..add isActive flag on this table and set the isActive flag to false //update the users of productSelectedReviewers to NOT select isActive = 0 public void updateProductReview(int productId, ProductState state, int userId, boolean isActive) throws PADataAccessException { if (log.isDebugEnabled()) { log.debug("Method updateProductReview(" + state.getCurrentProductStateID() + ", " + userId + ") ... begin"); } String sql = null; String deleteSQL = null; //TODO issue #4 on AK's email for 10/1..don't delete!!! //sql = MessageFormat.format(SP_DEL_REVIEWERS, new Object[] {String.valueOf(state.getCurrentProductStateID()), String.valueOf(userId)}); sql = MessageFormat.format(SP_UPDATE_PRODUCT_REVIEW, new Object[] { String.valueOf(state.getCurrentProductStateID()), String.valueOf(userId) }); JDBCUtil.executeUpdateDeleteInsert(sql, dataSource); if (log.isDebugEnabled()) { log.debug("Method updateProductReview(" + state.getCurrentProductStateID() + ", " + userId + ") ... end"); } } }