snd-escloud/sql/func.sql

/*
 Navicat MySQL Data Transfer

 Source Server         : 47.105.241.108
 Source Server Type    : MySQL
 Source Server Version : 50725
 Source Host           : 47.105.241.108:33060
 Source Schema         : service_platform

 Target Server Type    : MySQL
 Target Server Version : 50725
 File Encoding         : 65001

 Date: 22/11/2019 09:55:16
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Function structure for getCodeByValue
-- ----------------------------
DROP FUNCTION IF EXISTS `getCodeByValue`;
delimiter ;;
CREATE FUNCTION `getCodeByValue`(codeName varchar(64) , codeValue varchar(64))
 RETURNS varchar(64) CHARSET utf8
BEGIN
	
		RETURN 
    (select `code` from dict where `name` = codeName AND `value` = codeValue);
END
;;
delimiter ;

-- ----------------------------
-- Function structure for getFirstPinyin
-- ----------------------------
DROP FUNCTION IF EXISTS `getFirstPinyin`;
delimiter ;;
CREATE FUNCTION `getFirstPinyin`(P_NAME varchar(64))
 RETURNS varchar(255) CHARSET utf8
BEGIN
		DECLARE V_RETURN VARCHAR(255);
    DECLARE V_BOOL INT DEFAULT 0;
    DECLARE V_NUMBER INT DEFAULT 1;
    DECLARE FIRST_VARCHAR VARCHAR(1);

    SET FIRST_VARCHAR = left(CONVERT(P_NAME USING gbk),1);
    SELECT FIRST_VARCHAR REGEXP '[a-zA-Z]' INTO V_BOOL;
    SELECT FIRST_VARCHAR REGEXP '[^0-9.]' INTO V_NUMBER;
    IF V_BOOL = 1 THEN
      SET V_RETURN = UPPER(FIRST_VARCHAR);
		ELSE
				IF V_NUMBER = 0 THEN
					CASE FIRST_VARCHAR
					WHEN 0 THEN SET V_RETURN ='A'; 
					WHEN 1 THEN SET V_RETURN ='B'; 
					WHEN 2 THEN SET V_RETURN ='C'; 
					WHEN 3 THEN SET V_RETURN ='D'; 
					WHEN 4 THEN SET V_RETURN ='E'; 
					WHEN 5 THEN SET V_RETURN ='F'; 
					WHEN 6 THEN SET V_RETURN ='G'; 
					WHEN 7 THEN SET V_RETURN ='H'; 
					WHEN 8 THEN SET V_RETURN ='I'; 
					WHEN 9 THEN SET V_RETURN ='J'; 
					END CASE;
				ELSE
					IF ELT(INTERVAL(CONV(HEX(left(CONVERT(P_NAME USING gbk),1)),16,10),   
								0xB0A1,0xB0C5,0xB2C1,0xB4EE,0xB6EA,0xB7A2,0xB8C1,0xB9FE,0xBBF7,   
								0xBFA6,0xC0AC,0xC2E8,0xC4C3,0xC5B6,0xC5BE,0xC6DA,0xC8BB,  
								0xC8F6,0xCBFA,0xCDDA,0xCEF4,0xD1B9,0xD4D1),   
						'A','B','C','D','E','F','G','H','J','K','L','M','N','O','P','Q','R','S','T','W','X','Y','Z') IS NULL THEN
						SET V_RETURN = 'Z';
					ELSE
						SET V_RETURN = ELT(INTERVAL(CONV(HEX(left(CONVERT(P_NAME USING gbk),1)),16,10),   
								0xB0A1,0xB0C5,0xB2C1,0xB4EE,0xB6EA,0xB7A2,0xB8C1,0xB9FE,0xBBF7,   
								0xBFA6,0xC0AC,0xC2E8,0xC4C3,0xC5B6,0xC5BE,0xC6DA,0xC8BB,  
								0xC8F6,0xCBFA,0xCDDA,0xCEF4,0xD1B9,0xD4D1),   
						'A','B','C','D','E','F','G','H','J','K','L','M','N','O','P','Q','R','S','T','W','X','Y','Z'); 
						END IF;
				END IF;	
    END IF;
    RETURN V_RETURN;
END
;;
delimiter ;

-- ----------------------------
-- Function structure for getPushDate
-- ----------------------------
DROP FUNCTION IF EXISTS `getPushDate`;
delimiter ;;
CREATE FUNCTION `getPushDate`(notify_id int,notify_detail_id int)
 RETURNS varchar(45) CHARSET utf8 COLLATE utf8_bin
BEGIN
	
				IF (notify_detail_id > 0)
       			THEN
						return  (select detail.update_time as push_date from z_notify_detail detail WHERE detail.id = notify_detail_id and detail.is_push = 1);
				ELSE
						return	(select (CASE When n.update_time IS NULL THEN n.create_time ELSE n.update_time END)
											from z_notify n WHERE n.id = notify_id and n.is_push = '1');
				END IF;
END
;;
delimiter ;

-- ----------------------------
-- Event structure for e_delete_log
-- ----------------------------
DROP EVENT IF EXISTS `e_delete_log`;
delimiter ;;
CREATE EVENT `e_delete_log`
ON SCHEDULE
EVERY '1' DAY STARTS '2018-06-10 16:10:00'
DO DELETE FROM log WHERE create_time < DATE_SUB(CURRENT_TIMESTAMP,INTERVAL 90 DAY)
;
;;
delimiter ;

SET FOREIGN_KEY_CHECKS = 1;