2 Replies - 1449 Views - Last Post: 12 August 2010 - 04:51 AM Rate Topic: -----

#1 Guest_priyankamalusare*


Reputation:

How to retrieve the output of PHP file in CSV format

Posted 12 August 2010 - 02:32 AM

How to retrive the output of PHP file in CSV format as it is.

<?
   
 session_start(); 
	if(!(isset($_SESSION['UserName'])) || !(isset($_SESSION['UserRole']))  || !(isset($_SESSION['EmpId']))) 
	{
		echo "<script>alert('Loggedin User Roles/UserName Is Not Set IN Session. Login Again Or Contact Admin.');</script>";
		echo '<script language="Javascript">window.open("Index.php","_self");</script>';
 
 
    }
    else
    {
		$Username = trim($_SESSION['UserName']);
      	$EmpId = trim($_SESSION['EmpId']);
	}
	@include("UserAttendaceReportFromToDate.php");
 	@include("forward.php");

	$SelectedFromDate = explode(' ', trim($_POST['AttendanceFromDate']));
	$AttendanceFromDate = $SelectedFromDate[0];
    	$SelectedToDate = explode(' ', trim($_POST['AttendanceToDate']));
 	$AttendanceToDate = $SelectedToDate[0];
	$conn=mysql_connect("localhost","root","Wamp-2010") or die("Could Not Connect");
 	mysql_select_db("emp_attendance",$conn);
	
	$resultTeamList=0;
 	$SQLTeamList="Select DISTINCT TeamName FROM UserDetails ORDER BY TeamName";
	$resultTeamList=mysql_query($SQLTeamList) or die(mysql_error());
	$resultEmpid=0; 
   //query for EmpId 
	$SQLEmpId="SELECT DISTINCT EmpId FROM UserDetails WHERE UserName ='$UserName'";
	$resultEmpId=mysql_query($SQLEmpId) or die(mysql_error());
	$TemEmpId ="";
   	$TotalEmployees = 0;
	$TempDateCount = 0;
	if(mysql_num_rows($resultTeamList) > 0)
	{
		//Get The Table Header
		//Get all The Date falling between the From To Date
 
 

    if(mysql_num_rows($resultEmpId) > 0)
    {
		$resultDateList=0;
      	$SQLDateList="Select DISTINCT Date FROM Attendance Where Date Between '$AttendanceFromDate' AND '$AttendanceToDate' ORDER BY Date";
        $resultDateList=mysql_query($SQLDateList) or die(mysql_error());
	 
      	if(mysql_num_rows($resultDateList) > 0)
       	{
			echo "<face='Arial'><table border='1' align='left' bordercolor='black'><tr align='center' style='writing-mode: tb; font-family:verdana; font-size: 70%;'> <th>EMPLOYEE ID</th> <th>NAME</th> ";
            //Loop Through each date
			while($rowDate = mysql_fetch_array($resultDateList))
			{
				echo "<th style='writing-mode: tb-rl; filter: flipv fliph ; border:1px solid black'>" . trim($rowDate['Date']) . "</th>";  
				$TempDateCount = $TempDateCount + 1;   
			}
			//Insert the default Attendance Columns
			echo "<th style='background-color:FFFFFF'>PR</th>";
            echo "<th style='background-color:CCCCCC'>AB</th>";
            echo "<th style='background-color:CCCCFF'>PL</th>";
            echo "<th style='background-color:99CCFF'>SL</th>";
            echo "<th style='background-color:FFCCFF'>CO</th>";
            echo "<th style='background-color:99FFFF'>CL</th>";
            echo "<th style='background-color:Aqua'>HO</th>";
            echo "<th>TOTAL</th>";
            echo "</tr>";
			$TempDateCount = $TempDateCount + 11;
			$TemTeamName = "";
			$TemEmpId = "";
			//LOOP Through each Team
			while($rowTeam = mysql_fetch_array($resultTeamList))
			{               
				$TemTeamName = trim($rowTeam['TeamName']);  
				echo "<tr style='background-color:#FFCC66'><td align='left' style='writing-mode: tb; font-family:verdana; font-size: 70%;' colspan='" . $TempDateCount . "'><b>" . $TemTeamName ."</b></td></tr>";
				//Loop each Employees in the team
				$SQLEmpList="Select DISTINCT EmpId,EmpName FROM UserDetails Where TeamName = '$TemTeamName' ORDER BY EmpId";  
        			$resultEmpList=mysql_query($SQLEmpList) or die(mysql_error());
				$TemEmpId ="";
				//Loop through each employee  
				while($row = mysql_fetch_array($resultEmpList))
				{                      
					$TemEmpId = trim($row['EmpId']); 
					$TotalEmployees = $TotalEmployees + 1;
					echo "<tr><td align='left' style='writing-mode: tb; font-family:verdana; font-size: 70%;'>" . trim($TemEmpId) . "</td>";
					echo "<td align='left' style='writing-mode: tb; font-family:verdana; font-size: 70%;'>" . trim($row['EmpName']) . "</td>";
					//Get all The Date falling between the From To Date
					$resultDateList=0;
					$SQLDateList="Select DISTINCT Date FROM Attendance Where Date Between '$AttendanceFromDate' AND '$AttendanceToDate' ORDER BY Date";
					$resultDateList=mysql_query($SQLDateList) or die(mysql_error());
            				$TemDate ="";
					//Loop Through each date
					while($rowDate = mysql_fetch_array($resultDateList))
					{
						$TemDate = trim($rowDate['Date']);
						//Get the attendace for the date and the employee
						$resultAttendance=0;
						$SQLAttendance="SELECT * FROM Attendance WHERE EmpId = '$TemEmpId' AND Date = '$TemDate'";
						$resultAttendance=mysql_query($SQLAttendance) or die(mysql_error());
						if(mysql_num_rows($resultAttendance) > 0)
						{
							while($rowAttendance = mysql_fetch_array($resultAttendance))
							{
								echo "<td align='center' style='writing-mode: tb; font-family:verdana; font-size: 70%;'>" . trim($rowAttendance['MarkType']) . "</td>";
							}
						}
						else	
						{
							echo "<td align='center' style='writing-mode: tb; font-family:verdana; font-size: 70%;'>X</td>";
						}
					}
					//Now add the Totals values
					$resultAttendanceTotals=0;
					$SQLAttendanceTotals="SELECT
					Count_Totals.EmpId,
					CASE WHEN Count_Present.Totals_Present IS NULL THEN 0 ELSE Count_Present.Totals_Present END AS Totals_Present,
					CASE WHEN Count_PublicHoliday.Totals_PublicHoliday IS NULL THEN 0 ELSE Count_PublicHoliday.Totals_PublicHoliday END AS Totals_PublicHoliday,
					CASE WHEN Count_ApprovedLeave.Totals_ApprovedLeave IS NULL THEN 0 ELSE Count_ApprovedLeave.Totals_ApprovedLeave END AS Totals_ApprovedLeave,
					CASE WHEN Count_SickLeave.Totals_SickLeave IS NULL THEN 0 ELSE Count_SickLeave.Totals_SickLeave END AS Totals_SickLeave,
					CASE WHEN Count_OtherAbsence.Totals_OtherAbsence IS NULL THEN 0 ELSE Count_OtherAbsence.Totals_OtherAbsence END AS Totals_OtherAbsence, 
					CASE WHEN Count_Leave.Totals_Leave IS NULL THEN 0 ELSE Count_Leave.Totals_Leave END AS Totals_Leave,
					CASE WHEN Count_Holiday.Totals_Holiday IS NULL THEN 0 ELSE Count_Holiday.Totals_Holiday END AS Totals_Holiday,
					
					CASE WHEN Count_Attendance.Totals_Attendance IS NULL THEN 0 ELSE Count_Attendance.Totals_Attendance END AS Totals	
					FROM 
					(select EmpId, count(EmpId) As Totals from UserDetails WHERE EmpId = '$TemEmpId' GROUP BY EmpId) Count_Totals
					LEFT JOIN 
					(select EmpId,count(MarkType) As Totals_Present from attendance WHERE MarkType = 'PR' AND EmpId='$TemEmpId' AND Date Between '$AttendanceFromDate' AND '$AttendanceToDate' GROUP BY 
					EmpId) Count_Present ON Count_Totals.EmpId = Count_Present.EmpId
					LEFT JOIN 
					(select EmpId,count(MarkType) As Totals_PublicHoliday from attendance WHERE MarkType = 'AB' AND EmpId='$TemEmpId' AND Date Between '$AttendanceFromDate' AND '$AttendanceToDate' 
					GROUP BY EmpId) Count_PublicHoliday  ON Count_Totals.EmpId = Count_PublicHoliday.EmpId
					LEFT JOIN 
					(select EmpId,count(MarkType) As Totals_ApprovedLeave from attendance WHERE MarkType = 'PL' AND EmpId='$TemEmpId' AND Date Between '$AttendanceFromDate' AND '$AttendanceToDate' 
					GROUP BY EmpId) Count_ApprovedLeave  ON Count_Totals.EmpId = Count_ApprovedLeave.EmpId
					LEFT JOIN 
					(select EmpId,count(MarkType) As Totals_SickLeave from attendance WHERE MarkType = 'SL' AND EmpId='$TemEmpId' AND Date Between '$AttendanceFromDate' AND '$AttendanceToDate' GROUP 
					BY EmpId) Count_SickLeave ON Count_Totals.EmpId = Count_SickLeave.EmpId
					LEFT JOIN 
					(select EmpId,count(MarkType) As Totals_OtherAbsence from attendance WHERE MarkType = 'CO' AND EmpId='$TemEmpId' AND Date Between '$AttendanceFromDate' AND '$AttendanceToDate' 
					GROUP BY EmpId) Count_OtherAbsence ON Count_Totals.EmpId = Count_OtherAbsence.EmpId
					LEFT JOIN 
					(select EmpId,count(MarkType) As Totals_Leave from attendance WHERE MarkType = 'CL' AND EmpId='$TemEmpId' AND Date Between '$AttendanceFromDate' AND '$AttendanceToDate' GROUP BY EmpId) 
					Count_Leave ON Count_Totals.EmpId = Count_Leave.EmpId
					LEFT JOIN 
					(select EmpId,count(MarkType) As Totals_Holiday from attendance WHERE MarkType = 'HO' AND EmpId='$TemEmpId' AND Date Between '$AttendanceFromDate' AND '$AttendanceToDate' GROUP BY EmpId)
					Count_Holiday ON Count_Totals.EmpId = Count_Holiday.EmpId
					
					LEFT JOIN 
					(select EmpId,count(MarkType) As Totals_Attendance from attendance WHERE EmpId='$TemEmpId' AND Date Between '$AttendanceFromDate' AND '$AttendanceToDate' GROUP BY EmpId) Count_Attendance 
					ON Count_Totals.EmpId = Count_Attendance.EmpId";
					$resultAttendanceTotals=mysql_query($SQLAttendanceTotals) or die(mysql_error());
					if(mysql_num_rows($resultAttendanceTotals) > 0)
					{
						while($rowAttendanceTotals = mysql_fetch_array($resultAttendanceTotals))
						{
							echo "<td align='center' style='writing-mode: tb; font-family:verdana; font-size: 70%;'>" . trim($rowAttendanceTotals['Totals_Present']) . "</td>";
							echo "<td align='center' style='writing-mode: tb; font-family:verdana; font-size: 70%;' >" . trim($rowAttendanceTotals['Totals_PublicHoliday']) . "</td>";
							echo "<td align='center' style='writing-mode: tb; font-family:verdana; font-size: 70%;'>" . trim($rowAttendanceTotals['Totals_ApprovedLeave']) . "</td>";
							echo "<td align='center' style='writing-mode: tb; font-family:verdana; font-size: 70%;'>" . trim($rowAttendanceTotals['Totals_SickLeave']) . "</td>";
							echo "<td align='center' style='writing-mode: tb; font-family:verdana; font-size: 70%;'>" . trim($rowAttendanceTotals['Totals_OtherAbsence']) . "</td>";
							echo "<td align='center' style='writing-mode: tb; font-family:verdana; font-size: 70%;'>" . trim($rowAttendanceTotals['Totals_Leave']) . "</td>";
							echo "<td align='center' style='writing-mode: tb; font-family:verdana; font-size: 70%;'>" . trim($rowAttendanceTotals['Totals_Holiday']) . "</td>";
							
							echo "<td align='center' style='writing-mode: tb; font-family:verdana; font-size: 70%;'><b>" . trim($rowAttendanceTotals['Totals']) . "</b></td>";
						}
      
					}
					else
					{
						echo "<td align='center' style='writing-mode: tb; font-family:verdana; font-size: 70%;'>0</td>";
						echo "<td align='center' style='writing-mode: tb; font-family:verdana; font-size: 70%;'>0</td>";   
						echo "<td align='center' style='writing-mode: tb; font-family:verdana; font-size: 70%;'>0</td>";    
						echo "<td align='center' style='writing-mode: tb; font-family:verdana; font-size: 70%;'>0</td>"; 
						echo "<td align='center' style='writing-mode: tb; font-family:verdana; font-size: 70%;'>0</td>"; 
						echo "<td align='center' style='writing-mode: tb; font-family:verdana; font-size: 70%;'>0</td>";
						echo "<td align='center' style='writing-mode: tb; font-family:verdana; font-size: 70%;'>0</td>";
						
						echo "<td align='center' style='writing-mode: tb; font-family:verdana; font-size: 70%;'><b>0</b></td>";
					}  
					echo "</tr>";
				}
			}  //closing bracket while loop EmpId
		}
		//end of the team loop
		//Get The Total Row at the End
		//Get all The Date falling between the From To Date
		$resultDateList=0;
		$SQLDateList="Select DISTINCT Date FROM Attendance Where Date Between '$AttendanceFromDate' AND '$AttendanceToDate' ORDER BY Date";
		$resultDateList=mysql_query($SQLDateList) or die(mysql_error());
		echo "<tr><td colspan='" . $TempDateCount . "'></td></tr>";
		echo "<tr align='left' style='background-color:#66CCCC'><td style='writing-mode: tb; font-family:verdana; font-size: 70%;' colspan='2'><b>". $TotalEmployees . " TEAM MEMBERS</b></td>";
		$TemDate = "";
		//Loop Through each date
		while($rowDate = mysql_fetch_array($resultDateList))
		{
			$TemDate = trim($rowDate['Date']);
			$resultAttendanceTotals=0;
			$SQLAttendanceTotals="SELECT COUNT(*) AS Totals
			FROM UserDetails UD
			INNER JOIN Attendance AT
			ON AT.EmpId = UD.EmpID
			WHERE AT.Date = '$TemDate'";  //Total number of Employees under tht teamleader
			$resultAttendanceTotals=mysql_query($SQLAttendanceTotals) or die(mysql_error());
			if(mysql_num_rows($resultAttendanceTotals) > 0)
			{
				while($rowAttendanceTotals = mysql_fetch_array($resultAttendanceTotals))
				{
					echo "<td align='center' style='writing-mode: tb; font-family:verdana; font-size: 70%;'><b>" . trim($rowAttendanceTotals['Totals']) . "</b></td>";
				}
			}
			else
			{
				echo "<td align='center' style='writing-mode: tb; font-family:verdana; font-size: 70%;'><b>0</b></td>";
			}             
		}
		//Now Add the Grand Total
		$resultAttendanceTotals=0;
		$SQLAttendanceTotals="SELECT
		Count_Totals.TempColumn,
		Count_Totals.Totals  As TotalEmployees,
		CASE WHEN Count_Present.Totals_Present IS NULL THEN 0 ELSE Count_Present.Totals_Present END AS Totals_Present,
		CASE WHEN Count_PublicHoliday.Totals_PublicHoliday IS NULL THEN 0 ELSE Count_PublicHoliday.Totals_PublicHoliday END AS Totals_PublicHoliday,
		CASE WHEN Count_ApprovedLeave.Totals_ApprovedLeave IS NULL THEN 0 ELSE Count_ApprovedLeave.Totals_ApprovedLeave END AS Totals_ApprovedLeave,
		CASE WHEN Count_SickLeave.Totals_SickLeave IS NULL THEN 0 ELSE Count_SickLeave.Totals_SickLeave END AS Totals_SickLeave,
		CASE WHEN Count_OtherAbsence.Totals_OtherAbsence IS NULL THEN 0 ELSE Count_OtherAbsence.Totals_OtherAbsence END AS Totals_OtherAbsence,
		CASE WHEN Count_Leave.Totals_Leave IS NULL THEN 0 ELSE Count_Leave.Totals_Leave END AS Totals_Leave,
		CASE WHEN Count_Holiday.Totals_Holiday IS NULL THEN 0 ELSE Count_Holiday.Totals_Holiday END AS Totals_Holiday,
		
		CASE WHEN Count_Attendance.Totals_Attendance IS NULL THEN 0 ELSE Count_Attendance.Totals_Attendance END AS Totals	
		FROM 
		(select 1 As TempColumn, count(*) As Totals from UserDetails) Count_Totals
		LEFT JOIN 
		(SELECT 1 As TempColumn, COUNT(*) AS Totals_Present FROM UserDetails UD 
		INNER JOIN Attendance AT_Present 
		ON AT_Present.EmpId = UD.EmpId AND AT_Present.MarkType = 'PR' AND AT_Present.Date Between '$AttendanceFromDate' AND '$AttendanceToDate')
		Count_Present ON Count_Totals.TempColumn = Count_Present.TempColumn
		LEFT JOIN 
		(SELECT 1 As TempColumn, COUNT(*) AS Totals_PublicHoliday FROM UserDetails UD 
		INNER JOIN Attendance AT_Present 
		ON AT_Present.EmpId = UD.EmpId AND AT_Present.MarkType = 'AB' AND AT_Present.Date Between '$AttendanceFromDate' AND '$AttendanceToDate')
		Count_PublicHoliday  ON Count_Totals.TempColumn = Count_PublicHoliday.TempColumn
		 LEFT JOIN 
		(SELECT 1 As TempColumn, COUNT(*) AS Totals_ApprovedLeave FROM UserDetails UD 
		INNER JOIN Attendance AT_Present 
		ON AT_Present.EmpId = UD.EmpId AND AT_Present.MarkType = 'PL' AND AT_Present.Date Between '$AttendanceFromDate' AND '$AttendanceToDate')
		Count_ApprovedLeave  ON Count_Totals.TempColumn = Count_ApprovedLeave.TempColumn
		 LEFT JOIN 
		(SELECT 1 As TempColumn, COUNT(*) AS Totals_SickLeave FROM UserDetails UD 
		INNER JOIN Attendance AT_Present 
		ON AT_Present.EmpId = UD.EmpId AND AT_Present.MarkType = 'SL' AND AT_Present.Date Between '$AttendanceFromDate' AND '$AttendanceToDate')
		Count_SickLeave ON Count_Totals.TempColumn = Count_SickLeave.TempColumn
		LEFT JOIN 
		(SELECT 1 As TempColumn, COUNT(*) AS Totals_OtherAbsence FROM UserDetails UD 
		INNER JOIN Attendance AT_Present 
		ON AT_Present.EmpId = UD.EmpId AND AT_Present.MarkType = 'CO' AND AT_Present.Date Between '$AttendanceFromDate' AND '$AttendanceToDate') 
		Count_OtherAbsence ON Count_Totals.TempColumn = Count_OtherAbsence.TempColumn
		LEFT JOIN 
		(SELECT 1 As TempColumn, COUNT(*) AS Totals_Leave FROM UserDetails UD 
		INNER JOIN Attendance AT_Present 
		ON AT_Present.EmpId = UD.EmpId AND AT_Present.MarkType = 'CL' AND AT_Present.Date Between '$AttendanceFromDate' AND '$AttendanceToDate')
		Count_Leave ON Count_Totals.TempColumn = Count_Leave.TempColumn
		LEFT JOIN 
		(SELECT 1 As TempColumn, COUNT(*) AS Totals_Holiday FROM UserDetails UD 
		INNER JOIN Attendance AT_Present 
		ON AT_Present.EmpId = UD.EmpId AND AT_Present.MarkType = 'HO' AND AT_Present.Date Between '$AttendanceFromDate' AND '$AttendanceToDate') 
		Count_Holiday ON Count_Totals.TempColumn = Count_Holiday.TempColumn
		
		LEFT JOIN 
		(SELECT 1 As TempColumn, COUNT(*) AS Totals_Attendance FROM UserDetails UD 
		INNER JOIN Attendance AT_Present 
		ON AT_Present.EmpId = UD.EmpId AND AT_Present.Date Between '$AttendanceFromDate' AND '$AttendanceToDate')
		Count_Attendance ON Count_Totals.TempColumn = Count_Attendance.TempColumn";
		$resultAttendanceTotals=mysql_query($SQLAttendanceTotals) or die(mysql_error());

		if(mysql_num_rows($resultAttendanceTotals) > 0)
		{
			while($rowAttendanceTotals = mysql_fetch_array($resultAttendanceTotals))
			{
			echo "<td align='center' style='writing-mode: tb; font-family:verdana; font-size: 70%;'><b>" . trim($rowAttendanceTotals['Totals_Present']) . "</b></td>";
			echo "<td align='center' style='writing-mode: tb; font-family:verdana; font-size: 70%;'><b>" . trim($rowAttendanceTotals['Totals_PublicHoliday']) . "</b></td>";
			echo "<td align='center' style='writing-mode: tb; font-family:verdana; font-size: 70%;'><b>" . trim($rowAttendanceTotals['Totals_ApprovedLeave']) . "</b></td>";
			echo "<td align='center' style='writing-mode: tb; font-family:verdana; font-size: 70%;'><b>" . trim($rowAttendanceTotals['Totals_SickLeave']) . "</b></td>";
			echo "<td align='center' style='writing-mode: tb; font-family:verdana; font-size: 70%;'><b>" . trim($rowAttendanceTotals['Totals_OtherAbsence']) . "</b></td>";
			echo "<td align='center' style='writing-mode: tb; font-family:verdana; font-size: 70%;'><b>" . trim($rowAttendanceTotals['Totals_Leave']) . "</b></td>";
			echo "<td align='center' style='writing-mode: tb; font-family:verdana; font-size: 70%;'><b>" . trim($rowAttendanceTotals['Totals_Holiday']) . "</b></td>";
			
			echo "<td align='center' style='writing-mode: tb; font-family:verdana; font-size: 70%;'><b>" . trim($rowAttendanceTotals['Totals']) . "</b></td>";
			}
		}
		else
		{
			echo "<td align='center' style='writing-mode: tb; font-family:verdana; font-size: 70%;'><b>0</b></td>";
			echo "<td align='center' style='writing-mode: tb; font-family:verdana; font-size: 70%;'><b>0</b></td>";
			echo "<td align='center' style='writing-mode: tb; font-family:verdana; font-size: 70%;'><b>0</b></td>";
			echo "<td align='center' style='writing-mode: tb; font-family:verdana; font-size: 70%;'><b>0</b></td>";
			echo "<td align='center' style='writing-mode: tb; font-family:verdana; font-size: 70%;'><b>0</b></td>";
			echo "<td align='center' style='writing-mode: tb; font-family:verdana; font-size: 70%;'><b>0</b></td>";
			echo "<td align='center' style='writing-mode: tb; font-family:verdana; font-size: 70%;'><b>0</b></td>";
			
			echo "<td align='center' style='writing-mode: tb; font-family:verdana; font-size: 70%;'><b>0</b></td>";
		} 
		echo "</tr></table>";
   	}
	else
	{
		echo "<script>alert('No Attendance Entered For The Dates Selected!')</script>";
	}
	}
	else
 	{
		echo "<script>alert('No Employee Records Exists In The System!')</script>";
	}
	 
  //  echo $pageContents;
    mysql_close($conn);

