<?php
function getprojectname($postdata,$xcrud){

	$jobno = $postdata->get('projectid');
   
$db41 = Xcrud_db::get_instance();
	    $query = "SELECT 
		             project_name
		               
					  
		          FROM projectdetails
				    
				   WHERE projectid = '" . $jobno . "'";
        
        $db41->query($query);
        $out41 = $db41->result();
        foreach($out41 as $output41){
			
			$name=$output41['project_name'];
			
			
		
        } 

$postdata->set('name', $name);
	
}
function getdepartment($postdata,$xcrud){

	$accnum1 = $postdata->get('account_number');
   
$db41 = Xcrud_db::get_instance();
	    $query = "SELECT 
		             departmentid
		               
					  
		          FROM debtoraccount
				    
				   WHERE account_number = '" . $accnum1 . "'";
        
        $db41->query($query);
        $out41 = $db41->result();
        foreach($out41 as $output41){
			
			$departid=$output41['departmentid'];
			
			
			$db42= Xcrud_db::get_instance();
	    $query = "SELECT 
		             departmentname
		               
					  
		          FROM department
				    
				   WHERE departmentid = '" . $departid . "'";
        
        $db42->query($query);
        $out42 = $db42->result();
        foreach($out42 as $output42){
			
			$departname=$output42['departmentname'];
			
				
            
        }
		
        } 

$postdata->set('department', $departname);
	
}



function calculateprices($postdata,$xcrud,$projectcostingdetail){
	
			$id = $postdata->get('projectid');
	     $margin = $postdata->get('margin');
		$userid = $_SESSION['UserID'];
	
$db500 = Xcrud_db::get_instance();
	    $query = "SELECT margin_limit
				   			  
		          FROM www_users
				  
				  WHERE userid = '" . $userid. "'";
				  	 	   
        
        $db500 ->query($query);
        $out500 = $db500 ->result();
        foreach($out500 as $output500){
            $marginlimit = $output500 ['margin_limit'];
			
			if ($margin < $marginlimit) {
				
				 $projectcostingdetail->set_exception('margin','You have inputed margin rate below your margin limit. Contact your manager and change your margin limit','error');
			}
			
			
		}
	
	
	
	
	

		
	$unitprice = $postdata->get('unit_price');
	$stockdetails = $postdata->get('stock_details');
	$stockunitcost = $postdata->get('stock_unit_cost');
	$laborrate = $postdata->get('labor_rate');
	$qty = $postdata->get('qty');
	
	
	
	
	//now check unit prices since this above only determins the stock id:
	
	
	
	$db77000 = Xcrud_db::get_instance();
	    $query = "SELECT description,
						landing_cost
		                 
				   			  
		          FROM jobcostingsheet
				  
				  WHERE id = '" . $unitprice. "'";
				  	 	   
        
        $db77000 ->query($query);
        $out77000  = $db77000 ->result();
        foreach($out77000  as $output77000){
            $landingcost = $output77000 ['landing_cost'];
			 $nonstockname = $output77000 ['description'];
		}
	
	
	
	$db87000 = Xcrud_db::get_instance();
	    $query = "SELECT 	description,
							materialcost
				   			  
		          FROM stockmaster
				  
				  WHERE stockid = '" . $stockdetails. "'";
				  	 	   
        
        $db87000 ->query($query);
        $out87000  = $db87000 ->result();
        foreach($out87000  as $output87000){
            $cost = $output87000 ['materialcost'];
			  $stockname= $output87000 ['description'];
			
		}
	$nonstockcost = $landingcost * $qty;
	
	$stockcost = $cost * $qty;
	
	
	
	//get other expenses calculated
	
	$othercost = $postdata->get('other_cost');
	
	$totalothercost = $othercost * $qty;
	
	
	
	
	$laborcost = $laborrate * $qty;
	
	$tc = $nonstockcost + $stockcost + $laborcost + $totalothercost;
	
	
	
	$postdata->set('total_cost', $tc);
	
	
	
	

	$pricemargin = $tc * ($margin/'100');
	
	$price = $tc + $pricemargin;
	
	
	
	$postdata->set('price', $price);
	
	$unitsalespricemargin = $landingcost * ($margin/'100');
	$unitsalesprice = $landingcost + $unitsalespricemargin;
	
	$postdata->set('nonstockprice', $unitsalesprice);
	
	
	$stocksalespricemargin = $cost * ($margin/'100');
	
	$stocksalesprice = $cost+ $stocksalespricemargin;
	
	
	$postdata->set('stockprice', $stocksalesprice);
	
	$postdata->set('nonstockname', $nonstockname);
	$postdata->set('stockname', $stockname);
	 
}

