display and insert expected date of release on submit PHP MYSQL

[RascelleGrepo]

Hello guys, I am on my revision of my thesis and I am actually stuck on this thing. My thesis is Online Requisition of Document and the panel told me that when the student submits his/her request, the expected date or tentative date release of document will automatically display or popped out on the screen. The expected date of release will be inserted on the database.

Now my problem is, I don't know how to do that. I don't find any reference about it. And the date of releasing should also depend on the working days of the university registrar of our school. They have 4 working days (MONDAY to THURSDAY only). So another problem, when the student request on tuesday, the expected date of release is next tuesday. etc.

Please help me. I really dont know how to do it. Thank you.. I really appreciate your help. God Bless.

[gabrielcastillo]

What kind of code are you working with? By release date do you mean when the student submits the document to the online requisition?

[cereal]

So another problem, when the student request on tuesday, the expected date of release is next tuesday. etc.

So, a delay of 4 working days? In MySQL you can create a function based on WEEKDAY() which:

Returns the weekday index for date (0 = Monday, 1 = Tuesday, … 6 = Sunday).

Now, here the code to run in a MySQL client:

drop function if exists releasedate;
delimiter //

CREATE FUNCTION releasedate(date1 DATETIME, ninterval INT UNSIGNED)
RETURNS DATETIME DETERMINISTIC
BEGIN
DECLARE dt DATETIME;
DECLARE i INT UNSIGNED;
DECLARE wd INT UNSIGNED;
SET i = 0;
SET wd = WEEKDAY(date1);
SET dt = date1;
WHILE i < ninterval DO
    SET dt:=DATE_ADD(dt, INTERVAL 1 DAY);
    SET wd:=WEEKDAY(dt);
    CASE WHEN wd in(0,1,2,3) THEN SET i:=i+1;
    ELSE SET i:=i;
    END CASE;
END WHILE;
RETURN dt;
END//

delimiter ;

The WHILE loop will check each date since the starting time and it will add 1 to an internal counter each time the new date is a weekday between 0 and 3. At each loop it will overwrite the dt variable which saves the new date. The loop finishes when the internal counter hits the second argument of the function. The returned value is defined by:

RETURNS DATETIME DETERMINISTIC

And actually returned by:

RETURN dt;

And you can use it like this:

select releasedate(now(), 4) as release_date;
+---------------------+
| release_date        |
+---------------------+
| 2014-03-03 22:03:16 |
+---------------------+
1 row in set (0.04 sec)

Where the first argument is a datetime value, the second argument is an integer value that sets the delay, in this case four days.

It can be used also in the insert queries, for example:

insert into tableA (document_type, request_date, release_date) values('Document A', now(), releasedate(now(), 4));

Note, this will not consider holidays, to include them you need an external table and use a flow control statement to verify if the weekday of the current date is a working day && an holiday, in this case the counter SET i:=i+1; should not fire.

Docs:

• https://dev.mysql.com/doc/refman/5.6/en/create-function.html
• http://dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html#function_weekday
• https://dev.mysql.com/doc/refman/5.6/en/case.html
• http://dev.mysql.com/doc/refman/5.6/en/while.html

Hope I've understood your issue, bye!