report generate from database into text file

[preetg]

Hello all
I am trying to do--Attendance of the month march
and i want format for text file is like
employeecode 1 2 3 4 5 6 7 8------30
E01 A P P P p P P P
E02
--
--
--
I m using mysql database. My tables for this is :
for employee code at0310..... for dates m using table mar10
but for employee present and absent i need to print it by coding
but couldnt understand how to do it......plz help me out .. the code dat i m using is not correct.........
dat display date and ecode but ecode is correct like shown above but dates are not like dat and how i dont hw print A and P ....

<?
$myFile = "att.txt";
$conn = mysql_connect("localhost", "root", "root")
or die("Could not connect: ".mysql_error());
mysql_select_db("master",$conn)
or die("Could not select db: " . mysql_error());
$fhandle = fopen($myFile, 'w')
or die("Error opening file.");
$res = mysql_query("SELECT emp_company.ecode, mar10.Date FROM emp_company,mar10 ");
)
while ($row = mysql_fetch_assoc($res))

fwrite ($fhandle, "{$row['ecode']}\t {$row['Date']}\t \r\n");
fclose($fhandle);
echo "Report Generated ";

?>

[as.bhanuprakash]

Can you please give the DB table structures which you are using ?

[preetg]

CREATE TABLE emp_company (
ecode varchar(15) NOT NULL default '',
ename varchar(15) NOT NULL default '',
ecom varchar(15) NOT NULL default '',
edoj date NOT NULL default '0000-00-00',
edol date NOT NULL default '0000-00-00',
edpt varchar(15) NOT NULL default '',
esubdpt varchar(15) NOT NULL default '',
eesicode varchar(15) NOT NULL default '',
epf varchar(17) NOT NULL default '',
ecardno varchar(20) NOT NULL default '',
ebank int(15) NOT NULL default '0',
PRIMARY KEY (ecode)
) TYPE=MyISAM;
TABLE:----mar10
CREATE TABLE mar10 (
Date int(5) NOT NULL default '0'
) TYPE=MyISAM;

[as.bhanuprakash]

How do you get to know if a particular employee was present for a particular working day ? In the table structures mentioned above there is no field which stores that information. Are you retrieving that information from a log file or something ?
Please provide more information about that.

[preetg]

i will get info about present or absent of employee through at0310 table. ecode from employee table and that ecode i will find out into at0310 table whether that ecode is present in at0310(attendance table) table or not whr den further i match dat ecode with date and time into at0310 table............ bez at0310 table having all fields dat download from attendance machine which having daily info about employee's......i hope you understand this...........

[preetg]

this is attendance table----------
CREATE TABLE at0310 (
ecardno varchar(20) NOT NULL default '',
atdate date NOT NULL default '0000-00-00',
attime time NOT NULL default '00:00:00',
IO varchar(10) NOT NULL default '',
serialno int(15) NOT NULL default '0',
cardrd varchar(15) NOT NULL default '',
ecode varchar(15) NOT NULL default ''
) TYPE=MyISAM;

IO is for IN and Out time for employees........

[as.bhanuprakash]

<?php
 
 $dates = array();
 $employee = array();
 $attendance_record = array();
 $getdates = "select * from mar2010 order by Date";
 $dates_resultset = mysql_query($getdates);
 while ($dates_data = mysql_fetch_assoc($dates_resultset)) {
 	$dates[] = $dates_data['date'];
 }
 
 $get_employee = "select * from emp_company";
 $employee_resultset = mysql_query($get_employee);
 while ($employee_data = mysql_fetch_assoc($employee_resultset)) {
 	$get_attendance = "select * from at0310 where ecode=".$employee_data['ecode']." and atdate in (".implode(",",$dates).") order by atdate;";
 	$employee[$employee_data['ecode']] = $employee_data['name'];
 	$attendance_resultset = mysql_query($get_attendance);
 	// Now we build the data into a mutlidimensional array() of format $attendance_record[$empcode][$date]['attendance']
 	while ($attendance_data = mysql_fetch_assoc($attendance_resultset)) {
 		// I dont know if there will a record stored in at0310 table even if the employee is absent.
 		// Hence i assume the following 2 possibilities.
 		// 1. There is no record present in at0310 if the employee is absent.
 		// 2. There is a record present in at3010 even if the employee is absent but the value in field IO is null.
 		
 		// USE any one of the following as applicable.
 		
 		
 		// FOR SCENARIO 1
 		$attendance_record[$employee_data['ecode']][$attendance_data['atdate']]['attendance'] = "P";
 		
 		// FOR SCENARIO 2
 		if ($attendance_data['IO'] != "") {
 			$attendance_record[$employee_data['ecode']][$attendance_data['atdate']]['attendance'] = "P";
 		} else {
 			$attendance_record[$employee_data['ecode']][$attendance_data['atdate']]['attendance'] = "A";
 		}
 	}
 }
 
 $tabledata = "";
 $tabledata .= "Emp code/ Name \t";
 foreach ($dates as $key => $value) {
 	$tabledata .= $value." \t";
 }
 $tabledata .= "\r\n";
 foreach ( $employee as $emp_key => $emp_value ) {
 	$tabledata .= $emp_key."/".$emp_value;
 	foreach ( $dates as $dates_key => $dates_value ) {
 		$tabledata .= "\t";
 		if ( isset($attendance_record[$emp_key][$dates_value]['attendance']) ) {
 			$tabledata .= $attendance_record[$emp_key][$dates_value]['attendance'];
 		} else {
 			$tabledata .= "A";
 		}
 		
 	}
 	$tabledata .= "\r\n";
 }
 
 $file_resource = fopen("attendance_march.txt","w+");
 fwrite($file_resource,$tabledata);
 
