<?php
$where 					= " WHERE 1=1 AND (is_funeral = 1 ";
$job_contract_ids		= $gsi->getJobIdsFrom("enquiry","celebrant","funeral");
if(isset($job_contract_ids) && $job_contract_ids !=""){
	$where 	.= " OR id IN($job_contract_ids)) ";
}else{
	$where 	.= " )";
}
$next_page_condition 	= "";
$paging ="";
if($_REQUEST['start']){
	$paging .="&start=".$_REQUEST['start'];
}

$j_id = utility::app_crypt($_REQUEST['j_id'],'d');
if($_REQUEST['id'] != $j_id){
	die("Invaild URL");
}
####################################### ARCHIVING/UNARCHIVING ORGANISATION #######################################
if(isset($_REQUEST['action']) && $_REQUEST['action'] == 'archive'){	
	foreach($_REQUEST["client_ids"] as $cli_id){
		mysqli_query($conn,"UPDATE job SET is_archive = '1' WHERE id = '$cli_id' ");
	}
	$query_string = (isset($_REQUEST['query_string']) && !empty($_REQUEST['query_string'])) ? $_REQUEST['query_string'] : "";
	$query_string = str_ireplace("action=archive", "", $query_string);
	$query_string = "&".$query_string;
	
	$_SESSION['info_message'] = "Client(s) archived sucessfully";
	header("Location: index?page=view_funeral$query_string");
	exit();
}
if(isset($_REQUEST['action']) && $_REQUEST['action'] == 'unarchive'){	
	foreach($_REQUEST["client_ids"] as $cli_id){
		mysqli_query($conn,"UPDATE job SET is_archive = '0' WHERE id = '$cli_id' ");
	}
	$query_string = (isset($_REQUEST['query_string']) && !empty($_REQUEST['query_string'])) ? $_REQUEST['query_string'] : "";
	$query_string = str_ireplace("action=unarchive", "", $query_string);
	$query_string = "&".$query_string;
	
	$_SESSION['info_message'] = "Client(s) unarchived sucessfully";
	header("Location: index?page=view_funeral$query_string");
	exit();
}
################################################## END ARCHIVING/UNARCHIVING ORGANISATION ###################################
###########################################  STRAT FILTER CONITIONS    #####################################
$search 	= isset($_REQUEST['search']) ? $_REQUEST['search'] : "";
if($search) {
	if(strpos($search," ") !== false){
		$search_array = explode(" ",$search);
		$search_str1 = $search_array[0];
		$search_str2 = $search_array[1];
		$where	.= " AND ((first_name = '$search_str1' AND last_name = '$search_str2') OR (first_name = '$search_str2' AND last_name = '$search_str1')) ";
		$next_page_condition.="&search=$search";
	}else{
		$where	.= " AND (id = '$search' OR company_name LIKE '%$search%' OR first_name LIKE '%$search%' OR last_name LIKE '%$search%' OR email LIKE '%".utility::app_crypt($search,'e')."%' OR postcode LIKE '%$search%') ";
		$next_page_condition.="&search=$search";
	}
}
$funeral_director 	= isset($_REQUEST['funeral_director']) ? $_REQUEST['funeral_director'] : "";
if($funeral_director) {
    $where	.= " AND funeral_director = '$funeral_director' ";
    $next_page_condition.="&funeral_director=$funeral_director";
}
$crematorium 	= isset($_REQUEST['crematorium']) ? $_REQUEST['crematorium'] : "";
if($crematorium) {
    $where	.= " AND crematorium = '$crematorium' ";
    $next_page_condition.="&crematorium=$crematorium";
}


