Sum data from coumn

Begbie · November 2, 2009

Hello, I am new at this forum and basicly I am new to php. Now I am stuck with the problem. I hope I will

explain well my problem and also I hope that someone coul help me how to solve this problem .

Ok. I made DB with some informations about employees. On that DB I colud not store all information that

I need to list so I had to calculate them. As you can see on this link http://tinyurl.com/yaa4msp

I have two tables where on the first one are information about employee.

On the second table are information for every day , and every shift.

I created function getTableForUser , where I am getting llinformation for one user depending on his id and

I created function getTableForChef , where I am getting infomation for every day and every shift.

So far everything worked ok, but now I need to store in table that I get from function getTableForChef

one information that I should calculate from function getTableForUser and I don't know how to do that.

In tabel from function getTableForUser I manged to calculate, using data from DB, information "Gesamt".

Value for this column is stored in variable $gesamt_gehalt. Basicly this information is earnings from one

employee on one date and on one shift.

Now I need to calculate for all employees their earnings and to insert that sum in table from function

getTableForChef in column "Gehalt".

So example for this would be:

First table (getTableForUser)

----------------------------------------

Employee Date Shift Gesamt(earning)

Worker1 14.10.2009 V 41.94

Worker1 14.10.2009 A 99.32

Worker1 23.10.2009 V 240.6

Worker1 23.10.2009 A 240.6

Employee Date Shift Gesamt(earning)

Worker2 23.10.2009 V 174.45

Worker2 23.10.2009 A 312.66

Second table (getTableForChef)

---------------------------------------------

Date Shift Gehalt(sum earning)

14.10.2009 V 41.94

14.10.2009 A 99.32

23.10.2009 V 415,05

23.10.2009 A 553,26

I know that I am asking too much but please if someone could help me I would be very greateful.

Here is the code and thanks in advance.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1" />

<title>Problem</title>

<link rel="stylesheet" type="text/css" href="../../CSS/main.css" />
<link rel="stylesheet" type="text/css" href="../../CSS/inputform.css" />
<?php
include_once ("../auth.php");
include_once ("../authconfig.php");
include_once ("../check.php");

include_once ("functions.php");

?>
<script type="text/javascript">

</script>
</head>
<body>
<?php
		$datum=$_POST['chosenDate']; 	//First date - von
		$datum2=$_POST['chosenDate2'];	//Second date - bis
		$vonDatum=strtotime(date($datum,time())); 	//First date - von transformed into timestamp
		$bisDatum=strtotime(date($datum2,time()));	//Second date - bis transformed into timestamp

$connection = mysql_connect($dbhost, $dbusername, $dbpass); 
$SelectedDB = mysql_select_db($dbname); 


echo mysql_error();              // if any error is there that will be printed to the screen

function getStand($datum){ //function to collect date from one table =>(stand,fehlend)

$stand = "SELECT stand, fehlend 
		FROM wochen_stand_fehlend
		WHERE wochenr = ".date('W', $datum)." 
		AND jahr = ".date('Y', $datum)."	
		";

$stand_query = mysql_query($stand);
$row_users4 = mysql_fetch_array($stand_query);

return $row_users4;

}

function getTableForChef($vonDatum, $bisDatum){ //IN THIS FUNCTION I HAVE TO INSERT SUM OF DATA THAT I GET FROM FUNCTION getTableForUser
$chef_data="SELECT s.datum, s.schicht, s.bargeld, s.kassiert, s.belege, s.gutschein, s.rechnung, s.sonstiges, s.umsatz 
	FROM schichtumsatz s
	WHERE s.datum BETWEEN ".$vonDatum." AND ".$bisDatum."
	ORDER BY s.datum ASC	
	";

$chef_query = mysql_query($chef_data); 

$chef_table = '<div>'.
'<table>'.
		'<tr>'.
			'<th>Datum</th>'.
			'<th>Schicht</th>'. 
			'<th>Eingegeben</th>'.
			'<th>Kassiert</th>'.
			'<th>Umsatz</th>'.
			'<th>Untersch.Belege</th>'.
			'<th>Gutscheine</th>'.
			'<th>Rechnung</th>'.
			'<th>Sonst</th>'.
			'<th>Trinkgeld</th>'.
			'<th>Provision</th>'.
			'<th>Gehalt</th>'.
			'<th>Bar</th>'.
		'</tr>';

while($row_chef = mysql_fetch_array($chef_query) ){

$eingegeben = $row_chef["bargeld"] + $row_chef["belege"] + $row_chef["gutschein"] + $row_chef["rechnung"] + $row_chef["sonstiges"];
$bar = $eingegeben - $row_chef["gutschein"] - $row_chef["rechnung"] - $row_chef["sonstiges"];


$chef_table .=	

		'<tr>'.
			'<td>'.date('d.m.Y',$row_chef["datum"]).'</td>'. 
			'<td>'.$row_chef["schicht"].'</td>'.
			'<td>'.$eingegeben.'</td>'.
			'<td>'.$row_chef["kassiert"].'</td>'.
			'<td>'.$row_chef["umsatz"].'</td>'.
			'<td>'.$row_chef["belege"].'</td>'.
			'<td>'.$row_chef["gutschein"].'</td>'.
			'<td>'.$row_chef["rechnung"].'</td>'.
			'<td>'.$row_chef["sonstiges"].'</td>'.
			'<td>'.'</td>'.
			'<td>'.'</td>'.
			'<td>'.'</td>'.
			'<td>'.$bar.'</td>'.
		'</tr>';  

}
$chef_table .='</table>'.
'</div>';

return $chef_table;
}

function getTableForUser($id, $vonDatum, $bisDatum){ //IN THIS FUNCTION I GET ALL INFORMATION FROM ONE EMPLOYEE

$user_data="SELECT s.datum, s.schicht, au.stundenlohn, a.von, a.bis,
	s.bargeld, s.belege, s.gutschein, s.rechnung,  s.sonstiges, s.umsatz,
	s.kassiert
	FROM arbeitszeiten a, authuser au, schichtumsatz s 
	WHERE s.datum BETWEEN ".$vonDatum." AND ".$bisDatum."
	AND au.id=".$id." 
	AND a.mitarbeiterid = ".$id."
	AND a.schicht=s.id
	ORDER BY s.datum ASC, schicht DESC
	";

$zeit_summe ="SELECT s.datum, s.schicht, s.bargeld, au.stundenlohn, a.von, a.bis, summe.zeit 
		FROM arbeitszeiten a, authuser au, schichtumsatz s,
			( SELECT s.datum, s.schicht, sum(a.bis - a.von) As zeit
			  FROM arbeitszeiten a, schichtumsatz s 
			  WHERE a.schicht = s.id
			  AND s.datum BETWEEN ".$vonDatum." AND ".$bisDatum."
			  GROUP BY s.datum, s.schicht ) AS summe
		WHERE s.datum BETWEEN ".$vonDatum." AND ".$bisDatum."
		AND au.id=".$id." 
		AND a.mitarbeiterid = ".$id."
		AND a.schicht=s.id
		AND s.datum = summe.datum
		AND s.schicht = summe.schicht
		ORDER BY s.datum ASC, s.schicht DESC
		";



$user_data_query = mysql_query($user_data);
$zeit_summe_query = mysql_query($zeit_summe);


echo mysql_error();  

$user_table='<div name="mitarbeiter" id="mitarbeiter">'.
		'<table id="tabele">'.
			'<tr>'.
				'<th>Datum</th>'. 
				'<th>Von</th>'.  
				'<th>Bis</th>'.  
				'<th>Stunden</th>'.
				'<th>Schicht</th>'.
				'<th>Stundenlohn</th>'.
				'<th>Gehalt</th>'.
				'<th>TG/Stunde</th>'.
				'<th>Trinkgeld</th>'.
				'<th>Provision/Stunde</th>'.
				'<th>Provision</th>'.
				'<th>Gesamt</th>'.
			'</tr>';

while ($row_users2 = mysql_fetch_array($user_data_query)){

$row_users3 = mysql_fetch_array($zeit_summe_query);
//
//
// THIS PART IS CALCULATING PART. HERE I TAKE DATE FROM DB AND CALCULATE THEM 
//
//

$gesamt_zeit = stunden($row_users2["von"], $row_users2["bis"]);
$eingegeben = $row_users2["bargeld"] + $row_users2["belege"] + $row_users2["gutschein"] + $row_users2["rechnung"] + $row_users2["sonstiges"];

$x = 0.5;
$putzgeld = $x * $row_users3['zeit'];

$gehalt = $gesamt_zeit * $row_users2["stundenlohn"];

$row_stand_fehlend = getStand($row_users2["datum"]);
$stand = round($row_stand_fehlend["stand"]/14.,2);
$fehlend = round($row_stand_fehlend["fehlend"]/14.,2);
$tgprost = $eingegeben - $putzgeld - $stand - $fehlend;
$tgprost2 = $tgprost/ $row_users3['zeit'];
$tgprost2 = round($tgprost2,2);
$tg = $gesamt_zeit * $tgprost2;
$tg = round($tg,2);
$diff = $row_users2["umsatz"] - $row_users2["belege"];
$pro = $diff/$row_users3['zeit'];
$pro = round($pro,2);

$test = 52.5;
if($pro>$test){
$provisionprostunde = 0.02*$row_users2["umsatz"]/$row_users3['zeit'];
}else{
$provisionprostunde = 0;
}

$provisionprostunde = round($provisionprostunde,2);
$provision = $provisionprostunde * $gesamt_zeit;
$provision = round($provision,2);


//THIS DATA $gesamt_gehalt IS WHAT I AM TALKING ABOUT

$gesamt_gehalt = $gehalt + $tg + $provision;

//
//
// END OF CALCULATING PART. 
//
//

$user_table	.=
			'<tr>'.
				'<td>'.date('d.m.Y',$row_users2["datum"]).'</td>'. 
				'<td>'.$row_users2["von"].'</td>'.
				'<td>'.$row_users2["bis"].'</td>'.
				'<td>'.$gesamt_zeit.'</td>'.
				'<td>'.$row_users2["schicht"].'</td>'.
				'<td>'.$row_users2["stundenlohn"].'</td>'. 
				'<td>'.$gehalt.'</td>'.
				'<td>'.$tgprost2.'</td>'.
				'<td>'.$tg.'</td>'.
				'<td>'.$provisionprostunde.'</td>'.  
				'<td>'.$provision.'</td>'.
				'<td>'.$gesamt_gehalt.'</td>'.
			'</tr>';

}
$user_table.='</table>'.
	'</div>';
return $user_table;
}

