PDA

View Full Version : Need help in ON DUPLICATE KEY with Time Involve



newphpbees
23 Dec 2011, 02:39 AM
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:


<?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('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');
?>