$phone_no 	= isset($_REQUEST['phone_no']) ? $_REQUEST['phone_no'] : "";
if($phone_no) {
	//$enc_phone_no = utility::app_crypt($phone_no,"e");
    //$where	.= " AND (phone_no1 = '$enc_phone_no' OR phone_no2 = '$enc_phone_no' OR phone_no3 = '$enc_phone_no') ";
	$job_ids = "";
	$sql1 = "select * FROM job_phone_log where phone LIKE '%".$phone_no."%' ";
	$rs1 =  mysqli_query($conn,$sql1);
	if(mysqli_num_rows($rs1) > 0){
		while($row1 = mysqli_fetch_assoc($rs1)){
			$job_ids .= utility::app_crypt($row1["phone_log"],"d").",";
		}
		$job_ids = substr($job_ids,0,-1);
	}
    $where	.= " AND id IN($job_ids) ";
    $next_page_condition.="&phone_no=$phone_no";
}

$email_search 	= isset($_REQUEST['email_search']) ? $_REQUEST['email_search'] : "";
if($email_search) {
	//$enc_phone_no = utility::app_crypt($email_search,"e");
    //$where	.= " AND (phone_no1 = '$enc_phone_no' OR phone_no2 = '$enc_phone_no' OR phone_no3 = '$enc_phone_no') ";
	$job_ids = "";
	$sql2 = "select * FROM job_email_log where email LIKE '%".$email_search."%' ";
	$rs2 =  mysqli_query($conn,$sql2);
	if(mysqli_num_rows($rs2) > 0){
		while($row2 = mysqli_fetch_assoc($rs2)){
			$job_ids .= utility::app_crypt($row2["email_log"],"d").",";
		}
		$job_ids = substr($job_ids,0,-1);
	}
    $where	.= " AND id IN($job_ids) ";
    $next_page_condition.="&email_search=$email_search";
}

/* $sector 	= isset($_REQUEST['sector']) ? implode(",",$_REQUEST['sector']) : "";
if($sector) {
    $where	.= " AND sector LIKE '%$sector%' ";
	$sector = explode(",",$sector);
    $next_page_condition.="&sector=$sector";
} */

