SitePoint Sponsor

User Tag List

Page 4 of 4 FirstFirst 1234
Results 76 to 87 of 87
  1. #76
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    658
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Okay, I will try your suggestion.
    Thank you

  2. #77
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    658
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I tried this code as you suggested:
    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('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
               )WHERE time_to_sec(time(TimeIn) BETWEEN '01:35:01' and '07:00:00')"
    )
               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
               ) WHERE time_to_sec(time(TimeIn) BETWEEN '17:35:01' and '01:35:00')"
    )
               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('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 
               ) WHERE time_to_sec(time(TimeIn) BETWEEN '10:35:01' and '17:35:00 ')"

               or die(
    mysql_error()); 
    the shift for 05:35:00 - 13:35:00 the result is correct, but in 21:35:00- 05:35:00 no output, and in 13:35:00 -21:35:00 theres an wrong output when my timein is: 2011-10-26 13:35:00 timeout: 2011-10-28 20:35:00 rendered output = 09:00 it should be 07:00 because he timeout early, and this another sample with wrong output timein= 2011-10-27 13:25:00 timeout= 2011-10-28 00:35:00 the rendered = 29:00:00 it should be rendered = 08:00

    Thank you

  3. #78
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,217
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    you really, really, really must learn to look at your own code critically

    attention to detail is ~so~ important in working with SQL

    i mean, look at this --
    Code:
    WHERE time_to_sec(time(TimeIn) BETWEEN '01:35:01' and '07:00:00')
    just looking at it, i can see a humoungous logical problem

    please note what is being compared to '01:35:01' and '07:00:00' -- it's time(Timein)

    so you are comparing times, that's great

    and the result, whether time(Timein) is between those two values or not, is either true of false

    then you wrap this true/false expression inside the time_to_sec function??? why??? you can't convert true or false to seconds!!!


    you simply must slow down and concentrate on your own work

    how long have you been struggling with this? i get the feeling you can do it, but you are just too accustomed to asking for help when the slightest little change doesn't work

    i'm going to bow out of this depressingly long thread for a few days, in the hopes that it will encourage you to take the initiative and fix your own problems
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #79
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    658
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I really tried to solved it, but I'm not good in mysql especially with condition, till now I tried to think why does has no output in 21"35:00 - 05:35:00 shift. Until now, I tried my best to solved it, but still I did not solved.

    Thank you so much..

  5. #80
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    658
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I tried to analyzed and I tried to revised my query, but still, I did not resolved my problem.

    Thank you for trying to help me, and I'm sorry if some of your suggestion I did not understand some of your suggestion, Sorry I'm not goog in logic but i tried my best..

    Thank you so much...

  6. #81
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Code:
    WHERE time(TimeIn) BETWEEN '01:35:01' and '07:00:00'

  7. #82
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    658
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I tried your suggested code:

    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());
        }
    }

    $sql "select * from earnings";
    $result =  mysql_query($sql$con);
    if (!
    $result) {
        die(
    mysql_error());
    }
    $total mysql_num_rows($result);
    if (
    $total 0) {
        
    $sql "delete from earnings";
        
    $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 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
               )WHERE time(TimeIn) BETWEEN '01:35:01' and '07:00:00'"
    )
               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
               ) WHERE time(TimeIn) BETWEEN '17:35:01' and '01:35:00'"
    )
               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('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 
               ) WHERE time(TimeIn) BETWEEN '09:35:01' and '17:35:00 '"

               or die(
    mysql_error()); 

      
    $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>
    the output it 05:35:00 - 13:35:00 is correct, but in 13:35:00-21:35:00 some output is correct , and the other is wrong and 21:35:00 - 05:35:00 no rendered output or zero

    I will attach my database.

    Thank you so much...
    Attached Files Attached Files

  8. #83
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    658
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I tried to analyze the query, but still I did not resolved

    Thank you for your help

  9. #84
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,217
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    my advice is, ask your boss to hire someone to help you

    doesn't have to be permanent, doesn't even have to be on site
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #85
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    658
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What's wrong in my code?

    I need to resolved it ..

    I hope you can help me...

    Thank you so much

  11. #86
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    658
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What's wrong in the code that you suggested?

    I need to resolved it because it's so important and yet I did not resolved until today..

    Thank you for your help and support...I really appreciated

  12. #87
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    658
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Until today I am in the process of try and error in my problem in rendered...I tried my best to resolved it, but still I can't...

    It's so important for me to resolved it because its so needed...

    I hope you would not get tired to help and support me..

    Thank you so muchh


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
  •