function stunden($von,$bis) {
	//$von = $row["von"];
	//$bis = $row["bis"];
	$stunden = $bis - $von;
	return $stunden;
}
?>
<div id="wrapper">

<div id="back"><a href="../../index2.php">zurück</a> | <a href="../members/POS/calendar_script/index3.php">Datum neu wählen</a></div> 
<div id="logout"><a href="../../logout.php">logout</a></div>
<div class="pos_titel">
	Point of Sale - Lohn  
</div>
<div>
</div>

<?php echo getTableForUser(2, $vonDatum, $bisDatum); ?>
<?php echo getTableForChef($vonDatum, $bisDatum); ?> 

</body>
</html>

philoertel · November 2, 2009

Are you asking how to write the query?

select sum(Gesamt) from Earnings where Date='14.10.2009' and Shift='V'

(I used Earnings because I can't tell what your first table is called) or you could get it all in one go:

select Date, Shift, sum(Gesamt) from Earnings group by Date, Shift

Begbie · November 2, 2009

Basically I need exactly the thing that you have described, but problem is in that, that I don't have this column 'Gesamt' in my DB (I have to calculate it by myself) so I have to solve this problem on some other way.

I hope it is possible by PHP, or if someone knows other way I am ready to hear it Any help is welcomed. And of course thanx

Sign In

Sum data from coumn

Recommended Posts

Begbie

Link to comment

Share on other sites

philoertel

Link to comment

Share on other sites

Begbie

Link to comment

Share on other sites

Archived

Browse

Activity

Important Information