<?php  ob_start(); error_reporting(1); ?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Import data from access to Mysql</title>
</head>
<body>
<?php 
   function getFieldValue($tablename,$condition,$coloumnName)
      {
         
         $query = mysql_query("select $coloumnName from $tablename where $condition");
         if(mysql_num_rows($query)>0){
             $data = mysql_fetch_array($query);
            return $data[$coloumnName];
         }
         return "";
      }
       function RecordInsert($table, $dataArray)
      { 
          global $db;
          $sql="Insert into ".$table ." set ";
          $i=0;
          foreach($dataArray as $key => $val){
              if($i==0)
                $sql.=" ".$key." = '".$val."'";
              else
                $sql.=", ".$key." = '".$val."'";      
              $i++;
          }
          $r=mysql_query($sql);
          return ($r); 
      } 
	  
	  function RecordEdit($table, $dataArray, $cond)
 		{
		global $db;
 		 $sql="update  ".$table ." set ";
 		$i=0;
 		foreach($dataArray as $key => $val)
 			{
 			if($i==0)
 			$sql.=" ".$key." = '".$val."'";
 			else
 			$sql.=",".$key." = '".$val."'";			
 			$i++;
 			}
			  $sql .=" where  $cond" ;
			 
		$r=DB_query($sql,$db);
		return ($r); 
 	} 
$con=mysql_connect("localhost","root" ,"");

		mysql_set_charset('utf8',$con);
		if(! $con)
		{
		 die('Error 101'.mysql_error());
		}
		$x= mysql_select_db('mxerp', $con);

