<?php 

//    $ssql="SELECT ftcode,
//                mid(ftcode,1,3) as ftmesin,
//                mid(ftcode,4,9) as ftnik,
//                mid(ftcode,13,1) as fttipe,
//                concat(
//                mid(ftcode,14,4),'-',
//                mid(ftcode,18,2),'-',
//                mid(ftcode,20,2),' ',
//                mid(ftcode,22,2),':',
//                mid(ftcode,24,2),':',
//                mid(ftcode,26,2)
//                ) as fddate
//            FROM tlattendance
//            WHERE ftcode<>''";


	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);
			$ssql="REPLACE INTO tlattendance(ftcode) VALUES('{$buffer}')";
            mysql_query($ssql);
		}
		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)
                        )
                WHERE ftmesin is null
                ";

    mysql_query($ssql);
    $strSQL["Insert"][]=$ssql;
    #---------------------------------

    #belum ada sama sekali
   
    $date_1=date('d/m/Y');
    $date_2=CDate($date_1);
    $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);
    $strSQL["Insert"][]=$ssql;
     
   $ssql ="INSERT INTO txattendance_det
            (
                ftattendance_no, fnindex, ftemployee_code, ftshift_code,
                fnemployee_status,ftabsen_code, fdworking_enter_time, 
                fdworking_exit_date, fdworking_exit_time, ftworking_hours,
                ftnotes
            )
            SELECT '{$ftattendance_no}' as ftattendance_no, c.fnindex AS fnindex,a.ftemployee_code,a.ftshift_code,
                                    ifnull(c.fnemployee_status,0) AS fnemployee_status,c.ftabsen_code,
                                    b.fdshift_in AS fdworking_enter_time,
                                   CAST( CASE WHEN b.fdshift_in<=b.fdshift_out THEN
                                                 '{$date_2}'
                                                 ELSE
                                                        DATE_ADD('{$date_2}', INTERVAL 1 DAY)
                                                        END AS DATE)   AS fdworking_exit_date,

                                     b.fdshift_out AS fdworking_exit_time,

                                        TIMEDIFF(CONCAT(
                                            CASE WHEN b.fdshift_in<=b.fdshift_out THEN
                                                 '{$date_2}'
                                                 ELSE
                                                        DATE_ADD('{$date_2}', INTERVAL 1 DAY)
                                                        END
                                            ,' ',b.fdshift_out),DATE_ADD(CONCAT('{$date_2} ',b.fdshift_in),INTERVAL 1 HOUR)) as ftworking_hours,
                                    c.ftnotes
                            FROM tlemployee a INNER JOIN
                                    tlshift  b ON a.ftshift_code=b.ftshift_code
                                    INNER JOIN tlattendance_employee d ON a.ftemployee_code=d.ftnik 
                                    LEFT JOIN txattendance_det c ON 1=0
                            WHERE   a.fntype=0 and a.fnstatus<>4 and b.ftshift_day=DATE_FORMAT('{$date_2}','%W')
                            ORDER BY a.ftemployee_name";
   
         mysql_query($ssql);
         $strSQL["Insert"][]=$ssql;
         pr($strSQL);
         mysql_query("COMMIT");
       
                             
?>

