<?php
include("../../../include/startup.php"); 

ob_clean();
function is_cyclic($parent,$child){
	global $conn;
	$adj_list=array();
	$adj_list[]=$parent;
	$stack[]=$child;
	
	while(count($stack)>0){
		$id=$stack[0];
		$adj_list[]=$id;
		array_splice($stack, 0, 1);
		$sql="select * from product_link where parent_id='".$id."'";
		$res=mysqli_query($conn,$sql);
		while($row=mysqli_fetch_assoc($res)){
			if(in_array($row["child_id"], $adj_list)){
				//echo "Cyclic";
				return 1;
			}else{
				$stack[]=$row["child_id"];
			}
			
		}
		
	}
	//echo "Not Cyclic ".$parent."->".$child;
	return 0;
}

function get_parent_list($id){
	global $conn;
	$parent_list=array();
	$stack=array();
	$stack[]=$id;
	while(count($stack)>0){
		$id=$stack[0];
		$parent_list[]=$id;
		array_splice($stack, 0, 1);
		$sql="select * from product_link where parent_id='".$id."'";
		$res=mysqli_query($conn,$sql);
		while($row=mysqli_fetch_assoc($res)){
			$stack[]=$row["child_id"];
		}
		
	}
	return implode(",",$parent_list);
}
function calculate_child_cost($id){
	global $conn;
	$total_cost=0;
	$total_labour_cost=0;
	$total_machine_cost=0;
	
	$stack=array();
	$stack[]=$id;
	while(count($stack)>0){
		$id=$stack[0];
		array_splice($stack, 0, 1);
		$sql="select * from product_name where id='".$id."'";
		$res=mysqli_query($conn,$sql);
		$row=mysqli_fetch_assoc($res);
		$total_cost=$total_cost + ($row["cost"] * $row["qty"]);
		$total_labour_hours=$total_labour_hours + ($row["labour_hours"]);
		$total_labour_cost=$total_labour_cost + ($row["labour_cost"] * 1);
		$total_machine_cost=$total_machine_cost + ($row["machine_cost"]);
		$total_final_cost = $total_cost + $total_labour_cost + $total_machine_cost;
		$sql="select * from product_link where parent_id='".$id."'";
		$res=mysqli_query($conn,$sql);
		while($row=mysqli_fetch_assoc($res)){
			$stack[]=$row["child_id"];
		}
		
	}
	
	return array($total_labour_hours,$total_cost,$total_labour_cost,$total_final_cost,$total_machine_cost);
}
if(isset($_REQUEST['action']) && $_REQUEST['action']=='get_package_price'){
	$packages_id = $_REQUEST['packages_id'];
	$row = utility::getTableRowByField("fixed_packages","id",$packages_id);
	$returnArray["price"] = $row["price"];
	echo json_encode($returnArray);
}else if(isset($_REQUEST['action']) && $_REQUEST['action']=='get_hourly_packages_price'){
	$hourly_packages_id = $_REQUEST['hourly_packages_id'];
	$row = utility::getTableRowByField("hourly_packages","id",$hourly_packages_id);
	$returnArray["price"] = $row["price"];
	
	if($hourly_packages_id == 1){
		$months = 3;
	}else if($hourly_packages_id == 2){
		$months = 6;
	}else if($hourly_packages_id == 3){
		$months = 12;
	}
	//$days_in_a_month = utility::getTableFieldById('keycode','code','name','days_in_a_month');
	//$hours_in_a_day = utility::getTableFieldById('keycode','code','name','hours_in_a_day');
	//$total_hours = $months * $days_in_a_month * $hours_in_a_day;
	$total_hours = $row["total_consultancy_hours"];
	$returnArray["total_hours"] = $total_hours;
	echo json_encode($returnArray);
}else if(isset($_REQUEST['action']) && $_REQUEST['action']=='send_mail'){
	include('send_mail_modal.php');
	die;
}else if(isset($_REQUEST['action']) && $_REQUEST['action']=='mail_send'){
	$job_id = $_REQUEST['job_id'];
	$admin_id = utility::getTableFieldById("job_admin_mapping","admin_id","job_id",$job_id);
	$mail_format_subject = addslashes($_REQUEST['mail_format_subject']);
	$mail_format_body =  str_replace("Â","",$_REQUEST['mail_format_body']);
	$mail_format_body = addslashes($mail_format_body);
	$email_id = utility::getTableFieldById("job","email","id",$job_id);
	$email_id_test = "parvindar1995@gmail.com";
	
	$email_date = date("Y-m-d");
	$sql = "INSERT INTO email_track(job_id,email_subject,email_body,email_id,email_date) VALUES('$job_id','$mail_format_subject','$mail_format_body','$email_id','$email_date')";
	mysqli_query($conn,$sql);
	$email_track_id = mysqli_insert_id($conn);
	/***************************** File Upload *************************************************/
	$digits = 5;
	
	$file_path 		= array();
	foreach($_FILES['mail_document'] as $file_pro => $file_value){
		foreach($_FILES['mail_document'][$file_pro] as $k => $v){
			if(!(isset($v) && $v !="")){
				continue;
			}
			${$file_pro}[$k] = $v;
		}
    }
	foreach($name as $key => $file_name){
		$my_path = $_SERVER['DOCUMENT_ROOT']."/{$other_fields["project_folder_name"]}admin/document_upload/";
		
		$random_number = rand(pow(10, $digits-1), pow(10, $digits)-1);
		$uploaddir = '../../document_upload/';
		$document_name_array = explode(".",$file_name);
		$document_name = $document_name_array[0]."_".$random_number.".".$document_name_array[1];
		$uploadfile = $uploaddir . $document_name;
		array_push($file_path,$my_path.$document_name);
		move_uploaded_file($tmp_name[$key], $uploadfile);
		$sql= "INSERT into mail_document_upload(job_id,email_track_id,document_name) VALUES('$job_id','$email_track_id','$document_name')";
		mysqli_query($conn,$sql);
	}
	/***************************** End File Upload *************************************************/	
	$mail_format_subject = $_REQUEST['mail_format_subject'];
	$mail_format_body = $_REQUEST['mail_format_body'];
	//echo $functions->HTMLemailWithoutSignatureBlock($mail_format_body,$admin_id);
	//die;
	//////////////////////// send mail ////////////////////
	$site_email = $config->siteEmail;
	if(utility::mailSMTP_new($email_id, $mail_format_subject, $functions->HTMLemailWithoutSignatureBlock($mail_format_body,$admin_id), $file_path,$site_email,$site_email,"","","",$admin_id)){
		utility::mailSMTP_new($email_id_test, $mail_format_subject, $functions->HTMLemailWithoutSignatureBlock($mail_format_body,$admin_id), $file_path,$site_email,$site_email,"","","",$admin_id);
		echo "Mail send successfully";
	}else{
		echo "There is some error.";
	}
	die;
	///////////////// End send mail  //////////////////////
}else if(isset($_REQUEST['action']) && $_REQUEST['action']=='timer_time'){
	//date_default_timezone_set('Europe/London');
	$job_id = $_REQUEST['job_id'];
	$button = $_REQUEST['button'];
	if($button == "start"){
		$_SESSION["timer_running"] = 1;
		$sql = "SELECT * FROM timer WHERE job_id = $job_id ";
		$rs = mysqli_query($conn,$sql);
		if(mysqli_num_rows($rs) > 0){
			$row = mysqli_fetch_assoc($rs);
			$is_running = $row["is_running"];
			$changed_on = date('Y-m-d H:i:s');
			if($is_running == 0){
				mysqli_query($conn,"UPDATE timer SET is_running = '1',changed_on = '$changed_on' WHERE job_id = $job_id ");
				$t_time = $row['timer_time'];
				$t_time_array = explode(":",$t_time);
				$t_time_hours = $t_time_array[0];
				$t_time_mins = $t_time_array[1];
				$t_time_secs = $t_time_array[2];
				$returnArray = array();
				$returnArray['t_time_hours'] = ltrim($t_time_hours,'0'); 
				$returnArray['t_time_mins'] = ltrim($t_time_mins,'0');
				$returnArray['t_time_secs'] = ltrim($t_time_secs,'0'); 
				echo json_encode($returnArray);
				die;
			}
		}else{
			$changed_on = date('Y-m-d H:i:s');
			mysqli_query($conn,"INSERT into timer(job_id,is_start,is_running,timer_time,changed_on) VALUES('$job_id','1','1','00:00:00','$changed_on')");
		}
		die;
	}else if($button == "pause"){
		$changed_on = date('Y-m-d H:i:s');
		$timer_value = $_REQUEST['timer_value'];
		mysqli_query($conn,"UPDATE timer SET timer_time = '$timer_value',is_running = '0',changed_on = '$changed_on' WHERE job_id = $job_id ");
	}else if($button == "page_reload"){
		$timer_value = $_REQUEST['timer_value'];
		$sql = "SELECT * FROM timer WHERE job_id = $job_id AND is_start = '1' ";
		$rs = mysqli_query($conn,$sql);
		if(mysqli_num_rows($rs) > 0){
			$row = mysqli_fetch_assoc($rs);
			$is_running = $row["is_running"];
			if($is_running == 1){
				$changed_on = $row["changed_on"];
				$seconds  = strtotime(date('Y-m-d H:i:s')) - strtotime($changed_on);
				$timer_time = $gsi->getTimerTimeInSecondByJobId($job_id);
				$seconds  = $seconds + $timer_time;
	 
				$hours = floor($seconds / 3600);
				$mins = floor(($seconds - ($hours*3600)) / 60);
				$secs = floor($seconds % 60);
				$timer_value_new = $hours.":".$mins.":".$secs;
				$changed_on = date('Y-m-d H:i:s');
				mysqli_query($conn,"UPDATE timer SET timer_time = '$timer_value_new',changed_on = '$changed_on' WHERE job_id = $job_id ");
				$t_time = utility::getTableFieldById("timer","timer_time","job_id",$job_id);
				$t_time_array = explode(":",$t_time);
				$t_time_hours = $t_time_array[0];
				$t_time_mins = $t_time_array[1];
				$t_time_secs = $t_time_array[2];
				$returnArray = array();
				$returnArray['timer_value_new'] = $t_time; 
				$returnArray['t_time_hours'] = $hours; 
				$returnArray['t_time_mins'] = $mins; 
				$returnArray['t_time_secs'] = $secs; 
				echo json_encode($returnArray);
				die;
			}else{
				$returnArray = array();
				$t_time = utility::getTableFieldById("timer","timer_time","job_id",$job_id);
				$t_time_array = explode(":",$t_time);
				$t_time_hours = $t_time_array[0];
				$t_time_mins = $t_time_array[1];
				$t_time_secs = $t_time_array[2];
				$returnArray = array();
				$returnArray['timer_value_new'] = $t_time; 
				$returnArray['t_time_hours'] = ltrim($t_time_hours,'0'); 
				$returnArray['t_time_mins'] = ltrim($t_time_mins,'0');
				$returnArray['t_time_secs'] = ltrim($t_time_secs,'0'); 
				$returnArray['pause'] = 1; 
				echo json_encode($returnArray);
				die;
			}
		}
	}else if($button == "clear"){
		unset($_SESSION["timer_running"]);
		$timer_value = $_REQUEST['timer_value'];
		mysqli_query($conn,"UPDATE timer SET timer_time = '$timer_value',is_start = '0' WHERE job_id = $job_id ");
		$timer_time_in_second = $gsi->getTimerTimeInSecondByJobId($job_id);
		$timer_time_in_minute = $timer_time_in_second / 60;
		if($timer_time_in_minute <= 15){
			$timesheet_time = "00:15:00";
		}else if($timer_time_in_minute > 15 && $timer_time_in_minute <= 30){
			$timesheet_time = "00:30:00";
		}else if($timer_time_in_minute > 30 && $timer_time_in_minute <= 45){
			$timesheet_time = "00:45:00";
		}else if($timer_time_in_minute > 45 && $timer_time_in_minute <= 60){
			$timesheet_time = "00:60:00";
		}else if($timer_time_in_minute > 60 && $timer_time_in_minute <= 75){
			$timesheet_time = "01:15:00";
		}else if($timer_time_in_minute > 75 && $timer_time_in_minute <= 90){
			$timesheet_time = "01:30:00";
		}else{
			$timesheet_time = $timer_value;
		}
		///////////////////////// Timesheet Enrty /////////////////////////////////////
		$source =  $gsi->getEnqQuoteJobStatusById($job_id);	
		$admin_id = $_SESSION['user_id'];
		$timesheet_text = "Timesheet entry";
		//$entry_for_month = $gsi->getEntryForMonthByJobId($job_id);
		$timesheet_date	 = date("Y-m-d");
		//$timesheet_time = $timer_value;
		$created_on = date("Y-m-d");
		$sql= "INSERT INTO timesheet (job_id,source,admin_id,timesheet_text,timesheet_date,timesheet_time,updated_by,created_on) VALUES('$job_id','$source','$admin_id','$timesheet_text','$timesheet_date','$timesheet_time','".$_SESSION['user_id']."','$created_on')";
		mysqli_query($conn,$sql);
		///////////////////////// End Timesheet Enrty /////////////////////////////////////
		mysqli_query($conn,"DELETE FROM timer WHERE job_id = $job_id ");
	}
}else if(isset($_REQUEST['action']) && $_REQUEST['action']=='get_address_by_org_id'){
	$dataArray = array();
	$dataArray["status"] = 0;
	$org_id = $_REQUEST['org_id'];
	$sql = "SELECT * FROM organisation_management WHERE id = '$org_id' ";
	$rs = mysqli_query($conn,$sql);
	if(mysqli_num_rows($rs) > 0){
		$row = mysqli_fetch_assoc($rs);
		$row=utility::validate_crypt_data($row);
		
		$dataArray["address1"] 		= $row['address1'];
		$dataArray["address2"] 		= $row['address2'];
		$dataArray["address3"] 		= $row['address3'];
		$dataArray["city"] 			= $row['city'];
		$dataArray["county"] 		= $row['county'];
		$dataArray["postcode"] 		= $row['postcode'];
		$dataArray["phone_no"] 		= $row['phone_no'];
		$dataArray["landline_phone_no"] = $row['landline_phone_no'];
		$dataArray["status"]		= 1;
	}
	echo json_encode($dataArray);

}else if(isset($_REQUEST['action']) && $_REQUEST['action']=='validate_client_email'){
	$email = utility::app_crypt($_REQUEST['email'],'e');
	$id = $_REQUEST['id'];
	$where = "";
	if(isset($id) && $id !=""){
		$where = " AND id != '$id' ";
	}
	$returnArray = array();
	$sql = "SELECT * FROM job WHERE email = '$email' $where";
	$rs = mysqli_query($conn,$sql);
	if(mysqli_num_rows($rs) > 0){
		$row = mysqli_fetch_assoc($rs);
		$returnArray['status'] = "failure";
	}else{
		$returnArray['status'] = "success";
	}
	echo json_encode($returnArray);	
}else if(isset($_REQUEST['action']) && $_REQUEST['action']=='validate_enquiry_email'){
	$email = utility::app_crypt($_REQUEST['email'],'e');
	$id = $_REQUEST['id'];
	$where = "";
	if(isset($id) && $id !=""){
		$where = " AND id != '$id' ";
	}
	$returnArray = array();
	$sql = "SELECT * FROM enquiry_firstname_mapping WHERE email = '$email' $where";
	$rs = mysqli_query($conn,$sql);
	if(mysqli_num_rows($rs) > 0){
		$row = mysqli_fetch_assoc($rs);
		$returnArray['status'] = "failure";
	}else{
		$returnArray['status'] = "success";
	}
	echo json_encode($returnArray);	
}else if(isset($_REQUEST['action']) && $_REQUEST['action']=='count_no_of_appointment'){
	$returnArray = array();
	$returnArray["status"] = 0;
	$job_id = $_REQUEST['job_id'];
	$sql = "SELECT count(id) as total_appointment FROM appointment WHERE job_id = '$job_id' ";
	$rs = mysqli_query($conn,$sql);
	if(mysqli_num_rows($rs) > 0){
		$row = mysqli_fetch_assoc($rs);
		$returnArray["total_appointment"] = $row['total_appointment'];
		$returnArray["status"] = "success";
	}
	echo json_encode($returnArray);

}else if(isset($_REQUEST['action']) && $_REQUEST['action']=='count_no_of_notes'){
	$returnArray = array();
	$returnArray["status"] = 0;
	$job_id = $_REQUEST['job_id'];
	$sql = "SELECT count(id) as total_notes FROM notes WHERE note_type = 0 AND job_id = '$job_id' ";
	$rs = mysqli_query($conn,$sql);
	if(mysqli_num_rows($rs) > 0){
		$row = mysqli_fetch_assoc($rs);
		$returnArray["total_notes"] = $row['total_notes'];
		$returnArray["status"] = "success";
	}
	echo json_encode($returnArray);

}else if(isset($_REQUEST['action']) && $_REQUEST['action']=='count_no_of_reminder'){
	$returnArray = array();
	$returnArray["status"] = 0;
	$job_id = $_REQUEST['job_id'];
	$sql = "SELECT count(id) as total_reminder FROM notes WHERE note_type = 1 AND job_id = '$job_id' ";
	$rs = mysqli_query($conn,$sql);
	if(mysqli_num_rows($rs) > 0){
		$row = mysqli_fetch_assoc($rs);
		$returnArray["total_reminder"] = $row['total_reminder'];
		$returnArray["status"] = "success";
	}
	echo json_encode($returnArray);

}else if(isset($_REQUEST['action']) && $_REQUEST['action']=='get_part_num_category_zone'){
	$dataArray = array();
	$dataArray["status"] = 0;
	$part_num_category_id = $_REQUEST['part_num_category_id'];
	$sql = "SELECT id,part_num_internal FROM category WHERE id = '$part_num_category_id' ";
	$rs = mysqli_query($conn,$sql);
	if(mysqli_num_rows($rs) > 0){
		$row = mysqli_fetch_assoc($rs);
		$row=utility::validate_crypt_data($row);
		$dataArray["part_num_internal"]  = $row['part_num_internal'];
		$dataArray["status"]		= 1;
	}
	echo json_encode($dataArray);

}else if(isset($_REQUEST['action']) && $_REQUEST['action']=='delete_enquiry_mapping_name'){
	$id = $_REQUEST['na_mapping_id'];
	//echo "DELETE FROM job_enquiry_mapping WHERE id = '$id' ";
	mysqli_query($conn,"DELETE FROM job_enquiry_mapping WHERE id = '$id' ");
	$returnArray["status"] = "success";
	echo json_encode($returnArray);
}
else if(isset($_REQUEST['action']) && $_REQUEST['action']=='delete_product_mapping'){
	$id = $_REQUEST['na_mapping_id'];
	//echo "DELETE FROM job_enquiry_mapping WHERE id = '$id' ";
	mysqli_query($conn,"DELETE FROM job_product_mapping WHERE id = '$id' ");
	$returnArray["status"] = "success";
	echo json_encode($returnArray);
}
else if(isset($_REQUEST['action']) && $_REQUEST['action']=='delete_inventory_ajax'){
	$id = $_REQUEST['inventory_id'];
	//echo "DELETE FROM job_product_mapping WHERE id = '$id' ";
	mysqli_query($conn,"DELETE FROM inventory WHERE id = '$id' ");
	$returnArray["status"] = "success";
	echo json_encode($returnArray);
}else if(isset($_REQUEST['action']) && $_REQUEST['action']=='ajax_inventory_fetch'){ 
	////////////// For Listing data through JSON ///////////////////////////
	$item_id = $_REQUEST['item_id'];
    $query_for_status = "SELECT * FROM inventory WHERE 1 = 1 AND item_id='$item_id' ORDER BY id DESC ";
	
	$sql_for_status = mysqli_query($conn,$query_for_status);
	$result_for_status = mysqli_num_rows($sql_for_status);
	$equipment_for_status = "";
	if($result_for_status > 0){
		
		$equipment_for_status .= '{';
		$equipment_for_status .= '"data" : [';
		while($row_status = mysqli_fetch_assoc($sql_for_status)){
			$equipment_for_status .= '{"quantity_in_stock":"'  . $row_status['quantity_in_stock'].'",';
			$equipment_for_status .= '"minimum_stock":"'  . $row_status['minimum_stock'].'",';
			$equipment_for_status .= '"id":"'  . $row_status['id'] . '"},';
			
		}
		$equipment_for_status = substr($equipment_for_status,0,-1);
		$equipment_for_status .= "]";
		$equipment_for_status .= '}';
	}else{
		$equipment_for_status .= '{';
		$equipment_for_status .= '"data" : [';
		$equipment_for_status .= "]";
		$equipment_for_status .= '}';
	}
	echo $equipment_for_status;
}else if(isset($_REQUEST['action']) && $_REQUEST['action']=='add_item_inventory'){ 
	////////////// For Insert and Update ///////////////////////////
	utility::addslashesOnAllInput(array("query_string"));
    	
	$page_action		 = $_REQUEST['page_action_inventory'];	
	$id				 	 = $_REQUEST['id'];	
	$item_id		 		 = $_REQUEST['item_id'];	
	$quantity_in_stock			 = $_REQUEST['quantity_in_stock'];	
	$quantity_allocated     = $_REQUEST['quantity_allocated'];
	$minimum_stock     = $_REQUEST['minimum_stock'];
	$maximum_stock     = $_REQUEST['maximum_stock'];
	$minimum_ebq     = $_REQUEST['minimum_ebq'];
	$maximum_ebq     = $_REQUEST['maximum_ebq'];
	$standard_cost_ebq     = $_REQUEST['standard_cost_ebq'];
	$default_workshop     = $_REQUEST['default_workshop'];
	$default_receipt_location     = $_REQUEST['default_receipt_location'];
	$default_issue_location     = $_REQUEST['default_issue_location'];
	
	$created_by			 = $_SESSION["user_id"];
	$updated_by		 	 = $_SESSION["user_id"];
	$created_on		 	 = date("Y-m-d H:i:s");
	$updated_on		 	 = date("Y-m-d H:i:s");
	
	
	if($page_action == "add"){
    	$sql = "INSERT INTO inventory (item_id, quantity_in_stock, quantity_allocated, minimum_stock, maximum_stock, minimum_ebq, maximum_ebq, standard_cost_ebq, default_workshop, default_receipt_location, default_issue_location, created_by, created_on, updated_by) VALUES('$item_id','$quantity_in_stock','$quantity_allocated','$minimum_stock','$maximum_stock','$minimum_ebq','$maximum_ebq','$standard_cost_ebq','$default_workshop','$default_receipt_location','$default_issue_location','$created_by','$created_on','$updated_by')"; 
    	mysqli_query($conn,$sql);
	}else{
		$sql= "UPDATE inventory SET quantity_in_stock = '$quantity_in_stock',quantity_allocated = '$quantity_allocated',minimum_stock = '$minimum_stock',maximum_stock = '$maximum_stock',minimum_ebq = '$minimum_ebq',maximum_ebq = '$maximum_ebq',standard_cost_ebq = '$standard_cost_ebq',default_workshop = '$default_workshop',default_receipt_location = '$default_receipt_location',default_issue_location = '$default_issue_location',updated_by = '$updated_by' ,updated_on = '$updated_on' WHERE id = '$id'";
		mysqli_query($conn,$sql);
	  }

}else if(isset($_REQUEST['action']) && $_REQUEST['action']=='get_edit_inventory'){
	$id = $_REQUEST['id'];
	$sql= "SELECT * FROM inventory WHERE id = $id";
    $result_edit = mysqli_query($conn,$sql);
    $row_edit = mysqli_fetch_assoc($result_edit);

    $data_array = array();
	foreach($row_edit as $key => $value){
		if($key == "created_on"){
			//$value = utility::getFormatedDateReturn($value);
			$valueDate = date("d-m-Y", strtotime($value));
			$data_array["created_on"] = $valueDate;
		}else{
			 $data_array[$key] = $value;
            //$data_array[$key . '_base64'] = base64_encode(file_get_contents($value));
		}
	}
	
	$returnArray["data_array"] = $data_array;
	$returnArray["status"] = "success";
	
    echo json_encode($returnArray, JSON_FORCE_OBJECT);
}else if(isset($_REQUEST['action']) && $_REQUEST['action']=='add_job_notes'){ 
	////////////// For Insert and Update ///////////////////////////
	utility::addslashesOnAllInput(array("query_string"));
	
	$id					 = $_REQUEST['id'];
	$page_action		 = $_REQUEST['page_action_notes'];
	$job_id		 		 = $_REQUEST['job_id'];
	$note_text			 = $_REQUEST['note_text'];
	$created_by			 = $_SESSION["user_id"];
	$updated_by		 	 = $_SESSION["user_id"];
	$created_on		 	 = date("Y-m-d H:i:s");
	
	if($page_action == "add"){
		$sql = "job_id = '".$job_id."',note_text = '".$note_text."',created_by = '".$created_by."', created_on = '".$created_on."', updated_by = '".$updated_by."'";
		 $sql= "INSERT INTO job_notes SET ".$sql;
		mysqli_query($conn,$sql);
	}else{
		$sql = "note_text = '".$note_text."',updated_by = '".$updated_by."'";
		$sql = "UPDATE job_notes SET ".$sql." WHERE id = '".$id."'";
		mysqli_query($conn,$sql);
	}
}else if(isset($_REQUEST['action']) && $_REQUEST['action']=='ajax_job_notes_fetch'){ 
	////////////// For Listing data through JSON ///////////////////////////
	$job_id = $_REQUEST['job_id'];
    $query_for_notes = "SELECT * FROM job_notes WHERE 1=1 AND job_id='$job_id' ORDER BY id DESC ";
	$sql_for_notes = mysqli_query($conn,$query_for_notes);
	$result_for_notes = mysqli_num_rows($sql_for_notes);
	$notes_post_out = "";
	if($result_for_notes > 0){
		
		$notes_post_out .= '{';
		$notes_post_out .= '"data" : [';
		while($row_notes = mysqli_fetch_assoc($sql_for_notes)){
			
			$notes_post_out .= '{"note_text":"'  . $row_notes['note_text']. '",';
			$notes_post_out .= '"created_by":"'  . Admin::getAdminsById($row_notes["created_by"]). '",';
			$notes_post_out .= '"created_on":"'  . date("d-m-Y g:i:s A", strtotime($row_notes['created_on'])). '",';
			$notes_post_out .= '"id":"'  . $row_notes['id'] . '"},';
		}
		$notes_post_out = substr($notes_post_out,0,-1);
		$notes_post_out .= "]";
		$notes_post_out .= '}';
	}else{
		$notes_post_out .= '{';
		$notes_post_out .= '"data" : [';
		$notes_post_out .= "]";
		$notes_post_out .= '}';
	}
	echo $notes_post_out;
}else if(isset($_REQUEST['action']) && $_REQUEST['action']=='get_edit_job_notes'){
	$id = $_REQUEST['id'];
	$sql= "SELECT * FROM job_notes WHERE id = '$id'";
    $result_edit = mysqli_query($conn,$sql);
    $row_edit = mysqli_fetch_assoc($result_edit);

    $data_array = array();
	foreach($row_edit as $key => $value){
		if($key == "created_on"){
			$value = utility::getFormatedDateReturn($value);
		}else{
			$data_array[$key] = $value;
		}
	}
	
	$returnArray["data_array"] = $data_array;
	$returnArray["status"] = "success";
	echo json_encode($returnArray, JSON_FORCE_OBJECT);
}else if(isset($_REQUEST['action']) && $_REQUEST['action']=='get_product_grp_cost'){
	$dataArray = array();
	$dataArray["items"][]=array("part_number"=>"");
	$dataArray["status"] = 0;
	$product_group = $_REQUEST['product_group'];
		$id=$product_group;
		$sql="select * from product_name where id='".$id."'";
		$res=mysqli_query($conn,$sql);
		
		$rs = mysqli_query($conn,$sql);
		
		if(mysqli_num_rows($rs) > 0){
			$row = mysqli_fetch_assoc($rs);
			$child_cost=calculate_child_cost($row["id"]);
			$dataArray["labour_hours"]  	 =$child_cost[0];
			$dataArray["total_com_cost"]  	 =$child_cost[1];
			$dataArray["total_labour_cost"]  =$child_cost[2];
			$dataArray["total_cost"] 		 =$child_cost[1]+$child_cost[2];
			$dataArray["short_code"]  	  	 =strtoupper($row["group_code"]);
			$dataArray["group_name"]  	  	 =ucwords($row["group_name"]);
			$dataArray["id"]  	  	 =$row["id"];
			$dataArray["status"]			 = 1;
		}
	
	echo json_encode($dataArray);

}else if(isset($_REQUEST['action']) && $_REQUEST['action']=='add_product_stock'){ 
	////////////// For Insert and Update ///////////////////////////
	utility::addslashesOnAllInput(array("query_string"));
    	
	$page_action		 = $_REQUEST['page_action_product'];	
	$id				 	 = $_REQUEST['id'];	
	$job_id		 		 = $_REQUEST['job_id'];	
	$product			 = $_REQUEST['product'];	
	$estimated_value     = $_REQUEST['estimated_value'];
	
	$created_by			 = $_SESSION["user_id"];
	$updated_by		 	 = $_SESSION["user_id"];
	$created_on		 	 = date("Y-m-d H:i:s");
	
	
	if($page_action == "add"){
		 $sql = "INSERT INTO job_product_mapping (job_id,product,estimated_value,created_by,created_on,updated_by) VALUES('$job_id','$product','$estimated_value','$created_by','$created_on','$updated_by')";
		mysqli_query($conn,$sql);
	}else{		
		 $sql= "UPDATE job_product_mapping SET product = '$product',estimated_value = '$estimated_value',updated_by = '$updated_by' WHERE id = '$id' ";
		mysqli_query($conn,$sql);
	}
}else if(isset($_REQUEST['action']) && $_REQUEST['action']=='ajax_product_stock_fetch'){ 
	////////////// For Listing data through JSON ///////////////////////////
	$pro_id = $_REQUEST['pro_id'];
    $query_for_status = "SELECT id,pro_id ,stock_location,stock_ltion_qty FROM product_stock WHERE 1 = 1 AND pro_id='$pro_id' ORDER BY id DESC ";
	
	$sql_for_status = mysqli_query($conn,$query_for_status);
	$result_for_status = mysqli_num_rows($sql_for_status);
	$equipment_for_status = "";
	if($result_for_status > 0){
		
		$equipment_for_status .= '{';
		$equipment_for_status .= '"data" : [';
		while($row_status = mysqli_fetch_assoc($sql_for_status)){
			$product_name = utility::getTableFieldById("product_main","name","id",$row_status['pro_id']);
			$equipment_for_status .= '{"pro_id":"'  . $product_name .'",';
			$equipment_for_status .= '"stock_location":"'  . $gsi->getLocationById($row_status['stock_location']).'",';
			$equipment_for_status .= '"stock_ltion_qty":"'  . $row_status['stock_ltion_qty'].'",';
			$equipment_for_status .= '"id":"'  . $row_status['id'] . '"},';
			
		}
		
		$equipment_for_status = substr($equipment_for_status,0,-1);
		$equipment_for_status .= "]";
		$equipment_for_status .= '}';
	}else{
		$equipment_for_status .= '{';
		$equipment_for_status .= '"data" : [';
		$equipment_for_status .= "]";
		$equipment_for_status .= '}';
	}
	echo $equipment_for_status;
}else if(isset($_REQUEST['action']) && $_REQUEST['action']=='delete_product_stock_mapping'){
	$id = $_REQUEST['product_id'];
	//echo "DELETE FROM job_enquiry_mapping WHERE id = '$id' ";
	mysqli_query($conn,"DELETE FROM product_stock WHERE id = '$id' ");
	mysqli_query($conn,"DELETE FROM product_stock_dipartment_map WHERE pro_stock_id = '$id' ");
	$returnArray["status"] = "success";
	echo json_encode($returnArray);
}
else if(isset($_REQUEST['action']) && $_REQUEST['action']=='delete_department_mapping'){
	$id = $_REQUEST['na_mapping_id'];
	//echo "DELETE FROM job_enquiry_mapping WHERE id = '$id' ";
	mysqli_query($conn,"DELETE FROM product_stock_dipartment_map WHERE id = '$id' ");
	$returnArray["status"] = "success";
	echo json_encode($returnArray);
}else if(isset($_REQUEST['action']) && $_REQUEST['action']=='get_edit_stock_product'){
	$id = $_REQUEST['id'];
	$sql= "SELECT * FROM product_stock WHERE id = $id";
    $result_edit = mysqli_query($conn,$sql);
    $row_edit = mysqli_fetch_assoc($result_edit);

    $data_array = array();
	foreach($row_edit as $key => $value){
		if($key == "stock_check_date"){
			$valueDate = date("d-m-Y", strtotime($value));
			$data_array["stock_check_date"] = $valueDate;
		}else{
			 $data_array[$key] = $value;
		}
	}
				
	$returnArray["data_array"] = $data_array;
	$returnArray["status"] = "success";
	
    echo json_encode($returnArray, JSON_FORCE_OBJECT);
}else if(isset($_REQUEST['action']) && $_REQUEST['action']=='get_cyclic_status'){
	$parent=$_REQUEST["parent_id"];
	$child=$_REQUEST["child_id"];
	$dataArray=array();
	$dataArray["status"]="sucess";
	$dataArray["result"]=is_cyclic($parent,$child);	
	echo json_encode($dataArray);
    die;
}/* else if(isset($_REQUEST['action']) && $_REQUEST['action']=='get_group_details'){
	$id = $_REQUEST['id'];
	$pro_id = $_REQUEST['pro_id'];
	$data=array();
		$p_id = utility::getTableFieldById("product_name","id","pro_id",$pro_id);
		$sql = "SELECT * FROM product_name WHERE pro_id = '$pro_id'";

		if ($pro_id) {
			$sql .= " UNION SELECT * FROM product_name WHERE id IN (SELECT child_id FROM product_link WHERE parent_id = '$p_id')";
		}
		$sql .= " ORDER BY id DESC";
		
	$res = mysqli_query($conn,$sql);
	
	if(mysqli_num_rows($res) > 0){
		while($row = mysqli_fetch_array($res)){
			$child_cost = calculate_child_cost($row["id"]);
			$checked_status=0;
			$cycle_status=1;
			if($_REQUEST["page_edit"]="edit"){
				$sql="select * from product_link where parent_id='".$id."' and child_id='".$row["id"]."'";
				//echo "<br/>".$sql;
				$res1=mysqli_query($conn,$sql);
				if(mysqli_num_rows($res1)>0){
					$checked_status=1;
				}
				$row1=mysqli_fetch_array($res1);
				if($id!=$row["id"]){
					//echo "<br/>R".$row["id"];
					$cycle_status=is_cyclic($id,$row["id"]);
				}
			}
			$data[]=array("checked_status"=>$checked_status,
							"cycle_status"=>$cycle_status,
							"chk_val"=>$row["id"],
							"group_code"=>strtoupper($row["group_code"]),
							"group_name"=>"<a href='#' data-toggle='modal' data-target='#ShowModal' alt='".$row["id"]."' class='text-primary'>".ucwords($row["group_name"])."</a>",
							"qty"=>$row["qty"].'   '.$row["unit"],
							"cost"=>$row["cost"],
							"labour_cost"=>$row["labour_cost"],
							"labour_hours"=>$row["labour_hours"],
							"group_number"=>$row["group_number"],
							"total_cost"=>$child_cost[1],
							"total_labour_cost"=>$child_cost[2],
							"total_final_cost"=>$child_cost[1]+$child_cost[2]
							);
		}
	}
	
	$finaldata["data"]=$data;
	//echo "<pre>";
	//print_r($finaldata);
	//echo "</pre>";
    
	echo json_encode($finaldata);
   
} */else if(isset($_REQUEST['action']) && $_REQUEST['action']=='get_group_details_edit'){
	$id = $_REQUEST['id'];
	$pro_id = $_REQUEST['pro_id'];
	$data=array();
	$sql="SELECT * FROM product_name order by id DESC";
	$res = mysqli_query($conn,$sql);
	
	if(mysqli_num_rows($res) > 0){
		while($row = mysqli_fetch_array($res)){
			$child_cost = calculate_child_cost($row["id"]);
			$checked_status=0;
			$cycle_status=1;
			if($_REQUEST["page_edit"]="edit"){
				$sql="select * from product_link where parent_id='".$id."' and child_id='".$row["id"]."'";
				//echo "<br/>".$sql;
				$res1=mysqli_query($conn,$sql);
				if(mysqli_num_rows($res1)>0){
					$checked_status=1;
				}
				$row1=mysqli_fetch_array($res1);
				if($id!=$row["id"]){
					//echo "<br/>R".$row["id"];
					$cycle_status=is_cyclic($id,$row["id"]);
				}
			}
			$data[]=array("checked_status"=>$checked_status,
							"cycle_status"=>$cycle_status,
							"chk_val"=>$row["id"],
							"group_code"=>strtoupper($row["group_code"]),
							"group_name"=>"<a href='#' data-toggle='modal' data-target='#ShowModal' alt='".$row["id"]."' class='text-primary'>".ucwords($row["group_name"])."</a>",
							"qty"=>$row["qty"].'   '.$row["unit"],
							"cost"=>$row["cost"],
							"labour_cost"=>$row["labour_cost"],
							"labour_hours"=>$row["labour_hours"],
							"group_number"=>$row["group_number"],
							"total_cost"=>$child_cost[1],
							"total_labour_cost"=>$child_cost[2],
							"total_final_cost"=>$child_cost[1]+$child_cost[2]
							);
		}
	}
	
	$finaldata["data"]=$data;
	//echo "<pre>";
	//print_r($finaldata);
	//echo "</pre>";
    
	echo json_encode($finaldata);
   
}elseif(isset($_REQUEST['action']) && $_REQUEST['action']=='get_group_list'){
	//$data[]="Ajax Call Data";
	//echo json_encode($data);
    //die;
	$id=$_REQUEST["id"];
	$sql = "SELECT main_product_id, sub_part_id, sub_part_name, quantity_required, type, unit_cost, 
               product_labour_cost, product_labour_hours, machine_cost, machine_hours 
        FROM bom";
$result = $conn->query($sql);

$bomData = [];
if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        $bomData[] = [
            'main_product_id' => $row['main_product_id'],
            'sub_part_id' => $row['sub_part_id'],
            'sub_part_name' => $row['sub_part_name'],
            'quantity_required' => $row['quantity_required'],
            'type' => $row['type'],
            'unit_cost' => $row['unit_cost'],
            'product_labour_cost' => $row['product_labour_cost'],
            'product_labour_hours' => $row['product_labour_hours'],
            'machine_cost' => $row['machine_cost'],
            'machine_hours' => $row['machine_hours']
        ];
    }
}

