@Bean(name = "entityManagerFactory")
    public LocalContainerEntityManagerFactoryBean entityManagerFactory() {

        EclipseLinkJpaVendorAdapter vendorAdapter = new EclipseLinkJpaVendorAdapter();
        vendorAdapter.setGenerateDdl(false);
        vendorAdapter.setShowSql(true);
        vendorAdapter.setDatabase(Database.MYSQL);

        LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();
        em.setDataSource(GamePropsDataSource());
        em.setPackagesToScan(new String[]{"ru.stoloto.protocol.entities"});
        em.setPersistenceUnitName("persistanceUnitGameProps");
        em.setJpaVendorAdapter(vendorAdapter);
        em.setJpaProperties(additionalProperties());
        Map<String, String> jpaProperties = new HashMap<>();
        jpaProperties.put("eclipselink.weaving", "false");
        jpaProperties.put("jpaDialect"," org.springframework.orm.jpa.vendor.EclipseLinkJpaDialect");
        jpaProperties.put("eclipselink.allow-zero-id","true");
        jpaProperties.put("eclipselink.query-results-cache","true");
        em.setJpaPropertyMap(jpaProperties);
        em.afterPropertiesSet();

        return em;

    }

    @Bean
    public EclipseLinkJpaDialect eclipseLinkJpaDialect() {
        return new EclipseLinkJpaDialect();
    }

SET NAMES 'utf8' COLLATE 'utf8_unicode_ci'\p;
use db_rt\p;

-- ===========================================================================
-- 1. drop/create 

DELIMITER $$

DROP PROCEDURE IF EXISTS `gettemplate` \p$$

CREATE PROCEDURE `gettemplate` (
	IN a_id BIGINT UNSIGNED,
	IN a_agntid INTEGER UNSIGNED,
	IN a_type ENUM ('type1', 'type2'),
	IN a_drawid INTEGER UNSIGNED,
	IN a_tm BIGINT UNSIGNED
)
l_proc: begin

    DECLARE V_SIGN_OK INTEGER UNSIGNED DEFAULT 0;
    DECLARE V_INVALID_ID INTEGER UNSIGNED DEFAULT 1;
    DECLARE V_NO_READY_ID INTEGER UNSIGNED DEFAULT 2;
    DECLARE V_NO_READY_ID2 INTEGER UNSIGNED DEFAULT 3;
    
    DECLARE v_res_sign INTEGER UNSIGNED DEFAULT V_SIGN_OK;
    
    DECLARE v_tempid BIGINT UNSIGNED;
    DECLARE v_lawid BIGINT UNSIGNED;
    DECLARE v_adsid BIGINT UNSIGNED;

    DECLARE v_temp_text VARCHAR(4000);
    DECLARE v_law_text VARCHAR(4000);
    DECLARE v_ads_text VARCHAR(1000);
    
    DECLARE v_gmid BIGINT UNSIGNED DEFAULT NULL;
   
    SELECT `id` INTO v_gmid FROM `gm` WHERE `pr_id` = a_id;
    
    IF v_gmid IS NOT NULL THEN
    
		
		SELECT MAX(`id`) INTO v_tempid FROM `rt_temp` 
		WHERE v_gmid = `gmid` AND `st` = 'can' AND
			(FROM_UNIXTIME(a_tm) >= `min_date` OR `min_date` IS NULL ) AND
			(a_drawid >= `min_id` OR `min_id` IS NULL);

		IF v_tempid IS NOT NULL THEN
			SELECT `temp_text` INTO v_template_text FROM `receipt_template` WHERE `id` = v_tempid;
		ELSE
			SET v_res_sign = V_NO_ID;
		END IF;


		IF v_res_sign = V_SIGN_OK THEN
			
			SELECT MAX(`id`) INTO v_law_info_id FROM `law_info`
			WHERE v_gmid = `gm_id` AND `st` = 'can' AND `txt_type` = a_type AND
				(FROM_UNIXTIME(a_tm) >= `min_date` OR `min_date` IS NULL ) AND
				(a_drawid >= `min_id` OR `min_id` IS NULL);

			IF v_lawid IS NOT NULL THEN
				SELECT `lawtxt` INTO v_law_text FROM `lawinfo` WHERE `id` = v_lawid;
			ELSE
				SET v_res_sign = V_NO_READY_ID2;
			END IF;

		END IF;


		IF v_res_sign = V_SIGN_OK THEN
			
			SELECT MAX(`id`) INTO v_adsid FROM `adsinfo`
			WHERE (v_gmid = `gm_id` OR `gm_id` IS NULL) AND `st` = 'can' AND
			(a_agntid = `agntid` OR `agntid` IS NULL) AND
			(FROM_UNIXTIME(a_tm) >= `min_date` OR `min_date` IS NULL ) AND
			(FROM_UNIXTIME(a_tm) <= `max_date` OR `max_date` IS NULL ) AND
			(a_drawid >= `min_id` OR `min_id` IS NULL) AND
			(a_drawid <= `max_id` OR `max_id` IS NULL);


			IF v_ads_id IS NOT NULL THEN
				SELECT `ads_text` INTO v_ads_text FROM `adsinfo` WHERE `id` = v_ads_id;
			END IF;

		END IF;
		
	ELSE
		SET v_res_sign = V_INVALID_ID;
	END IF;
	
	SELECT v_res_sign, v_temp_text, v_law_text, v_ads_text, v_temp_id, v_law_id, v_ads_id;
	
end
\p$$

DELIMITER ;

-- ===========================================================================


GRANT EXECUTE ON PROCEDURE `db_rt`.`gettemplate` TO 'a_user'@'%' \p;

-- the END
-- ===========================================================================