(0010051)
|
webuddha
|
2013-06-22 10:48
(edited on: 2013-06-24 13:58) |
|
The total score is a value from 0..100, calculated based on the following weights.
70% = DateScore
10% = ActivityScore
10% = PriorityScore
10% = CompleteScore
IF(`action`.`action_complete`=100
,0
,(
((IF( `action`.`date_finish` = CURDATE()
, 0.25
, IF(
`action`.`date_finish` > CURDATE()
, IF( (DATEDIFF(`action`.`date_finish`,CURDATE())>365), 0, 0.25 - ((DATEDIFF(`action`.`date_finish`,CURDATE())/365)*.25) )
, IF( (DATEDIFF(CURDATE(),`action`.`date_finish`)>90), 1, 0.25 + ((DATEDIFF(CURDATE(),`action`.`date_finish`)/90)*.75) )
)
)) * 70)
+ ((IF(
(DATEDIFF(NOW(),`action`.`date_activity`)>90)
, 1
, IF(
`action`.`date_activity`='0000-00-00 00:00:00'
, IF(
(DATEDIFF(NOW(),`action`.`date_modified`)>90)
, 1
, (DATEDIFF(NOW(),`action`.`date_modified`)/90)
)
, (DATEDIFF(NOW(),`action`.`date_activity`)/90)
)
)) * 10)
+ ((((`action`.`action_priority` - 1) / 2)) * 10)
+ ((IF( `action`.`date_finish` <= CURDATE()
, 1
, IF( ((DATEDIFF(`action`.`date_finish`,CURDATE()) / (DATEDIFF(`action`.`date_finish`,`action`.`date_start`)+1)) > (100 - `action`.`action_complete`))
, ( (100 - `action`.`action_complete`) / (DATEDIFF(`action`.`date_finish`,CURDATE()) / (DATEDIFF(`action`.`date_finish`,`action`.`date_start`)+1)) )
, 0
)
)) * 10)
)
) AS `calc_actionScore`
|
|