File: /home/cafsindia/refimac_cafsjobs_com/ajax/loaddata_rep.php
<?php
include_once('../dbconnect.php');
// Turn off all error reporting
error_reporting(0);
//session_start();
$user=$_SESSION['clid'];
$perm=$_SESSION['cper'];
$mid=$_REQUEST['mid'];
$smid=$_REQUEST['smid'];
$pid=$_REQUEST['pid'];
$type=$_REQUEST['type'];
if($pid=="4_1_1")
{
// Customerview
if($type=="1")
{
//print_r($_POST);
//print_r($_POST['call_type']);
//echo $cars;
//call_type
$call_type=implode(",",$_POST['call_type']);
if($call_type!="")
{
$call_type=str_replace(',',"','",$call_type);
$query.="and call_type in ('".$call_type."')";
}
$tech_name=implode(",",$_POST['tech_name']);
if($tech_name!="")
{
$tech_name=str_replace(',',"','",$tech_name);
$query.="and tech_name in ('".$tech_name."')";
}
$brand_name=implode(",",$_POST['brand_name']);
if($brand_name!="")
{
$brand_name=str_replace(',',"','",$brand_name);
$query.="and brand_name in ('".$brand_name."')";
}
$surround_area=implode(",",$_POST['surround_area']);
if($surround_area!="")
{
$surround_area=str_replace(',',"','",$surround_area);
$query.="and CUSTOMER_SURROUND_AREA in ('".$surround_area."')";
}
//echo $query;
//echo $call_type;
//die;
?>
<div class="row portlet light">
<div class="col-md-12">
<h3 class="heading">View / Search </h3>
<table class="table table-striped table-bordered table-hover dt-responsive" id="sample">
<thead>
<tr>
<th>S.No.</th>
<th>Call Status</th>
<th>Customer Code</th>
<th>Customer Name</th>
<th>Customer Address</th>
<th>CUSTOMER AREA</th>
<th>CUSTOMER SURROUND AREA</th>
<th>CUSTOMER CITY</th>
<th>CUSTOMER PINCODE</th>
<th>CUSTOMER PHONE</th>
<th>CUSTOMER MOBILE</th>
<th>CUSTOMER CONTACT PERSON</th>
<th>COMPLAINT NO.</th>
<th>COMPLAINT Date</th>
<th>Call Type</th>
<th>Brand Name</th>
<th>Product</th>
<th>Fault Report</th>
<th>Location</th>
<th>Service Request Datetime</th>
<th>Tech. Name</th>
<th>Remarks</th>
</tr>
</thead>
<tbody>
<?php
if($_POST['call_status']=="3")
{
$sql1="SELECT cust_code,CUSTOMER_NAME,CUSTOMER_ADDRESS,CUSTOMER_AREA,CUSTOMER_SURROUND_AREA,CUSTOMER_CITY,CUSTOMER_PINCODE,CUSTOMER_PHONE,CUSTOMER_MOBILE,CUSTOMER_CONTACT_PERSON,complaint_no,complaint_date,complaint_time,call_type,brand_name,product,fault_report,location,service_req_datetime,(select EMP_NAME from employee where EMP_ID=tech_name) as tech_name,(select MAKE_NAME from make where MAKE_ID=brand_name) as brand_name, (select product_name from serviceapp_product where product_id=product) as product,remarks,status FROM `serviceapp_callreg` where status in ('1','3') and complaint_date between '".date("Y-m-d", strtotime($_POST['from_date']))."' and '".date("Y-m-d", strtotime($_POST['to_date']))."' $query";
}
if($_POST['call_status']=="2")
{
$sql1="SELECT cust_code,CUSTOMER_NAME,CUSTOMER_ADDRESS,CUSTOMER_AREA,CUSTOMER_SURROUND_AREA,CUSTOMER_CITY,CUSTOMER_PINCODE,CUSTOMER_PHONE,CUSTOMER_MOBILE,CUSTOMER_CONTACT_PERSON,complaint_no,complaint_date,complaint_time,call_type,brand_name,product,fault_report,location,service_req_datetime,(select EMP_NAME from employee where EMP_ID=tech_name) as tech_name,(select MAKE_NAME from make where MAKE_ID=brand_name) as brand_name, (select product_name from serviceapp_product where product_id=product) as product,remarks,status FROM `serviceapp_callreg` where status='2' and complaint_date between '".date("Y-m-d", strtotime($_POST['from_date']))."' and '".date("Y-m-d", strtotime($_POST['to_date']))."' $query";
}
if($_POST['call_status']=="4")
{
$sql1="SELECT cust_code,CUSTOMER_NAME,CUSTOMER_ADDRESS,CUSTOMER_AREA,CUSTOMER_SURROUND_AREA,CUSTOMER_CITY,CUSTOMER_PINCODE,CUSTOMER_PHONE,CUSTOMER_MOBILE,CUSTOMER_CONTACT_PERSON,complaint_no,complaint_date,complaint_time,call_type,brand_name,product,fault_report,location,service_req_datetime,(select EMP_NAME from employee where EMP_ID=tech_name) as tech_name,(select MAKE_NAME from make where MAKE_ID=brand_name) as brand_name, (select product_name from serviceapp_product where product_id=product) as product,remarks,status FROM `serviceapp_callreg` where status='4' and complaint_date between '".date("Y-m-d", strtotime($_POST['from_date']))."' and '".date("Y-m-d", strtotime($_POST['to_date']))."' $query";
}
if($_POST['call_status']=="All")
{
$sql1="SELECT cust_code,CUSTOMER_NAME,CUSTOMER_ADDRESS,CUSTOMER_AREA,CUSTOMER_SURROUND_AREA,CUSTOMER_CITY,CUSTOMER_PINCODE,CUSTOMER_PHONE,CUSTOMER_MOBILE,CUSTOMER_CONTACT_PERSON,complaint_no,complaint_date,complaint_time,call_type,brand_name,product,fault_report,location,service_req_datetime,(select EMP_NAME from employee where EMP_ID=tech_name) as tech_name,(select MAKE_NAME from make where MAKE_ID=brand_name) as brand_name, (select product_name from serviceapp_product where product_id=product) as product,remarks,status FROM `serviceapp_callreg` where complaint_date between '".date("Y-m-d", strtotime($_POST['from_date']))."' and '".date("Y-m-d", strtotime($_POST['to_date']))."' $query";
}
//echo $sql1;
$qu1=mysql_query($sql1);
$i=1;
while($rd=mysql_fetch_array($qu1))
{
?>
<tr>
<td><?php echo $i;?></td>
<td><?php if($rd['status']==1) echo "Assigned"; if($rd['status']==2) echo "Completed"; if($rd['status']==3) echo "Pending"; if($rd['status']==4) echo "Cancelled";?></td>
<td><?php echo $rd['cust_code'];?></td>
<td><?php echo $rd['CUSTOMER_NAME'];?></td>
<td><?php echo $rd['CUSTOMER_ADDRESS'];?></td>
<td><?php echo $rd['CUSTOMER_AREA'];?></td>
<td><?php echo $rd['CUSTOMER_SURROUND_AREA'];?></td>
<td><?php echo $rd['CUSTOMER_CITY'];?></td>
<td><?php echo $rd['CUSTOMER_PINCODE'];?></td>
<td><?php echo $rd['CUSTOMER_PHONE'];?></td>
<td><?php echo $rd['CUSTOMER_MOBILE'];?></td>
<td><?php echo $rd['CUSTOMER_CONTACT_PERSON'];?></td>
<td><?php echo $rd['complaint_no'];?></td>
<td><?php echo date("d-m-Y", strtotime($rd['complaint_date']));?></td>
<td><?php echo $rd['call_type'];?></td>
<td><?php echo $rd['brand_name'];?></td>
<td><?php echo $rd['product'];?></td>
<td><?php echo $rd['fault_report'];?></td>
<td><?php echo $rd['location'];?></td>
<td><?php echo date("d-m-Y", strtotime($rd['service_req_datetime']));?></td>
<td><?php echo $rd['tech_name'];?></td>
<td><?php echo $rd['remarks'];?></td>
</tr>
<?php $i++; }?>
</tbody>
</table>
</div>
</div>
<?php
}
}
elseif($pid=="4_2_1")
{
// Customerview
if($type=="1")
{
//print_r($_POST);
//print_r($_POST['call_type']);
//echo $cars;
//call_type
$call_status=implode(",",$_POST['call_status']);
if($call_status!="")
{
$call_status=str_replace(',',"','",$call_status);
$query.="and service.COMPLETED in ('".$call_status."')";
}
$tech_name=implode(",",$_POST['tech_name']);
if($tech_name!="")
{
$tech_name=str_replace(',',"','",$tech_name);
$query.="and EMPLOYEE_ID in ('".$tech_name."')";
}
$surround_area=implode(",",$_POST['surround_area']);
if($surround_area!="")
{
$surround_area=str_replace(',',"','",$surround_area);
$query.="and CUSTOMER_SURROUND_AREA in ('".$surround_area."')";
}
$month=$_REQUEST['to_date'];
$mymavl=explode('-',$month);
//echo $query;
//echo $call_type;
//die;
?>
<div class="row portlet light">
<div class="col-md-12">
<h3 class="heading">View / Search </h3>
<table class="table table-striped table-bordered table-hover dt-responsive" id="sample">
<thead>
<tr>
<th>S.No.</th>
<th>Service Status</th>
<th>Name</th>
<th>Address </th>
<th>Area</th>
<th>Surround Area/City</th>
<th>Phone</th>
<th>Mobile</th>
<th>Contact Person</th>
<?php if($option=="Group Service"){?> <th>Mac. Count</th> <?php }?>
<!--<th>Contact Person</th>-->
</tr>
</thead>
<tbody>
<?php
$sql1="SELECT customer.CUSTOMER_NAME,customer.CUSTOMER_ADDRESS,customer.CUSTOMER_AREA,customer.CUSTOMER_SURROUND_AREA,customer.CUSTOMER_CITY,customer.CUSTOMER_PINCODE,customer.CUSTOMER_PHONE,customer.CUSTOMER_MOBILE,customer.CUSTOMER_CONTACT_PERSON,service.SERIALNO,sales.SALES_ID as sales_all_id,(select SERVICE_MODE_NAME FROM service_mode where service_mode.SERVICE_MODE_ID=sales.SERVICEMODE_ID) as SERVICEMODENAME ,(select PURCHASE_SERIALNUMBER FROM purchase where purchase.PURCHASE_ID=sales.PURCHASE_ID order by sales.SALES_ID limit 0,1) as SERIALNO,service.COMPLETED,service.LIST_INDEX FROM `service` inner join sales on sales.SALES_ID=service.SALES_ID inner join customer on customer.CUSTOMER_CODE=sales.CUSTOMER_CODE where month='".$mymavl[0]."' and year='".$mymavl[1]."' $query order by sales.CUSTOMER_CODE ";
//echo $sql1; die;
//$sql1="SELECT cust_code,CUSTOMER_NAME,CUSTOMER_ADDRESS,CUSTOMER_AREA,CUSTOMER_SURROUND_AREA,CUSTOMER_CITY,CUSTOMER_PINCODE,CUSTOMER_PHONE,CUSTOMER_MOBILE,CUSTOMER_CONTACT_PERSON,complaint_no,complaint_date,complaint_time,call_type,brand_name,product,fault_report,location,service_req_datetime,(select EMP_NAME from employee where EMP_ID=tech_name) as tech_name,(select MAKE_NAME from make where MAKE_ID=brand_name) as brand_name, (select product_name from serviceapp_product where product_id=product) as product,remarks FROM `serviceapp_callreg` where status='1' and complaint_date between '".date("Y-m-d", strtotime($_POST['from_date']))."' and '".date("Y-m-d", strtotime($_POST['to_date']))."' $query";
// echo $sql1;
$qu1=mysql_query($sql1);
$i=1;
while($rd=mysql_fetch_array($qu1))
{
?> <tr>
<td><?php echo $i;?></td>
<td class="center"><?php if($rd['COMPLETED']=='Y') echo "completed"; else echo "Pending"; ?></td>
<td ><?php echo $rd['CUSTOMER_NAME'];?></td>
<td ><?php echo $rd['CUSTOMER_ADDRESS'];?></td>
<td ><?php echo $rd['CUSTOMER_AREA'];?></td>
<td class="center" ><?php echo $rd['CUSTOMER_SURROUND_AREA'].'-'. $rd['CUSTOMER_CITY']; ?></td>
<!--<td class="center" ><?php echo $rd['CUSTOMER_CITY']; ?></td> -->
<td class="center" ><?php echo $rd['CUSTOMER_PHONE']; ?></td>
<td class="center" ><?php echo $rd['CUSTOMER_MOBILE']; ?></td>
<td class="center"><?php echo $rd['CUSTOMER_CONTACT_PERSON']; ?></td>
</tr>
<?php $i++; }?>
</tbody>
</table>
</div>
</div>
<?php
}
}
elseif($pid=="4_2_2")
{
// Customerview
if($type=="1")
{
//print_r($_POST);
//print_r($_POST['call_type']);
//echo $cars;
//call_type
$call_status=implode(",",$_POST['call_status']);
if($call_status!="")
{
$call_status=str_replace(',',"','",$call_status);
$query.="and service.COMPLETED in ('".$call_status."')";
}
$tech_name=implode(",",$_POST['tech_name']);
if($tech_name!="")
{
$tech_name=str_replace(',',"','",$tech_name);
$query.="and EMPLOYEE_ID in ('".$tech_name."')";
}
$surround_area=implode(",",$_POST['surround_area']);
if($surround_area!="")
{
$surround_area=str_replace(',',"','",$surround_area);
$query.="and CUSTOMER_SURROUND_AREA in ('".$surround_area."')";
}
$month=$_REQUEST['to_date'];
$mymavl=explode('-',$month);
//echo $query;
//echo $call_type;
//die;
?>
<div class="row portlet light">
<div class="col-md-12">
<h3 class="heading">View / Search </h3>
<table class="table table-striped table-bordered table-hover dt-responsive" id="sample">
<thead>
<tr>
<th>S.No.</th>
<th>Technician</th>
<th>Completed</th>
<th>Pending</th>
<!--<th>Contact Person</th>-->
</tr>
</thead>
<tbody>
<?php
//echo "SELECT (select EMP_NAME from employee where EMP_ID=service.EMPLOYEE_ID)as tech_name, count(*) as total,COMPLETED from service where DATE between '".date('Y-m-d',strtotime($_REQUEST['from_completion_date']))."' and '".date('Y-m-d',strtotime($_REQUEST['to_completion_date']))."' group by COMPLETED,EMPLOYEE_ID";die;
//$sql1="SELECT * from service";
$sql1="SELECT (select EMP_NAME from employee where EMP_ID=service.EMPLOYEE_ID)as tech_name, count(*) as total,SERIALNO,COMPLETED from service where DATE between '".date('Y-m-d',strtotime($_REQUEST['from_completion_date']))."' and '".date('Y-m-d',strtotime($_REQUEST['to_completion_date']))."' group by COMPLETED,EMPLOYEE_ID";
//$sql1="SELECT customer.CUSTOMER_NAME,customer.CUSTOMER_ADDRESS,customer.CUSTOMER_AREA,customer.CUSTOMER_SURROUND_AREA,customer.CUSTOMER_CITY,customer.CUSTOMER_PINCODE,customer.CUSTOMER_PHONE,customer.CUSTOMER_MOBILE,customer.CUSTOMER_CONTACT_PERSON,service.SERIALNO,sales.SALES_ID as sales_all_id,(select SERVICE_MODE_NAME FROM service_mode where service_mode.SERVICE_MODE_ID=sales.SERVICEMODE_ID) as SERVICEMODENAME ,(select PURCHASE_SERIALNUMBER FROM purchase where purchase.PURCHASE_ID=sales.PURCHASE_ID order by sales.SALES_ID limit 0,1) as SERIALNO,service.COMPLETED,service.LIST_INDEX FROM `service` inner join sales on sales.SALES_ID=service.SALES_ID inner join customer on customer.CUSTOMER_CODE=sales.CUSTOMER_CODE where month='".$mymavl[0]."' and year='".$mymavl[1]."' $query order by sales.CUSTOMER_CODE ";
$callcount_count_completed=0;
$callcount_count_pending=0;
//echo $sql1; die;
//$sql1="SELECT cust_code,CUSTOMER_NAME,CUSTOMER_ADDRESS,CUSTOMER_AREA,CUSTOMER_SURROUND_AREA,CUSTOMER_CITY,CUSTOMER_PINCODE,CUSTOMER_PHONE,CUSTOMER_MOBILE,CUSTOMER_CONTACT_PERSON,complaint_no,complaint_date,complaint_time,call_type,brand_name,product,fault_report,location,service_req_datetime,(select EMP_NAME from employee where EMP_ID=tech_name) as tech_name,(select MAKE_NAME from make where MAKE_ID=brand_name) as brand_name, (select product_name from serviceapp_product where product_id=product) as product,remarks FROM `serviceapp_callreg` where status='1' and complaint_date between '".date("Y-m-d", strtotime($_POST['from_date']))."' and '".date("Y-m-d", strtotime($_POST['to_date']))."' $query";
// echo $sql1;
$qu1=mysql_query($sql1);
$i=1;
while($rd=mysql_fetch_array($qu1))
{
if($rd['COMPLETED']==Y)
$callcount_count_completed=$rd['total'];
if($rd['COMPLETED']==N)
$callcount_count_pending=$rd['total'];
?> <tr>
<td ><?php echo $i;?></td>
<td ><?php echo $rd['tech_name'];?></td>
<td ><?php echo $callcount_count_completed;?></td>
<td ><?php echo $callcount_count_pending;?></td>
</tr>
<?php $i++; }?>
</tbody>
</table>
</div>
</div>
<?php
}
}
?>