// SQL Query for root product
$sql_1 = "SELECT pro_id, group_code, qty, group_name, cost, labour_cost, labour_hours, machine_cost, machine_hours 
          FROM product_name WHERE id = $id";
$resul_1 = $conn->query($sql_1);

if ($resul_1->num_rows > 0) {
    $row = $resul_1->fetch_assoc();
    $rootProduct = [
        'main_product_id' => 0,
        'sub_part_id' => $row['pro_id'],
        'sub_part_name' => $row['group_name'],
        'quantity_required' => $row['qty'],
        'type' => $row['group_code'],
        'unit_cost' => $row['cost'],
        'product_labour_cost' => $row['labour_cost'],
        'product_labour_hours' => $row['labour_hours'],
        'machine_cost' => $row['machine_cost'],
        'machine_hours' => $row['machine_hours']
    ];
}

array_unshift($bomData, $rootProduct);

    // Function to build the tree
function buildTree($data, $parentId, $parentQuantity = 1) {
    $tree = [];
    foreach ($data as $item) {
        if ($item['main_product_id'] == $parentId) {
            $item['adjusted_quantity'] = $item['quantity_required'] * $parentQuantity;

            // Calculate individual costs
            $item['total_labour_cost'] = $item['product_labour_cost'] * $item['adjusted_quantity'];
            $item['total_labour_hours'] = $item['product_labour_hours'] * $item['adjusted_quantity'];
            $item['total_machine_cost'] = $item['machine_cost'] * $item['adjusted_quantity'];
            $item['total_machine_hours'] = $item['machine_hours'] * $item['adjusted_quantity'];

            // Base total cost = unit cost + labour cost + machine cost
            $item['total_cost'] = ($item['unit_cost'] * $item['adjusted_quantity']) +
                                  $item['total_labour_cost'] +
                                  $item['total_machine_cost'];

            // Recursively process children
            $item['children'] = buildTree($data, $item['sub_part_id'], $item['adjusted_quantity']);

            // Add children's costs to parent node
            foreach ($item['children'] as $child) {
                $item['total_cost'] += $child['total_cost']; // Add child total costs
                $item['total_labour_cost'] += $child['total_labour_cost'];
                $item['total_machine_cost'] += $child['total_machine_cost'];
            }

            $tree[] = $item;
        }
    }
    return $tree;
}


    // Function to display the tree