function updatebudgettotals($postdata,$xcrud,$projectcostingdetail){

	
	
	
//get margin from projectcosting table
	
		
	
	$qn = $postdata->get('pcid');
	
	$db6000 = Xcrud_db::get_instance();
	    $query = "SELECT SUM(If(cost_type='materials',total_cost,0)) as 'materials',
		                 
						 SUM(If(cost_type='others',total_cost,0)) as 'others',
						 SUM(If(cost_type='labour',total_cost,0)) as 'labour',
						 SUM(price) as 'contractvalue',
						 
						 pcid
				   			  
		          FROM projectcostingdetail
				  
				  WHERE pcid = '" . $qn. "'";
				  	 	   
        
        $db6000 ->query($query);
        $out6000  = $db6000 ->result();
        foreach($out6000  as $output6000){
            
			$totalbudgetedmaterialcost = $output6000 ['materials'];
			
			$totalbudgetedotherscost = $output6000 ['others'];
			$totalbudgetedlabourcost = $output6000 ['labour'];
			$contractvalue = $output6000 ['contractvalue'];
			
	        
	
   			$db5002 = Xcrud_db::get_instance();
			$query2 = "UPDATE projectcosting SET  budgeted_material = '" . $totalbudgetedmaterialcost."',
										 
										 budgeted_others = '" . $totalbudgetedotherscost."',
										 budgeted_labor = '" . $totalbudgetedlabourcost."',
										 contract_value = '" . $contractvalue."'
										 
	
	           WHERE pcid = '" . $qn. "'";
	$db5002->query($query2);	
			


		} 
	
	
	//calculate total cost
	
	$totalbcost= $totalbudgetedmaterialcost + $totalbudgetedotherscost + $totalbudgetedlabourcost;
	
	
			
			//get vat rate from taxrate table
			$db250 = Xcrud_db::get_instance();
	    $query = "SELECT rate
				   			  
		          FROM taxrates
				  
				  WHERE taxid = '1'";
				  	 	   
        
        $db250->query($query);
        $out250  = $db250 ->result();
        foreach($out250 as $output250){
            $vatrate = $output250 ['rate'];
			
		}
			
			
			
			$contractvat = $contractvalue * ($vatrate/'100') ;
			$contractvip = $contractvalue + $contractvat;
			
	      $db5009 = Xcrud_db::get_instance();
			$query2 = "UPDATE projectcosting SET budgeted_total_cost = '" . $totalbcost."',
			                                     
												 contract_vip = '" . $contractvip."'
	
	           WHERE pcid = '" . $qn. "'";
	$db5009->query($query2);	
			
			


		} 
	
	  
		
	
	


function calculatepriceslabor($postdata,$xcrud,$projectcostingdetaillabor){
	
	$id = $postdata->get('projectid');
	     $margin = $postdata->get('margin');
		$userid = $_SESSION['UserID'];
	
$db500 = Xcrud_db::get_instance();
	    $query = "SELECT margin_limit
				   			  
		          FROM www_users
				  
				  WHERE userid = '" . $userid. "'";
				  	 	   
        
        $db500 ->query($query);
        $out500 = $db500 ->result();
        foreach($out500 as $output500){
            $marginlimit = $output500 ['margin_limit'];
			
			if ($margin < $marginlimit) {
				
				 $projectcostingdetaillabor->set_exception('margin','You have inputed margin rate  below your margin limit. Contact your manager change your margin limit','error');
			}
			
			
		}
	
	
	
	$rate = $postdata->get('rate');
	$hrs = $postdata->get('hours');
	$tl = ($rate* $hrs);
	
	
	$postdata->set('total_labor', $tl);
	
	
	
	
	$pricemarginlabor = $tl * ($margin/'100');
	
	$pricelabor = $tl + $pricemarginlabor;
	
	
	
	$postdata->set('labor_price', $pricelabor);
	 
}


function updatebudgettotalslabor($postdata,$xcrud){

		
		
	
	$qn = $postdata->get('pcid');
	
	$db6000 = Xcrud_db::get_instance();
	    $query = "SELECT 
						 SUM(total_labor) as 'total',
						 SUM(labor_price) as 'price',
					
						 pcid
				   			  
		          FROM projectcostingdetaillabor
				  
				  WHERE pcid = '" . $qn. "'";
				  	 	   
        
        $db6000 ->query($query);
        $out6000  = $db6000 ->result();
        foreach($out6000  as $output6000){
            $totalbudgetedlabor = $output6000 ['total'];
			$laborprice = $output6000 ['price'];
	
   			$db5002 = Xcrud_db::get_instance();
			$query2 = "UPDATE projectcosting SET budgeted_labor = '" . $totalbudgetedlabor."',
												 contractvalue2 = '" . $laborprice."'
	
	           WHERE pcid = '" . $qn. "'";
	$db5002->query($query2);	
			


		} 
	
	
//update total budgeted Cost
	
	$db6000 = Xcrud_db::get_instance();
	    $query = "SELECT budgeted_material, 
		                 budgeted_others,
						 budgeted_labor,
						 contractvalue1,
						 contractvalue2
				   			  
		          FROM projectcosting
				  
				  WHERE pcid = '" . $qn. "'";
				  	 	   
        
        $db6000 ->query($query);
        $out6000  = $db6000 ->result();
        foreach($out6000  as $output6000){
            
			$totalbudgetedmaterialcost = $output6000 ['budgeted_material'];
			
			$totalbudgetedotherscost = $output6000 ['budgeted_others'];
			$totalbudgetedlabour = $output6000 ['budgeted_labor'];
			$pricematerialothers = $output6000 ['contractvalue1'];
			$pricelabor = $output6000 ['contractvalue2'];
			
	        $totalbudgetedcost = $totalbudgetedmaterialcost + $totalbudgetedotherscost + $totalbudgetedlabour;
			
			
			$contractvalue = $pricematerialothers + $pricelabor;
			
			//get vat rate from taxrates table
				$db250= Xcrud_db::get_instance();
	    $query = "SELECT rate
				   			  
		          FROM taxrates
				  
				  WHERE taxid = '1'";
				  	 	   
        
        $db250->query($query);
        $out250  = $db250 ->result();
        foreach($out250 as $output250){
            $vatrate = $output250 ['rate'];
			
		}
			
			
			
			$contractvip = $contractvalue * ('1'+ ($vatrate/'100'));
			
			
			
			
	      $db5009 = Xcrud_db::get_instance();
			$query2 = "UPDATE projectcosting SET budgeted_total_cost = '" . $totalbudgetedcost."',
			                                     contract_value = '" . $contractvalue."',
												 contract_vip = '" . $contractvip."'
	
	           WHERE pcid = '" . $qn. "'";
	$db5009->query($query2);	
			
			


		} 
	
	  
	
	  
}