header('Content-type: text/html; charset=utf-8');
if(isset($_REQUEST['Process']))
{
	$start_date=str_replace('/','-',FormatDateForSQL($_REQUEST['start_date']));
	$end_date=str_replace('/','-',FormatDateForSQL($_REQUEST['end_date']));
            
			if($_REQUEST['branch']=='CETP'){
	  			$dbcon="Driver={Microsoft Access Driver (*.mdb)};Dbq=D:/Attendance/Delhi/eTimeTrackLite1.mdb";
			}
			if($_REQUEST['branch']=='COE'){
				$dbcon="Driver={Microsoft Access Driver (*.mdb)};Dbq=D:/Attendance/Mumbai/eTimeTrackLite1.mdb";
			}
			if($_REQUEST['branch']=='CSWP'){
				$dbcon="Driver={Microsoft Access Driver (*.mdb)};Dbq=D:/Attendance/Bangalore/eTimeTrackLite1.mdb";
			}
			if($_REQUEST['branch']=='HO'){
				$dbcon="Driver={Microsoft Access Driver (*.mdb)};Dbq=D:/Attendance/HO/eTimeTrackLite1.mdb";
			}
   
	if(!$dbcon)
	{
		die('Error101: File Not found.');
	}

	$db_connstr =odbc_connect($dbcon,'','');
	if(!$db_connstr)
	{
		die('Error102:Not Connected.');
	} 
	
	$sql="select * from vw_AttendanceLogs where Employees.Status='Working' and AttendanceDate >= #".$start_date."# and AttendanceDate <= #".$end_date."# order by AttendanceLogId ";
	
	$rs1 = odbc_exec($db_connstr, $sql);
	$m=0;
	while(odbc_fetch_row($rs1))
	{ 		
		if(! $x)
		{
			die('Error 102 Database Not Connected' );
		}
		
	 if(trim(odbc_result($rs1,"Status"))=='Present'){ $paFlag='P'; $leaveid='';}
     elseif(trim(odbc_result($rs1,"Status"))=='Absent'){ $paFlag='A'; $leaveid='';}
     elseif(trim(odbc_result($rs1,"Status"))=='WeeklyOff'){ $paFlag='WL'; $leaveid=''; }
     elseif(trim(odbc_result($rs1,"Status"))=='WeeklyOff Absent'){ $paFlag='WL'; $leaveid=''; }
     elseif(trim(odbc_result($rs1,"Status"))=='Holiday'){ $paFlag='HO'; $leaveid=''; }
     elseif(trim(odbc_result($rs1,"Status"))=='Present'){ $paFlag='P'; $leaveid=''; }
     elseif(trim(odbc_result($rs1,"Status"))=='Â½Present'){ $paFlag='H'; $leaveid=''; }
     elseif(trim(odbc_result($rs1,"Status"))=='Leave'){ $paFlag='L';  $leaveid='';}
     elseif(trim(odbc_result($rs1,"Status"))=='On Leave(EL)'){ $paFlag='L';  $leaveid= getFieldValue('leavemaster', 'leavecode="EL"','id');}
     elseif(trim(odbc_result($rs1,"Status"))=='Absent(Due to Continous Late)'){ $paFlag='ADC'; $leaveid='';}
     elseif(trim(odbc_result($rs1,"Status"))=='On Leave(OL)'){  $paFlag='L';       $leaveid= getFieldValue('leavemaster', 'leavecode="CL"','id'); }
     elseif(trim(odbc_result($rs1,"Status"))=='Absent (No OutPunch)')        	{ $paFlag='ANP'; $leaveid='';	}
     elseif(trim(odbc_result($rs1,"Status"))=='On Leave(½CL) (No OutPunch)'){ $paFlag='H'; $leaveid= getFieldValue('leavemaster','leavecode="CL"','id');}
     elseif(trim(odbc_result($rs1,"Status"))=='On Leave(½CL)'){ $paFlag='P'; $leaveid= getFieldValue('leavemaster', 'leavecode="CL"','id'); }
     elseif(trim(odbc_result($rs1,"Status"))=='Present  On Leave(½CL)'){ $paFlag='P';$leaveid=getFieldValue('leavemaster', 'leavecode="CL"','id');  }
     elseif(trim(odbc_result($rs1,"Status"))=='WeeklyOff  On Leave(EL)'){ $paFlag='WL';	$leaveid= getFieldValue('leavemaster', 'leavecode="EL"','id'); }

	 $entry_empid = getFieldValue('empmaster',"emp_code='".odbc_result($rs1,"EmployeeCode")."'",'id'); 
	   $olddate1=explode(" ",odbc_result($rs1,"AttendanceDate"));
	   $old_date =$olddate1[0];           
	   $newDate =explode("-",$old_date);
	   $attendanceDate=$old_date;
	   
	$get_old='';
	$get_old = getFieldValue('daily_attendance',"e_code='".odbc_result($rs1,"EmployeeCode")."' and attendance_date='".$attendanceDate."'",'id');
	
	if($get_old=='')
	 {
	   $empsite = getFieldValue('empmaster'," emp_code='".odbc_result($rs1,"EmployeeCode")."'" ,'emp_site');
	   $shiftStart =explode(" ",odbc_result($rs1,"InTime")); 
	   $endshift=explode(" ",odbc_result($rs1,"OutTime")); 		
	   $data['site']             =$_REQUEST['branch'];
	   $data['e_code']           =odbc_result($rs1,"EmployeeCode");	
	   $data['employee']         =$entry_empid;		  
	   $data['shift_start']      =odbc_result($rs1,"BeginTime");		 	
	   $data['shift_end']        =odbc_result($rs1,"EndTime");	 	
	   $data['time_in']          =$shiftStart[1];		 
	   $data['time_out']         =$endshift[1];		  	
	   $data['late_arrival']     =odbc_result($rs1,"LateBy");	
	   $data['office_hours']     =odbc_result($rs1,"ShiftDuration");	
	   $data['work_hrs']         =odbc_result($rs1,"Duration");		  		
	   $data['ot_hrs']           =odbc_result($rs1,"OverTime");	
	   $data['out_date']         =$attendanceDate;	
	   $data['attendance_date']  =$attendanceDate;
	   $data['created_date']     =date('Y-m-d');	
	   $data['attendance_day']   =$newDate[2];	
	   $data['attendance_month'] =$newDate[1];	
	   $data['attendance_year']  =$newDate[0];	
	   $data['pres_abs']         =$paFlag;	
	   $data['created_by']       =$_SESSION['UserID'];	
	   $data['companyId']        =$_SESSION['ACGCompanyId'];	
	   RecordInsert('daily_attendance',$data);
	   $m=1;
	}else{
		$empsite = getFieldValue('empmaster'," emp_code='".odbc_result($rs1,"EmployeeCode")."'" ,'emp_site');
	   $shiftStart =explode(" ",odbc_result($rs1,"InTime")); 
	   $endshift=explode(" ",odbc_result($rs1,"OutTime")); 		
	   $data['site']             =$_REQUEST['branch'];
	   $data['e_code']           =odbc_result($rs1,"EmployeeCode");	
	   $data['employee']         =$entry_empid;		  
	   $data['shift_start']      =odbc_result($rs1,"BeginTime");		 	
	   $data['shift_end']        =odbc_result($rs1,"EndTime");	 	
	   $data['time_in']          =$shiftStart[1];		 
	   $data['time_out']         =$endshift[1];		  	
	   $data['late_arrival']     =odbc_result($rs1,"LateBy");	
	   $data['office_hours']     =odbc_result($rs1,"ShiftDuration");	
	   $data['work_hrs']         =odbc_result($rs1,"Duration");		  		
	   $data['ot_hrs']           =odbc_result($rs1,"OverTime");	
	   $data['out_date']         =$attendanceDate;	
	   $data['attendance_date']  =$attendanceDate;
	   $data['created_date']     =date('Y-m-d');	
	   $data['attendance_day']   =$newDate[2];	
	   $data['attendance_month'] =$newDate[1];	
	   $data['attendance_year']  =$newDate[0];	
	   $data['pres_abs']         =$paFlag;	
	   $data['created_by']       =$_SESSION['UserID'];	
	   $data['companyId']        =$_SESSION['ACGCompanyId'];


		$cond2="id='".$get_old."'";
		$s->RecordEdit('daily_attendance', $data, $cond2);
	    $m=1;
	}
	}
  if($m==1){
	$_SESSION['msg']="Uploading Finished.";
	echo "<script>index.php?route=HRModule/Master/attendancebiometrix&modid=".$_SESSION['modid']."&frmid=".$_SESSION['frmid']."'</script>";
  }
}



