<?php
function fetchpaydata($postdata,$ebusiness,$xcrud){
	$startdate = $postdata->get('start_date');
	$enddate = $postdata->get('end_date');
	$payno = $postdata->get('pay_no');
	$cycle = $postdata->get('pay_cycle');
	
	if ($cycle == 'Weekly') {
		
		$period = '52';
		
	}
	elseif ($cycle == 'Monthly') {
		$period = '12';
		
	}
	elseif ($cycle == 'Fortnightly') {
		$period = '26';
		
	}
	//fetch timesheet
	
	//first gfs
	
	
		$db222 = Xcrud_db::get_instance();
	    $query = "SELECT timesuvaid,sectors,patrol_location
					  
		          FROM timesheetsuva 
				  				  
				   WHERE status = 'approved' AND start_date ='".$startdate."' AND end_date ='".$enddate."'";
        
        $db222->query($query);
        $outm222 = $db222->result();
        foreach($outm222 as $outputm222){
			
			         //fetch details
			 
			$db223 = Xcrud_db::get_instance();
	        $query = "SELECT id,employeeid,normal_hours,
					         SUM(normal_hours) as 'normal',SUM(time_half_hours) as 'timehalf',SUM(double_hours) as 'double'
		          FROM timesheetsuvadetails
				  				  
				   WHERE timesuvaid = '".$outputm222['timesuvaid']."' 
				   Group By employeeid";
        
            $db223->query($query);
               $outm223 = $db223->result();
			         foreach($outm223 as $outputm223){
			    //check location using timesuvaid to see if any premium rate to be paid
			
						       	$db224 = Xcrud_db::get_instance();
	        $query = "SELECT premium_rate
		          FROM guardforceclient
				  				  
				   WHERE clientid = '".$outputm222['patrol_location']."' 
				   ";
        
            $db224->query($query);
               $outm224 = $db223->result();
			                              foreach($outm224 as $outputm224){
						 
						          				 $premiumrate = $outputm224['premium_rate'];	  									  
											  
			
										  }
						 
						 //get employee full name and other details for payslip
						 
						 $employeeid =$outputm223['employeeid'];
						 
						 
						       	$db224 = Xcrud_db::get_instance();
	        $query = "SELECT first_name,middle_name,last_name,tin,fnpf,fnpf_code,job_role,start_date,employment_type,salary_type,annualsalary,base_wage_rate,payment_method,bank_name,account_number,account_name,total_paid_till_date,sick_leave_taken_till_date,annual_leave_taken_till_date,	total_bonuspaid_to_date,completed_pay_period,annual_income_plus_bonus,income_tax_withheld,srt_tax_withheld,totat_tax_withheld,
				fnpf_deducted_till_date,fnpf_employers_contribution_till_date,	fnpf_total_till_date
		          FROM employeemaster
				  				  
				   WHERE employeeid = '".$employeeid."' 
				   ";
        
            $db226->query($query);
               $outm226 = $db226->result();
			                              foreach($outm226 as $outputm226){
						 
						          				 $wagerate = $outputm226['base_wage_rate'];	
											     $salary =  $outputm226['annualsalary'];	
											  $firstname =  $outputm226['first_name'];
											  $middlename =  $outputm226['first_name'];
											  $lastname =  $outputm226['first_name'];
											  $tin =  $outputm226['first_name'];
											  $fnpf =  $outputm226['first_name'];
											  $fnpfcode =  $outputm226['first_name'];
											  $jobrole =  $outputm226['job_role'];
											  $start_dateemployee =  $outputm226['start_date'];
											  $paymentmethod =  $outputm226['payment_menthod'];
											  $accountnumber =  $outputm226['account_number'];
											  $accountname =  $outputm226['account_name'];
											   $employmenttype =  $outputm226['employment_type'];
											   $salarytype =  $outputm226['salary_type'];
											  
			
										  }
						 
						 
						 
						 
						 //calculations
						 
						
						 //normal pay
						 if ($salarytype !== 'Fixed') {
						 
							 $normalpay = $wagerate * $outputm223['normal'];
						
							  //bonus
						 
						 $timehalf = $outputm223['timehalf'] * ($wagerate * 1.5);
							 
							 $double = $outputm223['double'] * ($wagerate * 2);
							 
							 $premium = $outputm223['normal'] * $premiumrate;
							 
							 $bonus = $timehalf + $double + $premium;
							 
							 
							 //get all deductions apart from fnpf
							 
							   	$db20 = Xcrud_db::get_instance();
	        $query20 = "SELECT type,amount_to_deduct,ytd_amount,total_amount
		          FROM fixeddeductions 
				  				  
				   WHERE employeeid = '".$employeeid."' 
				   ";
        
            $db20->query($query);
               $outm20= $db20->result();
			                  $totalfixeddeduction = 0;           
							 foreach($outm20 as $outputm20){
						 
						          				 $typedeductions = $outputm20['type'];	  									  
											  
											      $amounttodeduct = $outputm20['amount_to_deduct'];	
								 $ytddeductionamount = $outputm20['ytd_amount'];	
											  
											   $totaltobededucted = $outputm20['total_amount'];
								 
								 
								 
								 //grab name for this type
								  	$db21 = Xcrud_db::get_instance();
	        $query21 = "SELECT type_detail
		          FROM payrol_adjustments_type_details
				  				  
				   WHERE id = '".$typedeductions."' 
				   ";
        
            $db21->query($query);
               $outm21= $db21->result();
			                             
							 foreach($outm21 as $outputm21){
						 
						          				 $typedetail = $outputm21['type_detail'];	  									  
							
										  }
								 
								 
								 
			
								 $totalfixeddeduction +=  $amounttodeduct;
							 }
								 //grab variable deductions
								 
								  	$db22 = Xcrud_db::get_instance();
	        $query22 = "SELECT payrol_adjustments.id,payrol_adjustments.employeeid,payrol_adjustments.type_details,payrol_adjustments.amount,payrol_adjustments.comments
		          FROM payrol_adjustments INNER JOIN payrol_adjustments_details ON  
				  payrol_adjustments.id = payrol_adjustments_details.id
				  INNER JOIN  payrol_adjustments_type_details ON
				  payrol_adjustments.type_details = payrol_adjustments_type_details.id
				  				  
				   WHERE  payrol_adjustments.start_date ='".$startdate."' AND payrol_adjustments.end_date ='".$enddate."'";
				 
            $db22->query($query22);
               $outm22= $db22->result();
			                    
								 $totalvariablededuction = 0;
							 foreach($outm22 as $outputm22){
								 
								$totalvdeduct = $outputm22['amount'];
								 
								 
								 $totalvariablededuction += $totalvdeduct;
								 
							 }
								 
								 
							 							 										  							 
							 //tax columns
							 
							 
							 
							  
						 //insert data in payrun details
						 
						  	$db225 = Xcrud_db::get_instance();
	        $query = "INSERT into payrun_details (pay_no,employeeid,normal_pay,	ytd_amount,	bonus,	ytd_bonus,pay_cycle,	completed_cycle,	annual_income,annual_income_with_bonus,)
				  				  
				   WHERE clientid = '".$outputm222['patrol_location']."' 
				   ";
        
            $db225->query($query);
               $outm225 = $db225->result();
						 
						 
						 //update ytd values for reference
						 $query2 = "INSERT into employeemaster (pay_no,employeeid,normal_pay,	ytd_amount,	bonus,	ytd_bonus,pay_cycle,	completed_cycle,	annual_income,annual_income_with_bonus,)
				  				  
				   WHERE clientid = '".$outputm222['patrol_location']."' 
				   ";
        
            $db5->query($query2);
               $outm5 = $db5->result();
							 
							 
							 
						 }
						  elseif ($salarytype !== 'Fixed') { 
						 $normalpay = $salary / $period;
												 
							 
						 }
			
			
						 
						
						 
					 }
			
			
			//get total amount to payout and save it in payno profile
			
		}
			
			
		}
	
	
		