<?php
function GenerateAbsensi($dept) {
  if($dept=="STAFF") {
        $ftdepartment_code="D01";
        $ftdepartment_code_where="='D01'";
        $ftdepartment_code_notes="GENERATE DARI MESIN ABSEN DEPARTMENT STAFF";
  }
  else {
         $ftdepartment_code="";
         $ftdepartment_code_where="<>'D01'";
         $ftdepartment_code_notes="GENERATE DARI MESIN ABSEN DEPARTMENT NON STAFF";
  }
  #==========================================
    #========ABSENSI===========================
    #==========================================
       $now=date('Y-m_d');

     $ssql_1="select fnid,ftmesin,ftnik,fttipe, max(fddate) as fddate,max(fttime) as fttime
            from tlattendance
            WHERE ftdepartment_code{$ftdepartment_code_where} AND  ifnull(fnlock,0)=0
            AND fddate BETWEEN '2009-01-30' AND '{$now}'
            group by ftmesin,ftnik,fttipe,fddate,fttime
            ORDER BY ftmesin ASC, ftnik ASC, fddate ASC,fttime 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  ftdepartment_code{$ftdepartment_code_where} AND fttipe='M' AND fddate='{$date_2}' AND
                            ftnik='{$r_1['ftnik']}' AND fnlock=0
                        group by ftmesin,ftnik,fttipe,fddate,fttime
                        having max(fttime)<'{$r_1['fttime']}'
                        ORDER BY ftmesin ASC, ftnik ASC, fddate ASC,fttime ASC,fttipe desc";

              $ssql_2_1="select ftmesin,ftnik,fttipe,max(fddate) as fddate,max(fttime) as fttime
                        from tlattendance
                        where  ftdepartment_code{$ftdepartment_code_where} AND fttipe='M'
                        AND fddate=date_add('{$date_2}',INTERVAL -1 DAY) AND
                            ftnik='{$r_1['ftnik']}' AND fnlock=0
                        group by ftmesin,ftnik,fttipe,fddate,fttime
                        ORDER BY ftmesin ASC, ftnik ASC, fddate ASC,fttime ASC,fttipe desc";

               $ssql_2_2="select ftmesin,ftnik,fttipe,max(fddate) as fddate,max(fttime) as fttime
                        from tlattendance
                        where  ftdepartment_code{$ftdepartment_code_where} AND fttipe='K' AND fddate='{$date_2}' AND
                            ftnik='{$r_1['ftnik']}' AND fnlock=0
                        group by ftmesin,ftnik,fttipe
                        ORDER BY ftmesin ASC, ftnik ASC, fddate ASC,fttime ASC,fttipe desc";
              $ssql_2_3="SELECT date_add('{$date_2}',INTERVAL -1 DAY) AS m";

            if(RetRstField($ssql_2_0)) // ada jam masuk dan keluar di tanggal yang sama
            {
                //fungsi tidak dilanjutkan
            }

            elseif((RetRstField($ssql_2_1))&&(RetRstField($ssql_2_2))) // lemburannya beda hari
           {
                   $date_2=RetRstField($ssql_2_3);
                   $date_1=CDateRev($date_2);

            }


         }
         else {
               $ssql_2="SELECT a.fnid
                        FROM
                        txattendance_hdr a
                        WHERE
                            a.fdattendance_date='{$date_2}' AND a.fnstatus<>9 AND
                            a.ftnotes='{$ftdepartment_code_notes}'";

             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, '{$ftdepartment_code_notes}' AS ftnotes, '0' AS fnstatus,
                                '' AS ftworking_enter_time,'' AS ftdesciption
                            ";
                    mysql_query($ssql) or die("ERROR : ".$ssql);
                    $strSQL["Insert"][]=$ssql;
             }
         }


              $ssql_3="SELECT a.ftattendance_no
                        FROM
                        txattendance_hdr a
                        WHERE a.fnstatus<>9 AND
                            a.fdattendance_date='{$date_2}' AND
                            a.ftnotes='{$ftdepartment_code_notes}'";
             $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);


             if(!RetRstField($ssql_5 )) {

                  if($r_1['fttipe']=='M')
                  {
                        $fnemployee_status="0";
                        $ftabsen_code="";
                        $fdworking_enter_time=$r_1['fttime'];
                        $ftnotes="";
                        $ssql="SELECT fnstatus
                                FROM txattendance_hdr
                                WHERE ftattendance_no='{$ftattendance_no}'";
                        if(RetRstField($ssql)=="0")
                        {
                              $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="SELECT fnstatus
                                FROM txattendance_hdr
                                WHERE ftattendance_no='{$ftattendance_no}'";
                        if(RetRstField($ssql)=="0")
                        {
                               $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 {


                 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;

           #====================================
           #========LEMBURAN====================
           #====================================
        if($r_1['fttipe']=='K')
           {
                $ssql_1="SELECT a.fnid
                        FROM
                            txovertime_hdr a
                        WHERE
                            a.fdovertime_date='{$date_2}'  AND
                            a.ftattendance_no='{$ftattendance_no}'  AND
                            a.ftnotes='{$ftdepartment_code_notes}'";

//              pr($ssql_1);
                 if(!RetRstField($ssql_1)) {
                     $ftovertime_no=getAutoNumber('hrd007','txovertime_hdr','ftovertime_no',$date_1);
                     $ssql="INSERT INTO txovertime_hdr
                                (
                                    ftattendance_no, ftovertime_no, fdovertime_date, ftdivision_code,
                                    ftdepartment_code, ftposition_code, ftgroup_pengajian_code,
                                    ftshift_code, ftnotes, fnstatus,
                                    ftdesciption
                                )
                                  SELECT '{$ftattendance_no}' AS ftattendance_no, '{$ftovertime_no}' AS ftovertime_no,  '{$date_2}' AS fdovertime_date,  'SELURUH' AS ftdivision_code,
                                     'SELURUH' AS ftdepartment_code,  'SELURUH' AS ftposition_code,  'SELURUH' AS ftgroup_pengajian_code,
                                     'SELURUH' AS ftshift_code,  '{$ftdepartment_code_notes}' AS ftnotes,  '0' AS fnstatus,
                                     '' AS ftdesciption
                            ";

                     mysql_query($ssql) or die("ERROR : ".$ssql);
                      $strSQL["Insert"][]=$ssql;
                 }
                  $ssql_3="SELECT a.ftovertime_no
                            FROM
                            txovertime_hdr a
                            WHERE a.fnstatus<>9 AND
                                a.fdovertime_date='{$date_2}' AND
                                a.ftnotes='{$ftdepartment_code_notes}'";

                 $ftovertime_no=RetRstField($ssql_3);

                 if(RetRstField("SELECT ff13_hari_libur(fdattendance_date,ftdivision_code,ftdepartment_code) AS LIBUR FROM txattendance_hdr WHERE ftattendance_no='{$ftattendance_no}' ")!=1) {
                      $ssql_4="SELECT TIMEDIFF(
                                    CONCAT(b.fdworking_exit_date,' ',b.fdworking_exit_time)
                                    ,
                                   CONCAT(a.fdattendance_date,' ',c.fdshift_out)
                                 ) as ftovertime_hours ,
                                 c.fdshift_out,
                                 b.ftshift_code,
                                b.fdworking_exit_time, b.fdworking_exit_date
                            FROM txattendance_hdr a,
                                txattendance_det b,
                                tlshift c
                            WHERE a.ftattendance_no='{$ftattendance_no}' AND
                                a.ftattendance_no=b.ftattendance_no AND
                                b.ftemployee_code='{$ftemployee_code}' AND
                                CONCAT(a.fdattendance_date,' ',c.fdshift_out) <CONCAT(b.fdworking_exit_date,' ',b.fdworking_exit_time) AND
                                c.ftshift_day=DATE_FORMAT(a.fdattendance_date,'%W') AND
                                b.ftshift_code=c.ftshift_code";
                 }
                 else {

                       $ssql_4=" SELECT TIMEDIFF(
                                    CONCAT(b.fdworking_exit_date,' ',b.fdworking_exit_time )
                                    ,
                                   CONCAT(a.fdattendance_date,' ',b.fdworking_enter_time)
                                 ) as ftovertime_hours ,
                                 b.fdworking_exit_time as fdshift_out,
                                 b.ftshift_code,
                                b.fdworking_exit_time, b.fdworking_exit_date
                                FROM txattendance_hdr a,
                                txattendance_det b
                                WHERE a.ftattendance_no='{$ftattendance_no}' AND
                                a.ftattendance_no=b.ftattendance_no";


                 }


                 $ssql_5="SELECT a.fnid
                          FROM txovertime_det a
                            WHERE a.ftovertime_no='{$ftovertime_no}' AND
                                    a.ftemployee_code='{$ftemployee_code}'";


                 $rs_2=mysql_query($ssql_4);
                 $r_2=mysql_fetch_assoc($rs_2);
                 $fdovertime_enter_time=$r_2['fdshift_out'];
                 $fdovertime_exit_date=$r_2['fdworking_exit_date'];
                 $fdovertime_exit_time=$r_2['fdworking_exit_time'];
                 $ftovertime_hours=$r_2['ftovertime_hours'];

                 if(!RetRstField($ssql_5))
                 {
                     $ssql="SELECT fnstatus
                                FROM txovertime_hdr
                                WHERE ftovertime_no='{$ftovertime_no}'";
                        if(RetRstField($ssql)=="0")
                        {
                             $ssql="INSERT INTO txovertime_det
                                    (
                                        ftovertime_no, fnindex, ftemployee_code, ftshift_code,
                                        fnemployee_status, fdovertime_enter_time, fdovertime_exit_date,
                                        fdovertime_exit_time, ftovertime_hours, ftnotes
                                    )
                                       SELECT '{$ftovertime_no}' AS ftovertime_no,'' AS fnindex, '{$ftemployee_code}' AS ftemployee_code, '{$r_2['ftshift_code']}' AS ftshift_code,
                                        '0' AS fnemployee_status, '{$fdovertime_enter_time}' AS fdovertime_enter_time, '{$fdovertime_exit_date}' AS fdovertime_exit_date,
                                        '{$fdovertime_exit_time}' AS fdovertime_exit_time, '{$ftovertime_hours}' AS ftovertime_hours, '' AS ftnotes";

                              mysql_query($ssql) or die("ERROR : ".$ssql);
                              $strSQL["Insert"][]=$ssql;
                        }

                 }
                 else {
                       $ssql="UPDATE txovertime_det
                            SET fdovertime_enter_time='{$fdovertime_enter_time}',
                                fdovertime_exit_date='{$fdovertime_exit_date}',
                                fdovertime_exit_time='{$fdovertime_exit_time}',
                                ftovertime_hours='{$ftovertime_hours}'
                             WHERE ftovertime_no='{$ftovertime_no}' AND
                                    ftemployee_code='{$ftemployee_code}'";

                      mysql_query($ssql) or die("ERROR : ".$ssql);
                      $strSQL["Insert"][]=$ssql;
                 }

           }
           #====================================
    }


}

    $awal=microtime(true);
    mysql_query("BEGIN");
    #------------------------------------------------
	$handle = @fopen("C:/Program Files/CenturyBatteryAbsen/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="INSERT IGNORE INTO tlattendance (ftcode,fnlock) VALUES('{$buffer}',0)";
                           mysql_query($ssql) or die("ERROR : ".$ssql);
                               
                            
//                echo $ssql."<br>";
                    // }
		}
                
		fclose($handle);
               
	}

     $ssql="UPDATE  tlattendance
                SET
                    ftnik=mid(ftcode,4,11)
                WHERE ftmesin is null
                ";
    # pr($ssql);die("");
      mysql_query($ssql);


    $ssql="UPDATE  tlattendance a,  tlemployee b
                SET
                    a.ftmesin=mid(a.ftcode,1,3) ,
                    a.ftnik=mid(a.ftcode,4,11) ,
                    a.fttipe= mid(a.ftcode,15,1) ,
                    a.fddate=concat(
                            mid(a.ftcode,16,4),'-',
                            mid(a.ftcode,20,2),'-',
                            mid(a.ftcode,22,2)
                        ) ,
                    a.fttime =
                        concat(
                            mid(a.ftcode,24,2),':',
                            mid(a.ftcode,26,2),':',
                            mid(a.ftcode,28,2)
                        ),
                   a.ftcreated_user='{$_SESSION['username']}',
                   a.fdcreated_date=now(),
                   a.ftdepartment_code=b.ftdepartment_code
                WHERE a.ftmesin is null and a.ftnik=b.ftemployee_code
                ";
     

   //mysql_query("BEGIN");

    mysql_query($ssql);
    mysql_query("COMMIT");

    $akhir=microtime(true);
    $lama=$akhir-$awal; // lama proses transfer data dari mesin absen ke table sistem
    
     

    $awal_staff=microtime(true);

    mysql_query("BEGIN");
    GenerateAbsensi("STAFF");
    mysql_query("COMMIT");
    
    $akhir_staff=microtime(true);
    $lama_staff=$akhir_staff - $awal_staff; // lama proses fungsi generateabsensi("staff")


    $awal_nonstaff=microtime(true);

    mysql_query("BEGIN");
    GenerateAbsensi("NON STAFF");  
    mysql_query("COMMIT");

    $akhir_nonstaff=microtime(true);
    $lama_nonstaff=$akhir_nonstaff - $awal_nonstaff; // lama proses fungsi generateabsensi("non staff")

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

    echo "<script>alert('Generate Sukses ....'); </script>";

?>
<html>
    <head></head>
    <body>
        <table border="1" width="800px" align="center">
	<tr>
            <td>Keterangan </td>
            <td>Status </td>
            <td>Waktu </td>
	</tr>
        <tr>
            <td>Ambil Data Dari Mesin Absen </td>
            <td><label>Selesai</label></td>
            <td><label><?php echo $lama." Detik"; ?></label></td>
	</tr>
        <tr>
            <td>Proses Data Absensi Staff </td>
            <td><label>Selesai</label></td>
            <td><label><?php echo $lama_staff." Detik"; ?></label></td>
	</tr>
        <tr>
            <td>Proses Data Absensi Non Staff</td>
            <td><label>Selesai</label></td>
            <td><label><?php echo $lama_nonstaff." Detik"; ?></label></td>
	</tr>
	</table>
        <center><p style="font-weight: bold; font-size: 14pt; font-family: 'calibri'"> Generate Absensi Berhasil Di Proses</p></center>
    </body>
</html>