function displayTree($tree, $level = 0) {
    $output = "<ul>";
    foreach ($tree as $node) {
        $indent = str_repeat("&nbsp;&nbsp;&nbsp;", $level);
        $output .= "<li>";
        $output .= "{$indent}<b>{$node['sub_part_name']}</b> - {$node['type']} 
                    | Qty: {$node['adjusted_quantity']} 
                    | Unit Cost: {$node['unit_cost']} 
                    | L Cost: {$node['total_labour_cost']} 
                    | L Hours: {$node['total_labour_hours']} 
                    | M Cost: {$node['total_machine_cost']} 
                    | M Hours: {$node['total_machine_hours']}
					| Total Cost: {$node['total_cost']}";
        if (!empty($node['children'])) {
            $output .= displayTree($node['children'], $level + 1);
        }
        $output .= "</li>";
    }
    $output .= "</ul>";
    return $output;
}


    // Build the tree
    $tree = buildTree($bomData, 0);
	
	
	
	
	function displayTreeTable($tree, $level = 0) {
    foreach ($tree as $node) {
        $indent = str_repeat("&nbsp;&nbsp;&nbsp;", $level); 
        echo "<tr>";
        echo "<td>{$indent}{$node['sub_part_name']}</td>";
        echo "<td>{$node['type']}</td>";
        echo "<td>{$node['adjusted_quantity']}</td>";
        echo "<td>{$node['unit_cost']}</td>";
        echo "<td>{$node['total_cost']}</td>";
        echo "<td>
                <form method='POST' action='delete_item.php' style='display:inline;'>
                    <input type='hidden' name='id' value='{$node['sub_part_id']}'>
                    <button type='submit' onclick=\"return confirm('Are you sure you want to delete this item?');\" style='border:none; background:none; cursor:pointer;'>
                        <i class='fas fa-trash-alt' style='color:red;'></i>
                    </button>
                </form>
              </td>";
        echo "</tr>";
        
        if (!empty($node['children'])) {
            displayTreeTable($node['children'], $level + 1); 
        }
    }
}
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	

    // Prepare the response
    if (!empty($tree)) {
        // Return HTML content for the tree in the response
        echo json_encode([
            'status' => 'success',
            'htmlContent' => displayTree($tree)
        ]);
    } else {
        // Return a message if no data
        echo json_encode([
            'status' => 'error',
            'message' => 'No data to display'
        ]);
    }

    die;
}elseif(isset($_REQUEST['action']) && $_REQUEST['action']=='get_group_list_for_listing'){
	//$data[]="Ajax Call Data";
	//echo json_encode($data);
    //die;
	$id = $_REQUEST["id"];
$sql = "SELECT main_product_id, sub_part_id, sub_part_name, quantity_required, type, unit_cost, 
               product_labour_cost, product_labour_hours, machine_cost, machine_hours 
        FROM bom";
$result = $conn->query($sql);

$bomData = [];
if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        $bomData[] = [
            'main_product_id' => $row['main_product_id'],
            'sub_part_id' => $row['sub_part_id'],
            'sub_part_name' => $row['sub_part_name'],
            'quantity_required' => $row['quantity_required'],
            'type' => $row['type'],
            'unit_cost' => $row['unit_cost'],
            'product_labour_cost' => $row['product_labour_cost'],
            'product_labour_hours' => $row['product_labour_hours'],
            'machine_cost' => $row['machine_cost'],
            'machine_hours' => $row['machine_hours']
        ];
    }
}

