1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Need help in ON DUPLICATE KEY with Time Involve

Discussion in 'PHP' started by newphpcoder, Dec 23, 2011.

  1. newphpcoder

    newphpcoder New Member

    Joined:
    Sep 24, 2010
    Messages:
    101
    Likes Received:
    0
    Trophy Points:
    0
    Occupation:
    Web Programmer
    Location:
    Philippines
    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');
    ?>
    
     

Share This Page