Go4Expert

Go4Expert (http://www.go4expert.com/)
-   PHP (http://www.go4expert.com/forums/php/)
-   -   Need help in ON DUPLICATE KEY with Time Involve (http://www.go4expert.com/forums/help-duplicate-key-time-involve-t27424/)

newphpcoder 23Dec2011 14:06

Need help in ON DUPLICATE KEY with Time Involve
 
Hi....
I want to know what syntax should i used for UPDATE or ON DUPLICATE KEY.
Because now, I only have syntax for insert query.,I want to add syntax for update or DUPLICATE key.
here is my code:
Code:

<?php
  include 'config.php';
 
  $currentEmpID = $_SESSION['empID'];
 
  $DATE1 = $_GET['Regfirstinput'];
  $DATE2  = $_GET['Regsecondinput'];
 
    $smarty->assign('DATE1', $DATE1);
    $smarty->assign('DATE2', $DATE2);
  $result = mysql_query("INSERT INTO payroll.reg_att(EMP_NO, DATE_DTR, LOGIN, LOGOUT, TotalHours)
SELECT EMP_NO, DATE(LOGIN), LOGIN, LOGOUT, TIMEDIFF(LOGOUT, LOGIN)  FROM attendance.employee_attendance")
 or die(mysql_error());
 
 
 $result = mysql_query("UPDATE payroll.reg_att SET Rendered =  case
when time_to_sec(time(TotalHours)) <= time_to_sec('03:00:00')
then sec_to_time(time_to_sec('00:00:00'))
when time_to_sec(time(LOGIN)) BETWEEN time_to_sec('20:35:00') AND time_to_sec('21:35:00') AND time_to_sec(time(LOGOUT)) BETWEEN time_to_sec('05:35:00') AND time_to_sec('06:35:00')
then sec_to_time(time_to_sec('08:00:00'))
when time_to_sec(time(LOGIN)) BETWEEN time_to_sec('04:35:00') AND time_to_sec('05:35:00') AND time_to_sec(time(LOGOUT)) BETWEEN time_to_sec('13:35:00') AND time_to_sec('14:35:00')
then sec_to_time(time_to_sec('08:00:00'))
when time_to_sec(time(LOGIN)) BETWEEN time_to_sec('12:35:00') AND time_to_sec('13:35:00') AND time_to_sec(time(LOGOUT)) BETWEEN time_to_sec('21:35:00') AND time_to_sec('22:35:00')
then sec_to_time(time_to_sec('08:00:00'))
when time_to_sec(time(LOGIN)) BETWEEN time_to_sec('06:00:00') AND time_to_sec('07:00:00') AND time_to_sec(time(LOGOUT)) BETWEEN time_to_sec('16:00:00') AND time_to_sec('16:59:00')
then sec_to_time(time_to_sec('08:00:00'))
when time_to_sec(time(LOGIN)) BETWEEN time_to_sec('07:01:00') AND time_to_sec('08:00:00') AND time_to_sec(time(LOGOUT)) BETWEEN time_to_sec('17:00:00') AND time_to_sec('17:59:00')
then sec_to_time(time_to_sec('08:00:00'))
when time_to_sec(time(LOGIN)) BETWEEN time_to_sec('07:01:00') AND time_to_sec('08:00:00') AND time_to_sec(time(LOGOUT)) BETWEEN time_to_sec('18:00:00') AND time_to_sec('18:59:00')
then sec_to_time(time_to_sec('08:00:00'))
when time_to_sec(time(LOGIN)) BETWEEN time_to_sec('21:35:00') AND time_to_sec('22:35:00') AND time_to_sec(time(LOGOUT)) BETWEEN time_to_sec('05:35:00') AND time_to_sec('07:35:00')
then sec_to_time(time_to_sec(concat(substr(LOGIN, 1,10), ' ' , '08:00:00')) -
(time_to_sec(LOGIN) - time_to_sec(concat(substr(LOGIN, 1,10), ' ' , '21:35:00'))))
when time_to_sec(time(LOGIN)) BETWEEN time_to_sec('13:35:00') AND time_to_sec('15:35:00') AND time_to_sec(time(LOGOUT)) BETWEEN time_to_sec('21:35:00') AND time_to_sec('22:35:00')
then sec_to_time(time_to_sec(concat(substr(LOGIN, 1,10), ' ' , '08:00:00')) -
(time_to_sec(LOGIN) - time_to_sec(concat(substr(LOGIN, 1,10), ' ' , '13:35:00'))))
when time_to_sec(time(LOGIN)) BETWEEN time_to_sec('05:35:00') AND time_to_sec('07:35:00') AND time_to_sec(time(LOGOUT)) BETWEEN time_to_sec('13:35:00') AND time_to_sec('15:35:00')
then sec_to_time(time_to_sec(concat(substr(LOGIN, 1,10), ' ' , '08:00:00')) -
(time_to_sec(LOGIN) - time_to_sec(concat(substr(LOGIN, 1,10), ' ' , '05:35:00'))))
when time_to_sec(time(LOGIN)) BETWEEN time_to_sec('07:00:00') AND time_to_sec('07:59:00') AND time_to_sec(time(LOGOUT)) BETWEEN time_to_sec('16:00:00') AND time_to_sec('16:59:00')
then sec_to_time(time_to_sec(concat(substr(LOGIN, 1,10), ' ' , '08:00:00')) -
(time_to_sec(LOGIN) - time_to_sec(concat(substr(LOGIN, 1,10), ' ' , '07:00:00'))))
when time_to_sec(time(LOGIN)) BETWEEN time_to_sec('08:00:00') AND time_to_sec('08:59:00') AND time_to_sec(time(LOGOUT)) BETWEEN time_to_sec('16:00:00') AND time_to_sec('16:59:00')
then sec_to_time(time_to_sec(concat(substr(LOGIN, 1,10), ' ' , '08:00:00')) -
(time_to_sec(LOGIN) - time_to_sec(concat(substr(LOGIN, 1,10), ' ' , '08:00:00'))))
when time_to_sec(time(LOGIN)) BETWEEN time_to_sec('08:00:00') AND time_to_sec('08:59:00') AND time_to_sec(time(LOGOUT)) BETWEEN time_to_sec('17:00:00') AND time_to_sec('17:59:00')
then sec_to_time(time_to_sec(concat(substr(LOGIN, 1,10), ' ' , '08:00:00')) -
(time_to_sec(LOGIN) - time_to_sec(concat(substr(LOGIN, 1,10), ' ' , '08:00:00'))))
when time_to_sec(time(LOGIN)) BETWEEN time_to_sec('08:00:00') AND time_to_sec('08:59:00') AND time_to_sec(time(LOGOUT)) BETWEEN time_to_sec('18:00:00') AND time_to_sec('18:59:00')
then sec_to_time(time_to_sec(concat(substr(LOGIN, 1,10), ' ' , '08:00:00')) -
(time_to_sec(LOGIN) - time_to_sec(concat(substr(LOGIN, 1,10), ' ' , '08:00:00'))))
when time_to_sec(time(LOGIN)) BETWEEN time_to_sec('20:35:00') AND time_to_sec('21:35:00') AND time_to_sec(time(LOGOUT)) BETWEEN time_to_sec('03:35:00') AND time_to_sec('05:35:00')
then sec_to_time(time_to_sec(concat(substr(LOGOUT, 1,10), ' ' , '08:00:00')) +
(time_to_sec(LOGOUT) - time_to_sec(concat(substr(LOGOUT, 1,10), ' ' , '05:35:00'))))
when time_to_sec(time(LOGIN)) BETWEEN time_to_sec('12:35:00') AND time_to_sec('13:35:00') AND time_to_sec(time(LOGOUT)) BETWEEN time_to_sec('18:01:00') AND time_to_sec('21:35:00')
then sec_to_time(time_to_sec(concat(substr(LOGOUT, 1,10), ' ' , '08:00:00')) +
(time_to_sec(LOGOUT) - time_to_sec(concat(substr(LOGOUT, 1,10), ' ' , '21:35:00'))))
when time_to_sec(time(LOGIN)) BETWEEN time_to_sec('04:00:00') AND time_to_sec('05:35:00') AND time_to_sec(time(LOGOUT)) BETWEEN time_to_sec('09:01:00') AND time_to_sec('13:35:00')
then sec_to_time(time_to_sec(concat(substr(LOGOUT, 1,10), ' ' , '08:00:00')) +
(time_to_sec(LOGOUT) - time_to_sec(concat(substr(LOGOUT, 1,10), ' ' , '13:35:00'))))
when time_to_sec(time(LOGIN)) BETWEEN time_to_sec('06:00:00') AND time_to_sec('07:00:00') AND time_to_sec(time(LOGOUT)) BETWEEN time_to_sec('13:01:00') AND time_to_sec('16:00:00')
then sec_to_time(time_to_sec(concat(substr(LOGOUT, 1,10), ' ' , '08:00:00')) +
(time_to_sec(LOGOUT) - time_to_sec(concat(substr(LOGOUT, 1,10), ' ' , '16:00:00'))))
when time_to_sec(time(LOGIN)) BETWEEN time_to_sec('07:01:00') AND time_to_sec('08:00:00') AND time_to_sec(time(LOGOUT)) BETWEEN time_to_sec('13:01:00') AND time_to_sec('16:00:00')
then sec_to_time(time_to_sec(concat(substr(LOGOUT, 1,10), ' ' , '08:00:00')) +
(time_to_sec(LOGOUT) - time_to_sec(concat(substr(LOGOUT, 1,10), ' ' , '16:00:00'))))
when time_to_sec(time(LOGIN)) BETWEEN time_to_sec('07:01:00') AND time_to_sec('08:00:00') AND time_to_sec(time(LOGOUT)) BETWEEN time_to_sec('14:01:00') AND time_to_sec('17:00:00')
then sec_to_time(time_to_sec(concat(substr(LOGOUT, 1,10), ' ' , '08:00:00')) +
(time_to_sec(LOGOUT) - time_to_sec(concat(substr(LOGOUT, 1,10), ' ' , '17:00:00'))))
when time_to_sec(time(LOGIN)) BETWEEN time_to_sec('07:01:00') AND time_to_sec('08:00:00') AND time_to_sec(time(LOGOUT)) BETWEEN time_to_sec('15:01:00') AND time_to_sec('18:00:00')
then sec_to_time(time_to_sec(concat(substr(LOGOUT, 1,10), ' ' , '08:00:00')) +
(time_to_sec(LOGOUT) - time_to_sec(concat(substr(LOGOUT, 1,10), ' ' , '18:00:00'))))
when time_to_sec(time(LOGIN)) BETWEEN time_to_sec('21:35:00') AND time_to_sec('22:35:00') AND time_to_sec(time(LOGOUT)) BETWEEN time_to_sec('03:35:00') AND time_to_sec('05:35:00')
then sec_to_time(time_to_sec(concat(substr(LOGOUT, 1,10), ' ' , '08:00:00')) +
(time_to_sec(LOGOUT) - time_to_sec(concat(substr(LOGOUT, 1,10), ' ' , '05:35:00'))) + time_to_sec(concat(substr(LOGIN, 1,10), ' ' , '08:00:00')) -
(time_to_sec(LOGIN) - time_to_sec(concat(substr(LOGIN, 1,10), ' ' , '21:35:00'))) - time_to_sec('08:00:00'))
when time_to_sec(time(LOGIN)) BETWEEN time_to_sec('13:35:00') AND time_to_sec('14:35:00') AND time_to_sec(time(LOGOUT)) BETWEEN time_to_sec('18:01:00') AND time_to_sec('21:35:00')
then sec_to_time(time_to_sec(concat(substr(LOGOUT, 1,10), ' ' , '08:00:00')) +
(time_to_sec(LOGOUT) - time_to_sec(concat(substr(LOGOUT, 1,10), ' ' , '21:35:00'))) + time_to_sec(concat(substr(LOGIN, 1,10), ' ' , '08:00:00')) -
(time_to_sec(LOGIN) - time_to_sec(concat(substr(LOGIN, 1,10), ' ' , '13:35:00'))) - time_to_sec('08:00:00'))
when time_to_sec(time(LOGIN)) BETWEEN time_to_sec('05:35:00') AND time_to_sec('06:35:00') AND time_to_sec(time(LOGOUT)) BETWEEN time_to_sec('10:01:00') AND time_to_sec('13:35:00')
then sec_to_time(time_to_sec(concat(substr(LOGOUT, 1,10), ' ' , '08:00:00')) +
(time_to_sec(LOGOUT) - time_to_sec(concat(substr(LOGOUT, 1,10), ' ' , '13:35:00'))) + time_to_sec(concat(substr(LOGIN, 1,10), ' ' , '08:00:00')) -
(time_to_sec(LOGIN) - time_to_sec(concat(substr(LOGIN, 1,10), ' ' , '05:35:00'))) - time_to_sec('08:00:00'))
when time_to_sec(time(LOGIN)) BETWEEN time_to_sec('07:00:00') AND time_to_sec('07:59:00') AND time_to_sec(time(LOGOUT)) BETWEEN time_to_sec('12:01:00') AND time_to_sec('16:00:00')
then sec_to_time(time_to_sec(concat(substr(LOGOUT, 1,10), ' ' , '08:00:00')) +
(time_to_sec(LOGOUT) - time_to_sec(concat(substr(LOGOUT, 1,10), ' ' , '16:00:00'))) + time_to_sec(concat(substr(LOGIN, 1,10), ' ' , '08:00:00')) -
(time_to_sec(LOGIN) - time_to_sec(concat(substr(LOGIN, 1,10), ' ' , '07:00:00'))) - time_to_sec('08:00:00'))
when time_to_sec(time(LOGIN)) BETWEEN time_to_sec('08:00:00') AND time_to_sec('08:59:00') AND time_to_sec(time(LOGOUT)) BETWEEN time_to_sec('12:01:00') AND time_to_sec('16:00:00')
then sec_to_time(time_to_sec(concat(substr(LOGOUT, 1,10), ' ' , '08:00:00')) +
(time_to_sec(LOGOUT) - time_to_sec(concat(substr(LOGOUT, 1,10), ' ' , '16:00:00'))) + time_to_sec(concat(substr(LOGIN, 1,10), ' ' , '08:00:00')) -
(time_to_sec(LOGIN) - time_to_sec(concat(substr(LOGIN, 1,10), ' ' , '08:00:00'))) - time_to_sec('08:00:00'))
when time_to_sec(time(LOGIN)) BETWEEN time_to_sec('08:00:00') AND time_to_sec('08:59:00') AND time_to_sec(time(LOGOUT)) BETWEEN time_to_sec('12:01:00') AND time_to_sec('17:00:00')
then sec_to_time(time_to_sec(concat(substr(LOGOUT, 1,10), ' ' , '08:00:00')) +
(time_to_sec(LOGOUT) - time_to_sec(concat(substr(LOGOUT, 1,10), ' ' , '17:00:00'))) + time_to_sec(concat(substr(LOGIN, 1,10), ' ' , '08:00:00')) -
(time_to_sec(LOGIN) - time_to_sec(concat(substr(LOGIN, 1,10), ' ' , '08:00:00'))) - time_to_sec('08:00:00'))
when time_to_sec(time(LOGIN)) BETWEEN time_to_sec('08:00:00') AND time_to_sec('08:59:00') AND time_to_sec(time(LOGOUT)) BETWEEN time_to_sec('12:01:00') AND time_to_sec('18:00:00')
then sec_to_time(time_to_sec(concat(substr(LOGOUT, 1,10), ' ' , '08:00:00')) +
(time_to_sec(LOGOUT) - time_to_sec(concat(substr(LOGOUT, 1,10), ' ' , '18:00:00'))) + time_to_sec(concat(substr(LOGIN, 1,10), ' ' , '08:00:00')) -
(time_to_sec(LOGIN) - time_to_sec(concat(substr(LOGIN, 1,10), ' ' , '08:00:00'))) - time_to_sec('08:00:00'))
END")  or die(mysql_error());     
$result = mysql_query("INSERT INTO payroll.reg_hours(EMP_NO, Hours) SELECT EMP_NO, sec_to_time(SUM(time_to_sec(Rendered))) FROM payroll.reg_att GROUP BY EMP_NO") or die(mysql_error());

$sql = "SELECT MIN(DATE(LOGIN)) AS FDATE, MAX(DATE(LOGIN)) AS LDATE FROM payroll.reg_att";
$rsDate = $conn2->Execute($sql);
$FDATE = $rsDate->fields['FDATE'];
$LDATE = $rsDate->fields['LDATE'];
$smarty->assign('FDATE', $FDATE);
$smarty->assign('LDATE', $LDATE);
 $smarty->display('header_att.tpl');
 $smarty->display('RegAttendance.tpl'); 
 $smarty->display('footer.tpl');
?>



All times are GMT +5.5. The time now is 12:52.