$sql_1 = "SELECT pro_id, group_code, qty, group_name, cost, labour_cost, labour_hours, machine_cost, machine_hours 
          FROM product_name WHERE id = $id";
$resul_1 = $conn->query($sql_1);

if ($resul_1->num_rows > 0) {
    $row = $resul_1->fetch_assoc();
    $rootProduct = [
        'main_product_id' => 0,
        'sub_part_id' => $row['pro_id'],
        'sub_part_name' => $row['group_name'],
        'quantity_required' => $row['qty'],
        'type' => $row['group_code'],
        'unit_cost' => $row['cost'],
        'product_labour_cost' => $row['labour_cost'],
        'product_labour_hours' => $row['labour_hours'],
        'machine_cost' => $row['machine_cost'],
        'machine_hours' => $row['machine_hours']
    ];
    array_unshift($bomData, $rootProduct);
}

function buildTree($data, $parentId, $parentQuantity = 1) {
    $tree = [];
    foreach ($data as $item) {
        if ($item['main_product_id'] == $parentId) {
            $item['adjusted_quantity'] = $item['quantity_required'] * $parentQuantity;

            // Calculate individual costs
            $item['total_labour_cost'] = $item['product_labour_cost'] * $item['adjusted_quantity'];
            $item['total_labour_hours'] = $item['product_labour_hours'] * $item['adjusted_quantity'];
            $item['total_machine_cost'] = $item['machine_cost'] * $item['adjusted_quantity'];
            $item['total_machine_hours'] = $item['machine_hours'] * $item['adjusted_quantity'];

            // Base total cost = unit cost + labour cost + machine cost
            $item['total_cost'] = ($item['unit_cost'] * $item['adjusted_quantity']) +
                                  $item['total_labour_cost'] +
                                  $item['total_machine_cost'];

            // Recursively process children
            $item['children'] = buildTree($data, $item['sub_part_id'], $item['adjusted_quantity']);

            // Add children's costs to parent node
            foreach ($item['children'] as $child) {
                $item['total_cost'] += $child['total_cost']; // Add child total costs
                $item['total_labour_cost'] += $child['total_labour_cost'];
                $item['total_machine_cost'] += $child['total_machine_cost'];
            }

            $tree[] = $item;
        }
    }
    return $tree;
}


