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

function calculate_child_cost($id){
	global $conn;
	$total_cost=0;
	$total_labour_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_final_cost = $total_cost + $total_labour_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);
}
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_product_mapping_ajax'){
	$id = $_REQUEST['product_id'];
	//echo "DELETE FROM job_product_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']=='ajax_job_product_fetch'){ 
	////////////// For Listing data through JSON ///////////////////////////
	$job_id = $_REQUEST['job_id'];
    $query_for_status = "SELECT * FROM job_product_mapping WHERE 1 = 1 AND job_id='$job_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 .= '{"product":"'  . $gsi->getProductById($row_status['product']).'",';
			$equipment_for_status .= '"estimated_value":"'  . $row_status['estimated_value'].'",';
			$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_job_product'){ 
	////////////// 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']=='get_edit_job_product'){
	$id = $_REQUEST['id'];
	$sql= "SELECT * FROM job_product_mapping 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']=='delete_job_notes'){
	$notes_id = $_REQUEST['notes_id'];
	mysqli_query($conn,"DELETE FROM job_notes WHERE id = '$notes_id'");
	$returnArray["status"] = "success";
	echo json_encode($returnArray);
}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_total_comonents_cost'){
    $dataArray = array();
    $dataArray["status"] = 0;
    $product_grp_id = $_REQUEST['product_grp_id'];

    $sql = "SELECT id FROM product_group_mapping WHERE parent_group_id = ?";
    $stmt = mysqli_prepare($conn, $sql);
    mysqli_stmt_bind_param($stmt, "i", $product_grp_id);
    mysqli_stmt_execute($stmt);
    $result = mysqli_stmt_get_result($stmt);
    
    if(mysqli_num_rows($result) > 0){
        $totalComCost = 0;
        $totalLabourHours = 0;
        $totalLabourCost = 0;
        $totalCost = 0;
        $totalQuantity = 0; // Variable to hold total quantity
        
        while($row = mysqli_fetch_assoc($result)){
            $id = $row['id'];
            
            $hoursSql = "SELECT components_costs, labour_hours, labour_hours_total_parts, total_cost, quantity FROM product_main WHERE FIND_IN_SET(?, parent_product_groups)";
            $hoursStmt = mysqli_prepare($conn, $hoursSql);
            mysqli_stmt_bind_param($hoursStmt, "i", $id);
            mysqli_stmt_execute($hoursStmt);
            $hoursResult = mysqli_stmt_get_result($hoursStmt);
            
            if(mysqli_num_rows($hoursResult) > 0){
                while ($hoursRow = mysqli_fetch_assoc($hoursResult)) {
                    $totalComCost += $hoursRow['components_costs'] /* * $hoursRow['quantity'] */;
                    $totalLabourHours += $hoursRow['labour_hours'];
                    $totalLabourCost += $hoursRow['labour_hours_total_parts'];
                    $totalCost += $hoursRow['total_cost'];
                    
                    $totalQuantity += $hoursRow['quantity']; // Add quantity to total
                }
            }
			//$totalCost = $totalLabourCost + $totalComCost; 
        }

        $dataArray["total_com_cost"] = $totalComCost;
        $dataArray["total_labour_hours"] = $totalLabourHours;
        $dataArray["total_labour_cost"] = $totalLabourCost;
        $dataArray["total_cost"] = $totalCost;
        $dataArray["total_quantity"] = $totalQuantity; // Add total quantity to result
        $dataArray["status"] = 1;
    }
    
    echo json_encode($dataArray);
}


die;
?>