<?php 

	session_cache_expire(1440);
    session_start();
    session_name("insys");
    include "include/config.php";
    include "include/functions.php";

     mysql_query("BEGIN");
    #------------------------------------------------
	$handle = @fopen("d:/PTDMI.SDF", "r");
	if ($handle) {
		while (!feof($handle)) {
			$buffer = fgets($handle, 4096);
            if(trim($buffer)=='') continue;
            $ssql_0="SELECT a.fnid 
                    FROM tlattendance a
                    WHERE a.ftcode='{$buffer}'";
            if(!RetRstField($ssql_0)) {
                $ssql="REPLACE INTO tlattendance(ftcode) VALUES('{$buffer}')";
                mysql_query($ssql) or die("ERROR : ".$ssql);
//                echo $ssql."<br>";
            }
		}
		fclose($handle);
	}
    
    $ssql="UPDATE  tlattendance
                SET
                    ftmesin=mid(ftcode,1,3) ,
                    ftnik=mid(ftcode,4,9) ,
                    fttipe= mid(ftcode,13,1) ,
                    fddate=concat(
                            mid(ftcode,14,4),'-',
                            mid(ftcode,18,2),'-',
                            mid(ftcode,20,2)
                        ) ,
                    fttime =
                        concat(
                            mid(ftcode,22,2),':',
                            mid(ftcode,24,2),':',
                            mid(ftcode,26,2)
                        ),
                   ftcreated_user='{$_SESSION['username']}',
                   fdcreated_date=now() 
                WHERE ftmesin is null
                ";

    mysql_query($ssql);
//     mysql_query("COMMIT");
//     exit;
    #--------------------------------------------------
    $ssql_1="select fnid,ftmesin,ftnik,fttipe,max(fddate) as fddate,max(fttime) as fttime
            from tlattendance
            WHERE ifnull(fnlock,0)=0
            group by ftmesin,ftnik,fttipe
            ORDER BY ftnik asc,fttipe desc";
 
    $rs_1=mysql_query($ssql_1);