function displayTreeTable($tree, $level = 0) {
    $output = '';
    foreach ($tree as $node) {
        $indent = str_repeat("&nbsp;&nbsp;&nbsp;", $level);
        $output .= "<tr id='row-{$node['sub_part_id']}'>";
        
        $output .= "<td><input type='checkbox' class='select-checkbox' name='chk[]' value='{$node['sub_part_id']}'></td>";
        $output .= "<td>{$indent}{$node['sub_part_name']}</td>";
        $output .= "<td>{$node['type']}</td>";
        $output .= "<td>{$node['adjusted_quantity']}</td>";
        $output .= "<td>{$node['unit_cost']}</td>";
        $output .= "<td>{$node['total_labour_cost']}</td>";
        $output .= "<td>{$node['total_labour_hours']}</td>";
        $output .= "<td>{$node['total_machine_cost']}</td>";
        $output .= "<td>{$node['total_machine_hours']}</td>";
        $output .= "<td>{$node['total_cost']}</td>";
        $output .= "<td>
            <button class='delete-btn' data-id='{$node['sub_part_id']}' data-main-product-id='{$node['main_product_id']}' style='border:none; background:none; cursor:pointer;'>
                <i class='fas fa-trash-alt' style='color:red;'></i>
            </button>
        </td>";
        $output .= "</tr>";

        if (!empty($node['children'])) {
            $output .= displayTreeTable($node['children'], $level + 1);
        }
    }
    return $output;
}