?>


This post has been edited by no2pencil: 12 August 2010 - 02:44 AM
Reason for edit:: Corrected code tags


Is This A Good Question/Topic? 0

Replies To: How to retrieve the output of PHP file in CSV format

#2 JackOfAllTrades  Icon User is offline

  • Saucy!
  • member icon

Reputation: 6092
  • View blog
  • Posts: 23,612
  • Joined: 23-August 08

Re: How to retrieve the output of PHP file in CSV format

Posted 12 August 2010 - 04:27 AM

How about instead of writing the data out in HTML, you create a CSV string for each row? Seems logical, eh?
Was This Post Helpful? 0
  • +
  • -

#3 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5901
  • View blog
  • Posts: 12,805
  • Joined: 16-October 07

Re: How to retrieve the output of PHP file in CSV format

Posted 12 August 2010 - 04:51 AM

Brute force, cut and paste all the way, huh? And CSS out the window. If this was clean XHTML you could just transform your output into CSV with some XSL. But let's look at pure PHP options. It's really just a question of how you offer your output. Unfortunately, you don't use anything remotely like functions. The continuous code as one stream of consciousness is very hard to modify.

Let's take a look at this bit:
while($rowAttendanceTotals = mysql_fetch_array($resultAttendanceTotals)) {
	echo "<td align='center' style='writing-mode: tb; font-family:verdana; font-size: 70%;'>" . trim($rowAttendanceTotals['Totals_Present']) . "</td>";
	echo "<td align='center' style='writing-mode: tb; font-family:verdana; font-size: 70%;' >" . trim($rowAttendanceTotals['Totals_PublicHoliday']) . "</td>";
	echo "<td align='center' style='writing-mode: tb; font-family:verdana; font-size: 70%;'>" . trim($rowAttendanceTotals['Totals_ApprovedLeave']) . "</td>";
	echo "<td align='center' style='writing-mode: tb; font-family:verdana; font-size: 70%;'>" . trim($rowAttendanceTotals['Totals_SickLeave']) . "</td>";
	echo "<td align='center' style='writing-mode: tb; font-family:verdana; font-size: 70%;'>" . trim($rowAttendanceTotals['Totals_OtherAbsence']) . "</td>";
	echo "<td align='center' style='writing-mode: tb; font-family:verdana; font-size: 70%;'>" . trim($rowAttendanceTotals['Totals_Leave']) . "</td>";
	echo "<td align='center' style='writing-mode: tb; font-family:verdana; font-size: 70%;'>" . trim($rowAttendanceTotals['Totals_Holiday']) . "</td>";
	
	echo "<td align='center' style='writing-mode: tb; font-family:verdana; font-size: 70%;'><b>" . trim($rowAttendanceTotals['Totals']) . "</b></td>";
}