?>

I have used the array $attendance_record as multidimensional so that you can store as many information you want.

I hope the above snippet works.

[preetg]

thanks bhanuparkash for reply and getting time for reply............
i hope my prob will solve soon......... i used ur code...
i got these warnings-
Warning: Supplied argument is not a valid MySQL result resource in c:\apache\htdocs\newhr\danihelp.php on line 8

Warning: Supplied argument is not a valid MySQL result resource in c:\apache\htdocs\newhr\danihelp.php on line 13
i try to resolve it but cant........... plz help me out............

[as.bhanuprakash]

I guess you are getting those errors coz i haven't provided the database connection.
Also please make sure you go through the comments.

[preetg]

i already established connection ..... but also warnings

[as.bhanuprakash]

oh sorry, i made a mistake in the query...

$getdates = "select * from mar2010 order by Date";

change that to

$getdates = "select * from mar10 order by Date";

I had given the wrong table name.

[preetg]

m waiting for your reply..........i m very close to the solution.....but some warning msgs stucked me.....

Warning: Supplied argument is not a valid MySQL result resource in c:\apache\htdocs\newhr\danihelp.php on line 20

Warning: Supplied argument is not a valid MySQL result resource in c:\apache\htdocs\newhr\danihelp.php on line 20

Warning: Supplied argument is not a valid MySQL result resource in c:\apache\htdocs\newhr\danihelp.php on line 20

[preetg]

hi dear.............
we hav already made some modifications.......after made all the changes then got these warnings....plz help me out...here is the edited code..........

<?php
mysql_connect("localhost","root","root");
mysql_select_db("master");
$dates = array();
$employee = array();
$attendance_record = array();
$getdates = "select * from mar10 order by Date";
$dates_resultset = mysql_query($getdates);
while ($dates_data = mysql_fetch_assoc($dates_resultset))
{
$dates[] = $dates_data['Date'];
}
$get_employee = "select * from emp_company";
$employee_resultset = mysql_query($get_employee);
while ($employee_data = mysql_fetch_assoc($employee_resultset))
{
$get_attendance = "select * from at0310 where ecode=".$employee_data['ecode']." and atdate in (".implode(",",$dates).") order by atdate" or die(mysql_error());
$employee[$employee_data['ecode']] = $employee_data['ename'];
$attendance_resultset = mysql_query($get_attendance);
while ($attendance_data = mysql_fetch_assoc($attendance_resultset))
{
// FOR SCENARIO 1
$attendance_record[$employee_data['ecode']][$attendance_data['atdate']]['attendance'] = "P";
//if ($attendance_data['IO'] != "")
//{
//$attendance_record[$employee_data['ecode']][$attendance_data['atdate']]['attendance'] = "P";
//}
//else
//{
//$attendance_record[$employee_data['ecode']][$attendance_data['atdate']]['attendance'] = "A";
//}
}
  }
$tabledata = "";
$tabledata .= "Empcode Name \t";
foreach ($dates as $key => $value) {
$tabledata .= $value." \t";
}
$tabledata .= "\r\n";
foreach ( $employee as $emp_key => $emp_value )
{
$tabledata .= $emp_key."/".$emp_value;
foreach ( $dates as $dates_key => $dates_value )
{
$tabledata .= "\t";
if ( isset($attendance_record[$emp_key][$dates_value]['attendance']) ) {
$tabledata .= $attendance_record[$emp_key][$dates_value]['attendance'];
}
else
{
$tabledata .= "A";
 }
   }
$tabledata .= "\r\n";
}
$file_resource = fopen("att.txt","w+");
fwrite($file_resource,$tabledata);
mysql_close();
?>

[as.bhanuprakash]

OK, my bad there is one more thing i went unnoticed in the tables..
In table 'mar10' the field type of 'Date' is int(5) as you have mentioned in your earlier post. So what you have to do is
1. change this to type 'DATE'
OR
2. If you are storing the 'date' details in some other format and want to continue with the same field type, You will have to make changes in the script where you are storing the data into $dates on line

$dates[] = $dates_data['Date'];

Make sure that the value stored in the $dates array() is of format YYYY-mm-dd

One more thing you need to change is replace the query

$get_attendance = "select * from at0310 where ecode=".$employee_data['ecode']." and atdate in (".implode(",",$dates).") order by atdate" or die(mysql_error());

with

$get_attendance = "select * from at0310 where ecode=".$employee_data['ecode']." and atdate in ('".implode("','",$dates)."') order by atdate";

Also in the query that has been assigned to variable $get_attendance , do not add "or die(mysql_error())" ... coz that the place where query is executed.

If you really want to have that, put it after executing the query using mysql_query().

if(!$attendance_resultset) {
die(mysql_error());
}

[preetg]

hey.. you are genius.......... this prob solve........
i take date as a date Datatype and then dat run........ but coding part is done but the format for text file is not looking gud...........
dat is-
empcode/ename 2004-09-09 2004-08-14
but i want 1 2 3 like this .......... is it possible ?

[as.bhanuprakash]

Yes that can be done. You need to use the date() function for that.

To have Day of the month, 2 digits with leading zeros

foreach ($dates as $key => $value) {

$tabledata .= date("d",strtotime($value))." \t";

}

OR
To have Day of the month without leading zeros

foreach ($dates as $key => $value) {

$tabledata .= date("j",strtotime($value))." \t";

}

Check out the following link to know more about the functionality http://in3.php.net/manual/en/function.date.php

[preetg]

i m using php 2.22 may be this function not supported by this version of php...................i used this function but with P or A it display P10 A10........

[harrysngh]

hw we learnt php very wel ... plz tel me i m ver upset

[preetg]

first study w3schools.com

[preetg]

date function is working properly now...........