<?

class common_materials
{
	public function create_new($sql_string)
	{	
		global $database;
		
		$db_name = DB_CLIENT;
		
		$sql = "
		INSERT 
		INTO $db_name"."quotes_materials
		(quote_id, item, materials, supplier, quantity, price,total,job_flag, submitter, date_submitted) 
		VALUES ".$sql_string;	
	
		$result=$database->query($sql);	
		$lastid = $database->insert_ID();
		return $lastid;	
	}
	
	public function create_new_material($sql_string){
		global $database;
		
		$db_name = DB_CLIENT;
		
		$sql = "
		INSERT
		INTO $db_name"."quotes_materials
		(quote_id,material_no, item, materials, supplier, quantity, price,total,inv_exclude,job_flag, submitter, date_submitted)
		VALUES ".$sql_string;
		
		$result=$database->query($sql);
		$lastid = $database->insert_ID();
		return $lastid;
	}
	 
	 
	public function update_quote_materials($actual,$id)
	{	
		global $database;
		
		$db_name = DB_CLIENT;
		$submitter = $_SESSION['user']['id'];
		$current_date_time = date('Y-m-d G:i:s');
		
		$get_total_sql = "SELECT * FROM quotes_materials WHERE id = '$id'";
		$current_material = $database->getRow($get_total_sql);
		
		if($current_material){
			$material_no = $current_material['material_no'];
			
			$update  = "
			UPDATE quotes_materials
			SET
			actual_used = actual_used + '$actual',
			submitter = '$submitter',
			date_submitted = '$current_date_time'
			WHERE id = '$id'
			";
			$database->query($update);
			
			if($material_no!=0){
				$update  = "
				UPDATE $db_name"."materials_inventory
				SET
				total_quantity=total_quantity-$actual,
				submitter='$submitter',
				date_submitted='$current_date_time'
				WHERE id = '$material_no'
				";
				$database->query($update);

				logs::submit_logs(array("table" => "materials_inventory", "action_type" => 2,"foreign_id" => $material_no, "description" => "Used item x $actual"));
			}
		}
		
	}
	
	
	
	public function update_materials($context)
	{	
		global $database;
		
		$db_name = DB_CLIENT;
		$submitter = $_SESSION['user']['id'];
		$current_date_time = date('Y-m-d G:i:s');
		
		$materials = $context['materials']; 
		$item = $context['item'];
		$supplier = addslashes($context['supplier']);
		$quantity = addslashes($context['quantity']);
		$price = $context['price'];
		$total = $context['total'];
		$id = $context['id'];
		
		$sql = "
		UPDATE $db_name"."quotes_materials 
		SET
			item = '$item',
			materials = '$materials',
			supplier = '$supplier',
			quantity = '$quantity',
			price = '$price',
			total = '$total'
		WHERE id = '$id'
			";
		
		$results = $database->query($sql);		
	}
	
	
	public function get_quote_id($id)
	{	
		global $database;
		
		 
		 $sql  = "
				SELECT quote_id FROM jobs
					WHERE id = '$id'
			";		
		$result=$database->getRow($sql);		
		
		return $result['quote_id'];
	}
	
	
	public function get_quote_materials($id)
	{	
		global $database;
		
		 
		$get_materials  = "
		SELECT *
		FROM  quotes_materials
		WHERE quote_id = '$id' AND job_flag='0'
		";		
		$results = $database->getAll($get_materials);		
		
		return $results ;
	}
	
	
	
	public function get_quote_materials_job($id)
	{	
		global $database;
		
		 
		$get_materials  = "
		SELECT quotes_materials.*,materials_inventory.total_quantity
		FROM  quotes_materials
		LEFT JOIN materials_inventory ON materials_inventory.id = quotes_materials.material_no
		WHERE quote_id = '$id' 
		";		
		$results = $database->getAll($get_materials);		
		
		return $results ;
	}
	
	
	
	public function get_quote_materials_by_id($id)
	{	
		global $database;
		
		 
		$get_materials  = "
		SELECT *
		FROM  quotes_materials
		WHERE id = '$id'
		";		
		$results = $database->getRow($get_materials);		
		
		return $results ;
	}
	
	
	
	
	public function get_total($id)
	{	
		global $database;
		
		 
		$get_materials  = "
		SELECT SUM(total) as total
		FROM  quotes_materials
		WHERE quote_id = '$id' AND job_flag='0'
		";		
		$results = $database->getOne($get_materials);		
		
		return $results ;
	}
	
	
	
	
	public function get_materials_inventory()
	{	
		global $database;
		
		$db_name = DB_CLIENT;
		$submitter = $_SESSION['user']['id'];
		$current_date_time = date('Y-m-d G:i:s');
		$update  = "
		SELECT id,item_name,supplier,item_number,total_quantity
		FROM materials_inventory WHERE status =1
			";		
		$results = $database->query($update);
		
		foreach($results as $result)
		{
		
			$str .= '"'.stripslashes($result['item_number']).':'.stripslashes($result['item_name']).':'.stripslashes($result['supplier']).':'.$result['total_quantity'].':'.$result['id'].'",';
		}		
		
		$str = substr($str,0,-1);
		
		return $str;
	}
	
