1273 lines
52 KiB
PHP
Executable File
1273 lines
52 KiB
PHP
Executable File
<?php
|
|
/**
|
|
* Created by PhpStorm.
|
|
* User: karthi
|
|
* Date: 11/15/17
|
|
* Time: 5:49 PM
|
|
*/
|
|
defined('BASEPATH') OR exit('No direct script access allowed');
|
|
|
|
class Calltracking_model extends CI_Model {
|
|
/*
|
|
* get lead details
|
|
* @params mobile number,requestedby
|
|
* created by kms
|
|
* */
|
|
public function getLeadDetails($requestedBy=null,$mobileNumber=null,$branchID=null,$userType=null)
|
|
{
|
|
$this->db->select('LD.LeadID,LD.LeadName,LD.MobileNumber,LD.IsNewEnquiry,LT.ActivityStatus,LT.AssignedTo,LT.StatusCode,LT.University,LT.Course,LT.AlternateMobile,LT.Address');
|
|
$this->db->from(LEAD_DETAILS.' as LD');
|
|
$this->db->join(LEAD_TRACKING.' as LT', 'LT.LeadID = LD.LeadID');
|
|
$this->db->where('LD.MobileNumber',$mobileNumber);
|
|
$leadDet=$this->db->get()->last_row();
|
|
$result_array = [];
|
|
if($leadDet){
|
|
$result_array['existLeadDetails']=$leadDet;
|
|
}
|
|
else{
|
|
$this->db->select('ST.MobileNumber,ST.Firstname,ST.AlternateNumber,ST.PresentAddress,US.UniversityName,CU.CourseName');
|
|
$this->db->from(STUDENTS.' as ST');
|
|
$this->db->join(STUDENTCOURSE.' as STC', 'ST.StudentID = STC.StudentID');
|
|
$this->db->join(UNIVERSITY.' as US', 'US.UniversityID = STC.UniversityID');
|
|
$this->db->join(COURSE.' as CU', 'CU.CourseID = STC.CourseID');
|
|
$this->db->where('ST.MobileNumber',$mobileNumber);
|
|
$enrolledStudDet=$this->db->get()->last_row();
|
|
if($enrolledStudDet) {
|
|
$leadDet['LeadName'] = $enrolledStudDet->Firstname;
|
|
$leadDet['MobileNumber'] = $enrolledStudDet->MobileNumber;
|
|
$leadDet['IsNewEnquiry'] = "1";
|
|
$leadDet['University'] = $enrolledStudDet->UniversityName;
|
|
$leadDet['Course'] = $enrolledStudDet->CourseName;
|
|
$leadDet['ActivityStatus'] = "";
|
|
$leadDet['AssignedTo'] = "";
|
|
$leadDet['StatusCode'] = "";
|
|
$leadDet['AlternateMobile'] = $enrolledStudDet->AlternateNumber;
|
|
$leadDet['Address'] = $enrolledStudDet->PresentAddress;
|
|
$result_array['existLeadDetails'] = $leadDet;
|
|
}
|
|
else{
|
|
$leadDet['LeadName'] = "";
|
|
$leadDet['MobileNumber'] = "";
|
|
$leadDet['IsNewEnquiry'] = "1";
|
|
$leadDet['University'] = "";
|
|
$leadDet['Course'] = "";
|
|
$leadDet['ActivityStatus'] = "";
|
|
$leadDet['AssignedTo'] = "";
|
|
$leadDet['StatusCode'] = "";
|
|
$leadDet['AlternateMobile'] = "";
|
|
$leadDet['Address'] = "";
|
|
$result_array['existLeadDetails'] = $leadDet;
|
|
}
|
|
}
|
|
// store lead,university and course details in comman array
|
|
//$result_array['universityDetails']=$this->getUniversityDetails();
|
|
$result_array['activityDetails']=$this->getActivityDetails($mobileNumber);
|
|
// $result_array['statusDetails']=$this->getStatus();
|
|
$result_array['staffDetails']=$this->getStaff("1",$branchID,$userType,$requestedBy);
|
|
return $result_array;
|
|
|
|
}
|
|
|
|
/*
|
|
* get university details
|
|
* created by kms
|
|
* */
|
|
public function getUniversityDetails()
|
|
{
|
|
$this->db->select('UniversityID,UniversityName');
|
|
$this->db->where('IsActive','1');
|
|
$this->db->order_by('UniversityID','ASC');
|
|
$unviversityDetails = $this->db->get(UNIVERSITY);
|
|
|
|
if($unviversityDetails->result()){
|
|
$result_university = array();
|
|
foreach ($unviversityDetails->result() as $row)
|
|
{
|
|
$university_array['universityStatus'] = true;
|
|
$university_array['universityID'] = $row->UniversityID;
|
|
$university_array['universityName'] = $row->UniversityName;
|
|
$university_array['courseDetails']= $this->getCourseDetails($row->UniversityID);
|
|
$result_university[]=$university_array;
|
|
}
|
|
}
|
|
else {
|
|
$university_array['universityStatus'] = false;
|
|
$university_array['message'] = "No records found!";
|
|
$university_array['universityID'] = "";
|
|
$university_array['universityName'] = "";
|
|
$university_array['courseDetails']= "";
|
|
$result_university[]=$university_array;
|
|
}
|
|
|
|
|
|
return $result_university;
|
|
|
|
}
|
|
/*
|
|
* get course details
|
|
* @params university id
|
|
* created by kms
|
|
* */
|
|
public function getCourseDetails($universityID=null)
|
|
{
|
|
$this->db->select('CourseID,CourseName');
|
|
$this->db->where('UniversityID',$universityID);
|
|
$this->db->where('IsActive','1');
|
|
$this->db->order_by('CourseID','ASC');
|
|
$courseDetails = $this->db->get(COURSE);
|
|
return $courseDetails->result();
|
|
}
|
|
|
|
/*
|
|
* get activity details
|
|
* created by kms
|
|
* */
|
|
|
|
public function getActivityDetails($mobileNo=null)
|
|
{
|
|
$this->db->distinct();
|
|
$this->db->select('LT.ActivityStatus,LT.ActivityStatus');
|
|
$this->db->from(LEAD_DETAILS.' as LD');
|
|
$this->db->join(LEAD_TRACKING.' as LT', 'LT.LeadID = LD.LeadID');
|
|
$this->db->join(PICK_LIST_DETAILS.' as PLD', 'LT.StatusCode = PLD.ListCode');
|
|
$this->db->where('PLD.ListName !=','CLOSE');
|
|
$this->db->where('LD.MobileNumber',$mobileNo);
|
|
$getExistActivity=$this->db->get();
|
|
$existArrayActivity=array();
|
|
if($getExistActivity->result()){
|
|
foreach ($getExistActivity->result() as $ActRow){
|
|
|
|
array_push($existArrayActivity,$ActRow->ActivityStatus);
|
|
}
|
|
|
|
|
|
}
|
|
else{
|
|
$existArrayActivity=array();
|
|
}
|
|
|
|
$this->db->select('ActivityID,ActivityName');
|
|
$this->db->where('IsActive','1');
|
|
if($existArrayActivity){
|
|
$this->db->where_not_in('ActivityID',$existArrayActivity);
|
|
}
|
|
$this->db->order_by('ActivityID','ASC');
|
|
$activity = $this->db->get(ACTIVITY);
|
|
if($activity->result()){
|
|
$result_activity = array();
|
|
foreach ($activity->result() as $row)
|
|
{
|
|
$activity_array['activityStatus'] = true;
|
|
$activity_array['activityID'] = $row->ActivityID;
|
|
$activity_array['activityName'] = $row->ActivityName;
|
|
$activity_array['activityStatus']= $this->getStatus($row->ActivityID);
|
|
$result_activity[]=$activity_array;
|
|
}
|
|
|
|
}
|
|
else{
|
|
$activity_array['activityStatus'] = false;
|
|
$activity_array['activityID'] = "";
|
|
$activity_array['activityName'] = "No activity";
|
|
$activity_array['activityStatus']= "";
|
|
$result_activity[]=$activity_array;
|
|
|
|
}
|
|
|
|
return $result_activity;
|
|
}
|
|
|
|
/*
|
|
* get activity status details
|
|
* created by kms
|
|
* */
|
|
|
|
public function getStatus($activityID=null)
|
|
{
|
|
$this->db->select('AVS.StatusID,PLD.ListCode,PLD.ListName');
|
|
$this->db->from(ACTIVITY_STATUS .' as AVS');
|
|
$this->db->join(PICK_LIST_DETAILS.' as PLD', 'PLD.ListCode = AVS.StatusName');
|
|
$this->db->where('AVS.ActivityID',$activityID);
|
|
$this->db->where('AVS.IsActive','1');
|
|
$this->db->where('PLD.IsActive','1');
|
|
$status = $this->db->get();
|
|
return $status->result();
|
|
}
|
|
|
|
/*
|
|
* get staff details
|
|
* created by kms
|
|
*
|
|
* */
|
|
|
|
public function getStaff($status=null,$branchID=null,$userType=null,$requestedBy)
|
|
{
|
|
if($userType != SUPER_ADMIN){
|
|
|
|
$this->db->select('LO.ID,ST.StaffID,ST.Firstname');
|
|
$this->db->from(LOGIN.' as LO');
|
|
$this->db->join(STAFF_BRANCH.' as STB', 'STB.StaffID = LO.StaffID');
|
|
$this->db->join(STAFF.' as ST', 'ST.StaffID = LO.StaffID');
|
|
$this->db->join(BRANCH.' as BR', 'BR.BranchCode = STB.BranchCode');
|
|
$this->db->where('ST.IsActive',$status);
|
|
$this->db->where('BR.IsActive',$status);
|
|
if($userType== ADMIN){
|
|
$this->db->where('STB.BranchCode',$branchID);
|
|
$this->db->where('LO.ListCode !=',SUPER_ADMIN);
|
|
}
|
|
if($userType== EMPLOYEE){
|
|
$this->db->where('STB.BranchCode',$branchID);
|
|
$this->db->where('LO.ID',$requestedBy);
|
|
}
|
|
$staffDetails=$this->db->get();
|
|
if($staffDetails){
|
|
$result_staff = array();
|
|
foreach ($staffDetails->result() as $row)
|
|
{
|
|
$university_array['loginId'] = $row->ID;
|
|
$university_array['staffName'] = $row->Firstname.'-'.$row->StaffID;
|
|
$result_staff[]=$university_array;
|
|
}
|
|
|
|
}
|
|
else{
|
|
$result_staff[]="";
|
|
}
|
|
}
|
|
else{
|
|
$this->db->select('BR.BranchCode,BR.BranchName');
|
|
$this->db->from(BRANCH.' as BR');
|
|
$this->db->where('BR.IsActive',$status);
|
|
$branchDetails=$this->db->get();
|
|
|
|
if($branchDetails->result()){
|
|
foreach ($branchDetails->result() as $branchRow){
|
|
$branch_array['BranchCode'] = $branchRow->BranchCode;
|
|
$branch_array['BranchName'] = $branchRow->BranchName.'-'.$branchRow->BranchCode;
|
|
$branch_array['staffDetails'] = $this->superAdminBranch($branchRow->BranchCode,$status);
|
|
$result_staff[]=$branch_array;
|
|
}
|
|
|
|
}
|
|
else{
|
|
$result_staff[]="";
|
|
}
|
|
|
|
|
|
}
|
|
|
|
return $result_staff;
|
|
}
|
|
|
|
/*
|
|
* get staff details based on branch id
|
|
* created by kms
|
|
* */
|
|
public function superAdminBranch($branchID,$status){
|
|
|
|
$this->db->select('LO.ID,ST.StaffID,ST.Firstname');
|
|
$this->db->from(LOGIN.' as LO');
|
|
$this->db->join(STAFF_BRANCH.' as STB', 'STB.StaffID = LO.StaffID');
|
|
$this->db->join(STAFF.' as ST', 'ST.StaffID = LO.StaffID');
|
|
$this->db->where('ST.IsActive',$status);
|
|
$this->db->where('STB.BranchCode',$branchID);
|
|
$staffDetails=$this->db->get();
|
|
if($staffDetails){
|
|
$result_staff = array();
|
|
foreach ($staffDetails->result() as $row)
|
|
{
|
|
$university_array['loginId'] = $row->ID;
|
|
$university_array['staffName'] = $row->Firstname.'-'.$row->StaffID;
|
|
$result_staff[]=$university_array;
|
|
}
|
|
|
|
}
|
|
else{
|
|
$result_staff[]="";
|
|
}
|
|
|
|
return $result_staff;
|
|
|
|
}
|
|
|
|
|
|
/*
|
|
* add lead details
|
|
* created by kms
|
|
* */
|
|
|
|
public function addleadDetails($lead_Details=null,$track_Details=null,$followup_Details=null)
|
|
{
|
|
$this->db->insert(LEAD_DETAILS, $lead_Details);
|
|
// this if for insert lead details
|
|
if($this->db->affected_rows() == '1'){
|
|
// get and store insert id from db
|
|
$track_Details['LeadID']=$this->db->insert_id();
|
|
$this->db->insert(LEAD_TRACKING, $track_Details);
|
|
// this if for insert tracking details
|
|
if($this->db->affected_rows() == '1'){
|
|
$followup_Details['TrackingID']=$this->db->insert_id();
|
|
$this->db->insert(LEAD_TRACKING_FOLLOWUP, $followup_Details);
|
|
// this if for insert follow up details
|
|
if($this->db->affected_rows() == '1'){
|
|
$result['leadStatus'] = true;
|
|
$result['message'] = "Successfully lead details added";
|
|
}
|
|
else{
|
|
$result['leadStatus'] = false;
|
|
$result['message'] = "Something went wrong.please try again";
|
|
}
|
|
|
|
}
|
|
else{
|
|
$result['leadStatus'] = false;
|
|
$result['message'] = "Something went wrong.please try again";
|
|
}
|
|
|
|
}
|
|
|
|
else {
|
|
$result['leadStatus'] = false;
|
|
$result['message'] = "Something went wrong.please try again";
|
|
}
|
|
|
|
return $result;
|
|
|
|
}
|
|
|
|
/*
|
|
* get all tracked details
|
|
* created by kms
|
|
* */
|
|
|
|
public function getTrackingDetails($search=null)
|
|
{
|
|
$this->db->select('LTF.FollowupOn,LD.LeadID,LD.LeadName,LD.MobileNumber,LD.IsNewEnquiry,LT.TrackingID,LT.ActivityStatus,LT.AssignedTo,LT.StatusCode,LT.University,LT.Course,LT.AlternateMobile,LT.Address,LT.Flag,ST.StaffID,ST.Firstname,PLD1.ListCode as statusListCode,PLD1.ListName as statusListName,AV.ActivityID,AV.ActivityName');
|
|
$this->db->from(LEAD_TRACKING_FOLLOWUP.' as LTF');
|
|
$this->db->join(LEAD_TRACKING.' as LT','LT.TrackingID=LTF.TrackingID');
|
|
$this->db->join(LEAD_DETAILS.' as LD', 'LT.LeadID = LD.LeadID');
|
|
$this->db->join(LOGIN.' as LO', 'LO.ID = LT.AssignedTo');
|
|
$this->db->join(STAFF.' as ST', 'ST.StaffID = LO.StaffID');
|
|
$this->db->join(ACTIVITY.' as AV', 'AV.ActivityID = LT.ActivityStatus');
|
|
$this->db->join(PICK_LIST_DETAILS.' as PLD1', 'PLD1.ListCode = LT.StatusCode');
|
|
$this->db->where('LTF.IsActive', '1');
|
|
if($search['CreatedBranch'] !='All' AND $search['requestedDept'] != EMPLOYEE){
|
|
$this->db->where('LT.CreatedBranch', $search['CreatedBranch']);
|
|
|
|
}
|
|
if ($search['requestedDept'] == EMPLOYEE){
|
|
|
|
$this->db->where('LT.AssignedTo', $search['requestedBy']);
|
|
$this->db->where('LT.CreatedBranch', $search['CreatedBranch']);
|
|
|
|
}
|
|
if($search['searchDate']!='' OR $search['searchDate']!=null){
|
|
/* if(sizeof($search['searchDate']) == '1'){
|
|
$this->db->where('LTF.FollowupOn <= ', $search['searchDate'][0]);
|
|
$this->db->where('PLD1.ListName !=', 'CLOSE');
|
|
|
|
}*/
|
|
|
|
$this->db->where_in('LTF.FollowupOn', $search['searchDate']);
|
|
$this->db->order_by('LTF.FollowupOn','DESC');
|
|
$this->db->group_by('LT.TrackingID');
|
|
|
|
}
|
|
else{
|
|
$this->db->order_by('LTF.FollowupOn','DESC');
|
|
$this->db->group_by('LT.TrackingID');
|
|
}
|
|
|
|
// $this->db->group_by('LD.MobileNumber');
|
|
$leadDet=$this->db->get();
|
|
|
|
if($this->db->affected_rows()==0){
|
|
$result_array['trackingStatus']=false;
|
|
$result_array['trackingDetails']="";
|
|
}
|
|
else{
|
|
if($leadDet){
|
|
$result_array = array();
|
|
$result_array['trackingStatus']=true;
|
|
|
|
foreach ($leadDet->result() as $row)
|
|
{
|
|
|
|
$tracking_array["LeadID"]=$row->LeadID;
|
|
$tracking_array["LeadName"]=$row->LeadName;
|
|
$tracking_array["MobileNumber"]=$row->MobileNumber;
|
|
$tracking_array["IsNewEnquiry"]=$row->IsNewEnquiry;
|
|
$tracking_array["Flag"]=$row->Flag;
|
|
$tracking_array["TrackingID"]=$row->TrackingID;
|
|
$tracking_array["ActivityStatus"]=$row->ActivityStatus;
|
|
$tracking_array["AssignedTo"]=$row->AssignedTo;
|
|
$tracking_array["Firstname"]=$row->Firstname;
|
|
$tracking_array["StatusCode"]=$row->StatusCode;
|
|
$tracking_array["statusName"]=$row->statusListName;
|
|
$tracking_array["UniversityName"]=$row->University;
|
|
$tracking_array["CourseName"]=$row->Course;
|
|
$tracking_array["ActivityID"]=$row->ActivityID;
|
|
$tracking_array["ActivityName"]=$row->ActivityName;
|
|
$tracking_array["AlternateMobile"]=$row->AlternateMobile;
|
|
$tracking_array["Address"]=$row->Address;
|
|
// $tracking_array["followupDetails"]=$this->getTrackingFollowup($row->TrackingID);
|
|
// $followupDetails=$this->getTrackingFollowup($row->TrackingID);
|
|
|
|
// $tracking_array["followupDetails"]=$followupDetails[0]->FollowupOn;
|
|
$tracking_array["followupDetails"]=$row->FollowupOn;
|
|
$result[]=$tracking_array;
|
|
|
|
|
|
}
|
|
$result_array['trackingDetails']=$result;
|
|
// $result_array['statusDetails']=$this->getStatus();
|
|
|
|
|
|
}
|
|
}
|
|
$result_array['pendingTrackingDetails'] = $this->getPrevPendingTrackingDetails($search);
|
|
|
|
|
|
return $result_array;
|
|
|
|
}
|
|
|
|
public function getPrevPendingTrackingDetails($search=null)
|
|
{
|
|
$this->db->select('LTF.FollowupOn,LD.LeadID,LD.LeadName,LD.MobileNumber,LD.IsNewEnquiry,LT.TrackingID,LT.ActivityStatus,LT.AssignedTo,LT.StatusCode,LT.University,LT.Course,LT.AlternateMobile,LT.Address,LT.Flag,ST.StaffID,ST.Firstname,PLD1.ListCode as statusListCode,PLD1.ListName as statusListName,AV.ActivityID,AV.ActivityName');
|
|
$this->db->from(LEAD_TRACKING_FOLLOWUP.' as LTF');
|
|
$this->db->join(LEAD_TRACKING.' as LT','LT.TrackingID=LTF.TrackingID');
|
|
$this->db->join(LEAD_DETAILS.' as LD', 'LT.LeadID = LD.LeadID');
|
|
$this->db->join(LOGIN.' as LO', 'LO.ID = LT.AssignedTo');
|
|
$this->db->join(STAFF.' as ST', 'ST.StaffID = LO.StaffID');
|
|
$this->db->join(ACTIVITY.' as AV', 'AV.ActivityID = LT.ActivityStatus');
|
|
$this->db->join(PICK_LIST_DETAILS.' as PLD1', 'PLD1.ListCode = LT.StatusCode');
|
|
$this->db->where('LTF.IsActive', '1');
|
|
if($search['CreatedBranch'] !='All' AND $search['requestedDept'] != EMPLOYEE){
|
|
$this->db->where('LT.CreatedBranch', $search['CreatedBranch']);
|
|
|
|
}
|
|
if ($search['requestedDept'] == EMPLOYEE){
|
|
|
|
$this->db->where('LT.AssignedTo', $search['requestedBy']);
|
|
$this->db->where('LT.CreatedBranch', $search['CreatedBranch']);
|
|
|
|
}
|
|
if(($search['searchDate']!='' OR $search['searchDate']!=null) AND sizeof($search['searchDate']) == '1'){
|
|
|
|
// $this->db->where('LTF.FollowupOn <= ', $search['searchDate'][0]);
|
|
$this->db->where('PLD1.ListName !=', 'CLOSE');
|
|
$leadDet=$this->db->get();
|
|
|
|
if($leadDet->result()){
|
|
|
|
$result_array['trackingStatus']=true;
|
|
|
|
foreach ($leadDet->result() as $row)
|
|
{
|
|
|
|
$tracking_array["LeadID"]=$row->LeadID;
|
|
$tracking_array["LeadName"]=$row->LeadName;
|
|
$tracking_array["MobileNumber"]=$row->MobileNumber;
|
|
$tracking_array["IsNewEnquiry"]=$row->IsNewEnquiry;
|
|
$tracking_array["Flag"]=$row->Flag;
|
|
$tracking_array["TrackingID"]=$row->TrackingID;
|
|
$tracking_array["ActivityStatus"]=$row->ActivityStatus;
|
|
$tracking_array["AssignedTo"]=$row->AssignedTo;
|
|
$tracking_array["Firstname"]=$row->Firstname;
|
|
$tracking_array["StatusCode"]=$row->StatusCode;
|
|
$tracking_array["statusName"]=$row->statusListName;
|
|
$tracking_array["UniversityName"]=$row->University;
|
|
$tracking_array["CourseName"]=$row->Course;
|
|
$tracking_array["ActivityID"]=$row->ActivityID;
|
|
$tracking_array["ActivityName"]=$row->ActivityName;
|
|
$tracking_array["AlternateMobile"]=$row->AlternateMobile;
|
|
$tracking_array["Address"]=$row->Address;
|
|
// $tracking_array["followupDetails"]=$this->getTrackingFollowup($row->TrackingID);
|
|
// $followupDetails=$this->getTrackingFollowup($row->TrackingID);
|
|
|
|
// $tracking_array["followupDetails"]=$followupDetails[0]->FollowupOn;
|
|
$tracking_array["followupDetails"]=$row->FollowupOn;
|
|
$result[]=$tracking_array;
|
|
|
|
|
|
}
|
|
$result_array['trackingDetails']=$result;
|
|
|
|
|
|
}
|
|
else{
|
|
$result_array['trackingStatus']=false;
|
|
}
|
|
}
|
|
else{
|
|
$result_array['trackingStatus']=false;
|
|
}
|
|
|
|
|
|
|
|
|
|
|
|
return $result_array;
|
|
|
|
|
|
|
|
|
|
}
|
|
/*
|
|
* get never closed tracking list by current date
|
|
* created by kms
|
|
* */
|
|
|
|
|
|
/*
|
|
* get tracking followup Details
|
|
* @params tracking id
|
|
* created by kms
|
|
* */
|
|
|
|
public function getTrackingFollowup($trackingId=null)
|
|
{
|
|
$this->db->select('LTF.InteractionId,LTF.TrackingID,LTF.FollowupOn,LTF.FollowupComments,DATE_FORMAT(LTF.CreatedOn, "%d-%m-%Y %h:%i %p") AS CreatedOn,ST.FirstName');
|
|
|
|
$this->db->from(LEAD_TRACKING_FOLLOWUP.' as LTF');
|
|
$this->db->join(LOGIN.' as LO', 'LO.ID = LTF.CreatedBy');
|
|
$this->db->join(STAFF.' as ST', 'ST.StaffID = LO.StaffID');
|
|
$this->db->where('TrackingID',$trackingId);
|
|
$this->db->order_by('InteractionId','DESC');
|
|
$trackingDetails = $this->db->get();
|
|
return $trackingDetails->result();
|
|
}
|
|
|
|
/*
|
|
* update followup details
|
|
* created by kms
|
|
* */
|
|
|
|
public function updateFollowupDetails($updateDetails=null,$insertDetails=null)
|
|
{
|
|
$deactivateStatus['IsActive']=false;
|
|
$this->db->where('TrackingID',$updateDetails['TrackingID']);
|
|
$this->db->update(LEAD_TRACKING_FOLLOWUP, $deactivateStatus);
|
|
if($this->db->affected_rows() > '0') {
|
|
$this->db->insert(LEAD_TRACKING_FOLLOWUP, $insertDetails);
|
|
// this if for insert follow up details
|
|
if ($this->db->affected_rows() == '1') {
|
|
$this->db->where('TrackingID', $updateDetails['TrackingID']);
|
|
$upateStatus = $this->db->update(LEAD_TRACKING, $updateDetails);
|
|
$result['followupStatus'] = true;
|
|
$result['trackingID'] = $updateDetails['TrackingID'];
|
|
$result['message'] = "Successfully followup details updated";
|
|
} else {
|
|
$result['followupStatus'] = false;
|
|
$result['message'] = "Something went wrong.please try again";
|
|
}
|
|
}
|
|
else{
|
|
$activateStatus['IsActive']=true;
|
|
$this->db->where('TrackingID',$updateDetails['TrackingID']);
|
|
$this->db->update(LEAD_TRACKING_FOLLOWUP, $activateStatus);
|
|
$result['followupStatus'] = false;
|
|
$result['message'] = "Something went wrong.please try again";
|
|
}
|
|
|
|
|
|
|
|
return $result;
|
|
|
|
}
|
|
|
|
|
|
/*
|
|
* get tracking information when page is loading
|
|
* created by kms
|
|
* */
|
|
|
|
|
|
public function getLoadFollowupDetails($trackingID)
|
|
{
|
|
$this->db->select('LD.LeadID,LD.LeadName,LD.MobileNumber,LD.IsNewEnquiry,LT.TrackingID,LT.ActivityStatus,LT.AssignedTo,LT.CreatedBranch,BR.BranchName,LT.StatusCode,LT.University,LT.Course,LT.AlternateMobile,LT.Address,LT.ReferredBy,ST.StaffID,ST.Firstname,PLD1.ListCode as statusListCode,PLD1.ListName as statusListName,AV.ActivityID,AV.ActivityName');
|
|
$this->db->from(LEAD_DETAILS.' as LD');
|
|
$this->db->join(LEAD_TRACKING.' as LT', 'LT.LeadID = LD.LeadID');
|
|
$this->db->join(LOGIN.' as LO', 'LO.ID = LT.AssignedTo');
|
|
$this->db->join(STAFF.' as ST', 'ST.StaffID = LO.StaffID');
|
|
$this->db->join(ACTIVITY.' as AV', 'AV.ActivityID = LT.ActivityStatus');
|
|
$this->db->join(PICK_LIST_DETAILS.' as PLD1', 'PLD1.ListCode = LT.StatusCode');
|
|
$this->db->join(BRANCH.' as BR', 'BR.BranchCode = LT.CreatedBranch');
|
|
|
|
$this->db->where('LT.TrackingID',$trackingID['TrackingID']);
|
|
if($trackingID['CreatedBranch'] !='All' AND $trackingID['requestedDept'] != EMPLOYEE){
|
|
$this->db->where('LT.CreatedBranch', $trackingID['CreatedBranch']);
|
|
|
|
}
|
|
if ($trackingID['requestedDept'] == EMPLOYEE){
|
|
|
|
$this->db->where('LT.AssignedTo', $trackingID['UpdatedBy']);
|
|
$this->db->where('LT.CreatedBranch', $trackingID['CreatedBranch']);
|
|
|
|
}
|
|
|
|
$leadDet=$this->db->get();
|
|
|
|
if($leadDet){
|
|
$result_array = array();
|
|
$result_array['followupStatus']=true;
|
|
|
|
foreach ($leadDet->result() as $row)
|
|
{
|
|
$activityID = $row->ActivityStatus;
|
|
$tracking_array["LeadID"]=$row->LeadID;
|
|
$tracking_array["LeadName"]=$row->LeadName;
|
|
$tracking_array["MobileNumber"]=$row->MobileNumber;
|
|
$tracking_array["IsNewEnquiry"]=$row->IsNewEnquiry;
|
|
$tracking_array["TrackingID"]=$row->TrackingID;
|
|
$tracking_array["ActivityStatus"]=$row->ActivityStatus;
|
|
$tracking_array["AssignedTo"]=$row->AssignedTo;
|
|
$tracking_array["CreatedBranch"]=$row->CreatedBranch;
|
|
$tracking_array["CreatedBranchName"]=$row->BranchName;
|
|
$tracking_array["Firstname"]=$row->Firstname;
|
|
$tracking_array["StaffID"]=$row->StaffID;
|
|
$tracking_array["StatusCode"]=$row->StatusCode;
|
|
$tracking_array["statusName"]=$row->statusListName;
|
|
$tracking_array["UniversityName"]=$row->University;
|
|
$tracking_array["CourseName"]=$row->Course;
|
|
$tracking_array["ActivityID"]=$row->ActivityID;
|
|
$tracking_array["ActivityName"]=$row->ActivityName;
|
|
$tracking_array["AlternateMobile"]=$row->AlternateMobile;
|
|
$tracking_array["Address"]=$row->Address;
|
|
$tracking_array["ReferredBy"]=$row->ReferredBy;
|
|
$tracking_array["followupDetails"]=$this->getTrackingFollowup($row->TrackingID);
|
|
$tracking_array["collectTrackedID"]=$this->getStudentTrackedID($row->MobileNumber,$trackingID['CreatedBranch'],$trackingID['requestedDept'],$trackingID['UpdatedBy']);
|
|
|
|
$result[]=$tracking_array;
|
|
|
|
|
|
}
|
|
$result_array['trackingDetails']=$result;
|
|
$result_array['statusDetails']=$this->getStatus($activityID);
|
|
$result_array['staffDetails']=$this->getStaff("1",$trackingID['CreatedBranch'],$trackingID['requestedDept'],$trackingID['UpdatedBy']);
|
|
|
|
|
|
$result_array['trackingDetails']=$result;
|
|
|
|
}
|
|
else{
|
|
$result_array['followupStatus']=false;
|
|
$result_array['trackingDetails']="";
|
|
}
|
|
|
|
|
|
return $result_array;
|
|
|
|
}
|
|
|
|
/*
|
|
* get student tracked id
|
|
* @params mobile number
|
|
* created by kms
|
|
* */
|
|
public function getStudentTrackedID($stuMobile=null,$branchId=null,$reqDept=null,$updatedBy=null)
|
|
{
|
|
$this->db->distinct();
|
|
$this->db->select('LT.TrackingID,LT.LeadID,AV.ActivityID,AV.ActivityName,PLD.ListName');
|
|
$this->db->from(LEAD_DETAILS.' as LD');
|
|
$this->db->join(LEAD_TRACKING.' as LT', 'LT.LeadID = LD.LeadID');
|
|
$this->db->join(ACTIVITY.' as AV', 'AV.ActivityID = LT.ActivityStatus');
|
|
$this->db->join(PICK_LIST_DETAILS.' as PLD', 'PLD.ListCode = LT.StatusCode');
|
|
$this->db->where('LD.MobileNumber',$stuMobile);
|
|
|
|
if($branchId !='All' AND $reqDept != EMPLOYEE){
|
|
$this->db->where('LT.CreatedBranch', $branchId);
|
|
|
|
}
|
|
if ($reqDept == EMPLOYEE){
|
|
|
|
$this->db->where('LT.AssignedTo', $updatedBy);
|
|
$this->db->where('LT.CreatedBranch', $branchId);
|
|
|
|
|
|
}
|
|
|
|
$this->db->order_by('LT.TrackingID','ASC');
|
|
$studeTrackID = $this->db->get();
|
|
return $studeTrackID->result();
|
|
}
|
|
/*
|
|
* used to get dash board call track details
|
|
* created by kms
|
|
* */
|
|
public function getDashboardTrackingDetails($serach=null)
|
|
{
|
|
$now = new DateTime();
|
|
$now->setTimezone(new DateTimezone('Asia/Kolkata'));
|
|
$todayFollowupDate=$now->format('d-m-Y');
|
|
|
|
$todayDateTime=$now->format('Y-m-d');
|
|
|
|
|
|
|
|
// $follouwpDetails=$this->todayFollowupDetails($todayFollowupDate,CLOSE,'1');
|
|
$follouwpDetails=$this->todayFollowUp($todayFollowupDate,$serach);
|
|
if($follouwpDetails){
|
|
$result_array['todayStatus']=true;
|
|
$result_array['todayFolloupDetails']=$follouwpDetails;
|
|
}
|
|
else{
|
|
$result_array['todayStatus']=false;
|
|
$result_array['todayFolloupDetails']="";
|
|
|
|
}
|
|
// $follouwpPendingDetails=$this->todayFollowupDetails($todayFollowupDate,CLOSE,'2');
|
|
$follouwpPendingDetails=$this->tillPendingTrackingDetails(CLOSE,$serach);
|
|
|
|
if($follouwpPendingDetails){
|
|
$result_array['todayPendingStatus']=true;
|
|
$result_array['PendingFolloupDetails']=$follouwpPendingDetails;
|
|
}
|
|
else{
|
|
$result_array['todayPendingStatus']=false;
|
|
$result_array['PendingFolloupDetails']="";
|
|
|
|
}
|
|
// $todayLeadDetails=$this->todayFollowupDetails($todayDateTime,OPEN,'3');
|
|
$todayLeadDetails=$this->todayCreatedLead($todayDateTime,$serach);
|
|
if($todayLeadDetails){
|
|
$result_array['todayLeadStatus']=true;
|
|
$result_array['todayLeadDetails']=$todayLeadDetails;
|
|
}
|
|
else{
|
|
$result_array['todayLeadStatus']=false;
|
|
$result_array['todayLeadDetails']="";
|
|
|
|
}
|
|
|
|
return $result_array;
|
|
|
|
}
|
|
|
|
/*
|
|
* get dash board today followupdetails
|
|
* created by kms
|
|
* */
|
|
public function todayFollowUp($todayDate,$search=null){
|
|
$this->db->distinct();
|
|
$this->db->select('LD.LeadName,LD.MobileNumber,LT.TrackingID,LT.LeadID,AV.ActivityID,AV.ActivityName,LTF.FollowupOn,LT.StatusCode,PLD1.ListName as statusListName');
|
|
$this->db->from(LEAD_TRACKING_FOLLOWUP.' as LTF');
|
|
$this->db->join(LEAD_TRACKING.' as LT','LT.TrackingID=LTF.TrackingID');
|
|
$this->db->join(LEAD_DETAILS.' as LD', 'LT.LeadID = LD.LeadID');
|
|
$this->db->join(LOGIN.' as LO', 'LO.ID = LT.AssignedTo');
|
|
$this->db->join(STAFF.' as ST', 'ST.StaffID = LO.StaffID');
|
|
$this->db->join(ACTIVITY.' as AV', 'AV.ActivityID = LT.ActivityStatus');
|
|
$this->db->join(PICK_LIST_DETAILS.' as PLD1', 'PLD1.ListCode = LT.StatusCode');
|
|
if($search['BranchID'] !='All' AND $search['requestedDept'] != EMPLOYEE){
|
|
$this->db->where('LT.CreatedBranch', $search['BranchID']);
|
|
|
|
}
|
|
if ($search['requestedDept'] == EMPLOYEE){
|
|
|
|
$this->db->where('LT.AssignedTo', $search['requestedBy']);
|
|
$this->db->where('LT.CreatedBranch', $search['BranchID']);
|
|
|
|
}
|
|
$this->db->where('LTF.FollowupOn',$todayDate);
|
|
$trackDetails=$this->db->get();
|
|
|
|
if($trackDetails->result()){
|
|
foreach ($trackDetails->result() as $row1)
|
|
{
|
|
|
|
$tracking_array["LeadID"]=$row1->LeadID;
|
|
$tracking_array["LeadName"]=$row1->LeadName;
|
|
$tracking_array["MobileNumber"]=$row1->MobileNumber;
|
|
$tracking_array["TrackingID"]=$row1->TrackingID;
|
|
$tracking_array["FollowupOn"]=$row1->FollowupOn;
|
|
$tracking_array["StatusCode"]=$row1->StatusCode;
|
|
$tracking_array["statusName"]=$row1->statusListName;
|
|
$tracking_array["ActivityID"]=$row1->ActivityID;
|
|
$tracking_array["ActivityName"]=$row1->ActivityName;
|
|
$result[]=$tracking_array;
|
|
|
|
}
|
|
$result_array['trackingDetails']=$result;
|
|
return $result_array;
|
|
}
|
|
else {
|
|
return false;
|
|
}
|
|
|
|
}
|
|
/*
|
|
* get today lead details
|
|
* created by kms
|
|
* */
|
|
public function todayCreatedLead($todayDate,$search=null){
|
|
$this->db->distinct();
|
|
$this->db->select('LD.LeadName,LD.MobileNumber,LT.TrackingID,LT.LeadID,AV.ActivityID,AV.ActivityName,LTF.FollowupOn,LT.StatusCode,PLD1.ListName as statusListName');
|
|
$this->db->from(LEAD_TRACKING_FOLLOWUP.' as LTF');
|
|
$this->db->join(LEAD_TRACKING.' as LT','LT.TrackingID=LTF.TrackingID');
|
|
$this->db->join(LEAD_DETAILS.' as LD', 'LT.LeadID = LD.LeadID');
|
|
$this->db->join(LOGIN.' as LO', 'LO.ID = LT.AssignedTo');
|
|
$this->db->join(STAFF.' as ST', 'ST.StaffID = LO.StaffID');
|
|
$this->db->join(ACTIVITY.' as AV', 'AV.ActivityID = LT.ActivityStatus');
|
|
$this->db->join(PICK_LIST_DETAILS.' as PLD1', 'PLD1.ListCode = LT.StatusCode');
|
|
$this->db->where('LTF.IsActive', '1');
|
|
if($search['BranchID'] !='All' AND $search['requestedDept'] != EMPLOYEE){
|
|
$this->db->where('LT.CreatedBranch', $search['BranchID']);
|
|
|
|
}
|
|
if ($search['requestedDept'] == EMPLOYEE){
|
|
|
|
$this->db->where('LT.AssignedTo', $search['requestedBy']);
|
|
$this->db->where('LT.CreatedBranch', $search['BranchID']);
|
|
|
|
}
|
|
$this->db->like('LD.CreatedOn', $todayDate, 'after');
|
|
$trackDetails=$this->db->get();
|
|
|
|
if($trackDetails->result()){
|
|
foreach ($trackDetails->result() as $row1)
|
|
{
|
|
|
|
$tracking_array["LeadID"]=$row1->LeadID;
|
|
$tracking_array["LeadName"]=$row1->LeadName;
|
|
$tracking_array["MobileNumber"]=$row1->MobileNumber;
|
|
$tracking_array["TrackingID"]=$row1->TrackingID;
|
|
$tracking_array["FollowupOn"]=$row1->FollowupOn;
|
|
$tracking_array["StatusCode"]=$row1->StatusCode;
|
|
$tracking_array["statusName"]=$row1->statusListName;
|
|
$tracking_array["ActivityID"]=$row1->ActivityID;
|
|
$tracking_array["ActivityName"]=$row1->ActivityName;
|
|
$result[]=$tracking_array;
|
|
|
|
}
|
|
$result_array['trackingDetails']=$result;
|
|
return $result_array;
|
|
}
|
|
else {
|
|
return false;
|
|
}
|
|
}
|
|
|
|
/*
|
|
* get till pending tracking details
|
|
* created by kms
|
|
* */
|
|
public function tillPendingTrackingDetails($status,$search=null){
|
|
$this->db->distinct();
|
|
$this->db->select('LD.LeadName,LD.MobileNumber,LT.TrackingID,LT.LeadID,AV.ActivityID,AV.ActivityName,LTF.FollowupOn,LT.StatusCode,PLD1.ListName as statusListName');
|
|
$this->db->from(LEAD_TRACKING_FOLLOWUP.' as LTF');
|
|
$this->db->join(LEAD_TRACKING.' as LT','LT.TrackingID=LTF.TrackingID');
|
|
$this->db->join(LEAD_DETAILS.' as LD', 'LT.LeadID = LD.LeadID');
|
|
$this->db->join(LOGIN.' as LO', 'LO.ID = LT.AssignedTo');
|
|
$this->db->join(STAFF.' as ST', 'ST.StaffID = LO.StaffID');
|
|
$this->db->join(ACTIVITY.' as AV', 'AV.ActivityID = LT.ActivityStatus');
|
|
$this->db->join(PICK_LIST_DETAILS.' as PLD1', 'PLD1.ListCode = LT.StatusCode');
|
|
$this->db->where('LTF.IsActive', '1');
|
|
$this->db->where('PLD1.ListName !=', $status);
|
|
if($search['BranchID'] !='All' AND $search['requestedDept'] != EMPLOYEE){
|
|
$this->db->where('LT.CreatedBranch', $search['BranchID']);
|
|
|
|
}
|
|
if ($search['requestedDept'] == EMPLOYEE){
|
|
|
|
$this->db->where('LT.AssignedTo', $search['requestedBy']);
|
|
$this->db->where('LT.CreatedBranch', $search['BranchID']);
|
|
|
|
}
|
|
$trackDetails=$this->db->get();
|
|
|
|
if($trackDetails->result()){
|
|
foreach ($trackDetails->result() as $row1)
|
|
{
|
|
|
|
$tracking_array["LeadID"]=$row1->LeadID;
|
|
$tracking_array["LeadName"]=$row1->LeadName;
|
|
$tracking_array["MobileNumber"]=$row1->MobileNumber;
|
|
$tracking_array["TrackingID"]=$row1->TrackingID;
|
|
$tracking_array["FollowupOn"]=$row1->FollowupOn;
|
|
$tracking_array["StatusCode"]=$row1->StatusCode;
|
|
$tracking_array["statusName"]=$row1->statusListName;
|
|
$tracking_array["ActivityID"]=$row1->ActivityID;
|
|
$tracking_array["ActivityName"]=$row1->ActivityName;
|
|
$result[]=$tracking_array;
|
|
|
|
}
|
|
$result_array['trackingDetails']=$result;
|
|
return $result_array;
|
|
}
|
|
else {
|
|
return false;
|
|
}
|
|
}
|
|
|
|
|
|
/*
|
|
* get date wise followup details
|
|
* @params date and status and check status
|
|
* */
|
|
public function todayFollowupDetails($todayDate=null,$status=null,$check=null)
|
|
{
|
|
$this->db->distinct();
|
|
$this->db->select('LT.TrackingID');
|
|
$this->db->from(LEAD_DETAILS.' as LD');
|
|
$this->db->from(LEAD_TRACKING.' as LT', 'LT.LeadID = LD.LeadID');
|
|
$this->db->order_by('LT.TrackingID','ASC');
|
|
/* if($check!='3'){
|
|
$this->db->where('LT.StatusCode !=',$status);
|
|
}
|
|
else if($check=='2'){
|
|
$this->db->where('LT.StatusCode !=',$status);
|
|
}*/
|
|
if($check=='3'){
|
|
$this->db->like('LD.CreatedOn', $todayDate, 'after');
|
|
}
|
|
|
|
$leadDetails=$this->db->get();
|
|
if($leadDetails->result()){
|
|
$result_array = array();
|
|
foreach ($leadDetails->result() as $row){
|
|
$this->db->select('LTF.TrackingID,LTF.InteractionId');
|
|
$this->db->from(LEAD_TRACKING_FOLLOWUP.' as LTF');
|
|
$this->db->where('LTF.TrackingID',$row->TrackingID);
|
|
$lastFollowpID=$this->db->get()->last_row();
|
|
// get last followup details date wise
|
|
$this->db->distinct();
|
|
$this->db->select('LD.LeadName,LD.MobileNumber,LT.TrackingID,LT.LeadID,AV.ActivityID,AV.ActivityName,LTF.FollowupOn,LT.StatusCode,PLD1.ListName as statusListName');
|
|
$this->db->from(LEAD_DETAILS.' as LD');
|
|
$this->db->join(LEAD_TRACKING.' as LT', 'LT.LeadID = LD.LeadID');
|
|
$this->db->join(LEAD_TRACKING_FOLLOWUP.' as LTF', 'LTF.TrackingID = LT.TrackingID');
|
|
$this->db->join(ACTIVITY.' as AV', 'AV.ActivityID = LT.ActivityStatus');
|
|
$this->db->join(PICK_LIST_DETAILS.' as PLD1', 'PLD1.ListCode = LT.StatusCode');
|
|
$this->db->where('LTF.InteractionId',$lastFollowpID->InteractionId);
|
|
|
|
// check today date based follwup details
|
|
if($check=='1'){
|
|
$this->db->where('LTF.FollowupOn',$todayDate);
|
|
// $this->db->where('LT.StatusCode !=',$status);
|
|
}
|
|
// check today date based pending followp details
|
|
/* else if($check=='2'){
|
|
// $this->db->where('LTF.FollowupOn >',$todayDate);
|
|
$this->db->where('LT.StatusCode !=',$status);
|
|
}*/
|
|
else if($check=='3'){
|
|
$this->db->like('LD.CreatedOn', $todayDate, 'after');
|
|
}
|
|
$trackDetails=$this->db->get();
|
|
|
|
if($trackDetails->result()){
|
|
foreach ($trackDetails->result() as $row1)
|
|
{
|
|
|
|
$tracking_array["LeadID"]=$row1->LeadID;
|
|
$tracking_array["LeadName"]=$row1->LeadName;
|
|
$tracking_array["MobileNumber"]=$row1->MobileNumber;
|
|
$tracking_array["TrackingID"]=$row1->TrackingID;
|
|
$tracking_array["FollowupOn"]=$row1->FollowupOn;
|
|
$tracking_array["StatusCode"]=$row1->StatusCode;
|
|
$tracking_array["statusName"]=$row1->statusListName;
|
|
$tracking_array["ActivityID"]=$row1->ActivityID;
|
|
$tracking_array["ActivityName"]=$row1->ActivityName;
|
|
$result[]=$tracking_array;
|
|
|
|
}
|
|
$result_array['trackingDetails']=$result;
|
|
}
|
|
|
|
|
|
}
|
|
|
|
return $result_array;
|
|
|
|
}
|
|
|
|
else{
|
|
return false;
|
|
}
|
|
|
|
}
|
|
|
|
|
|
/*
|
|
* get all followup details
|
|
* created by kms
|
|
* */
|
|
|
|
public function getDashboardFollowupDetails($search=null)
|
|
{
|
|
$this->db->distinct();
|
|
$this->db->select('LT.TrackingID');
|
|
$this->db->from(LEAD_DETAILS.' as LD');
|
|
$this->db->from(LEAD_TRACKING.' as LT', 'LT.LeadID = LD.LeadID');
|
|
$this->db->order_by('LT.TrackingID','ASC');
|
|
$leadDetails=$this->db->get();
|
|
if($leadDetails->result()){
|
|
foreach ($leadDetails->result() as $row){
|
|
$this->db->select('LTF.TrackingID,LTF.InteractionId');
|
|
$this->db->from(LEAD_TRACKING_FOLLOWUP.' as LTF');
|
|
$this->db->where('LTF.TrackingID',$row->TrackingID);
|
|
$lastFollowpID=$this->db->get()->last_row();
|
|
// get last followup details date wise
|
|
$this->db->distinct();
|
|
$this->db->select('DATE_FORMAT(LTF.CreatedOn, "%d-%m-%Y") AS CreatedOn,LD.LeadName,LD.MobileNumber,LT.TrackingID,LT.LeadID,AV.ActivityID,AV.ActivityName,LTF.FollowupOn,LT.StatusCode,PLD1.ListName as statusListName');
|
|
$this->db->from(LEAD_DETAILS.' as LD');
|
|
$this->db->join(LEAD_TRACKING.' as LT', 'LT.LeadID = LD.LeadID');
|
|
$this->db->join(LEAD_TRACKING_FOLLOWUP.' as LTF', 'LTF.TrackingID = LT.TrackingID');
|
|
$this->db->join(ACTIVITY.' as AV', 'AV.ActivityID = LT.ActivityStatus');
|
|
$this->db->join(PICK_LIST_DETAILS.' as PLD1', 'PLD1.ListCode = LT.StatusCode');
|
|
$this->db->where('LTF.InteractionId',$lastFollowpID->InteractionId);
|
|
|
|
$result[]=$this->db->get()->result();
|
|
|
|
}
|
|
$result_array['details']=$result;
|
|
$result_array['followupStatus']=true;
|
|
|
|
return $result_array;
|
|
|
|
}
|
|
|
|
else{
|
|
$result_array['details']="";
|
|
$result_array['followupStatus']=false;
|
|
}
|
|
return $result_array;
|
|
|
|
}
|
|
/*
|
|
* get recent lead details
|
|
* params: date
|
|
* created by kms
|
|
* */
|
|
|
|
public function getRecentleadDetails($search=null)
|
|
{
|
|
|
|
$this->db->select('LD.LeadID,LD.LeadName,LD.MobileNumber,LD.IsNewEnquiry,LT.TrackingID,LT.ActivityStatus,LT.AssignedTo,LT.StatusCode,LT.University,LT.Course,LT.AlternateMobile,LT.Address,LT.Flag,ST.StaffID,ST.Firstname,PLD1.ListCode as statusListCode,PLD1.ListName as statusListName,AV.ActivityID,AV.ActivityName,DATE_FORMAT(LT.CreatedOn, "%d-%m-%Y") AS CreatedOn,DATE_FORMAT(LT.UpdatedOn, "%d-%m-%Y") AS UpdatedOn');
|
|
$this->db->from(LEAD_DETAILS.' as LD');
|
|
$this->db->join(LEAD_TRACKING.' as LT', 'LT.LeadID = LD.LeadID');
|
|
$this->db->join(LOGIN.' as LO', 'LO.ID = LT.AssignedTo');
|
|
$this->db->join(STAFF.' as ST', 'ST.StaffID = LO.StaffID');
|
|
$this->db->join(ACTIVITY.' as AV', 'AV.ActivityID = LT.ActivityStatus');
|
|
$this->db->join(PICK_LIST_DETAILS.' as PLD1', 'PLD1.ListCode = LT.StatusCode');
|
|
if($search['loadDate'] !='' ) {
|
|
$this->db->like('LD.CreatedOn', $search['loadDate'], 'after');
|
|
}
|
|
if($search['CreatedBranch'] !='All'){
|
|
$this->db->where('LT.CreatedBranch', $search['CreatedBranch']);
|
|
|
|
}
|
|
$this->db->order_by('LT.TrackingID', 'DESC');
|
|
$leadDet=$this->db->get();
|
|
if($this->db->affected_rows()==0){
|
|
$result_array['trackingStatus']=false;
|
|
$result_array['trackingDetails']="";
|
|
}
|
|
else{
|
|
if($leadDet){
|
|
$result_array = array();
|
|
$result_array['trackingStatus']=true;
|
|
|
|
foreach ($leadDet->result() as $row)
|
|
{
|
|
|
|
$tracking_array["LeadID"]=$row->LeadID;
|
|
$tracking_array["LeadName"]=$row->LeadName;
|
|
$tracking_array["MobileNumber"]=$row->MobileNumber;
|
|
$tracking_array["IsNewEnquiry"]=$row->IsNewEnquiry;
|
|
$tracking_array["TrackingID"]=$row->TrackingID;
|
|
$tracking_array["Flag"]=$row->Flag;
|
|
$tracking_array["ActivityStatus"]=$row->ActivityStatus;
|
|
$tracking_array["AssignedTo"]=$row->AssignedTo;
|
|
$tracking_array["Firstname"]=$row->Firstname;
|
|
$tracking_array["StatusCode"]=$row->StatusCode;
|
|
$tracking_array["statusName"]=$row->statusListName;
|
|
$tracking_array["UniversityName"]=$row->University;
|
|
$tracking_array["CourseName"]=$row->Course;
|
|
$tracking_array["ActivityID"]=$row->ActivityID;
|
|
$tracking_array["ActivityName"]=$row->ActivityName;
|
|
$tracking_array["AlternateMobile"]=$row->AlternateMobile;
|
|
$tracking_array["Address"]=$row->Address;
|
|
$tracking_array["CreatedOn"]=$row->CreatedOn;
|
|
$tracking_array["UpdatedOn"]=$row->UpdatedOn;
|
|
// $tracking_array["followupDetails"]=$this->getTrackingFollowup($row->TrackingID);
|
|
$followupDetails=$this->getTrackingFollowup($row->TrackingID);
|
|
|
|
$tracking_array["followupDetails"]=$followupDetails[0]->FollowupOn;
|
|
$result[]=$tracking_array;
|
|
|
|
|
|
}
|
|
$result_array['trackingDetails']=$result;
|
|
// $result_array['statusDetails']=$this->getStatus();
|
|
$result_array['trackingDetails']=$result;
|
|
|
|
}
|
|
}
|
|
return $result_array;
|
|
|
|
}
|
|
/*
|
|
* get recent track close details
|
|
* created by kms
|
|
* */
|
|
public function getRecentCloseDetails($search=null)
|
|
{
|
|
$this->db->select('LD.LeadID,LD.LeadName,LD.MobileNumber,LD.IsNewEnquiry,LT.TrackingID,LT.ActivityStatus,LT.AssignedTo,LT.StatusCode,LT.University,LT.Course,LT.AlternateMobile,LT.Address,LT.Flag,ST.StaffID,ST.Firstname,PLD1.ListCode as statusListCode,PLD1.ListName as statusListName,AV.ActivityID,AV.ActivityName,DATE_FORMAT(LT.CreatedOn, "%d-%m-%Y") AS CreatedOn,DATE_FORMAT(LT.UpdatedOn, "%d-%m-%Y") AS UpdatedOn');
|
|
$this->db->from(LEAD_DETAILS.' as LD');
|
|
$this->db->join(LEAD_TRACKING.' as LT', 'LT.LeadID = LD.LeadID');
|
|
$this->db->join(LOGIN.' as LO', 'LO.ID = LT.AssignedTo');
|
|
$this->db->join(STAFF.' as ST', 'ST.StaffID = LO.StaffID');
|
|
$this->db->join(ACTIVITY.' as AV', 'AV.ActivityID = LT.ActivityStatus');
|
|
$this->db->join(PICK_LIST_DETAILS.' as PLD1', 'PLD1.ListCode = LT.StatusCode');
|
|
if($search['loadDate'] !='' ) {
|
|
$this->db->like('LT.UpdatedOn', $search['loadDate'], 'after');
|
|
}
|
|
if($search['CreatedBranch'] !='All'){
|
|
$this->db->where('LT.CreatedBranch', $search['CreatedBranch']);
|
|
|
|
}
|
|
$this->db->like('PLD1.ListName', 'CLOSE', 'after');
|
|
$this->db->order_by('LT.TrackingID', 'DESC');
|
|
$leadDet=$this->db->get();
|
|
if($this->db->affected_rows()==0){
|
|
$result_array['trackingStatus']=false;
|
|
$result_array['trackingDetails']="";
|
|
}
|
|
else{
|
|
if($leadDet){
|
|
$result_array = array();
|
|
$result_array['trackingStatus']=true;
|
|
|
|
foreach ($leadDet->result() as $row)
|
|
{
|
|
|
|
$tracking_array["LeadID"]=$row->LeadID;
|
|
$tracking_array["LeadName"]=$row->LeadName;
|
|
$tracking_array["MobileNumber"]=$row->MobileNumber;
|
|
$tracking_array["IsNewEnquiry"]=$row->IsNewEnquiry;
|
|
$tracking_array["TrackingID"]=$row->TrackingID;
|
|
$tracking_array["Flag"]=$row->Flag;
|
|
$tracking_array["ActivityStatus"]=$row->ActivityStatus;
|
|
$tracking_array["AssignedTo"]=$row->AssignedTo;
|
|
$tracking_array["Firstname"]=$row->Firstname;
|
|
$tracking_array["StatusCode"]=$row->StatusCode;
|
|
$tracking_array["statusName"]=$row->statusListName;
|
|
$tracking_array["UniversityName"]=$row->University;
|
|
$tracking_array["CourseName"]=$row->Course;
|
|
$tracking_array["ActivityID"]=$row->ActivityID;
|
|
$tracking_array["ActivityName"]=$row->ActivityName;
|
|
$tracking_array["AlternateMobile"]=$row->AlternateMobile;
|
|
$tracking_array["Address"]=$row->Address;
|
|
$tracking_array["CreatedOn"]=$row->CreatedOn;
|
|
$tracking_array["UpdatedOn"]=$row->UpdatedOn;
|
|
// $tracking_array["followupDetails"]=$this->getTrackingFollowup($row->TrackingID);
|
|
$followupDetails=$this->getTrackingFollowup($row->TrackingID);
|
|
|
|
$tracking_array["followupDetails"]=$followupDetails[0]->FollowupOn;
|
|
$result[]=$tracking_array;
|
|
|
|
|
|
}
|
|
$result_array['trackingDetails']=$result;
|
|
// $result_array['statusDetails']=$this->getStatus();
|
|
$result_array['trackingDetails']=$result;
|
|
|
|
}
|
|
}
|
|
return $result_array;
|
|
|
|
}
|
|
/*
|
|
* get recent pending details
|
|
* created by kms
|
|
* */
|
|
public function getRecentPendingDetails($search=null)
|
|
{
|
|
$this->db->select('LD.LeadID,LD.LeadName,LD.MobileNumber,LD.IsNewEnquiry,LT.TrackingID,LT.ActivityStatus,LT.AssignedTo,LT.StatusCode,LT.University,LT.Course,LT.AlternateMobile,LT.Address,LT.Flag,ST.StaffID,ST.Firstname,PLD1.ListCode as statusListCode,PLD1.ListName as statusListName,AV.ActivityID,AV.ActivityName,DATE_FORMAT(LT.CreatedOn, "%d-%m-%Y") AS CreatedOn,DATE_FORMAT(LT.UpdatedOn, "%d-%m-%Y") AS UpdatedOn');
|
|
$this->db->from(LEAD_DETAILS.' as LD');
|
|
$this->db->join(LEAD_TRACKING.' as LT', 'LT.LeadID = LD.LeadID');
|
|
$this->db->join(LOGIN.' as LO', 'LO.ID = LT.AssignedTo');
|
|
$this->db->join(STAFF.' as ST', 'ST.StaffID = LO.StaffID');
|
|
$this->db->join(ACTIVITY.' as AV', 'AV.ActivityID = LT.ActivityStatus');
|
|
$this->db->join(PICK_LIST_DETAILS.' as PLD1', 'PLD1.ListCode = LT.StatusCode');
|
|
if($search['loadDate'] !='' ){
|
|
$this->db->like('LT.UpdatedOn', $search['loadDate'], 'after');
|
|
|
|
}
|
|
if($search['CreatedBranch'] !='All'){
|
|
$this->db->where('LT.CreatedBranch', $search['CreatedBranch']);
|
|
|
|
}
|
|
$this->db->where('PLD1.ListName !=', 'CLOSE');
|
|
$this->db->order_by('LT.TrackingID', 'DESC');
|
|
$leadDet=$this->db->get();
|
|
if($this->db->affected_rows()==0){
|
|
$result_array['trackingStatus']=false;
|
|
$result_array['trackingDetails']="";
|
|
}
|
|
else{
|
|
if($leadDet){
|
|
$result_array = array();
|
|
$result_array['trackingStatus']=true;
|
|
|
|
foreach ($leadDet->result() as $row)
|
|
{
|
|
|
|
$tracking_array["LeadID"]=$row->LeadID;
|
|
$tracking_array["LeadName"]=$row->LeadName;
|
|
$tracking_array["MobileNumber"]=$row->MobileNumber;
|
|
$tracking_array["IsNewEnquiry"]=$row->IsNewEnquiry;
|
|
$tracking_array["TrackingID"]=$row->TrackingID;
|
|
$tracking_array["Flag"]=$row->Flag;
|
|
$tracking_array["ActivityStatus"]=$row->ActivityStatus;
|
|
$tracking_array["AssignedTo"]=$row->AssignedTo;
|
|
$tracking_array["Firstname"]=$row->Firstname;
|
|
$tracking_array["StatusCode"]=$row->StatusCode;
|
|
$tracking_array["statusName"]=$row->statusListName;
|
|
$tracking_array["UniversityName"]=$row->University;
|
|
$tracking_array["CourseName"]=$row->Course;
|
|
$tracking_array["ActivityID"]=$row->ActivityID;
|
|
$tracking_array["ActivityName"]=$row->ActivityName;
|
|
$tracking_array["AlternateMobile"]=$row->AlternateMobile;
|
|
$tracking_array["Address"]=$row->Address;
|
|
$tracking_array["CreatedOn"]=$row->CreatedOn;
|
|
$tracking_array["UpdatedOn"]=$row->UpdatedOn;
|
|
// $tracking_array["followupDetails"]=$this->getTrackingFollowup($row->TrackingID);
|
|
$followupDetails=$this->getTrackingFollowup($row->TrackingID);
|
|
|
|
$tracking_array["followupDetails"]=$followupDetails[0]->FollowupOn;
|
|
$result[]=$tracking_array;
|
|
|
|
|
|
}
|
|
$result_array['trackingDetails']=$result;
|
|
// $result_array['statusDetails']=$this->getStatus();
|
|
$result_array['trackingDetails']=$result;
|
|
|
|
}
|
|
}
|
|
return $result_array;
|
|
|
|
}
|
|
/*
|
|
* update call track as important
|
|
* created by kms
|
|
* */
|
|
public function updateFlagStatus($trackID=null,$flagStatus=null){
|
|
$updateDetails['Flag']= $flagStatus;
|
|
$this->db->where('TrackingID',$trackID);
|
|
$this->db->update(LEAD_TRACKING, $updateDetails);
|
|
if($this->db->affected_rows() > '0') {
|
|
$result['updatedStatus']=true;
|
|
|
|
}
|
|
else{
|
|
$result['updatedStatus']=false;
|
|
|
|
}
|
|
return $result;
|
|
|
|
}
|
|
|
|
} |