First, lets clean that up a bit:
function showFieldTR($value, $isBold) {
	echo "<td align='center' style='writing-mode: tb; font-family:verdana; font-size: 70%;'>";
	if ($isBold) { echo "<b>"; }
	echo $value;
	if ($isBold) { echo "</b>"; }
	echo "</td>";
}

function showAttendanceTotalsTR($row) {
	$fields = array('Totals_Present','Totals_PublicHoliday','Totals_ApprovedLeave', 'Totals_SickLeave','Totals_OtherAbsence','Totals_Leave','Totals_Holiday');
	echo "<tr>";
	foreach ($fields as $field) { showFieldTR(trim($row[$field]), false); }
	showFieldTR(trim($row['Totals']), true);
	echo "</tr>";
}

//...
while($rowAttendanceTotals = mysql_fetch_array($resultAttendanceTotals)) {
	showAttendanceTotalsTR($rowAttendanceTotals)
}



What does this buy you, besides lack of repeating code? Well, for CSV, you'd just do this:
function showAttendanceTotalsCSV($row) {
	$fields = array('Totals_Present','Totals_PublicHoliday','Totals_ApprovedLeave', 'Totals_SickLeave','Totals_OtherAbsence','Totals_Leave','Totals_Holiday');
	
	foreach ($fields as $field) { echo trim($row[$field]).','; }
	echo trim($row['Totals']) . "\n";
}

