mercredi 28 octobre 2015

How to use IF and ELSE in function select?

This following code will calculate Age based on Gregorian calendar:

  function select($idPatient) 
  {

  $sql .= "FLOOR((TO_DAYS(GREATEST(IF(decease_date='0000-00-00',CURRENT_DATE,decease_date),IFNULL(decease_date,CURRENT_DATE))) - TO_DAYS  (birth_date)) / 365) AS age";


  return ($this->exec($sql) ? $this->numRows() : false);
   }

I've two lines of sql code that I'm gonna use it into above code using IF and ELSE

This is what I want:

If CURRENT_DATE is bigger than 1600 then

$sql .= "FLOOR((TO_DAYS(GREATEST(IF(decease_date='0000-00-00',CURRENT_DATE,decease_date),IFNULL(decease_date,CURRENT_DATE))) - TO_DAYS(birth_date)) / 365) AS age";

If CURRENT_DATE is less than 1600 then

$sql .= "FLOOR((TO_DAYS(GREATEST(IF(decease_date='0000-00-00',CURRENT_DATE,decease_date),IFNULL(decease_date,CURRENT_DATE))) - TO_DAYS(birth_date)) / 365.25) - 621 AS age";

How to make it above function ?

MORE INFORMATION:

This is about difference between Gregorian Calendar and Persian Calendar. This year is 2015 and This year in Persian Calendar is 1394 , difference between these two years is 621 years.

If user will enter birthday in Gregorian calendar for example: 2000, so age is 15 years old, because 2000 is bigger than 1600 so, SQL will use first line of calculation, and if user will enter DOB in Persian Calendar 1379, so his age is 15 yrs old, and because 1379 is less than 1600, SQL will use second line of calculation.

This was sample of problem:

enter image description here

Thank you

Aucun commentaire:

Enregistrer un commentaire