Sum coumn data - Please Help

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Begbie
Forum Newbie
Posts: 2
Joined: Mon Nov 02, 2009 10:03 am

Sum coumn data - Please Help

Post by Begbie »

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.

Code: Select all

 
<!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&uuml;ck</a>&nbsp;|&nbsp;<a href="../members/POS/calendar_script/index3.php">Datum neu w&auml;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>
User avatar
akuji36
Forum Contributor
Posts: 190
Joined: Tue Oct 14, 2008 9:53 am
Location: Hartford, Connecticut

Re: Sum coumn data - Please Help

Post by akuji36 »

Hello

Take a look at this:

http://www.tizag.com/mysqlTutorial/mysqlsum.php

It uses mysql sum function to collect values and totals them.

thanks

Rod
Post Reply