$county 	= isset($_REQUEST['county']) ? $_REQUEST['county'] : "";
if($county) {
    $where	.= " AND county = '$county' ";
    $next_page_condition.="&county=$county";
}
$is_archive 	= isset($_REQUEST['is_archive']) ? $_REQUEST['is_archive'] : "";
if($is_archive) {	
    $where	.= " AND is_archive = '$is_archive' ";
    $next_page_condition.="&is_archive=$is_archive";
}else{
	$where	.= " AND is_archive = '0' ";
}
/*$show_archive 	= isset($_REQUEST['show_archive']) ? $_REQUEST['show_archive'] : "";
if(!($show_archive)) {
	$archive_status_id = $config->archive_status_id;
    if(!$job_id){
		$where	.= " AND prospect_status != '$archive_status_id' ";
		$next_page_condition.="&show_archive=$show_archive";
	}
}else{
		$archive_status_id = $config->archive_status_id;
		$where	.= " AND prospect_status = '$archive_status_id' ";
		$next_page_condition.="&show_archive=$show_archive";
}*/
$renewal_date 	= isset($_REQUEST['renewal_date']) ? $_REQUEST['renewal_date'] : "";
if($renewal_date) {	
	$from_date = isset($_REQUEST['from_date']) ? utility::setFormatedDate($_REQUEST['from_date']) : "";
	$to_date = isset($_REQUEST['to_date']) ? utility::setFormatedDate($_REQUEST['to_date']) : "";
	if($from_date && $to_date){
		$where1 = " AND job_contract.renewal_date BETWEEN '$from_date 00:00:00' and '$to_date 23:59:59' ";
		$next_page_condition.="&from_date=".$_REQUEST['from_date']."&to_date=".$_REQUEST['to_date']." ";
	}else if($from_date){
		$where1 = " AND job_contract.renewal_date >= '$from_date' ";
		$next_page_condition.="&from_date=".$_REQUEST['from_date'];
	}else if($to_date){
		$where1 = " AND job_contract.renewal_date <= '$to_date' ";
		$next_page_condition.="&to_date=".$_REQUEST['to_date'];
	}
	$job_ids = "";
	$sql1 = "SELECT * FROM job INNER JOIN job_contract ON job.id=job_contract.job_id WHERE 1=1 $where1 ";
	$rs1 =  mysqli_query($conn,$sql1);
	if(mysqli_num_rows($rs1) > 0){
		while($row1 = mysqli_fetch_assoc($rs1)){
			$job_ids .= $row1["job_id"].",";
		}
		$job_ids = substr($job_ids,0,-1);
	}
	$where 	.= " AND id IN($job_ids) ";
    $next_page_condition.="&renewal_date=$renewal_date";
}else{
	$from_date = isset($_REQUEST['from_date']) ? utility::setFormatedDate($_REQUEST['from_date']) : "";
	$to_date = isset($_REQUEST['to_date']) ? utility::setFormatedDate($_REQUEST['to_date']) : "";
	if($from_date && $to_date){
		$where .= " AND funeral_date BETWEEN '$from_date 00:00:00' and '$to_date 23:59:59' ";
		$next_page_condition.="&from_date=".$_REQUEST['from_date']."&to_date=".$_REQUEST['to_date']." ";
	}else if($from_date){
		$where.= " AND funeral_date >= '$from_date' ";
		$next_page_condition.="&from_date=".$_REQUEST['from_date'];
	}else if($to_date){
		$where.= " AND funeral_date <= '$to_date' ";
		$next_page_condition.="&to_date=".$_REQUEST['to_date'];
	}
}
$last_year 	= isset($_REQUEST['last_year']) ? $_REQUEST['last_year'] : "";
if($last_year){
	$current_date = date("Y-m-d");
	$to_date = strtotime($current_date.' -1 year');
	$from_date = strtotime($current_date.' -1 year -1 month');
	$to_date = date('Y-m-d', $to_date);
	$from_date = date('Y-m-d', $from_date);
	
	$where .= " AND funeral_date BETWEEN '$from_date 00:00:00' and '$to_date 23:59:59' ";
	$next_page_condition.="&last_year=$last_year";
}
$admin_id_search 	= isset($_REQUEST['admin_id_search']) ? $_REQUEST['admin_id_search'] : "";
if($_REQUEST['admin_id_search']){
	$consultant_ids = "";
	$sql1 = "select job_id FROM job_admin_mapping where admin_id = '".$admin_id_search."' ";
	$rs1 =  mysqli_query($conn,$sql1);
	if(mysqli_num_rows($rs1) > 0){
		while($row1 = mysqli_fetch_assoc($rs1)){
			$consultant_ids .= $row1["job_id"].",";
		}
		$consultant_ids = substr($consultant_ids,0,-1);
	}
	$where 	.= " AND id IN($consultant_ids) ";
	$next_page_condition.="&admin_id_search=".$_REQUEST['admin_id_search'];
}
###########################################  	END  FILTER CONITIONS 	#####################################
$admin_in = "";
$admin_in_ids = "";
if($show_only_my_things  == 1){
	$sql1 = "select job_id FROM job_admin_mapping where admin_id = '".$_SESSION['user_id']."' ";
	$rs1 =  mysqli_query($conn,$sql1);
	if(mysqli_num_rows($rs1) > 0){
		while($row1 = mysqli_fetch_assoc($rs1)){
			$admin_in_ids .= $row1["job_id"].",";
		}
		$admin_in_ids = substr($admin_in_ids,0,-1);
	}
	
	if($admin_in_ids){
		$admin_in = " AND id IN($admin_in_ids)";
	}
	
	$where.=$admin_in;
}
###########################################  PAGING WITH PER PAGE  #####################################
$file_name		= "index"; // this is file name which is used during paging  , included at the bottom of the page
$table_name		= "job";
$paging_table_name	= $table_name;
$paging_query		= $where;
$join_query_for_paging = "SELECT * FROM $table_name  $where ";
$page_header = "Client Management";
include("paging/paging_query.inc.php");
############################################ END PAGING WITH PER PAGE  #######################################
$query = "SELECT * FROM $table_name $where ORDER BY id DESC LIMIT $eu, $limit";
$sql=mysqli_query($conn,$query);
$result=mysqli_num_rows($sql);