//...
while($rowAttendanceTotals = mysql_fetch_array($resultAttendanceTotals)) {
	showAttendanceTotalsCSV($rowAttendanceTotals)
}




I know you didn't ask about it, but that SQL... ouch. Let's look at one, all cleaned up:
$SQLAttendanceTotals="SELECT
		Count_Totals.EmpId,
		CASE WHEN Count_Present.Totals_Present IS NULL THEN 0 ELSE Count_Present.Totals_Present END AS Totals_Present,
		CASE WHEN Count_PublicHoliday.Totals_PublicHoliday IS NULL THEN 0 ELSE Count_PublicHoliday.Totals_PublicHoliday END AS Totals_PublicHoliday,
		CASE WHEN Count_ApprovedLeave.Totals_ApprovedLeave IS NULL THEN 0 ELSE Count_ApprovedLeave.Totals_ApprovedLeave END AS Totals_ApprovedLeave,
		CASE WHEN Count_SickLeave.Totals_SickLeave IS NULL THEN 0 ELSE Count_SickLeave.Totals_SickLeave END AS Totals_SickLeave,
		CASE WHEN Count_OtherAbsence.Totals_OtherAbsence IS NULL THEN 0 ELSE Count_OtherAbsence.Totals_OtherAbsence END AS Totals_OtherAbsence, 
		CASE WHEN Count_Leave.Totals_Leave IS NULL THEN 0 ELSE Count_Leave.Totals_Leave END AS Totals_Leave,
		CASE WHEN Count_Holiday.Totals_Holiday IS NULL THEN 0 ELSE Count_Holiday.Totals_Holiday END AS Totals_Holiday,
		CASE WHEN Count_Attendance.Totals_Attendance IS NULL THEN 0 ELSE Count_Attendance.Totals_Attendance END AS Totals	
	FROM (
		select EmpId, count(EmpId) As Totals 
			from UserDetails 
			WHERE EmpId = '$TemEmpId' 
			GROUP BY EmpId
		) Count_Totals
		LEFT JOIN (
			select EmpId,count(MarkType) As Totals_Present 
				from attendance 
				WHERE MarkType = 'PR' 
					AND EmpId='$TemEmpId' AND Date Between '$AttendanceFromDate' AND '$AttendanceToDate' 
				GROUP BY EmpId
			) Count_Present 
				ON Count_Totals.EmpId = Count_Present.EmpId
		LEFT JOIN (
			select EmpId,count(MarkType) As Totals_PublicHoliday 
				from attendance
				WHERE MarkType = 'AB' 
					AND EmpId='$TemEmpId' AND Date Between '$AttendanceFromDate' AND '$AttendanceToDate' 
				GROUP BY EmpId
			) Count_PublicHoliday 
				ON Count_Totals.EmpId = Count_PublicHoliday.EmpId
		LEFT JOIN (
			select EmpId,count(MarkType) As Totals_ApprovedLeave 
				from attendance
				WHERE MarkType = 'PL' 
					AND EmpId='$TemEmpId' AND Date Between '$AttendanceFromDate' AND '$AttendanceToDate' 
				GROUP BY EmpId
			) Count_ApprovedLeave  
				ON Count_Totals.EmpId = Count_ApprovedLeave.EmpId
		LEFT JOIN (
			select EmpId,count(MarkType) As Totals_SickLeave 
				from attendance
				WHERE MarkType = 'SL' 
					AND EmpId='$TemEmpId' AND Date Between '$AttendanceFromDate' AND '$AttendanceToDate' 
					GROUP BY EmpId
			) Count_SickLeave 
				ON Count_Totals.EmpId = Count_SickLeave.EmpId
		LEFT JOIN (
			select EmpId,count(MarkType) As Totals_OtherAbsence 
				from attendance 
				WHERE MarkType = 'CO' 
					AND EmpId='$TemEmpId' AND Date Between '$AttendanceFromDate' AND '$AttendanceToDate' 
				GROUP BY EmpId
			) Count_OtherAbsence 
				ON Count_Totals.EmpId = Count_OtherAbsence.EmpId
		LEFT JOIN (
			select EmpId,count(MarkType) As Totals_Leave 
				from attendance
				WHERE MarkType = 'CL' 
					AND EmpId='$TemEmpId' AND Date Between '$AttendanceFromDate' AND '$AttendanceToDate' 
				GROUP BY EmpId
			) Count_Leave 
				ON Count_Totals.EmpId = Count_Leave.EmpId
		LEFT JOIN (
			select EmpId,count(MarkType) As Totals_Holiday 
				from attendance 
				WHERE MarkType = 'HO' 
					AND EmpId='$TemEmpId' AND Date Between '$AttendanceFromDate' AND '$AttendanceToDate'
					GROUP BY EmpId
			) Count_Holiday 
				ON Count_Totals.EmpId = Count_Holiday.EmpId
		LEFT JOIN (
			select EmpId,count(MarkType) As Totals_Attendance 
				from attendance
				WHERE EmpId='$TemEmpId'
					AND Date Between '$AttendanceFromDate' AND '$AttendanceToDate' 
				GROUP BY EmpId
			) Count_Attendance 
				ON Count_Totals.EmpId = Count_Attendance.EmpId
