SitePoint Sponsor

User Tag List

Page 3 of 4 FirstFirst 1234 LastLast
Results 51 to 75 of 87
  1. #51
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,401
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by newphpcoder View Post
    My boss told me that I no need to add field for shift,

    Help me...Thank you so much
    I can't. Ask your boss. If he knows you can do it without that new field, then let him tell you how to do it.

  2. #52
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    657
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I toldl him, but he told me i don't need to add shift because every week employment change their shift

  3. #53
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,401
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by newphpcoder View Post
    I toldl him, but he told me i don't need to add shift because every week employment change their shift
    I understand he said you don't need to add shift. But ask him how to do the query without shift.

  4. #54
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    657
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    he really don't know because he has no knowledge about it

  5. #55
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    657
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I really don't know how to solve it, its urgent

  6. #56
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,401
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    Ok, so he tells you you don't need the shift, but he really has no knowledge about it. Sounds familiar

    If you have to do it without the shift column, you have to decide the limits of the check in times.
    For example:
    - if an employee checks in between 10:35:01 and 17:35:00 then he's working the 13:35 - 21:35 shift
    - if an employee checks in between 17:35:01 and 01:35:00 then he's working the 21:35 - 05:35 shift
    - if an employee checks in between 01:35:01 and 07:00:00 then he's working the 05:35 - 13:35 shift
    - if an employee checks in between 07:00:01 and 10:35:00 then he's working the 08:00 - 17:00 shift

    Once you've decided those limits, then you can implement them in the update query.

  7. #57
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    657
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    Ok, so he tells you you don't need the shift, but he really has no knowledge about it. Sounds familiar

    If you have to do it without the shift column, you have to decide the limits of the check in times.
    For example:
    - if an employee checks in between 10:35:01 and 17:35:00 then he's working the 13:35 - 21:35 shift
    - if an employee checks in between 17:35:01 and 01:35:00 then he's working the 21:35 - 05:35 shift
    - if an employee checks in between 01:35:01 and 07:00:00 then he's working the 05:35 - 13:35 shift
    - if an employee checks in between 07:00:01 and 10:35:00 then he's working the 08:00 - 17:00 shift

    Once you've decided those limits, then you can implement them in the update query.
    Where i can put the limit?Sorry. I'm not familiar with that..

    Thank you so much..

  8. #58
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    by limits, he was referring to the times within which it's obviously one shift and not another

    read his post again

    10:35:01 and 17:35:00 would be examples of the limits for the 13:35 - 21:35 shift

    these limits are your responsibility to decide
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #59
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    657
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    can you give an example coding for that?is it in query?
    sorry, i really don't know how can I code it.

    Thank you so much

  10. #60
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by newphpcoder View Post
    sorry, i really don't know how can I code it.
    come on, try
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #61
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    657
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    can you give me an idea and sample so i can try?

    Thank you

  12. #62
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,401
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    Instead of the 'shift' criteria, you'll now use the 'checkin limits' criteria to decide what rows must be updated in each of the 4 update queries.
    You put the 'shift' criteria in the WHERE clause.

    Guess where you have to put the new criteria?

  13. #63
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    657
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Not yet guess?


    So you mean I will still used my update statement then in i will only change the where clause???

    Thank you

  14. #64
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,401
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by newphpcoder View Post
    So you mean I will still used my update statement then in i will only change the where clause???

    Thank you
    Yes

  15. #65
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    657
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What column shoud i used in my where clause? the timein and timeout???

    Thank you

  16. #66
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by newphpcoder View Post
    What column shoud i used in my where clause? the timein and timeout???
    yes
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  17. #67
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    657
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Okay, I will try later

    I will update you what happen when I tried your suggested solution.

    Thank you so much...

  18. #68
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    657
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You mean I used this where code:
    PHP Code:
    WHERE timein BETWEEN  10:35:01 and 17:35:00  and timeout BETWEEN  10:35:01 and 17:35:00 
    Thank you

  19. #69
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by newphpcoder View Post
    WHERE timein BETWEEN 10:35:01 and 17:35:00 and timeout BETWEEN 10:35:01 and 17:35:00
    sorry, no, not quite

    let's disregard for the moment the fact that you have syntax errors (time values have to be properly delimited with single quotes, just like date values)

    the main problem is logical

    guido suggested this --
    if an employee checks in between 10:35:01 and 17:35:00 then he's working the 13:35 - 21:35 shift
    however, you want ~both~ the timein and timeout between 10:35:01 and 17:35:00

    is that reasonable? does that make logical sense?

    would an employee who is supposed to work the 13:35 - 21:35 shift actually punch out between 10:35:01 and 17:35:00?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  20. #70
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    657
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You mean timeout between 10:35:01 - 17:35:00? yes he can timeout before his real timeout schedule...

    Thank you

  21. #71
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    yeah, we know he can timeout before his real timeout schedule

    what happens if he times in at 13:35 and times out at 21:00 (i.e. 30 min before his timeout schedule)?

    your WHERE clause will exclude him, because you wrote AND timeout BETWEEN 10:35:01 and 17:35:00

    as i said, this is a logical problem, not an sql proiblem
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  22. #72
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    657
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    when he time in at 13:35 and timeout at 21:00 his rendered would be 7:30 because he timeout early.

  23. #73
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    657
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Here is my syntax for importing data from .xml to database and I also the updating and insert data based on the import attendance.

    PHP Code:
    <?php
    $data 
    = array();

    $con mysql_connect("localhost""root","");
    if (!
    $con) { 
      die(
    mysql_error());
    }
    $db mysql_select_db("payroll"$con);
    if (!
    $db) { 
      die(
    mysql_error());
    }

    $sql "select * from attendance";
    $result =  mysql_query($sql$con);
    if (!
    $result) {
        die(
    mysql_error());
    }
    $total mysql_num_rows($result);
    if (
    $total 0) {
        
    $sql "delete from attendance";
        
    $result =  mysql_query($sql$con);
        if (!
    $result) {
            die(
    mysql_error());
        }
    }

    function 
    add_employee($EMP_NO$Date$TimeIn$TimeOut)
      {
          global 
    $data
          
          
    $con mysql_connect("localhost""root","");
          if (!
    $con){ die(mysql_error());}
          
    $db mysql_select_db("payroll"$con);
          if (!
    $db) { 
              die(
    mysql_error());
          }

          
    $EMP_NO $EMP_NO;
          
    $Date $Date;
          
    $Date substr($Date,0,-13);
          
    $TimeIn $TimeIn;
          
    $TimeOut $TimeOut;
      


         
    $sql "INSERT INTO attendance (EMP_NO, DateAtt, TimeIn, TimeOut) VALUES ('$EMP_NO', '$Date', '$TimeIn', '$TimeOut')";
          
    mysql_query($sql$con);
          

          
    $data []= array('EMP_NO' => $EMP_NO'DateAtt' => $Date'TimeIn' => $TimeIn'TimeOut' => $TimeOut);
          
      }
      
      if ( 
    $_FILES['file']['tmp_name'] )
      {
          
    $dom DOMDocument::load$_FILES['file']['tmp_name'] );
             
          
    $rows $dom->getElementsByTagName'Row' );
          global 
    $last_row;
          
    $last_row false;
          
    $first_row true;
          foreach (
    $rows as $row)
          {
              if ( !
    $first_row )
              {
                
                  
    $EMP_NO "";
                  
    $Date "";
                  
    $TimeIn "";
                  
    $TimeOut "";

                  
                  
                  
    $index 1;
                  
    $cells $row->getElementsByTagName'Cell' );
              
                  foreach( 
    $cells as $cell )
                  { 
                      
    $ind $cell->getAttribute'Index' );
                      if ( 
    $ind != null $index $ind;
                      
                      if ( 
    $index == $EMP_NO $cell->nodeValue;
                      if ( 
    $index == $Date $cell->nodeValue;
                      if ( 
    $index == $TimeIn $cell->nodeValue;
                      if ( 
    $index == $TimeOut $cell->nodeValue;
                      
    $index += 1;
                  }

                 if (
    $EMP_NO=='' and $Date=='' and $TimeIn=='' and $TimeOut=='') {
                        
    $last_row true;
                  }      
                  else {

                        
    add_employee($EMP_NO$Date$TimeIn$TimeOut);


                  }      
              }
              if (
    $last_row==true) {
                  
    $first_row true;
              }     
              else {
                  
    $first_row false;
              }
          }
      }
     
     
    //Update Total Hours  
    $result mysql_query("UPDATE attendance SET TotalHours = sec_to_time(unix_timestamp(TimeOut) - unix_timestamp(TimeIn))"
     or die(
    mysql_error());  
     
      
    $result mysql_query("UPDATE attendance SET TotalHours = sec_to_time(unix_timestamp(TimeIn) - unix_timestamp(TimeOut)) WHERE Shift = 1"
     or die(
    mysql_error());  
    //Update Rendered 
        
    $result mysql_query("UPDATE attendance SET Rendered = sec_to_time(time_to_sec('08:00:00') + 
                case 
                  when time_to_sec(time(TimeIn)) < time_to_sec('05:35:00') then 0
                  else time_to_sec('05:35:00') - time_to_sec(time(TimeIn))
                end +
                case 
                  when time_to_sec(time(TimeOut)) > time_to_sec('13:35:00') then 0
                  else time_to_sec(time(TimeOut)) - time_to_sec('13:35:00')
                end
               )"
    )
               or die(
    mysql_error());
       
       
    $result mysql_query("UPDATE attendance SET Rendered = sec_to_time(time_to_sec('08:00:00') + 
                case 
                  when time_to_sec(time(TimeIn)) < time_to_sec('21:35:00') then 0
                  else  time_to_sec('21:35:00') - time_to_sec(time(TimeIn))
                end +
                case 
                    when time_to_sec(time(TimeOut)) > time_to_sec('05:35:00') then 0
                    else time_to_sec('05:35:00') - time_to_sec(time(TimeOut))
                    end
               )"
    )
               or die(
    mysql_error());  
               
     
     
    //Update OT
     
    $result mysql_query("UPDATE attendance SET OT = sec_to_time(time_to_sec(TotalHours) - time_to_sec(Rendered))"
     or die(
    mysql_error());  
     
     
    //Insert Sum of Total hours
      
    $result mysql_query("INSERT INTO earnings(EMP_NO, Hours) SELECT EMP_NO, sec_to_time(SUM(time_to_sec(Rendered))) FROM attendance GROUP BY EMP_NO"
     or die(
    mysql_error()); 
     

      
    ?>
      
      <html>
      <body>
      <table>
      <tr>
          <th>Employee Attendance</th>
      </tr>

      <?php foreach( $data as $row ) { ?>
      <tr>
      <td><?php echo( $row['EMP_NO'] ); ?></td> 
      <td><?php echo( $row['DateAtt'] ); ?></td> 
      <td><?php echo( $row['TimeIn'] ); ?></td>
      <td><?php echo( $row['TimeOut'] ); ?></td>
      </tr>
      <?php ?>
      </table>
      </body>
     </html>
    I have the problem in 21:35:00 - 05:35:00
    like for example:

    timein 2011-10-25 21:25:00
    timeout 2011-10-26 04:35:00
    the output using the query:
    rendered: 09:00:00
    it should be: 07:00:00 because he timeout early...

    and when I add this query for shifts: 13:35:00 - 21:35:00 some output in 05:35:00- 13:35:00 , and 21:35:00-05:35:00 is wrong:
    PHP Code:
    $result mysql_query("UPDATE attendance SET Rendered = sec_to_time(time_to_sec('08:00:00') + 
                 case 
                  when time_to_sec(time(TimeIn)) < time_to_sec('13:35:00') then 0
                  else time_to_sec('13:35:00') - time_to_sec(time(TimeIn))
                end +
                case 
                  when time_to_sec(time(TimeOut)) > time_to_sec('21:35:00') then 0
                  else time_to_sec('21:35:00') - time_to_sec(time(TimeOut))
                end 
               )"

     or die(
    mysql_error()); 

    Thank you

  24. #74
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    657
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I really don't know what would be the solution in my problem in shifts.

    Thank you so much for your help....

  25. #75
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,401
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    In the WHERE clause, check only timein.
    At least, if my logic is what you want. If not, tell us the logic that you want to use to decide what shift the employee is in.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •