Errors with Date and Phone Entity

[StuartB_75]

Hi,
Trying to set a new field in an entity as either a phone number, Years Difference, Days Difference or Hours difference creates the following errors:

Phone:
Database Error: 1044 - Access denied for user 'DEV_CCTVPM'@'localhost' to database 'DEV_CCTVPM'

Query: CREATE FUNCTION `rukovoditel_regex_replace`(pattern VARCHAR(1000),replacement VARCHAR(1000),original VARCHAR(1000)) RETURNS VARCHAR(1000) DETERMINISTIC BEGIN DECLARE temp VARCHAR(1000); DECLARE ch VARCHAR(1); DECLARE i INT; SET i = 1; SET temp = ''; IF original REGEXP pattern THEN loop_label: LOOP IF i>CHAR_LENGTH(original) THEN LEAVE loop_label; END IF; SET ch = SUBSTRING(original,i,1); IF NOT ch REGEXP pattern THEN SET temp = CONCAT(temp,ch); ELSE SET temp = CONCAT(temp,replacement); END IF; SET i=i+1; END LOOP; ELSE SET temp = original; END IF; RETURN temp; END

Page: /DEV_CCTVPM/index.php?module=entities/fields_configuration

Years Difference
Database Error: 1044 - Access denied for user 'DEV_CCTVPM'@'localhost' to database 'DEV_CCTVPM'

Query: CREATE FUNCTION `rukovoditel_years_diff`(`start_date` INT, `end_date` INT, `inc_days` TINYINT(1)) RETURNS int(11) BEGIN DECLARE years_diff INT; SET years_diff=0; IF inc_days=1 THEN SET years_diff = TIMESTAMPDIFF(YEAR, DATE_FORMAT(DATE_ADD(FROM_UNIXTIME(0),INTERVAL start_date SECOND),'%Y-%m-%d') , DATE_FORMAT(DATE_ADD(FROM_UNIXTIME(0),INTERVAL end_date SECOND),'%Y-%m-%d') ); ELSE SET years_diff = YEAR(DATE_FORMAT(DATE_ADD(FROM_UNIXTIME(0),INTERVAL end_date SECOND),'%Y-%m-%d')) - DATE_FORMAT(DATE_ADD(FROM_UNIXTIME(0),INTERVAL start_date SECOND),'%Y-%m-%d'); END IF; RETURN years_diff; END;

Page: /DEV_CCTVPM/index.php?module=entities/fields_configuration

Days Difference
Database Error: 1044 - Access denied for user 'DEV_CCTVPM'@'localhost' to database 'DEV_CCTVPM'

Query: CREATE FUNCTION `rukovoditel_days_diff`(`start_date` INT, `end_date` INT, `exclude_days` VARCHAR(64), `exclude_last_day` TINYINT(1), `exclude_holidays` TINYINT(1)) RETURNS int(11) BEGIN DECLARE days_diff INT; DECLARE inc_days TINYINT; SET days_diff=0; IF start_date>0 and end_date>0 and end_date>=start_date THEN #skip while if no restriction IF length(exclude_days)=0 and exclude_holidays!=1 THEN SET days_diff = (end_date-start_date)/86400; IF exclude_last_day!=1 THEN SET days_diff = days_diff+1; END IF; RETURN days_diff; END IF; WHILE FROM_UNIXTIME(start_date,'%Y-%m-%d')<=FROM_UNIXTIME(end_date,'%Y-%m-%d') DO SET inc_days=1; #exclude day of week IF find_in_set(DAYOFWEEK(FROM_UNIXTIME(start_date,'%Y-%m-%d')),exclude_days) THEN SET inc_days=0; END IF; #exclude last day IF exclude_last_day=1 and FROM_UNIXTIME(start_date,'%Y-%m-%d')=FROM_UNIXTIME(end_date,'%Y-%m-%d')THEN SET inc_days=0; END IF; #exclude holidays IF exclude_holidays=1 THEN SET @start_date_var = FROM_UNIXTIME(start_date,'%Y-%m-%d'); SET @is_holiday = (select count(*) from app_holidays h where h.start_date<= @start_date_var and h.end_date>=@start_date_var); if @is_holiday!=0 THEN SET inc_days=0; END if; END IF; IF inc_days=1 THEN SET days_diff =days_diff+1; END IF; SET start_date = start_date+86400; END WHILE; END IF; RETURN days_diff; END;

Page: /DEV_CCTVPM/index.php?module=entities/fields_configuration

Hours Difference
Database Error: 1044 - Access denied for user 'DEV_CCTVPM'@'localhost' to database 'DEV_CCTVPM'

Query: CREATE FUNCTION `rukovoditel_hours_diff`(`start_date` INT, `end_date` INT, `include_hours` VARCHAR(255), `exclude_days` VARCHAR(64), `exclude_holidays` TINYINT(1)) RETURNS FLOAT BEGIN DECLARE minutes_diff INT; DECLARE inc_minue TINYINT; DECLARE minutes_start TINYINT; DECLARE use_minutes_start TINYINT; DECLARE minutes_end TINYINT; IF start_date>0 and end_date>0 and end_date>start_date THEN #skip while if no restriction IF length(exclude_days)=0 and length(include_hours)=0 and exclude_holidays!=1 THEN RETURN (end_date-start_date)/3600; END IF; SET minutes_diff = 0; SET use_minutes_start = 0; SET minutes_start = MINUTE(FROM_UNIXTIME(start_date,'%Y-%m-%d %H:%i')); SET minutes_end = MINUTE(FROM_UNIXTIME(end_date,'%Y-%m-%d %H:%i')); SET start_date = start_date-(minutes_start*60); WHILE start_date<end_date DO SET inc_minue=1; #exclude day of week IF find_in_set(DAYOFWEEK(FROM_UNIXTIME(start_date,'%Y-%m-%d')),exclude_days) THEN SET inc_minue=0; END IF; #exclude not working hours IF find_in_set(HOUR(FROM_UNIXTIME(start_date,'%Y-%m-%d %H:%i')),include_hours)=0 and length(include_hours)>0 THEN SET inc_minue=0; END IF; #exclude holidays IF exclude_holidays=1 THEN SET @start_date_var = FROM_UNIXTIME(start_date,'%Y-%m-%d'); SET @is_holiday = (select count(*) from app_holidays h where h.start_date<= @start_date_var and h.end_date>=@start_date_var); if @is_holiday!=0 THEN SET inc_minue=0; END if; END IF; IF inc_minue=1 THEN SET minutes_diff =minutes_diff+60; END IF; #handle first hour minute IF inc_minue=1 and use_minutes_start=0 THEN SET use_minutes_start=1; ELSEIF use_minutes_start=0 THEN SET use_minutes_start=2; END IF; SET start_date = start_date+3600; END WHILE; #handle start minutes IF use_minutes_start=1 THEN SET minutes_diff =minutes_diff-minutes_start; END IF; #handle end minutes IF minutes_end>0 and inc_minue=1 THEN SET minutes_diff =(minutes_diff-60)+minutes_end; END IF; END IF; IF minutes_diff>0 THEN RETURN minutes_diff/60; ELSE RETURN 0; END IF; END;

Page: /DEV_CCTVPM/index.php?module=entities/fields_configuration

This in on Rukkovoditel 2.4 BETA 2, Extension 2.4

[support]

Database user should have permission to CREATE FUNCTION in database. This is required to this field type.