Calender picker and days difference without hours?

[Eric]

Hello,

we use for calculation of vacation days a table with formulas to calculate days left and so on for our employes.

What i face was a calculation irritation because i used for the form for input the from and to fields as calendar picker "days".

I realized, this turns out as wrong calculations and found out the reason is the time format that was used for rukovoditel_days_difference and got wrong results like 20 instead of 19 days left for example.
Now i changed the from and to field to the calendar picker with date and time.
But this means someone who is adding a new record has to choose time with hours and minutes additional to the date, this is what i want to avoid.

Is there a way to receive the correct result with a modification of my formula?

Code: Select all

sum(rukovoditel_days_diff(field_1575,field_1576,'1,7,8','',''))

[support]

There is option "Exclude last day" in days difference field type.

[Eric]

Hi,

i played with options for days_diff and discovered that days_diff is not working as it should i think.

Setting up date_diff field like this:


Turns out as:

(green circles show manually typed and correct values),(blue in row 1 is 2 weeks with workdays monday to friday, row 2 is just one week of work)

Also, when using 1 Sunday from the 2 Sundays who are available to choose in options i get wrong count back.

And i tried the combination of 1 Sunday and then the second one and removed the other, gave me different results, also wrong.

Why are there 2 Sundays available by the way?

@Sergey
If you can not reproduce please give me access to a test installation and i rebuild it.

[support]

Yes, give me an example on personal demo https://www.rukovoditel.net/demo.php

[Eric]

Strange... Added and tried demo but there everything seems correct

Any idea why i have 2 times Sunday (Sonntag) available? Might be the problem because on demo it is only 1 Sunday?



Also able to choose both for exclude days of week...


"rukovoditel_days_diff" function from db

Code: Select all

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

Maybe you would be so nice and confirm this is the correct code and settings in my db? Or ideas how i can fix it or where to look for?

[support]

Do you use latest version of app?

Any idea why i have 2 times Sunday (Sonntag) available?

Looks like there is issue in translation.
Open includes\languages\english.php
and find line

Code: Select all

define('TEXT_DATEPICKER_DAYS','"Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday"');

Compare this line in language file that you use.

[Eric]

Version 3.0.1

The english and german lang files from download have these values included, i did not change these before.

This is what i found in english lang file:

Code: Select all

define('TEXT_DATEPICKER_DAYS','"Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday"');
define('TEXT_DATEPICKER_DAYSSHORT','"Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun"');
define('TEXT_DATEPICKER_DAYSMIN','"Su", "Mo", "Tu", "We", "Th", "Fr", "Sa", "Su"');

Changed this to:

Code: Select all

define('TEXT_DATEPICKER_DAYS','"Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday"');
define('TEXT_DATEPICKER_DAYSSHORT','"Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat"');
define('TEXT_DATEPICKER_DAYSMIN','"Su", "Mo", "Tu", "We", "Th", "Fr", "Sa"');

in my german lang file:

Code: Select all

define('TEXT_DATEPICKER_DAYS','"Sonntag", "Montag", "Dienstag", "Mittwoch", "Donnerstag", "Freitag", "Samstag", "Sonntag"');
define('TEXT_DATEPICKER_DAYSSHORT','"So", "Mo", "Di", "Mi", "Do", "Fr", "Sa", "So"');
define('TEXT_DATEPICKER_DAYSMIN','"So", "Mo", "Di", "Mi", "Do", "Fr", "Sa", "So"');

Also changed the values here.

But the counting is still wrong, when adding a 5 day work week it is counting 5 but when going over a period with a weekend between then it is 1 day less:


Where to look next? Need help