$tree = buildTree($bomData, 0);

if (!empty($tree)) {
    ob_start();
    echo "<table class='table table-bordered table-striped' style='width: 100%;'>
            <thead>
                <tr>
                    <th></th>
                    <th>Part Name</th>
                    <th>Type</th>
                    <th>Quantity</th>
                    <th>Unit Cost</th>
                    <th>L Cost</th>
                    <th>L Hours</th>
                    <th>M Cost</th>
                    <th>M Hours</th>
					<th>Total Cost</th>
                    <th>Delete</th>
                </tr>
            </thead>
            <tbody>";
    echo displayTreeTable($tree);
    echo "</tbody></table>";
    $tableContent = ob_get_clean();

    echo json_encode([
        'status' => 'success',
        'htmlContent' => $tableContent
    ]);
} else {
    echo json_encode([
        'status' => 'error',
        'message' => 'No data to display'
    ]);
}

die;
}else if(isset($_REQUEST['action']) && $_REQUEST['action'] == 'delete_table_item') {
    $id = isset($_POST['id']) ? intval($_POST['id']) : 0;
    $mainProductId = isset($_POST['main_product_id']) ? intval($_POST['main_product_id']) : 0;
    
    mysqli_query($conn, "DELETE FROM bom WHERE sub_part_id = '$id' AND main_product_id = '$mainProductId'");

	
   
      echo json_encode(['status' => 'success', 'message' => 'Item deleted successfully.']);
}else if(isset($_REQUEST['action']) && $_REQUEST['action'] == 'delete_job_notes'){
    $notes_id = $_REQUEST['notes_id'];

    // First, let's delete the entry with ID = $notes_id from product_name table
    mysqli_query($conn, "DELETE FROM product_name WHERE id = '$notes_id'");

    // Now, let's find and delete all related entries in product_link table where parent_id = $notes_id
    mysqli_query($conn, "DELETE FROM product_link WHERE parent_id = '$notes_id'");

    // Now, let's find and delete all related entries in product_link table where child_id = $notes_id
    mysqli_query($conn, "DELETE FROM product_link WHERE child_id = '$notes_id'");

    // Also, let's check if there are any child links for $notes_id, and delete those records as well
    $child_ids_query = mysqli_query($conn, "SELECT child_id FROM product_link WHERE parent_id = '$notes_id'");
    if(mysqli_num_rows($child_ids_query) > 0){
        while($row = mysqli_fetch_assoc($child_ids_query)) {
            $child_id = $row['child_id'];

            // Delete child link records
            mysqli_query($conn, "DELETE FROM product_link WHERE child_id = '$child_id'");

            // Delete corresponding entries from product_name table
            mysqli_query($conn, "DELETE FROM product_name WHERE id = '$child_id'");
        }
    }

    // Finally, let's return success status
    $returnArray["status"] = "success";
    echo json_encode($returnArray);
}