$total_no_of_enquiries = mysqli_num_rows(mysqli_query($conn,$join_query_for_paging));
#################################   DELETE    #########################################
// when we click on delete this area of php code will execute
if(isset($_REQUEST['action']) && $_REQUEST['action']=='delete')
{
	$phone_log = utility::app_crypt($_REQUEST["id"],"e");
	$email_log = utility::app_crypt($_REQUEST["id"],"e");
	mysqli_query($conn,"DELETE FROM $table_name WHERE id = '".$_REQUEST['id']."' ");
	mysqli_query($conn,"DELETE FROM job_phone_log WHERE phone_log = '".$phone_log."' ");
	mysqli_query($conn,"DELETE FROM job_email_log WHERE email_log = '".$email_log."' ");
	include('common_delete.php');
	$_SESSION['info_message'] = "Funeral deleted sucessfully";
	header("location: $file_name?page=view_funeral".$next_page_condition.$paging);
	exit();
}
#################################  END DELETE    #########################################
//@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ To dublicate @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@//
if(isset($_REQUEST['action']) && $_REQUEST['action']=='duplicate'){
	include('duplicate.inc.php');
}
//@@@@@@@ Dublicate End @@@@@@@@@@@@@@@//
###########################################  End dublicate  #####################################
#################################  Export CSV    #########################################
if(isset($_REQUEST['action']) && $_REQUEST['action']=='export_job_details'){
  include("export_job_details_to_csv.inc.php");
}
###########################################  End Export  #####################################
?>
<div class="content-header">
    <div class="container-fluid">
        <div class="row mb-2">
            <div class="col-sm-12">
                <h1 class="m-0"><?=$page_header;?></h1>
            </div>
        </div>
        <!-- /.row -->
    </div>
    <!-- /.container-fluid -->