//    die("vvv:".mysql_num_rows($rs_1));
    while($r_1=mysql_fetch_assoc($rs_1)) {
       
         $date_1=CDateRev($r_1['fddate']);
         $date_2=$r_1['fddate'];

         if($r_1['fttipe']=='K') {
             #sampai sini
             
              $ssql_2_0="select ftmesin,ftnik,fttipe,max(fddate) as fddate,max(fttime) as fttime
                        from tlattendance
                        where fttipe='M' AND fddate='{$date_2}' AND
                            ftnik='{$r_1['ftnik']}'
                        group by ftmesin,ftnik,fttipe
                        ORDER BY ftnik asc,fttipe desc";
              $ssql_2_1="select ftmesin,ftnik,fttipe,max(fddate) as fddate,max(fttime) as fttime
                        from tlattendance
                        where fttipe='M' AND fddate=date_add('{$date_2}',INTERVAL -1 DAY) AND
                            ftnik='{$r_1['ftnik']}'
                        group by ftmesin,ftnik,fttipe
                        ORDER BY ftnik asc,fttipe desc";
               $ssql_2_2="select ftmesin,ftnik,fttipe,max(fddate) as fddate,max(fttime) as fttime
                        from tlattendance
                        where fttipe='K' AND fddate=date_add('{$date_2}',INTERVAL -1 DAY) AND
                            ftnik='{$r_1['ftnik']}'
                        group by ftmesin,ftnik,fttipe
                        ORDER BY ftnik asc,fttipe desc";
              $ssql_2_3="SELECT date_add('{$date_2}',INTERVAL -1 DAY) AS m";
            if(RetRstField($ssql_2_0)) {
//                 $ftattendance_no="";
//                 $date_2="";
                    
            }
            elseif((RetRstField($ssql_2_1))&&(!RetRstField($ssql_2_2))){                    
                   $date_2=RetRstField($ssql_2_3);
//                     echo "$ssql_2_1"."<br>";
//                    echo "$ssql_2_2"."<br>";
            }
//            echo $date_2."<BR>";
         
           
         }
         else {
              
               
               $ssql_2="SELECT a.fnid
                        FROM
                        txattendance_hdr a
                        INNER JOIN txattendance_hdr b
                        WHERE a.ftattendance_no=b.ftattendance_no AND
                            a.fdattendance_date='{$date_2}' AND
                            a.ftnotes='GENERATE DARI MESIN ABSEN'";
//          pr($ssql_2);
             if(!RetRstField($ssql_2)) {
                    $ftattendance_no=getAutoNumber('hrd001','txattendance_hdr','ftattendance_no',$date_1);
                    $ssql="INSERT INTO txattendance_hdr
                            (
                                ftattendance_no, fdattendance_date, ftdivision_code,
                                ftdepartment_code, ftposition_code,ftgroup_pengajian_code,
                                ftshift_code, ftnotes, fnstatus,
                                ftworking_enter_time,ftdesciption
                             )
                             SELECT
                                '{$ftattendance_no}' AS ftattendance_no,'{$date_2}' AS fdattendance_date, 'SELURUH' AS ftdivision_code,
                                'SELURUH' AS ftdepartment_code, 'SELURUH' AS ftposition_code,'SELURUH' AS ftgroup_pengajian_code,
                                'SELURUH' AS ftshift_code, 'GENERATE DARI MESIN ABSEN' AS ftnotes, '0' AS fnstatus,
                                '' AS ftworking_enter_time,'' AS ftdesciption
                            ";
                    mysql_query($ssql) or die("ERROR : ".$ssql);
                    $strSQL["Insert"][]=$ssql;
             }
         }

//        die("m");
              $ssql_3="SELECT a.ftattendance_no
                        FROM
                        txattendance_hdr a                       
                        WHERE a.fnstatus<>9 AND
                            a.fdattendance_date='{$date_2}' AND
                            a.ftnotes='GENERATE DARI MESIN ABSEN'";
             $ftattendance_no=RetRstField($ssql_3);

             $ssql_4="SELECT ftnik
                            FROM tlattendance_employee
                            WHERE ftreff='{$r_1['ftnik']}'";
             $ftemployee_ftnik=RetRstField($ssql_4);
              
             $ssql_5="SELECT a.fnid
                     FROM txattendance_det a
                     WHERE a.ftattendance_no='{$ftattendance_no}' AND
                            a.ftemployee_code='{$ftemployee_ftnik}'";
              
             $fnindex="";
             $ftemployee_code=$ftemployee_ftnik;
             $ssql_6="SELECT a.ftshift_code
                        FROM tlemployee a
                        WHERE a.ftemployee_code='{$ftemployee_code}'";

             $ftshift_code=RetRstField($ssql_6);

////                  pr($ssql_5);
             if(!RetRstField($ssql_5 )) {
//                  pr("1.".$r_1['fttipe']);
                  if($r_1['fttipe']=='M') {
                        $fnemployee_status="0";
                        $ftabsen_code="";
                        $fdworking_enter_time=$r_1['fttime'];
                        $ftnotes="";
                      $ssql ="INSERT INTO txattendance_det
                            (
                                ftattendance_no, fnindex, ftemployee_code, ftshift_code,
                                fnemployee_status,ftabsen_code, fdworking_enter_time,
                                ftnotes
                            )
                              SELECT     '{$ftattendance_no}' AS  ftattendance_no, '' AS  fnindex,'{$ftemployee_code}' AS   ftemployee_code, '{$ftshift_code}' AS  ftshift_code,
                                '{$fnemployee_status}' AS  fnemployee_status,'{$ftabsen_code}' AS  ftabsen_code, '{$fdworking_enter_time}' AS  fdworking_enter_time,
                                '{$ftnotes}' AS  ftnotes
                           ";
                        mysql_query($ssql) or die("ERROR : ".$ssql);
                        $strSQL["Insert"][]=$ssql;

                      
                       
                  }
                  else {
                         $fnemployee_status="0";
                         $ftabsen_code="";
                         $fdworking_exit_date=$r_1['fddate'];
                         $fdworking_exit_time=$r_1['fttime'];
                         $ftnotes="";
                       $ssql ="INSERT INTO txattendance_det
                            (
                                ftattendance_no, fnindex, ftemployee_code, ftshift_code,
                                fnemployee_status,ftabsen_code,
                                fdworking_exit_date, fdworking_exit_time, 
                                ftnotes
                            )
                              SELECT  '{$ftattendance_no}' AS  ftattendance_no, '{$fnindex}' AS  fnindex,'{$ftemployee_code}' AS   ftemployee_code, '{$ftshift_code}' AS  ftshift_code,
                                '{$fnemployee_status}' AS  fnemployee_status,'{$ftabsen_code}' AS  ftabsen_code, 
                                '{$fdworking_exit_date}' AS  fdworking_exit_date, '{$fdworking_exit_time}' AS  fdworking_exit_time,
                                '{$ftnotes}' AS  ftnotes
                           ";
                        mysql_query($ssql) or die("ERROR : ".$ssql);
                        $strSQL["Insert"][]=$ssql;
                  }
                
             }
             else {

//                  pr("1.".$r_1['fttipe']);
                 if($r_1['fttipe']=='M') {
                        $fnemployee_status="0";
                        $ftabsen_code="";
                        $fdworking_enter_time=$r_1['fttime'];
                        $ftnotes="";
                       $ssql=" UPDATE txattendance_det
                                SET fdworking_enter_time='{$fdworking_enter_time}' 
                                WHERE ftattendance_no='{$ftattendance_no}' AND
                                      ftemployee_code='{$ftemployee_code}'  ";
                        mysql_query($ssql)  or die("ERROR : ".$ssql);
                        $strSQL["Insert"][]=$ssql;
                         
                 }
                 else {
                        $fnemployee_status="0";
                         $ftabsen_code="";
                         $fdworking_exit_date=$r_1['fddate'];
                         $fdworking_exit_time=$r_1['fttime'];
                         $ftnotes="";
                      $ssql=" UPDATE txattendance_det
                                SET fdworking_exit_date='{$fdworking_exit_date}',
                                    fdworking_exit_time='{$fdworking_exit_time}'
                                WHERE ftattendance_no='{$ftattendance_no}' AND
                                      ftemployee_code='{$ftemployee_code}'  ";
                        mysql_query($ssql) or die("ERROR : ".$ssql);
                        $strSQL["Insert"][]=$ssql;
                 }
             }
             
               $ssql="update txattendance_hdr a,txattendance_det b
                            set b.ftworking_hours=TIMEDIFF(CONCAT(
                                                                CASE WHEN b.fdworking_enter_time<=b.fdworking_exit_time THEN
                                                                     a.fdattendance_date
                                                                     ELSE
                                                                            DATE_ADD(a.fdattendance_date, INTERVAL 1 DAY)
                                                                            END
                                                                ,' ',b.fdworking_exit_time),DATE_ADD(CONCAT(a.fdattendance_date,' ',b.fdworking_enter_time),INTERVAL 1 HOUR))
                        where a.ftattendance_no=b.ftattendance_no AND
                            a.ftattendance_no='{$ftattendance_no}' AND
                            b.ftemployee_code='{$ftemployee_code}'";
                        mysql_query($ssql) or die("ERROR : ".$ssql);
                        $strSQL["Insert"][]=$ssql;
                        
         
        
    }

     $ssql="UPDATE tlattendance SET fnlock=1 WHERE ifnull(fnlock,0)<>1";
     mysql_query($ssql) or die("ERROR : ".$ssql);
     $strSQL["Insert"][]=$ssql;

//         pr($strSQL);
         mysql_query("COMMIT");
         
    
   
       
                             
?>

