1 reply to this topic

[newphpcoder]

Hi...

I have query in highlighting null data using this code:

<?php


include 'config.php';


$currentEmpID = $_SESSION['empID']; 

 

 if(!isset($_POST['Regsubmit_'])){ 

     

    $DATE1 = $_GET['Regfirstinput'];

    $DATE2   = $_GET['Regsecondinput'];

 

 $sql = "SELECT DISTINCT IF(ISNULL(a.LOG_IN), 'rdc', '') AS LOGIN_CLASS, IF(ISNULL(a.LOG_OUT), 'rdc', '') AS LOGOUT_CLASS, a.EMP_ID, CONCAT(LASTNAME, ', ' , FIRSTNAME) AS FULLNAME, a.LOG_IN, a.LOG_OUT

 FROM $ATTENDANCE.attendance_build AS a JOIN $ADODB_DB.employment em ON (a.EMP_ID = em.EMP_NO AND em.STATUS IN ('Reg Operatives', 'Reg Staff')) 

 WHERE LOG_IN BETWEEN '$DATE1' AND '$DATE2' 

 OR ISNULL(LOG_IN) OR ISNULL(LOG_OUT)";

 

    $DTR = $conn3->GetAll($sql);

    $smarty->assign('attendance', $DTR);

 }

 $smarty->display('header_att.tpl');

 $smarty->display('RegAttendance.tpl');

 $smarty->display('footer.tpl');

?> 

and here is the tpl code:

{section name=att loop=$attendance}

  <tr>

    <td colspan="2">{$attendance[att].EMP_ID}</td>

    <td colspan="2">{$attendance[att].FULLNAME}</td>  

    <td colspan="2" class="{$attendance[att].LOGIN_CLASS}">{$attendance[att].LOG_IN|date_format:"%d-%m-%Y %I:%M %p"}</td>

    <td colspan="2" class="{$attendance[att].LOGOUT_CLASS}">{$attendance[att].LOG_OUT|date_format:"%d-%m-%Y %I:%M %p"}</td> 

  </tr>

{sectionelse}

  <tr><td colspan="1">No DATA</td></tr>

{/section}

this code highlight the null value of login or logout or both.

this is the css:

.rdc {background-color:#ff0000;}

Now, I need to revised my query statement, because i have separate code for adding attendance if the employee has no attendance or no login or no logout.

I just want to happen is if the employee is already add his attendance in NRS table or should I said if the LOG_IN in attendance table is equal to TIME_IN in NRS table the data will have a color yellow.

For Example:

I have this data in attendance table:

EMP_ID = 012012
LOG_IN = NULL
LOG_OUT = 2011-12-12 13:35:00

I will his attendance in NRS table to have his attendance:

EMP_NO = 012012
TIME_IN = 2011-12-12 05:35:00
TIME_OUT = 2011-12-12 13:35:00



In my above query the LOG_IN has a background color of RED.

I want to happen is if I add his attendance in NRS the EMP_NO, LOG_IN, LOGOUT will have a color to notice that it is already have in NRS.

Because theirs a scenario that the employee has no login or no logout or both.


Feel free to ask me if my explanation is not clear to you.

Thank you in advance

[mcgeemp]

Assuming you are using MySQL I went through and corrected that... I have worked with Oracle and MySQL and not seen if statements like you did, instead you use CASE statements.

Also DISTINCT is typically used for the first column called and typically a unique identifier (i.e. user ID, username, etc.).

SELECT

	/*Distinct only applies to the first specific column in which you are declaring it.  This is not necessilarly the case in which you would want to, generally on an ID field... */

	CASE WHEN a.LOG_IN IS NULL THEN 'rdc' ELSE '' END AS LOGIN_CLASS, /*typically for the 'else' in this case you would use ELSE NULL but if you specificially need it to be '' you can go with that too.*/

	CASE WHEN a.LOG_OUT IS NULL THEN 'rdc' ELSE '' END AS LOGOUT_CLASS, /*same rule applies as above*/

	a.EMP_ID,

	CONCAT(LASTNAME,CONCAT(', ',FIRSTNAME)) AS FULLNAME, /*assuming MYSQL here so corrected to allow for MYSQL as it only allows for 2 variables in CONCAT.*/

	a.LOG_IN,

	a.LOG_OUT

FROM

	$ATTENDANCE.attendance_build AS a /*unsure of use of $ here*/

JOIN

	$ADOB_DB.employment em 

ON	(a.EMP_ID = em.EMP_NO AND em.STATUS IN ('Reg Operatives', 'Reg Staff'))

WHERE	

	LOGIN_IN BETWEEN '$DATE1' AND '$DATE2'

OR	(LOGIN_IN IS NULL OR LOG_OUT IS NULL)