	public function get_materials_inventory_by_code()
	{	
		global $database;
		
		$db_name = DB_CLIENT;
		$submitter = $_SESSION['user']['id'];
		$current_date_time = date('Y-m-d G:i:s');
		$update  = "
		SELECT id,item_name,supplier,item_number,total_quantity
		FROM materials_inventory
		ORDER BY item_number ASC
			";		
		$results = $database->query($update);
		
		foreach($results as $result)
		{
		
			$str .= '"'.$result['item_number'].':'.stripslashes($result['item_name']).':'.stripslashes($result['supplier']).':'.$result['total_quantity'].':'.$result['id'].'",';
		}		
		
		$str = substr($str,0,-1);
		
		return $str;
	}
	
	
	public function get_suppliers_inventory()
	{	
		global $database;
		
		$db_name = DB_CLIENT;
		$submitter = $_SESSION['user']['id'];
		$current_date_time = date('Y-m-d G:i:s');
		$update  = "
		SELECT item_name,supplier,item_number
		FROM materials_inventory
			";		
		$results = $database->query($update);
		
		foreach($results as $result)
		{
		
			$str .= '"'.stripslashes($result['supplier']).':'.stripslashes($result['item_name']).':'.$result['item_number'].'",';
		}		
		
		return $str;
	}
	
	public function fetch_all_materials_inventory(){
		global $database;
		
		$db_name = DB_CLIENT;
		$submitter = $_SESSION['user']['id'];
		$current_date_time = date('Y-m-d G:i:s');
		$inventory  = "SELECT id,CONCAT(item_name,' : ',supplier,' : ',item_number) as name FROM materials_inventory";
		$results = $database->query($inventory);
		
		return $results;
	}
	
	/**fetch material quoted in the quotes with task - Does The client Accept the Quote?*/
	public function fetch_quote_material_client_accept_quote($material_no=0){
		global $database;
		
		$get_materials  = "
		SELECT quotes_materials.*,materials_inventory.total_quantity
		FROM  quotes_materials
		LEFT JOIN materials_inventory ON materials_inventory.id = quotes_materials.material_no
		LEFT JOIN project_workflow ON project_workflow.foreign_id = quotes_materials.quote_id AND project_workflow.table='quotes' AND project_workflow.workflow_id IN (1,4,6,7)
		WHERE project_workflow.direction =''
		AND quotes_materials.material_no !=0
		";
		
		if($material_no!=0){
			$get_materials .= " AND quotes_materials.material_no =$material_no";
		}
		
		$results = $database->getAll($get_materials);
		
		return $results;
	}
	
	
	/**fetch aggregated material quoted in the quotes with task - Does The client Accept the Quote?*/
	public function fetch_quote_material_client_accept_quote_summary(){
		global $database;
	
		$get_materials  = "
		SELECT  quotes_materials.material_no,quotes_materials.materials, quotes_materials.item,
				sum(quotes_materials.quantity) as total_required, 
				sum(quotes_materials.actual_used) as total_used,
				sum(materials_inventory.total_quantity) as total_available
		FROM  quotes_materials
		LEFT JOIN materials_inventory ON materials_inventory.id = quotes_materials.material_no
		LEFT JOIN project_workflow ON project_workflow.foreign_id = quotes_materials.quote_id AND project_workflow.table='quotes' AND project_workflow.workflow_id IN (1,4,6,7)
		WHERE project_workflow.direction =''
		AND quotes_materials.material_no !=0
		GROUP BY quotes_materials.material_no,quotes_materials.item,quotes_materials.materials 
		ORDER BY quotes_materials.item";
		//WITH ROLLUP";
		$results = $database->getAll($get_materials);
	
		return $results;
	}

	public function check_exists($data){
		global $database;
		$sql  = "SELECT COUNT(*) as cnt FROM quotes_materials
				WHERE materials = '".$data['materials']."'
				AND supplier = '".$data['supplier']."'
				AND quantity = '".$data['quantity']."'
				AND price = '".$data['price']."'
				AND quote_id= '".$data['quote_id']."'";

		$results = $database->getOne($sql);
		if($results>0)
			return true;
		else{
			return false;
		}
	}
}