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>