</div>
<section class="content">
	<div class="container-fluid">
		<div class="row">
			<div class="col-lg-12">
			<div class="card">
				<!----------------------- Message ------------------------------->
				<? if(isset($_SESSION['info_message'])){ ?>
				  <div class="alert alert-info alert-dismissable">
					<button type="button" class="close" data-dismiss="alert" aria-hidden="true">&times;</button>
					<? echo $_SESSION['info_message']; unset($_SESSION['info_message']);?>
				  </div>
				  <? } ?>
				<!----------------------- End Message ------------------------------->
				<div class="card">
					<div class="card-body">
					<!-- search form -->
					<?php include_once('funeral_management_search_options.inc.php');?>
					<!-- /.search form -->
					Total No. of enquiries <?=$total_no_of_enquiries;?>
					<!--<div style="float:right;margin-bottom:4px;">Export Enquiries Details &nbsp;&nbsp;<a href="index?page=view&action=export_job_details<?=$next_page_condition;?>"><i class="fa fa-download" style="font-size:20px;"></i></a></div>-->
					
						<form action="index?page=add_funeral" method="post" name="form1" id="form1">
						<div class="card-footer">
						   <button type="submit" name="add" class="btn btn-primary">Add Client</button>
						   <? if(!$is_archive){ ?>
						   <button type="button" name="archive_client" id="archive_client" class="btn btn-warning pull-right">Archive</button>
						   <? }else{ ?>
						   <button type="button" name="unarchive_client" id="unarchive_client" class="btn btn-warning pull-right">Unarchive</button>
						   <? } ?>
						</div>
						<? if($result>0){?>
						<div class="table-responsive">
						 <table id="example1" class="table table-bordered table-striped">
							<thead>
							  <tr>
								<!--<th>#</th>-->
								<th>Company Name / Contact Name</th>
								<th>Phone no.</th>
								<th>Job Date</th>
								<th style="width:90px;">L. S. Date</th>
								<th>Appointment</th>
								<th>Notes / Reminders</th>
								<th>Document</th>
								<th>Edit</th>
								<th>Delete</th>
								<th class="no-sort" style="text-align:center;">
									<div class="icheck-primary d-inline">
										<input type="checkbox" name="check_all" id="check_all">
										<label for="check_all"></label>
									</div>
								</th>
							  </tr>
							</thead>
							<tbody>
								<?
									while($row =mysqli_fetch_array($sql)){ 
									$row=utility::validate_crypt_data($row);
								?>
							  <tr>
								<!--<td><a href="index?page=view_enquiry_progress&from=view_funeral&id=<?//=$row['id'];?>"><?php //echo $row['id']; ?></a></td>-->
								<td>
									<a href="index?page=view_enquiry_progress&from=view_funeral&j_id=<?=urlencode(utility::app_crypt($row['id'],'e'));?>&id=<?=$row['id'].$next_page_condition.$paging;?>">
									<?php 
										if(isset($row['company_name']) && $row['company_name'] !=""){
											echo $row["company_name"];
										}
										if(isset($row['first_name']) && $row['first_name'] !=""){
											echo " / ".ucfirst($row['first_name'])." ".ucfirst($row['last_name']); 
										}									
									?>
									</a>
								</td>
								<td><?php echo $row['phone_no']; ?></td>
								<td nowrap><?php echo utility::getFormatedDate($row['funeral_date']); ?></td>
								<td nowrap>
									<?php 
										$last_date = $gsi->getLastNotesDateByJobId($row['id']);
										if($last_date !=""){
											echo utility::getFormatedDate($last_date); 
										}
									?>
								</td>
								<td><a href="" data-toggle="modal" data-target="#myModal" class="appointment" alt="<?=$row['id'];?>" style="">Appointment(<?=$gsi->getNumberOfAppointmentByJobId($row['id']);?>)</a></td>
								<td>
									<a href="" data-toggle="modal" data-target="#myModal" class="notes" alt="<?=$row['id'];?>" style="">Notes(<?=$gsi->getNumberOfNotesByJobId($row['id']);?>)</a> / <a href="" data-toggle="modal" data-target="#myModal" class="reminders" alt="<?=$row['id'];?>" style="">Reminders(<?=$gsi->getNumberOfReminderByJobId($row['id']);?>)</a>
								</td>
								<td><a href="" data-toggle="modal" data-target="#myModal" class="document" alt="<?=$row['id'];?>" style="">Document(<?=$gsi->getNumberOfDocumentByJobId($row['id']);?>)</a></td>
								<td><a href="index?page=add_funeral&action=edit&j_id=<?=urlencode(utility::app_crypt($row['id'],'e'));?>&id=<?=$row['id'].$next_page_condition.$paging;?>"> <i class="fa fa-fw fa-edit"></i> </a></td>
								<td><a href="index?page=view_funeral&action=delete&j_id=<?=urlencode(utility::app_crypt($row['id'],'e'));?>&id=<?=$row['id'].$next_page_condition.$paging; ?>" onclick=" javascript:return confirm('Are you sure! You want to delete');">
								<i class="fa fa-fw fa-trash"></i> </a></td>
								<td align="center">
									<div class="icheck-primary d-inline">
										<input class="checkbox" type="checkbox" name="client_ids[<?=$row['id']?>]" value="<?=$row['id']?>" id="primary">
										<label for="primary"></label>
									</div>
								</td>
							  </tr>
								<? } ?>
								<!-- Paging starts here  -->
								<? 
									$next_page_condition .="&page=view_funeral";
									include("paging/paging_row.inc.php") ?>
								<!-- Paging ends starts here  -->
							</tbody>
						 </table>
						 </div>
						 <? }else{ ?>
							No Client found...
						 <? } ?>
					</div>
					<div class="card-footer">
					   <button type="submit" name="add" class="btn btn-primary">Add Client</button>
					</div>
					</form>
				</div>
			</div>
			</div>
		</div>
	</div>
</section>

