Increase days automatically in database

Question

I have an application where each user can request their vacations. There are two types of users, fixed and temporary. Fixed users have 24 days a year for vacations. The doubt comes with the temporary users.

Temporary users are added 2 days per month worked until December 31 of that year. For example:

user1 entered the company on 25/01/2019, until 31/12/2019 would have to increase it 2 days per month worked until that date, in total 22 days. As of 01/01/2020, those 2 days, will be increased every calendar month and start from 0, so that 01/01/2020 would have 0 days and 01/02/2020 would have 2 days.

Is there a PHP function or in MySQL a procedure for those 2 days to automatically add them to the database?

In the database I have a user table that has the following fields:

name      available_days       start_date
=======   =================    ============
user1           0                25/01/2019

To those available_days is to which the days must be increased.

I have this function that calculates the difference in months since the user entered the company until today:

function difcurrentmonth($startdate){
 $date = new DateTime($startdate);
 $currentdate= (new DateTime)->format('Y-m-d H:i');
 $finaldate = new DateTime($currentdate);

 $dif = $date->diff($finaldate );

 $month = ( $dif->y * 12 ) + $dif->m;
 return $month;
}

$month = difcurrentmonth("2019-01-25");
$available_days= $month * 2;

@Sfili_81 I have a function that calculates the difference in months from the date the user entered until today's date. I edit the question — Maginon, Feb 21 '19 at 07:38
@xerox please show what you have done so that we can assist with direction. — RealSollyM, Feb 21 '19 at 07:39
@Xerox read this answer https://stackoverflow.com/questions/18737407/how-to-create-cron-job-using-php — RainDev, Feb 21 '19 at 07:54
@RainDev that is just 1 half of the problem. @Xerox You need to write an update statement to save the $available_days back into the `user` table. — RealSollyM, Feb 21 '19 at 08:02
The number of days is simply the number of months worked in a given year, times 2, right? — Strawberry, Feb 21 '19 at 08:07
@Strawberry Yes, since the employee enters the company, they are added 2 days per month worked until the end of that same year — Maginon, Feb 21 '19 at 08:43
So all you need to know is the date the employee started, and today's date. There's no point stoing anything else. — Strawberry, Feb 21 '19 at 11:07
@Strawberry Today's date is needed to go calculating the difference of months and increase 2 days if a month has passed — Maginon, Feb 21 '19 at 11:11

score 0 · Answer 1 · answered Feb 21 '19 at 11:28

Not quite an answer; too long for a comment...

-- Today is the 21st February 2019.

-- A temporary employee started work on 15th August 2018.

-- Accrued holiday resets on December 31st.

SELECT GREATEST('2018-08-15','2019-01-01') range_start
     , '2019-02-21' range_end
     , DATEDIFF('2019-02-21',GREATEST('2018-08-15','2019-01-01')) duration
     , ROUND(DATEDIFF('2019-02-21',GREATEST('2018-08-15','2019-01-01'))/30,0) accrued;
+-------------+------------+----------+---------+
| range_start | range_end  | duration | accrued |
+-------------+------------+----------+---------+
| 2019-01-01  | 2019-02-21 |       51 |       2 |
+-------------+------------+----------+---------+

In your scenario, '2018-08-15' would be replaced by a column from your database.

score -2 · Answer 2 · answered Feb 21 '19 at 08:12

-2

Firstly you will need to write a query that will get the available_days from the User table for user1.

I will assume you are using PDO for this example.

$stmt = $pdo_conn->prepare("UPDATE user SET available_days = available_days + 2 where [name] = :userName");
$stmt->bindParam(':userName', "user1");
$stmt->execute();

Create a cron job that runs on a certain day of the month. You might want to add the last_modified_date column to use as a safe catch to prevent updating the user multiple times within a given month.

answered Feb 21 '19 at 08:12

RealSollyM

1,530
1
22
35

The reason I went with this is because, even though I realise that the OP doesn't say anything about `used_leave_days`, one cannot just accumulate leave days without using them. What will happen if an employee has been with the company for 12 months but have taken 4 leave days? – RealSollyM Feb 26 '19 at 14:41

Increase days automatically in database

2 Answers2