?>
<script>
 /*function validation()
 {

 var branch=document.getElementById("branch").value;
   if(branch=='')
      {
          alert("Please select Branch");
        
          document.getElementById("branch").focus();
          return false;
      } else{
      	alert("Are you confirm for this Branch");  
      	return true;    
      }
      	
  }*/
</script>
<?php 
if(isset($_SESSION['msg']))
{
	echo "<script>alert('".$_SESSION['msg']."');</script>";
}
unset($_SESSION['msg']);
?>
<table width="100%" border="0" cellspacing="0" cellpadding="0">
	<tr>
		<td>
			<div class="formsarea">
				<strong><h2 align="center">Import data from access to Mysql</h2></strong>
				<form action="" method="post" enctype="multipart/form-data">
				<table align="center">
					<tr>
						<td width="13%"><strong>Branch</strong></td>
						<td width="35%">
							<select name="branch" id="branch">
								<option value="">Selected </option>
								<?php $locsql= DB_query("select * from locations where mst_companyid='".$_SESSION['ACGCompanyId']."' and loccode in ('".$_SESSION['AssignedBranch']."')",$db);
								while($locres=DB_fetch_array($locsql)) {  ?>
								<option value="<?php echo $locres['loccode']; ?>" <?php if($locres['loccode']==$_REQUEST['branch']){ echo "selected" ;} ?>><?php echo $locres['locationname']; ?></option> 
								<?php  } ?>
							</select>
						</td>
					</tr>
					<tr>
						<td width="13%"><strong>Start Date</strong></td>
						<td width="35%">
							<input type="text" name="start_date" id="start_date" value="" readonly style="background-color:#e5e5e5;" class="datepicker">
						</td>
					</tr>
					<tr>
						<td width="13%"><strong>End Date</strong></td>
						<td width="35%">
							<input type="text" name="end_date" id="end_date" value="" readonly style="background-color:#e5e5e5;" class="datepicker">
						</td>
					</tr>
					<tr>
						<td width="13%">&nbsp;</td>
						<td align="center">	
						<br>
						<br>					
						<input value="Process" name="Process" type="submit" onclick="validation();">
						</td>
					</tr>
				</table>
					
				</form>
			</div>
		</td>
  	</tr>
</table>
</body>
</html>