else if(isset($_REQUEST['action']) && $_REQUEST['action']=='add_job_parts_used'){ 
	////////////// For Insert and Update ///////////////////////////
	utility::addslashesOnAllInput(array("query_string"));
	$table_name = "product_extra_info";
	$id					 = $_REQUEST['id'];
	$page_action		 = $_REQUEST['page_action_part_used'];
	$pro_id		 = $_REQUEST['pro_id'];
	$sales_unit			 = $_REQUEST['sales_unit'];
	$conversion_rate			 = $_REQUEST['conversion_rate'];
	$purchase_unit			 = $_REQUEST['purchase_unit'];
	$standard_unit			 = $_REQUEST['standard_unit'];
	$created_by			 = $_SESSION["user_id"];
	$updated_by		 	 = $_SESSION["user_id"];
	$created_on		 	 = date("Y-m-d H:i:s");
	
	if($page_action == "add"){
		$sql = "pro_id = '".$pro_id."',sales_unit = '".$sales_unit."',conversion_rate = '".$conversion_rate."',purchase_unit = '".$purchase_unit."',standard_unit = '".$standard_unit."'";
		 $sql= "INSERT INTO $table_name SET ".$sql;
		mysqli_query($conn,$sql);
	}else{
		$sql = "sales_unit = '".$sales_unit."',conversion_rate = '".$conversion_rate."',purchase_unit = '".$purchase_unit."',standard_unit = '".$standard_unit."'";
		$sql = "UPDATE $table_name SET ".$sql." WHERE id = '".$id."'";
		mysqli_query($conn,$sql);
	}
}else if(isset($_REQUEST['action']) && $_REQUEST['action']=='ajax_job_parts_used_fetch'){ 
	////////////// For Listing data through JSON ///////////////////////////
	$pro_id = $_REQUEST['pro_id'];
    $query_for_part_used = "SELECT * FROM product_extra_info WHERE 1=1 AND pro_id='$pro_id' ORDER BY id DESC ";
	$sql_for_part_used = mysqli_query($conn,$query_for_part_used);
	$result_for_part_used = mysqli_num_rows($sql_for_part_used);
	$parts_used_post_out = "";
	if($result_for_part_used > 0){
		
		$parts_used_post_out .= '{';
		$parts_used_post_out .= '"data" : [';
		while($row_for_part_used = mysqli_fetch_assoc($sql_for_part_used)){
			
			$parts_used_post_out .= '{"sales_unit":"'  . $row_for_part_used['sales_unit']. '",';
			$parts_used_post_out .= '"conversion_rate":"'  . $row_for_part_used['conversion_rate']. '",';
			$parts_used_post_out .= '"purchase_unit":"'  . $row_for_part_used['purchase_unit']. '",';
			$parts_used_post_out .= '"standard_unit":"'  . $row_for_part_used['standard_unit']. '",';
			$parts_used_post_out .= '"id":"'  . $row_for_part_used['id'] . '"},';
		}
		$parts_used_post_out = substr($parts_used_post_out,0,-1);
		$parts_used_post_out .= "]";
		$parts_used_post_out .= '}';
	}else{
		$parts_used_post_out .= '{';
		$parts_used_post_out .= '"data" : [';
		$parts_used_post_out .= "]";
		$parts_used_post_out .= '}';
	}
	echo $parts_used_post_out;
}else if(isset($_REQUEST['action']) && $_REQUEST['action']=='get_edit_job_part_used'){
	$id = $_REQUEST['id'];
	$sql= "SELECT * FROM product_extra_info WHERE id = '$id'";
    $result_edit = mysqli_query($conn,$sql);
    $row_edit = mysqli_fetch_assoc($result_edit);

    $data_array = array();
	foreach($row_edit as $key => $value){
		if($key == "created_on"){
			$value = utility::getFormatedDateReturn($value);
		}else{
			$data_array[$key] = $value;
		}
	}
	
	$returnArray["data_array"] = $data_array;
	$returnArray["status"] = "success";
	echo json_encode($returnArray, JSON_FORCE_OBJECT);
}else if(isset($_REQUEST['action']) && $_REQUEST['action']=='delete_job_part_used'){
	$extra_id = $_REQUEST['extra_id'];
	mysqli_query($conn,"DELETE FROM product_extra_info WHERE id = '$extra_id'");
	$returnArray["status"] = "success";
	echo json_encode($returnArray);
}else if(isset($_REQUEST['action']) && $_REQUEST['action']=='delete_item_images'){
	$id = $_REQUEST['imageid'];
	$product_image = $_REQUEST['product_image'];
		$dir = "../product_images/";
		if(file_exists($dir.$product_image)){ 
			unlink($dir.$product_image);				
		}
	mysqli_query($conn,"DELETE FROM item_images WHERE id = '$id' ");
	$returnArray["status"] = "success";
	echo json_encode($returnArray);
}else if(isset($_REQUEST['action']) && $_REQUEST['action']=='add_route_details'){ 
	////////////// For Insert and Update ///////////////////////////
	utility::addslashesOnAllInput(array("query_string"));
	$table_name = "route_details";
	$id					 = $_REQUEST['id'];
	$page_action		 = $_REQUEST['page_action_part_used'];
	$item_id		 = $_REQUEST['item_id'];
	$route_no = $_REQUEST['route_no'];
    $route_desc = $_REQUEST['route_desc'];
    $part_no = $_REQUEST['part_no'];
    $previous_part_no = $_REQUEST['previous_part_no'];
    $operation = $_REQUEST['operation'];
    $machine_lock = isset($_REQUEST['machine_lock']) ? 1 : 0;
    $machine_high_priority = $_REQUEST['machine_high_priority'];
    $machine_medium_priority = $_REQUEST['machine_medium_priority'];
    $machine_low_priority = $_REQUEST['machine_low_priority'];
    $machine_rate = $_REQUEST['machine_rate'];
    $set_time = $_REQUEST['set_time'];
    $operation_time = $_REQUEST['operation_time'];
    $operation_unit = 0;
    $parts_per_operation = $_REQUEST['parts_per_operation'];
    $workers_needed = $_REQUEST['workers_needed'];
   $labour_rate = isset($_REQUEST['labour_rate']) && $_REQUEST['labour_rate'] !== '' ? (float) $_REQUEST['labour_rate'] : 0;

    $notes = $_REQUEST['notes'];
    
	$created_by					= $_SESSION['user_id'];
	$created_on					= date("Y-m-d H:i:s");	
	$updated_by					= $_SESSION['user_id'];
	  
	list($hours, $minutes) = explode(':', $operation_time);
		$hours = (int) $hours;  
		$minutes = (int) $minutes;  
		$total_hours = $hours + ($minutes / 60);  
		$labor_cost = $total_hours * $labour_rate;  

	if($page_action == "add"){
		    $sql = "INSERT INTO $table_name (route_no,item_id,route_desc, part_no, previous_part_no, operation, machine_lock, 
            machine_high_priority, machine_medium_priority, machine_low_priority, machine_rate, set_time, operation_time,operation_unit, 
            parts_per_operation, workers_needed, labour_rate, notes,created_on,created_by,updated_by) 
            VALUES ('$route_no','$item_id','$route_desc', '$part_no', '$previous_part_no', '$operation', '$machine_lock', 
            '$machine_high_priority', '$machine_medium_priority', '$machine_low_priority', '$machine_rate', '$set_time', 
            '$operation_time', '$operation_unit','$parts_per_operation', '$workers_needed', '$labour_rate', '$notes', '$created_on', '$created_by', '$updated_by')";
         mysqli_query($conn, $sql);
		 mysqli_query($conn,"UPDATE product_name SET machine_cost = '$machine_rate' ,machine_hours = '$total_hours',labour_hours = '$total_hours',labour_cost = '$labor_cost' WHERE pro_id = '$item_id' ");
	}else{
	        $sql = "UPDATE $table_name SET route_desc = '$route_desc', part_no = '$part_no', previous_part_no = '$previous_part_no',
            operation = '$operation', machine_lock = '$machine_lock', machine_high_priority = '$machine_high_priority',
            machine_medium_priority = '$machine_medium_priority', machine_low_priority = '$machine_low_priority', machine_rate = '$machine_rate',
            set_time = '$set_time', operation_time = '$operation_time',operation_unit = '$operation_unit', parts_per_operation = '$parts_per_operation',
            workers_needed = '$workers_needed', labour_rate = '$labour_rate', notes = '$notes', updated_by = '$updated_by' WHERE item_id = $item_id";
    mysqli_query($conn, $sql);
	 mysqli_query($conn,"UPDATE product_name SET machine_cost = '$machine_rate' ,machine_hours = '$total_hours',labour_hours = '$total_hours',labour_cost = '$labor_cost'  WHERE pro_id = '$item_id' ");
	}
}else if(isset($_REQUEST['action']) && $_REQUEST['action'] == 'remove_item_link'){
    $notes_id = $_REQUEST['notes_id'];

    // First, let's delete the entry with ID = $notes_id from product_name table
    //mysqli_query($conn, "DELETE FROM product_name WHERE id = '$notes_id'");

    // Now, let's find and delete all related entries in product_link table where parent_id = $notes_id
    mysqli_query($conn, "DELETE FROM product_link WHERE parent_id = '$notes_id'");
	mysqli_query($conn, "DELETE FROM bom WHERE main_product_id = '$notes_id' AND sub_part_id = '$sub_part_id'");

    // Now, let's find and delete all related entries in product_link table where child_id = $notes_id
    mysqli_query($conn, "DELETE FROM product_link WHERE child_id = '$notes_id'");

    // Also, let's check if there are any child links for $notes_id, and delete those records as well
    $child_ids_query = mysqli_query($conn, "SELECT child_id FROM product_link WHERE parent_id = '$notes_id'");
    if(mysqli_num_rows($child_ids_query) > 0){
        while($row = mysqli_fetch_assoc($child_ids_query)) {
            $child_id = $row['child_id'];

            // Delete child link records
            mysqli_query($conn, "DELETE FROM product_link WHERE child_id = '$child_id'");

            // Delete corresponding entries from product_name table
            //mysqli_query($conn, "DELETE FROM product_name WHERE id = '$child_id'");
        }
    }

    // Finally, let's return success status
    $returnArray["status"] = "success";
    echo json_encode($returnArray);
}else if(isset($_REQUEST['action']) && $_REQUEST['action']=='get_group_details'){
	$id = $_REQUEST['id'];
	$pro_id = $_REQUEST['pro_id'];
	$data=array();
		$p_id = utility::getTableFieldById("product_name","id","pro_id",$pro_id);
		$sql = "SELECT * FROM product_name WHERE pro_id = '$pro_id'";

		/* if ($pro_id) {
			$sql .= " UNION SELECT * FROM product_name WHERE id IN (SELECT child_id FROM product_link WHERE parent_id = '$p_id')";
		} */
		$sql .= " ORDER BY id DESC";
		
	$res = mysqli_query($conn,$sql);
	
	if(mysqli_num_rows($res) > 0){
		while($row = mysqli_fetch_array($res)){
			$child_cost = calculate_child_cost($row["id"]);
			$checked_status=0;
			$cycle_status=1;
			if($_REQUEST["page_edit"]="edit"){
				$sql="select * from product_link where parent_id='".$id."' and child_id='".$row["id"]."'";
				//echo "<br/>".$sql;
				$res1=mysqli_query($conn,$sql);
				if(mysqli_num_rows($res1)>0){
					$checked_status=1;
				}
				$row1=mysqli_fetch_array($res1);
				if($id!=$row["id"]){
					//echo "<br/>R".$row["id"];
					$cycle_status=is_cyclic($id,$row["id"]);
				}
			}
			$data[]=array("checked_status"=>$checked_status,
							"cycle_status"=>$cycle_status,
							"chk_val"=>$row["id"],
							"group_code"=>strtoupper($row["group_code"]),
							"group_name"=>"<a href='#' data-toggle='modal' data-target='#ShowModal' alt='".$row["id"]."' class='text-primary'>".ucwords($row["group_name"])."</a>",
							"qty"=>$row["qty"].'   '.$row["unit"],
							"cost"=>$row["cost"],
							"labour_cost"=>$row["labour_cost"],
							"labour_hours"=>$row["labour_hours"],
							"group_number"=>$row["group_number"],
							"m_cost"=>$row["machine_cost"],
							"m_hrs"=>$row["machine_hours"],
							"total_final_cost"=>$child_cost[1]+$child_cost[2]+$row["machine_cost"]
							);
		}
	}
	
	$finaldata["data"]=$data;
	//echo "<pre>";
	//print_r($finaldata);
	//echo "</pre>";
    
	echo json_encode($finaldata);
   
}else if (isset($_REQUEST['action']) && $_REQUEST['action'] == 'get_part_details') {
    $dataArray = array();
    $dataArray["status"] = "failure";

    $part_id = $_REQUEST['part_id'];

    // Fetch parent product details
    $sql = "SELECT id ,group_number,group_name,group_code,labour_cost,cost,machine_cost,machine_hours,labour_hours,qty FROM product_name WHERE pro_id = '$part_id'";
    $rs = mysqli_query($conn, $sql);

    if (mysqli_num_rows($rs) > 0) {
        $row = mysqli_fetch_assoc($rs);
        $dataArray["status"] = "success";

        // Initialize total cost
        $totalChildCost = 0;

        // Fetch child products linked to the parent
        $childSql = "
            SELECT pn.cost ,pn.qty 
            FROM product_link pl
            INNER JOIN product_name pn ON pl.child_id = pn.id
            WHERE pl.parent_id = '".$row['id']."'
        ";
        $childRs = mysqli_query($conn, $childSql);

        if (mysqli_num_rows($childRs) > 0) {
            while ($childRow = mysqli_fetch_assoc($childRs)) {
                $totalChildCost += $childRow['cost']*  $childRow['qty'];
            }
        }

        // Add fetched details to the response
        $dataArray["group_number"] = $row['group_number'];
        $dataArray["group_name"] = $row['group_name'];
        $dataArray["cost"] = $row['cost'];
        $dataArray["labour_cost"] = $row['labour_cost'];
        $dataArray["labour_hours"] = $row['labour_hours'];
        $dataArray["group_code"] = $row['group_code'];
        $dataArray["qty"] = $row['qty'];
        $dataArray["machine_cost"] = $row['machine_cost'];
        $dataArray["machine_hours"] = $row['machine_hours'];
        $dataArray["total_child_cost"] = $totalChildCost + $row['cost'] ; // Total cost of child components
    }

    echo json_encode($dataArray);
}


die;
?>