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

	$pgid= $postdata->get('pgid');
	$amount = $postdata->get('amount');
	
	$db411 = Xcrud_db::get_instance();
	    $query1 = "SELECT 
		            actual_others
		          FROM progressive_billing
				    
				   WHERE pgid = '" . $pgid . "'
				   ";
        
        $db411->query($query1);
        $out411 = $db411->result();
        foreach($out411 as $output411){
			
			$othersamount=$output411['actual_others'];
        } 
	
	$newamount = $othersamount + $amount;
	
	
$db41 = Xcrud_db::get_instance();
	    $query = "UPDATE progressive_billing SET  actual_others = '" . $newamount."'
										 
	
	           WHERE pgid = '" . $pgid . "'";
        
        $db41->query($query);
	
	
}
function actualcosting($postdata,$xcrud){
$pcid =$postdata->get('costing_no');
	$jobno = $postdata->get('projectid');
	$others = $postdata->get('actual_others');
	
	$start = $postdata->get('start_date');
   $end = $postdata->get('end_date');
		
	$startdate = date('Y-m-d',$start);
	$enddate = date('Y-m-d',$end);
	$margin = $postdata->get('margin');
	
	
$db41 = Xcrud_db::get_instance();
	    $query = "SELECT 
		            SUM(amount) as totalmaterial
		               
					  
		          FROM contractcharges
				    
				   WHERE contractref = '" . $jobno . "'
				   AND date BETWEEN '" . $start. "' AND '" . $end. "' 
				   ";
        
        $db41->query($query);
        $out41 = $db41->result();
        foreach($out41 as $output41){
			
			$totalmaterial=$output41['totalmaterial'];
			
			
		
        } 

$postdata->set('actual_material', $totalmaterial);
	
$totalactualcost = $others + $totalmaterial ;
	
	$postdata->set('total_actual_cost', $totalactualcost);

	//calculate bill rate
	
	//fetch 
	$db411 = Xcrud_db::get_instance();
	    $query1 = "SELECT 
		            budgeted_total_cost,
					contract_value
		               
					  
		          FROM projectcosting
				    
				   WHERE pcid = '" . $pcid . "'
				   ";
        
        $db411->query($query1);
        $out411 = $db411->result();
        foreach($out411 as $output411){
			
			$bugetedtotalcost=$output411['budgeted_total_cost'];
				$contractvalue = $output411['contract_value'];
        } 
	
	$billrate = $totalactualcost/$bugetedtotalcost;
	
		$bill = number_format($billrate,2);
	
	$postdata->set('bill_rate', $bill);
		

	
	$sellingprice = $contractvalue * $bill;
	
	
	
	$vipamount = $sellingprice * 1.09;
	
	
	$postdata->set('billing_amount_vep', $sellingprice);
	
	$postdata->set('billing_amount_vip', $vipamount);
	
	
	
}

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){

	//calculate total cost and selling price
	$id	= $postdata->get('pcid');
	$type = $postdata->get('cost_type');
		$unitcost = $postdata->get('unit_cost');
	
		$qty = $postdata->get('qty');
	
	$margin = $postdata->get('margin');
	
	$totalcost = $unitcost * $qty ;
	$postdata->set('total_cost', $totalcost);
	
	$sellingprice =  $totalcost  / (1 - ($margin/100));
	
	
	$postdata->set('price', $sellingprice);
								   
	$marginamount= $totalcost *(1-(($margin/100)));
	
	
	//update budgeted details
	$db42 = Xcrud_db::get_instance();
	    $query2 = "SELECT 
		             gl_type,
		              sales_gl ,
					  cogs_gl
					  
		          FROM projectcosting 				    
				   WHERE pcid = '" . $id. "'";
        
        $db42->query($query2);
        $out42 = $db42->result();
        foreach($out42 as $output42){
			
			$gltype =$output42['gl_type'];
			
			$salesgl = $output42['sales_gl'];
			$cogsgl = $output42['cogs_gl'];
            
        } 

 if ($gltype == 'single'){
	 
	 //copy glcodes in each line items
	 
	 $postdata->set('sales_gl', $salesgl);
	 $postdata->set('cogs_gl', $cogsgl);
	 
	 
	 
 }
	

	
		} 
	
	  
		
	
	


function getbudgetedtotals($xcrud){
	
$db9000 = Xcrud_db::get_instance();
	    $query = "SELECT pcid
						 
						 
				   			  
		          FROM projectcostingdetail";
				  	 	   
        
        $db9000 ->query($query);
        $out9000  = $db9000 ->result();
        foreach($out9000  as $output9000){
            
			$pcid = $output9000 ['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'
						 
						 
				   			  
		          FROM projectcostingdetail where pcid ='" . $pcid. "'";
				  	 	   
        
        $db6000 ->query($query);
        $out6000  = $db6000 ->result();
        foreach($out6000  as $output6000){
            
			$totalbudgetedmaterialcost = $output6000 ['materials'];
			
			$totalbudgetedotherscost = $output6000 ['others'];
			$totalbudgetedlabourcost = $output6000 ['labour'];
			$contractvalue = $output6000 ['contractvalue'];
			
	        $totalbcost= $totalbudgetedmaterialcost + $totalbudgetedotherscost + $totalbudgetedlabourcost;
			$profit = $contractvalue - $totalbcost;
		$contractvat = $contractvalue * 0.09 ;
			$contractvip = $contractvalue + $contractvat;
			
	
   			$db5002 = Xcrud_db::get_instance();
			$query2 = "UPDATE projectcosting SET  budgeted_material = '" . $totalbudgetedmaterialcost."',
										 
										 budgeted_others = '" . $totalbudgetedotherscost."',
										 budgeted_labor = '" . $totalbudgetedlabourcost."',
										 contract_value = '" . $contractvalue."',
										 budgeted_total_cost = '" . $totalbcost."',
										 estimated_profit = '" . $profit."',
										 contract_vip = '" . $contractvip."'
										 
	
	           WHERE pcid = '" . $pcid. "'";
	$db5002->query($query2);	
			


		
	
	


		}
		}
}


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;
			
			
			
			$vat = $contractvalue * 0.09;
			
			
			$contractvip = $contractvalue + $vat;
			
			
			
			
			
	      $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);	
			
			


		} 
	
	  
	
	  
}