";



Do you see repetition? You goal should alway be to identify and eliminate repetitive patterns. You already know how to use a case statement. Use it to your advantage. Also, look into IFNULL: http://dev.mysql.com...function_ifnull

$SQLAttendanceTotals="SELECT
		Count_Totals.EmpId,
		CASE WHEN IFNULL(AttTotals.Present,0) AS Totals_Present,
		CASE WHEN IFNULL(AttTotals.PublicHoliday,0) AS AS Totals_PublicHoliday,
		CASE WHEN IFNULL(AttTotals.ApprovedLeave,0) AS Totals_ApprovedLeave,
		CASE WHEN IFNULL(AttTotals.SickLeave,0) AS Totals_SickLeave,
		CASE WHEN IFNULL(AttTotals.OtherAbsence,0) AS Totals_OtherAbsence, 
		CASE WHEN IFNULL(AttTotals.Leave,0) AS Totals_Leave,
		CASE WHEN IFNULL(AttTotals.Holiday,0) AS Totals_Holiday,
		CASE WHEN IFNULL(AttTotals.Attendance,0) AS Totals
	FROM (
		select EmpId, count(EmpId) As Totals 
			from UserDetails 
			WHERE EmpId = '$TemEmpId' 
			GROUP BY EmpId
		) Count_Totals
		LEFT JOIN (
			select EmpId, 
					sum(CASE WHEN MarkType = 'PR' THEN 1 ELSE 0 END) AS As Present,
					sum(CASE WHEN MarkType = 'AB' THEN 1 ELSE 0 END) AS As PublicHoliday,
					sum(CASE WHEN MarkType = 'PL' THEN 1 ELSE 0 END) AS As ApprovedLeave,
					sum(CASE WHEN MarkType = 'SL' THEN 1 ELSE 0 END) AS As SickLeave,
					sum(CASE WHEN MarkType = 'CO' THEN 1 ELSE 0 END) AS As OtherAbsence,
					sum(CASE WHEN MarkType = 'CL' THEN 1 ELSE 0 END) AS As Leave,
					count(MarkType) As Attendance
				from attendance 
				WHERE EmpId='$TemEmpId' AND Date Between '$AttendanceFromDate' AND '$AttendanceToDate' 
				GROUP BY EmpId
			) AttTotals
				ON Count_Totals.EmpId = AttTotals.EmpId
				
";



Even this seems moderately redundatnt, since you don't actually use Totals from the other query. You could just go with the one select:
select EmpId, 
		sum(CASE WHEN MarkType = 'PR' THEN 1 ELSE 0 END) AS As Present,
		sum(CASE WHEN MarkType = 'AB' THEN 1 ELSE 0 END) AS As PublicHoliday,
		sum(CASE WHEN MarkType = 'PL' THEN 1 ELSE 0 END) AS As ApprovedLeave,
		sum(CASE WHEN MarkType = 'SL' THEN 1 ELSE 0 END) AS As SickLeave,
		sum(CASE WHEN MarkType = 'CO' THEN 1 ELSE 0 END) AS As OtherAbsence,
		sum(CASE WHEN MarkType = 'CL' THEN 1 ELSE 0 END) AS As Leave,
		count(MarkType) As Attendance
	from attendance 
	WHERE EmpId='$TemEmpId' AND Date Between '$AttendanceFromDate' AND '$AttendanceToDate' 
	GROUP BY EmpId



Hope this helps.
Was This Post Helpful? 1
  • +
  • -

Page 1 of 1