<script type="text/javascript">
$(function () {
	$(".job_contract").click(function(){	
		$("#myModalLabel").text("Job Contract Management");
		var hide_top_left = "&hide_top=1&hide_left=1";
		var job_id = $(this).attr("alt");		
		$(".modal_iframe").attr("src","<?=$config->frontEndURL?>/admin/job_contract/index?page=list&source=f&job_id="+job_id+hide_top_left);
	});
	$(".appointment").click(function(){	
		$("#myModalLabel").text("Appointment Management");
		var hide_top_left = "&hide_top=1&hide_left=1";
		var job_id = $(this).attr("alt");		
		$(".modal_iframe").attr("src","<?=$config->frontEndURL?>/admin/appointment/index?page=view&source=f&job_id="+job_id+hide_top_left);
	});
	$(".notes").click(function(){	
		$("#myModalLabel").text("Notes Management");
		var hide_top_left = "&hide_top=1&hide_left=1";
		var job_id = $(this).attr("alt");		
		$(".modal_iframe").attr("src","<?=$config->frontEndURL?>/admin/notes/index?page=view&source=f&job_id="+job_id+hide_top_left);
	});
	$(".reminders").click(function(){	
		$("#myModalLabel").text("Reminders Management");
		var hide_top_left = "&hide_top=1&hide_left=1";
		var job_id = $(this).attr("alt");		
		$(".modal_iframe").attr("src","<?=$config->frontEndURL?>/admin/notes/index?page=view_reminder&source=f&job_id="+job_id+hide_top_left);
	});
	/* $(".document").click(function(){	
		$("#myModalLabel").text("Documents Management");
		var hide_top_left = "&hide_top=1&hide_left=1";
		var job_id = $(this).attr("alt");		
		$(".modal_iframe").attr("src","<?=$config->frontEndURL?>/admin/document_management/index?page=view&source=f&job_id="+job_id+hide_top_left);
	}); */
	$(".document").click(function(){	
		$("#myModalLabel").text("Documents Management");
		var hide_top_left = "&hide_top=1&hide_left=1";
		var job_id = $(this).attr("alt");		
		$(".modal_iframe").attr("src","<?=$config->frontEndURL?>/admin/modules/fileuploads/image-files.php?id="+job_id+"&page=view&source=f"+hide_top_left);
	});
	$(".timesheet").click(function(){	
		$("#myModalLabel").text("Timesheet Management");
		var hide_top_left = "&hide_top=1&hide_left=1";
		var job_id = $(this).attr("alt");		
		$(".modal_iframe").attr("src","<?=$config->frontEndURL?>/admin/timesheet_management/index?page=view&source=f&job_id="+job_id+hide_top_left);
	});
	$(".close_button").click(function(){
		$(".modal_iframe").attr("src","");
		location.reload();
	}); 
	///////////////////////////// START ARCHIVING/UNARCHIVING ORGANISATION///////////////////////////////////////
	$("#archive_client").click(function(){
		if ($(".checkbox:checked").length == 0){
			alert("Please select Client");
			return false;
		}
		if(confirm("Are you sure you want archive the selected Client(s).")){
			$('#form1').attr("action","index?page=view&action=archive<?=$next_page_condition.$paging;?>");
			$('#form1').submit();
		}
	});
	$("#unarchive_client").click(function(){
		if ($(".checkbox:checked").length == 0){
			alert("Please select Client");
			return false;
		}
		if(confirm("Are you sure you want unarchive the selected Client(s).")){
			$('#form1').attr("action","index?page=view&action=unarchive<?=$next_page_condition.$paging;?>");
			$('#form1').submit();
		}
	});
	///////////////////////////// START ARCHIVING/UNARCHIVING ORGANISATION///////////////////////////////////////
	/* $('#check_all').on('ifChanged', function(event){
		$('.checkbox').iCheck( "toggle");
	}); */
	$('#check_all').click(function(event){
		if(event.target.checked){
			$('.checkbox').attr('checked','checked');
		}else{
			$('.checkbox').removeAttr('checked');
		}
	});
});
</script>