db=new DB();
if($_REQUEST['test']==1){
$this->db->show_errors = TRUE;
}else{
$this->db->show_errors = FALSE;
}
$this->from_email = 'no-reply@google.api.prophog2.com';
$this->from_name = 'Google Lead Processing Support';
$this->from = $this->from_name.'<'.$this->from_email.'>';
$this->replyTo = $this->from_email;
$this->app_dir = '/var/www/google.api.prophog2.com/';
$this->insurance_api_link = 'https://agent-recruiting.prophog2.com/api';
$this->insurance_api_key = 'wrH71keQw8Tr23KzSp3m';
$this->skipComps = array(" LLC", " L.L.C.", " Corp", "Bank", "Chase", "Lending", "Mortgage", "Funding", "National", "Association", "Corporatation", "Professional", "Home", "Group", " INC", "Service", "Finance", "Company", " Comp.", " Co.", "Federal", "Credit", "Union", "Counseling", "Advisor", "Foundation", "Trust", "America", " City", " State", " County", "Center", "Broker", "American", "America's", "Americash", "Advance", "Acceptance", "Receivable", "Heritage", "Empire", "Real Estate", "Management", "Solution", "Properties", "Property", "Loans", "Connection", "Process", "Fast Lane", "Fast Track", "Quality Loan", ".com", ".org");
}
//Google Data Extraction Functions Start ==============================
function googleGetListingStatus() {
$search_terms = $this->getSearchTerms();
$tot = doubleval($this->db->getRecordByID("count(*)", "leads", "where search_terms='".$search_terms."'", 1));
echo ''.$tot.'; ';
}
//====================================================================
private function getSearchTerms(){
$search_terms = str_ireplace(" ", " ", urldecode($_REQUEST['search_terms']));
$search_terms = trim(str_ireplace("Busienss", "Business", $search_terms));
return $search_terms;
}
//====================================================================
function googleAddListingDetails(){
$mydata = $_REQUEST['listingDetails']; $data247_balance=1;
$tool_version = doubleval($_REQUEST['tool_version']);
$ansArr['res']=0; $ansArr['isCorrectListingDetailsProvided'] = 0;
$ansArr['is_wireless']='n'; $ansArr['is_email_valid']='N';
$ansArr['is_address_valid']=''; $ansArr['is_home_vacant']='';
$ansArr['is_bad_number']=0; $ansArr['is_tcpa']=0; $ansArr['is_dnc']=0;
$myInsertDataArr = array();
$arr = (array)json_decode($mydata, true);
if($_REQUEST['test']==1){ echo "listingDetails array found = "; print_r($arr); }
if(!empty($arr)){
foreach((array)$arr as $key=>$val){
$myInsertDataArr[$key] = $val;
}
if($_REQUEST['test']==1){ echo "\nmyInsertDataArr found = "; print_r($myInsertDataArr); }
$myInsertDataArr['search_terms'] = $this->getSearchTerms();
$myInsertDataArr['first_name'] = $myInsertDataArr['name'];
//$myInsertDataArr['last_name'] = 'zpid';
$username = $_REQUEST['username'];
$folder_id = doubleval($_REQUEST['folder_id']);
$user_id = doubleval($_REQUEST['user_id']);
$validate_email = doubleval($_REQUEST['validate_email']);
$validate_address = doubleval($_REQUEST['validate_address']);
$verify_tcpa_dnc = doubleval($_REQUEST['verify_tcpa_dnc']);
$tbl = "ld_user"; $fld = "userid"; $fld2 = "username"; $validate_email=1;
//if($user_id<=0){ $user_id = doubleval($this->db->getRecordByID($fld, $tbl, "where ".$fld2." like '".$username."'", 1)); }
$usr = "fld=".$fld.";fld2=".$fld2.";tbl=".$tbl.";user_id=".$user_id.";tool_version=".$tool_version;
$myInsertDataArr['user_id'] = $user_id;
$myInsertDataArr['is_email_valid'] = 'N';
if(array_key_exists('email', $myInsertDataArr)){
if($myInsertDataArr['email']!='missing.phone@google.com' && !empty($myInsertDataArr['email'])){
if(($validate_email==1 && filter_var($myInsertDataArr['email'], FILTER_VALIDATE_EMAIL))){
//if(($is_ignore_email_domains==1 && $this->verifyEmailDomain($myInsertDataArr['email'], $email_domains)==0) || $is_ignore_email_domains==0){
$myInsertDataArr['is_email_valid'] = $this->chkIsEmailValid($myInsertDataArr['email'], 0);
//}
}
}
}
// if($tool_version>=9){
// if(strtoupper($myInsertDataArr['is_email_valid'])=='N'){ $myInsertDataArr['email']=''; }
// }
if(!empty($myInsertDataArr['phone'])){
if($this->left($myInsertDataArr['phone'], 2)==" 1" || $this->left($myInsertDataArr['phone'], 2)=="+1"){
$myInsertDataArr['phone'] = $this->right($myInsertDataArr['phone'], 2);
}
$myInsertDataArr['phone'] = $this->formatPhone($this->cleanPhone($myInsertDataArr['phone'], false));
}
if($validate_address==1){
//validate address using USPO
$url = "https://prophog.ai/api?qry=getUSPOData&req=getHomeDetailsArr&street_line_1=". urlencode($myInsertDataArr['street']) ."&city=". urlencode($myInsertDataArr['city']) ."&state_code=". urlencode($myInsertDataArr['state']) ."&postal_code=". urlencode($myInsertDataArr['zip']);
$headers = array("api_key: wrH71keQw8Tr23KzSp3m");
$json = $this->getData($url, $headers);
if($_REQUEST['test']==1){ echo "\n".$json; }
$resArr = json_decode($json, true);
if(!empty($resArr)){
if($resArr['status']=='ok'){
$ar = $resArr['result'][0];
$myInsertDataArr['is_address_valid'] = $ar['is_address_valid'];
if(!empty($ar['is_home_vacant'])){ $myInsertDataArr['is_home_vacant'] = $ar['is_home_vacant']; }
}
}
}
// Always scrub TCPA/DNC when a phone is present (also honors verify_tcpa_dnc=1)
if(!empty($myInsertDataArr['phone']) && ($verify_tcpa_dnc==1 || !isset($_REQUEST['verify_tcpa_dnc']) || $verify_tcpa_dnc!=0)){
$myInsertDataArr = $this->getBadNumberStatus($myInsertDataArr, $myInsertDataArr['phone']);
}
$id=0; $row=array(); $sms_email=''; $mms_email=''; $is_wireless='N'; $data247_id=0;
if(!empty($myInsertDataArr['phone'])){
$qry = "qry=getPhoneLineType&phone=". urlencode($myInsertDataArr['phone']);
$ar = $this->getDetailsFromInsuranceTool($qry, 1);
if($_REQUEST['test']==1){ echo "
ar = "; print_r($ar); }
$postArr = array();
if($ar['npa']!='' && $ar['nxx']!=''){
$is_wireless = $arr['is_wireless'];
$myInsertDataArr['npa'] = $ar['npa'];
$myInsertDataArr['nxx'] = $ar['nxx'];
$myInsertDataArr['block_id'] = $ar['block_id'];
$myInsertDataArr['ltype_id'] = $ar['line_type'];
$myInsertDataArr['is_wireless'] = $ar['is_wireless'];
}
}
//$county = $this->getCounty($myInsertDataArr['city'], $myInsertDataArr['zip'], $myInsertDataArr['state']);
$county = '';
if((!empty($myInsertDataArr['city']) || !empty($myInsertDataArr['zip'])) && !empty($myInsertDataArr['state'])){
$qry = "qry=getCounty&city=". urlencode($myInsertDataArr['city']) ."&state=". urldecode($myInsertDataArr['state']) ."&zip=". urldecode($myInsertDataArr['zip']);
$ar = $this->getDetailsFromInsuranceTool($qry, 1);
if($_REQUEST['test']==1){ echo "
res2 = "; print_r($ar); }
if(!empty($ar['county_name'])){
$county = $ar['county_name'];
}
}
$myInsertDataArr['county'] = $county;
$myInsertDataArr = array_map("trim", $myInsertDataArr);
if(preg_match('/[a-zA-Z]/',$myInsertDataArr['name']) && strlen($myInsertDataArr['name'])>3 && !empty($myInsertDataArr['phone'])){
$ansArr['isCorrectListingDetailsProvided'] = 1;
$wh = "where phone='".$myInsertDataArr['phone']."'"; //name='". $this->db->realEscapeString($myInsertDataArr['name']) ."' and
//if($user_id>0){ $wh .= " and user_id=".$user_id; }else{ $wh=''; }
//$res = $this->exeQry("Select * from leads ".$wh." limit 0,1");
//if(mysqli_num_rows($res)>0){ $row = mysqli_fetch_array($res); $id = $row['id']; }
$row = $this->db->query("Select * from leads ".$wh." limit 0,1")->fetchArray();
$id = doubleval($row['id']);
if($id==0){
foreach($myInsertDataArr as $key=>$val){
if(empty($myInsertDataArr[$key])){ unset($myInsertDataArr[$key]); }
}
$ans = $this->db->insertData($myInsertDataArr, 'leads', 1); //1; //print_r($myInsertDataArr);
}else{
$updateFields = array("rating_stars", "reviews_count", "street", "city", "state", "zip", "phone", "email", "website", "profile_link", "search_terms", "listing_details", "is_wireless", "npa", "nxx", "block_id", "ltype_id", "business_score", "review_1", "review_2", "review_3", "entity_no", "business_name", "business_type", "status", "effect_date", "expiry_date", "agent_content", "is_address_valid", "is_home_vacant", "is_bad_number", "is_tcpa", "is_dnc", "is_dnc_checked", "county");
foreach($myInsertDataArr as $key=>$val){
if(!in_array($key, $updateFields) || empty($myInsertDataArr[$key]) || strtolower($myInsertDataArr[$key])==strtolower($row[$key])){ unset($myInsertDataArr[$key]); }
}
if(count($myInsertDataArr)>0){
//$this->updateData($tbl='leads', $set, $wh="where id=".$id, $noPrifix=1);
$this->db->updateDataArr($myInsertDataArr, 'leads', $wh, 1);
$row = $this->db->query("Select * from leads ".$wh." limit 0,1")->fetchArray();
}
$myInsertDataArr['name'] = $row['name'];
if(empty($myInsertDataArr['phone'])){
$myInsertDataArr['phone'] = $row['phone'];
}
if(empty($myInsertDataArr['is_wireless']) || strtoupper($myInsertDataArr['is_wireless'])=='N'){
$myInsertDataArr['is_wireless'] = $row['is_wireless'];
}
$myInsertDataArr['npa'] = $row['npa'];
$myInsertDataArr['nxx'] = $row['nxx'];
$myInsertDataArr['block_id'] = $row['block_id'];
$myInsertDataArr['ltype_id'] = $row['ltype_id'];
if(empty($myInsertDataArr['email'])){
$myInsertDataArr['email'] = $row['email'];
}
if(empty($myInsertDataArr['is_email_valid']) || strtoupper($myInsertDataArr['is_email_valid'])=='N'){
$myInsertDataArr['is_email_valid'] = $row['is_email_valid'];
}
if(empty($myInsertDataArr['is_address_valid'])){
$myInsertDataArr['is_address_valid'] = $row['is_address_valid'];
}
$ans = 2;
}
}else{ $ans=3; }
$ansArr['res'] = $ans; $ansArr['county'] = $county;
// if(strtolower($is_wireless)=='y'){
// $lead_id = doubleval($this->db->getRecordByID('id','leads',"where name='".str_ireplace("'", "''", $myInsertDataArr['name'])."' and phone='".$myInsertDataArr['phone']."'".$wh, 1));
// //$user_id = doubleval($_REQUEST['user_id']);
// file_get_contents("https://leads.prophog.ai/api?qry=updateFolderStatus&id=".$lead_id."&folder_id=".$folder_id."&user_id=".$user_id);
// }
$ansArr = array_merge($ansArr, $myInsertDataArr);
$ansArr['username'] = $username;
$ansArr['user_id'] = $user_id;
$ansArr['usr'] = $usr;
}else{ $ansArr['error'] = 'listingDetails array not found correctly!'; }
$ans = json_encode($ansArr);
echo $ans;
}
//====================================================================
function exportCSV(){
$tbl = $_REQUEST['tbl'];
if($tbl!=''){
if(stripos($tbl, 'yp_')===false && stripos($tbl, 'zillow_')===false && stripos($tbl, 'google_')===false){ $tbl = DB_PREFIX.$tbl; }
$city = trim(urldecode($_REQUEST['city']));
$state = trim(urldecode($_REQUEST['state']));
$zip = trim(urldecode($_REQUEST['zip']));
$wh='';
if(!empty($city)){$wh="city='".$city."'";}
if(!empty($state)){if($wh!=''){$wh.=" and ";} $wh.="state='".$state."'";}
if(!empty($zip)){if($wh!=''){$wh.=" and ";} $wh.="zip='".$zip."'";}
if(stripos($tbl, 'google_')!==false){
$str = doubleval($this->db->getRecordByID("concat(id, '|', user_type) as id", "uploader_bot_users", "where name like '".$_REQUEST['username']."'", 1));
list($user_id, $user_type) = explode("|", $str); $user_id = doubleval($user_id); $user_type = doubleval($user_type);
if($user_id>0 & $user_type!=1){ if($wh!=''){ $wh.=" and "; } $wh.=" user_id=".$user_id; }
}
if($wh!=''){$wh=" Where ".$wh;}
$query = "SELECT * FROM ".$tbl.$wh." ORDER BY id";
$res = $this->db->query( $query)->fetchAll();
if(count($res) > 0){
$skipArr = array('id', 'insertion_date', 'data247_id', 'use_data247_api', 'user_id', 'npa', 'nxx', 'block_id');
$delimiter = ","; $fields=array();
$filename = $tbl."_" . date('Y-m-d') . ".csv";
$f = fopen('php://memory', 'w');
$sql = "SHOW COLUMNS FROM ".$tbl;
//$result = mysqli_query($conn, $sql);
$result = $this->db->query($sql)->fetchAll();
if(file_exists('test.txt')){unlink('test.txt');}
foreach($result as $row){
$key = $row['Field'];
//file_put_contents('test.txt', "key=".$key."\n", FILE_APPEND);
if(!in_array($key, $skipArr)){
//file_put_contents('test.txt', "-Added-\n", FILE_APPEND);
$fields[] = $key;
}
}
fputcsv($f, $fields, $delimiter);
foreach($res as $row){
$myRow = array();
foreach($row as $key=>$val){
if(!in_array($key, $skipArr)){
$myRow[] = $row[$key];
}
}
fputcsv($f, $myRow, $delimiter);
}
//move back to beginning of file
fseek($f, 0);
//set headers to download file rather than displayed
header('Content-Type: text/csv');
header('Content-Disposition: attachment; filename="' . $filename . '";');
//output all remaining data on a file pointer
fpassthru($f);
}
exit;
}else{echo 0;}
}
//====================================================================
function getFilterList(){
$tbl = DB_PREFIX.'contacts'; $wh='';
$field = $_REQUEST['field'];
if(stripos($field, 'yp_')!==false){
$tbl = DB_PREFIX.'contacts';
$field = str_ireplace('yp_', '', $field);
}elseif(stripos($field, 'zl_')!==false){
$tbl = 'zillow_listings';
$field = str_ireplace('zl_', '', $field);
}elseif(stripos($field, 'gl_')!==false){
$tbl = 'leads';
$field = str_ireplace('gl_', '', $field);
// $str = doubleval($this->db->getRecordByID("concat(id, '|', user_type) as id", "uploader_bot_users", "where name like '".$_REQUEST['username']."'", 1));
// list($user_id, $user_type) = explode("|", $str);
$user_id = doubleval($_REQUEST['user_id']); $user_type = doubleval($_REQUEST['user_type']);
if($user_id>0 & $user_type!=1){ $wh.=" and user_id=".$user_id; }
}
$opt='';
if($field!='zip' && $field!='city'){
$qry = "select distinct {$field} from ".$tbl." where {$field}!='' and {$field} is not null".$wh." order by {$field}";
$res = $this->db->query($qry)->fetchAll();
if(count($res)>0){
foreach($res as $row){
$opt.='';
}
}
}
echo $opt;
}
//====================================================================
function verifyEmailDomain($email, $domains){
$ans = 0;
if(stripos($email, "@")!==false){
list($user_id, $domain) = explode('@', $email);
$arr = explode(',', $domains);
foreach($arr as $ar){
if(stripos($domain, trim($ar))!==false){
$ans = 1; break;
}
}
}
return $ans;
}
//====================================================================
function chkIsEmailValid($email='', $echo=1){
$chkIsEmailValid = 'N';
$debug = doubleval($_REQUEST['debug']);
if(empty($email)){$email = trim($_REQUEST['email']);}
if(empty($email)){ if($echo==1){ echo $chkIsEmailValid; return; }else{ return $chkIsEmailValid; } }
//$chkIsEmailValid = $this->db->getRecordByID('is_email_valid','yp_contacts',"where email='".$email."'", 1);
//if($chkIsEmailValid!='Y'){
$chkIsEmailValid = $this->db->getRecordByID('is_email_valid','leads',"where email='".$email."'", 1);
//}
if($chkIsEmailValid!='Y'){
// Include library file
require_once 'includes/VerifyEmail.class.php';
$fromEmail = 'processing@prophog2.com';
$time_start = microtime(true);
// Initialize library class
$mail = new VerifyEmail();
$mail->setConnectionTimeout(30);
$mail->setStreamTimeout(3);
$mail->setStreamTimeoutWait(0);
$mail->setAtLeastDomainIsTrue(TRUE);
$mail->Debug= ($debug==1? TRUE:FALSE);//FALSE;
$mail->Debugoutput= 'html';
// Set email address for SMTP request
$mail->setEmailFrom($fromEmail);
// Check if email is valid and exist
if($mail->check($email)){
$chkIsEmailValid='Y';
if($debug==1){echo 'Email <'.$email.'> is exist!';}
}elseif(verifyEmail::validate($email)){
$chkIsEmailValid='N';
if($debug==1){echo 'Email <'.$email.'> is valid, but not exist!';}
}else{
$chkIsEmailValid='N';
if($debug==1){echo 'Email <'.$email.'> is not valid and not exist!';}
}
$execution_time = number_format((microtime(true) - $time_start), 0);
if($debug==1){echo "
Time consumed in verification: ". $execution_time ." seconds.
";}
}
if($debug==1){ echo "IsEmailValid="; }
if($echo==1){ echo $chkIsEmailValid; return; }else{ return $chkIsEmailValid; }
}
//Google Data Extraction Functions End ===============================
//====================================================================
function getLeads(){
$result=array(); $pagination=array();
$id = trim($_REQUEST['id']);
$name = trim($_REQUEST['name']);
$phone = trim($_REQUEST['phone']);
$page = doubleval($_REQUEST['page']);
$limit = doubleval($_REQUEST['limit']);
if(empty($id) && empty($name) && empty($phone)){
$status='ERROR-106'; $result[]=array("message"=>$GLOBALS['QRY_ERRORS']['ERROR-106']);
}else{
$wh = "";
$id = doubleval($id);
if(strlen($phone)>=10){
$phone = $this->formatPhone($phone);
list($leftPhone) = explode("-", $phone);
list($npa, $nxx) = explode(") ", $leftPhone);
$npa = trim(str_ireplace("(", "", $npa));
}else{ $npa=''; $nxx=''; }
if($phone!=''){
$phone = "phone='". $phone ."'";
if(!empty($nxx)){ $phone = "nxx=".$nxx." and ".$phone; }
if(!empty($npa)){ $phone = "npa=".$npa." and ".$phone; }
}
if($id>0){ if($wh!=''){ $wh .= " and "; } $wh .= "id=".$id; }
if(!empty($name)){ if($wh!=''){ $wh .= " and "; } $wh .= "name like '%". str_ireplace("'", "''", $name) ."%'"; }
if(!empty($phone)){ if($wh!=''){ $wh .= " and "; } $wh .= $phone; }
if($wh!=''){ $wh = "Where ".$wh; }
if($page<=0){$page=1;}
if($limit<=0){$limit=10;}elseif($limit>100){$limit=100;}
$start = ($page-1) * $limit;
$qry = "select * from ".DB_PREFIX."leads ".$wh." order by name limit ".$start.",".$limit;
$res = $this->db->query($qry)->fetchAll();
if(count($res)<=0){
$status='ERROR-107'; $result[]=array("message"=>$GLOBALS['QRY_ERRORS']['ERROR-107']);
}else{
$status='OK';
foreach($res as $row){
$tbl = "emp_history";
$row = $this->getOthers($tbl, $row);
$tbl = "fed_registration";
$row = $this->getOthers($tbl, $row);
$result[] = $row;
}
}
$pagination = $this->getPagination('leads', $wh, $page, $limit);
}
$res = json_encode($result);
$pag = json_encode($pagination);
return '{"response":{"status":"'.$status.'","results":'.$res.',"pagination":'.$pag.'},"qry":"getLeads"}';
}
//====================================================================
function getDetailsFromInsuranceTool($qry, $needArr=0, $echo=0){
//Get details from Insurance Tool:
$url = $this->insurance_api_link."?".$qry;
$headers = array("api_key: ".$this->insurance_api_key);
if($_REQUEST['test']==1){ echo "
url = $url; headers = ". print_r($headers, true); }
$curl = curl_init();
curl_setopt($curl, CURLOPT_URL, $url);
curl_setopt($curl, CURLOPT_RETURNTRANSFER, true);
curl_setopt($curl, CURLOPT_HTTP_VERSION, CURL_HTTP_VERSION_1_1);
curl_setopt($curl, CURLOPT_CUSTOMREQUEST, 'GET');
curl_setopt($curl, CURLOPT_HTTPHEADER, $headers);
$response = curl_exec($curl);
if(curl_errno($curl)>0){
$response = '{"status":"error","cURL Error":"' . curl_error($curl) . '"}';
}
curl_close($curl);
$response = json_decode($response,TRUE);
if($response['status']=='ok'){ $response = $response['result']; }
if($needArr==0){ $response = json_encode($response); }
if($echo==1){ if($needArr==1){ print_r($response); }else{ echo $response; } return; }else{ return $response; }
}
//====================================================================
private function getBadNumberStatus($postArr, $phone){
$ph = $this->cleanPhone($phone, false);
if($ph!=''){
$qry = "qry=isBadNumber&phone=". urlencode($ph);
$phoneArr = $this->getDetailsFromInsuranceTool($qry, 1);
if($_REQUEST['test']==1){ echo "
res2 = "; print_r($phoneArr); }
if(is_array($phoneArr) && array_key_exists("is_bad_number", $phoneArr)){
$is_tcpa = 0; $is_dnc = 0;
if(array_key_exists("status", $phoneArr) && $phoneArr['status']!='null' && !is_null($phoneArr['status'])){
if(stripos($phoneArr['status'], "tcpa")!==false){ $is_tcpa = 1; }
if(stripos($phoneArr['status'], "dnc")!==false){ $is_dnc = 1; }
}
$postArr['is_tcpa'] = $is_tcpa;
$postArr['is_dnc'] = $is_dnc;
// Rule: bad only when TCPA or DNC
$postArr['is_bad_number'] = ($is_tcpa==1 || $is_dnc==1) ? 1 : 0;
$postArr['is_dnc_checked'] = 1; // scanned (clean or flagged)
}
}
return $postArr;
}
//====================================================================
private function getBadNumberLabel($is_tcpa, $is_dnc){
if($is_tcpa==1 && $is_dnc==1){ return 'TCPA and DNC'; }
if($is_tcpa==1){ return 'TCPA'; }
if($is_dnc==1){ return 'DNC'; }
return 'Clean';
}
//====================================================================
function checkLeadBadNumber(){
$id = doubleval($_REQUEST['id']);
$ans = array("status"=>"error", "msg"=>"Lead not found", "id"=>$id);
if($id<=0){ $ans['msg'] = "Lead id required"; return json_encode($ans); }
$row = $this->db->query("SELECT id, phone, is_bad_number, is_tcpa, is_dnc FROM leads WHERE id=".$id." LIMIT 1")->fetchArray();
if(empty($row['id'])){ return json_encode($ans); }
$postArr = array("is_bad_number"=>0, "is_tcpa"=>0, "is_dnc"=>0);
$postArr = $this->getBadNumberStatus($postArr, $row['phone']);
if(!array_key_exists("is_bad_number", $postArr)){
$ans['msg'] = "Unable to check phone status";
$ans['phone'] = $row['phone'];
return json_encode($ans);
}
$this->db->updateDataArr($postArr, 'leads', "WHERE id=".$id, 1);
$ans['status'] = "ok";
$ans['msg'] = "Updated";
$ans['phone'] = $row['phone'];
$ans['is_bad_number'] = doubleval($postArr['is_bad_number']);
$ans['is_tcpa'] = doubleval($postArr['is_tcpa']);
$ans['is_dnc'] = doubleval($postArr['is_dnc']);
$ans['label'] = $this->getBadNumberLabel($ans['is_tcpa'], $ans['is_dnc']);
return json_encode($ans);
}
//====================================================================
function checkPageBadNumbers(){
$ids = trim($_REQUEST['ids'] ?? '');
$ids = preg_replace('/[^0-9,]/', '', $ids);
$ans = array("status"=>"error", "msg"=>"No ids provided", "checked"=>0, "results"=>array());
if($ids===''){ return json_encode($ans); }
$idArr = array_values(array_filter(array_map('doubleval', explode(',', $ids))));
if(empty($idArr)){ return json_encode($ans); }
$idArr = array_slice($idArr, 0, 50); // page safety cap
$results = array(); $checked = 0;
foreach($idArr as $id){
$_REQUEST['id'] = $id;
$row = json_decode($this->checkLeadBadNumber(), true);
if($row['status']=='ok'){ $checked++; }
$results[] = $row;
}
$ans['status'] = "ok";
$ans['msg'] = "Checked ".$checked." lead(s)";
$ans['checked'] = $checked;
$ans['results'] = $results;
return json_encode($ans);
}
//====================================================================
function updateBadNumber(){
// CLI / cron bulk scrub: php index.php qry=updateBadNumber id=0 limit=25
// only_unscanned=1 (default): skip phones already scanned (is_dnc_checked=1)
$tbl = "leads"; $x = 0; $skipped = 0;
$id = doubleval($_REQUEST['id']);
$limit = doubleval($_REQUEST['limit']);
if($limit<=0){ $limit = 25; }
if($limit>100){ $limit = 100; }
$pause = doubleval($_REQUEST['pause']);
if($pause<=0){ $pause = 2; }
$timeout = $pause * 1000;
$sleep_ms = doubleval($_REQUEST['sleep_ms'] ?? 500);
if($sleep_ms<0){ $sleep_ms = 0; }
if($sleep_ms>5000){ $sleep_ms = 5000; }
$only_unscanned = isset($_REQUEST['only_unscanned']) ? doubleval($_REQUEST['only_unscanned']) : 1;
$cli = (php_sapi_name()==='cli');
$wh = "phone!='' AND phone IS NOT NULL AND id>".$id;
// Skip phones already scanned (is_dnc_checked=1)
if($only_unscanned==1){
$wh .= " AND COALESCE(is_dnc_checked,0)=0";
}
$qry = "SELECT MIN(id) as id, phone FROM ".$tbl." WHERE ".$wh." GROUP BY phone ORDER BY id ASC LIMIT 0,".intval($limit);
$res = $this->db->query($qry)->fetchAll();
if(count($res)>0){
foreach($res as $row){
$postArr = array("is_bad_number"=>0, "is_tcpa"=>0, "is_dnc"=>0);
$postArr = $this->getBadNumberStatus($postArr, $row['phone']);
if(array_key_exists("is_bad_number", $postArr)){
$x += $this->db->updateDataArr($postArr, $tbl, "WHERE phone='".$this->db->realEscapeString($row['phone'])."'", 1);
if($this->db->error_msg!=''){ echo "
". $this->db->error_msg; }
}else{ echo "
not updated:- ". json_encode($row); $skipped++; }
$id = $row['id'];
if($cli && $sleep_ms>0){ usleep(intval($sleep_ms) * 1000); }
}
echo ($cli?"\n":"
")."Total records found: ". count($res) ."; Total phones updated: ". $x ."; skipped=".$skipped."; last_id=".$id.($cli?"\n":"
");
if(!$cli){
echo "Refreshing in ".$pause." seconds!
";
}else{
return "done; last_id=".$id."; updated=".$x."; skipped=".$skipped;
}
}else{
echo ($cli?"\n":"
")."There is no any data available!\n";
if($cli){ return "done; no more data; last_id=".$id; }
}
echo ($cli?"\n":"")."done!\n";
return '';
}
//====================================================================
function updatePhoneLineType(){
$tbl = DB_PREFIX."leads"; $x = 0;
$qry = "select id,phone from ".$tbl." where phone!='' and npa=0 and insertion_date>='2026-05-19 00:00:00' order by id limit 0,3000";
$res = $this->db->query($qry)->fetchAll();
if($_REQUEST['test']==1){ echo "
qry = ". $qry ."; found = ". count($res); }
foreach($res as $row){
$ph = $this->cleanPhone($row['phone'], false);
if($ph!=''){
$qry = "qry=getPhoneLineType&phone=". urlencode($ph);
$ar = $this->getDetailsFromInsuranceTool($qry, 1);
if($_REQUEST['test']==1){ echo "
ar = "; print_r($ar); }
$postArr = array();
if($ar['npa']!='' && $ar['nxx']!=''){
$postArr['npa'] = $ar['npa'];
$postArr['nxx'] = $ar['nxx'];
$postArr['block_id'] = $ar['block_id'];
$postArr['ltype_id'] = $ar['line_type'];
$postArr['is_wireless'] = $ar['is_wireless'];
$x += $this->db->updateDataArr($postArr, $tbl, "where id=".$row['id'], 1);
if($this->db->error_msg!=''){ echo "
". print_r($ar, true) ."
". $this->db->error_msg; }
}else{ echo "
". json_encode($ar); }
}else{ echo "
". json_encode($row); }
}
echo "
Total records found: ". count($res) ."; Total records updated: ". $x ."; done!";
}
//====================================================================
function updateCountyNames(){
$tbl = DB_PREFIX."emp_history"; $x = 0;
$id = doubleval($_REQUEST['id']);
$pause = doubleval($_REQUEST['pause']);
if($pause<=0){ $pause=5; } //pause is required in seconds
$timeout = $pause * 1000;
$max_id = doubleval($this->db->getRecordByID("max(id) as id", $tbl, " order by id desc limit 0,1", 1));
if($max_id<=0){ $max_id=17261564; }
$qry = "select * from ".$tbl." where id>".$id." and length(state)=2 and county IS NULL and not city is null and not state is null ORDER BY `id` ASC limit 0,1000";// and not zip is null
$res = $this->db->query($qry)->fetchAll();
if(count($res)>0){
foreach($res as $row){
$postArr = array();
$city = trim($row['city']);
$zip = trim($row['zip']);
$state = trim($row['state']);
if((!empty($city) || !empty($zip)) && !empty($state)){
$qry = "qry=getCounty&city=". urlencode($city) ."&state=". urldecode($state) ."&zip=". urldecode($zip);
$ar = $this->getDetailsFromInsuranceTool($qry, 1);
if($_REQUEST['test']==1){ echo "
res2 = "; print_r($ar); }
if(!empty($ar['county_name'])){
$postArr['county'] = $ar['county_name'];
$x += $this->db->updateDataArr($postArr, $tbl, "where city='".$this->db->realEscapeString($city)."' and state='".$state."' and zip='".$zip."'", 1);
if($this->db->error_msg!=''){ echo "
". print_r($res2, true) ."
". $this->db->error_msg; }
}else{ echo "
". json_encode($ar); }
}else{ echo "
". json_encode($row); }
$id = $row['id'];
}
echo "
Total records found: ". count($res) ."; Total records updated: ". $x ."; done!";
}else{ echo "
There is no any data available!\n"; goto Finish; }
echo "; id=".$id."\n
";
if($id<$max_id){ echo "Refreshing in 5 seconds!
"; }// && $x>0
Finish:
echo "Done\n";
}
//====================================================================
function updateFirstName(){
$tbl = DB_PREFIX."leads"; $x = 0;
$id = doubleval($_REQUEST['id']);
$pause = doubleval($_REQUEST['pause']);
if($pause<=0){ $pause=5; } //pause is required in seconds
$timeout = $pause * 1000;
$max_id = doubleval($this->db->getRecordByID("max(id) as id", $tbl, " order by id desc limit 0,1", 1));
if($max_id<=0){ $max_id=17261564; }
$qry = "select * from ".$tbl." where id>".$id." and first_name IS NULL ORDER BY `id` ASC limit 0,10000";
$res = $this->db->query($qry)->fetchAll();
if(count($res)>0){
foreach($res as $row){
$postArr = array();
$id = $row['id'];
if($this->stripos_arr($row['name'], $this->skipComps)){
$postArr['first_name'] = $row['name'];
}else{
$name = $this->getFullName($row['name']);
$postArr['first_name'] = $name['firstName'];
$postArr['last_name'] = $name['lastName'];
}
if(!empty($postArr)){
$x += $this->db->updateDataArr($postArr, $tbl, "where id=".$id, 1);
if($this->db->error_msg!=''){ echo "
". print_r($postArr, true) ."
". $this->db->error_msg; }
}else{ echo "
". json_encode($row); }
}
echo "
Total records found: ". count($res) ."; Total records updated: ". $x ."; done!";
}else{ echo "
There is no any data available!\n"; goto Finish; }
echo "; id=".$id."\n
";
if($id<$max_id){ echo "Refreshing in 5 seconds!
"; }// && $x>0
Finish:
echo "Done\n";
}
//====================================================================
function updateGender(){
$tbl = DB_PREFIX."leads"; $x = 0;
$id = doubleval($_REQUEST['id']);
$pause = doubleval($_REQUEST['pause']);
$test = doubleval($_REQUEST['test']);
if($pause<=0){ $pause=5; } //pause is required in seconds
$timeout = $pause * 1000;
$max_id = doubleval($this->db->getRecordByID("max(id) as id", $tbl, " order by id desc limit 0,1", 1));
if($max_id<=0){ $max_id=17261564; }
$qry = "select * from ".$tbl." where id>".$id." and NOT first_name LIKE '% %' and gender='U' ORDER BY `id` ASC limit 0,1000";
$res = $this->db->query($qry)->fetchAll();
if(count($res)>0){
foreach($res as $row){
$postArr = array();
$id = $row['id'];
$first_name = trim($row['first_name']);
if($this->stripos_arr($row['name'], $this->skipComps)==false){
if(!empty($first_name)){
$qry = "qry=getGender&first_name=". urlencode($first_name);
$ar = $this->getDetailsFromInsuranceTool($qry, 1);
if($_REQUEST['test']==1){ echo "
ar = "; print_r($ar); }
if(!empty($ar['gender']) && $ar['gender']!='U'){ $postArr['gender'] = $ar['gender']; }
}
}
if(!empty($postArr)){
$x += $this->db->updateDataArr($postArr, $tbl, "where id=".$id, 1);
if($this->db->error_msg!=''){ echo "
". print_r($postArr, true) ."
". $this->db->error_msg; }
}else{ echo "
". json_encode($row); }
}
echo "
Total records found: ". count($res) ."; Total records updated: ". $x ."; done!";
}else{ echo "
There is no any data available!\n"; goto Finish; }
echo "; id=".$id."\n
";
if($id<$max_id){ echo "Refreshing in 5 seconds!
"; }// && $x>0
Finish:
echo "Done\n";
//This below said query will update the gender information directly from phpMyAdmin:
//UPDATE hlguru_google_db.leads as ld SET ld.gender=IFNULL((SELECT IFNULL(gender, 'U') as gender FROM hlguru_leads_db.ar_gender as gr where gr.first_name=ld.first_name), 'U') where ld.gender = 'U' and NOT ld.first_name LIKE '% %' order by ld.id limit 1000;
}
//====================================================================
function updateSpanishNames(){
$tbl = DB_PREFIX."leads"; $x = 0;
$id = doubleval($_REQUEST['id']);
$pause = doubleval($_REQUEST['pause']);
$test = doubleval($_REQUEST['test']);
if($pause<=0){ $pause=5; } //pause is required in seconds
$timeout = $pause * 1000;
$max_id = doubleval($this->db->getRecordByID("max(id) as id", $tbl, " order by id desc limit 0,1", 1));
if($max_id<=0){ $max_id=17261564; }
$qry = "select * from ".$tbl." where id>".$id." and NOT first_name LIKE '% %' and is_hispanic_or_latino IS NULL ORDER BY `id` ASC limit 0,1000";
$res = $this->db->query($qry)->fetchAll();
if(count($res)>0){
foreach($res as $row){
$postArr = array();
$id = $row['id'];
$first_name = trim($row['first_name']);
if($this->stripos_arr($row['name'], $this->skipComps)==false){
if(!empty($first_name)){
$qry = "qry=getSpanishName&first_name=". urlencode($first_name);
$ar = $this->getDetailsFromInsuranceTool($qry, 1);
if($_REQUEST['test']==1){ echo "
ar = "; print_r($ar); }
if(!empty($ar['is_hispanic_or_latino'])){ $postArr['is_hispanic_or_latino'] = $ar['is_hispanic_or_latino']; }
if(!empty($ar['nationality'])){ $postArr['nationality'] = $ar['nationality']; }
}
}
if(!empty($postArr)){
$x += $this->db->updateDataArr($postArr, $tbl, "where id=".$id, 1);
if($this->db->error_msg!=''){ echo "
". print_r($postArr, true) ."
". $this->db->error_msg; }
}else{ echo "
". json_encode($row); }
}
echo "
Total records found: ". count($res) ."; Total records updated: ". $x ."; done!";
}else{ echo "
There is no any data available!\n"; goto Finish; }
echo "; id=".$id."\n
";
if($id<$max_id){ echo "Refreshing in 5 seconds!
"; }// && $x>0
Finish:
echo "Done\n";
//This below said query will update the gender information directly from phpMyAdmin:
//UPDATE hlguru_google_db.leads as ld SET ld.gender=IFNULL((SELECT IFNULL(gender, 'U') as gender FROM hlguru_leads_db.ar_gender as gr where gr.first_name=ld.first_name), 'U') where ld.gender = 'U' and NOT ld.first_name LIKE '% %' order by ld.id limit 1000;
}
//====================================================================
function get_pagination($current_page, $main_limit, $total, $root_func='', $skip=0){
$skip = doubleval($skip); //if skip=1 then it will skip the pagination click via main javascript pagination function.
$range = 5; if($current_page==0){ $current_page=1; }
if(strlen($current_page)>1){ $range = 4; }
elseif(strlen($current_page)>2){ $range = 3; }
elseif(strlen($current_page)>3){ $range = 2; }
elseif(strlen($current_page)>4){ $range = 1; }
$limit = $current_page*$main_limit;
$totPages = 0;
if($main_limit>0){ $totPages = ceil($total/$main_limit); }
$nextPage=($current_page+1>$totPages ? null : $current_page+1);
$prevPage=($current_page-1<=0 ? null : $current_page-1);
$start = (($current_page-1 ) * $main_limit) + 1;
if($total==0){ $start=0; }
if($limit>$total){$end = $total;}else{$end = $limit;}
if($total==1){ $start=1; $end=1; }
$pagination =
'
';
}
//====================================================================
function get_pagination_new($current_page, $main_limit, $total, $root_func='', $skip=0){
$skip = doubleval($skip); //if skip=1 then it will skip the pagination click via main javascript pagination function.
$range = 5; if($current_page==0){ $current_page=1; }
if(strlen($current_page)>1){ $range = 4; }
elseif(strlen($current_page)>2){ $range = 3; }
elseif(strlen($current_page)>3){ $range = 2; }
elseif(strlen($current_page)>4){ $range = 1; }
$limit = $current_page*$main_limit;
$totPages = 0;
if($main_limit>0){ $totPages = ceil($total/$main_limit); }
$nextPage=($current_page+1>$totPages ? null : $current_page+1);
$prevPage=($current_page-1<=0 ? null : $current_page-1);
$start = (($current_page-1 ) * $main_limit) + 1;
if($total==0){ $start=0; }
if($limit>$total){$end = $total;}else{$end = $limit;}
if($total==1){ $start=1; $end=1; }
$pagination =
'
';
}
//====================================================================
function generateCSV($list, $file_name='leads'){
//echo count($list); die();
header('Content-Type: text/csv; charset=utf-8');
header('Content-Disposition: attachment; filename='.$file_name.'.csv');
header('Cache-Control: must-revalidate, post-check=0, pre-check=0, private=false');
header('Content-Description: File Transfer');
header('Content-Transfer-Encoding: binary');
header('Pragma: public');
header('Expires: 0');
$output = fopen('php://output', 'w');
if (count($list) > 0) {
//fputcsv($output, array('No', 'First Name', 'Last Name', 'Email'));
fputcsv($output, array_keys($list[0]));
foreach ($list as $row) {
fputcsv($output, $row);
}
}
fclose($output);
}
//====================================================================
function generateTXT($data, $file_name='leads'){
//echo count($list); die();
header('Content-Type: text/csv; charset=utf-8');
header('Content-Disposition: attachment; filename='.$file_name.'.txt');
header('Cache-Control: must-revalidate, post-check=0, pre-check=0, private=false');
header('Content-Description: File Transfer');
header('Content-Transfer-Encoding: binary');
header('Pragma: public');
header('Expires: 0');
$output = fopen('php://output', 'w');
fwrite($output, $data);
fclose($output);
}
//====================================================================
function getClientIP() {
if (getenv("HTTP_CLIENT_IP") && strcasecmp(getenv("HTTP_CLIENT_IP"), "unknown")) {
$ip = getenv("HTTP_CLIENT_IP");
} else if (getenv("HTTP_X_FORWARDED_FOR") && strcasecmp(getenv("HTTP_X_FORWARDED_FOR"), "unknown")) {
$ip = getenv("HTTP_X_FORWARDED_FOR");
} else if (getenv("REMOTE_ADDR") && strcasecmp(getenv("REMOTE_ADDR"), "unknown")) {
$ip = getenv("REMOTE_ADDR");
} else if (isset($_SERVER['REMOTE_ADDR']) && $_SERVER['REMOTE_ADDR'] && strcasecmp($_SERVER['REMOTE_ADDR'], "unknown")) {
$ip = $_SERVER['REMOTE_ADDR'];
} else {
$ip = "0.0.0.0";//"unknown";
}
return($ip);
}
//====================================================================
private function get_pagination_arr($current_page, $page_limit, $tbl='nad', $wh=''){
$current_page = doubleval($current_page); if($current_page<=0){ $current_page=1; }
$page_limit = doubleval($page_limit); if($page_limit<=0){ $page_limit=PAGE_LIMIT; }
$total = doubleval($this->db->getRecordByID('count(*)', $tbl, $wh));
$limit = $current_page*$page_limit;
$totPages = 0;
if($page_limit>0){ $totPages = ceil($total/$page_limit); }
$nextPage=($current_page+1>$totPages ? null : $current_page+1);
$prevPage=($current_page-1<=0 ? null : $current_page-1);
$start = (($current_page-1 ) * $page_limit) + 1;
if($total==0){ $start=0; }
if($limit>$total){$end = $total;}else{$end = $limit;}
$showing = 'Showing '.$start.' to '.$end.' of '.$total.' entries.';
return array('showing'=>$showing, 'total_records'=>$total, 'page'=>$current_page, 'page_limit'=>$page_limit, 'prev_page'=>$prevPage, 'next_page'=>$nextPage, 'last_page'=>$totPages);
}
//====================================================================
private function whOpt($wh){ if($wh!=''){ $wh.=" and "; }else{ $wh="Where "; } return $wh; }
//====================================================================
private function getIsBadNumberCondition($alias=''){
$is_bad_number_wh = '';
$pfx = ($alias!='') ? $alias.'.' : '';
if(isset($_REQUEST['is_bad_number'])){
$is_bad_number = doubleval($_REQUEST['is_bad_number']);
if($is_bad_number<=0){ $is_bad_number_wh = $pfx."is_tcpa = 0"; }
elseif($is_bad_number==1){ $is_bad_number_wh = $pfx."is_tcpa = 1"; }
elseif($is_bad_number==2){ $is_bad_number_wh = $pfx."is_dnc = 1"; }
elseif($is_bad_number==3){ $is_bad_number_wh = $pfx."is_tcpa = 0"; }
elseif($is_bad_number==4){ $is_bad_number_wh = $pfx."is_dnc = 0"; }
elseif($is_bad_number==5){ $is_bad_number_wh = "(".$pfx."is_tcpa = 0 and ".$pfx."is_dnc = 0)"; }
}else{ $is_bad_number_wh = $pfx."is_tcpa = 0"; }
return $is_bad_number_wh;
}
//Business Owner programming started
//====================================================================
private function getLastUsedOn($user_id=0){
$user_id = doubleval($user_id);
if($user_id<=0){ $user_id = doubleval($_REQUEST['user_id']); }
$wh = "where user_id=". $user_id;
//$qry = "SELECT MAX(sent_on) as highest_date FROM (SELECT MAX(sent_on) as sent_on FROM ".DB_PREFIX."leads_sent_gl ". $wh ." UNION SELECT MAX(sent_on) as sent_on FROM ".DB_PREFIX."leads_sent_csv_gl ". $wh ." UNION SELECT MAX(sent_on) as sent_on FROM ".DB_PREFIX."leads_tracking_gl ". $wh .") as all_dates";
//$qry = "SELECT GREATEST(IFNULL((SELECT MAX(sent_on) FROM ".DB_PREFIX."leads_sent_gl ". $wh ."), '0000-00-00 00:00:00'), IFNULL((SELECT MAX(sent_on) FROM ".DB_PREFIX."leads_sent_csv_gl ". $wh ."), '0000-00-00 00:00:00'), IFNULL((SELECT MAX(sent_on) FROM ".DB_PREFIX."leads_tracking_gl ". $wh ."), '0000-00-00 00:00:00')) AS highest_date";
$qry = "SELECT IFNULL(last_activity, '0000-00-00 00:00:00') AS highest_date FROM ".DB_PREFIX."user_last_activity ". $wh;
$res = $this->db->query($qry)->fetchArray();
return $res['highest_date'];
}
//====================================================================
private function getUsedCredits($wh=""){
if($wh==""){
$arr = json_decode(urldecode($_REQUEST['user_wh']), true); $wh = $arr['wh'];
}
//$qry = "SELECT SUM(credits) as credits FROM (SELECT COUNT(*) as credits FROM ".DB_PREFIX."leads_sent_gl ". $wh ." UNION SELECT COUNT(*) as credits FROM ".DB_PREFIX."leads_sent_csv_gl ". $wh ." UNION SELECT COUNT(*) as credits FROM ".DB_PREFIX."leads_tracking_gl ". $wh .") as all_credits";
$qry = "SELECT ((SELECT COUNT(*) as credits FROM ".DB_PREFIX."leads_sent_gl ". $wh .") + (SELECT COUNT(*) as credits FROM ".DB_PREFIX."leads_sent_csv_gl ". $wh .") + (SELECT COUNT(*) as credits FROM ".DB_PREFIX."leads_tracking_gl ". $wh .")) as credits";
$res = $this->db->query($qry)->fetchArray();
return doubleval($res['credits']);
}
//====================================================================
function checkUsageLimit($user_wh=array()){
if(empty($user_wh)){ $user_wh = json_decode(urldecode($_REQUEST['user_wh']), true); }
$last_used_on = $this->getLastUsedOn($user_wh['user_id']);
$wh = $user_wh['wh'];
$used = $this->getUsedCredits($wh);
$user_wh = array('credit_used'=>$used, 'last_used_on'=>$last_used_on);
return json_encode($user_wh);
}
//====================================================================
function resetCredits(){
$arr = json_decode(urldecode($_REQUEST['user_wh']), true); $wh = $arr['wh'];
$updateData = array("is_suppressed"=>1);
$this->db->updateDataArr($updateData, "leads_sent_gl", $wh);
$this->db->updateDataArr($updateData, "leads_sent_csv_gl", $wh);
$this->db->updateDataArr($updateData, "leads_tracking_gl", $wh);
return 1;
}
//====================================================================
function getCall(){
return $this->saveTracking(1);
}
//====================================================================
function getText(){
return $this->saveTracking(2);
}
//====================================================================
function getEmail(){
return $this->saveTracking(3);
}
//====================================================================
private function saveTracking($track_id){
$user_id = doubleval($_REQUEST['user_id']);
$usage_left = doubleval($_REQUEST['usage_left']);
if($usage_left<=0){ return 0; }else{
$lead_id = doubleval($_REQUEST['lead_id']);
$opt_leads = doubleval($_REQUEST['opt_leads']);
$val = trim($_REQUEST['val']);
if($val=='undefined' && $track_id<=3){
$fld = "phone";
if($track_id==3){ $fld = "email"; }
if($opt_leads==8){
$val = $this->db->getRecordByID($fld, "leads", "where id=".$lead_id, 1);
}
}
$insData['lead_id'] = $lead_id;
$insData['track_val'] = $val;
$insData['track_id'] = $track_id;
$insData['user_id'] = $user_id;
$insData['client_ip'] = $this->getClientIP();
$insData['opt_leads'] = $opt_leads;
$this->db->insertData($insData, 'leads_tracking_gl');
return 1;
}
}
//====================================================================
private function urldecodeRequest(){
foreach($_REQUEST as $key=>$val){
if(!is_array($val)){
$_REQUEST[$key] = trim(urldecode($val));
}
}
//$_REQUEST = array_map("trim", $_REQUEST);
}
// ===================================================================
// Helper for Numeric Conditions
// ===================================================================
private function addNumericCondition(&$conditions, $column, $operator, $value) {
$value = trim($value ?? '');
if ($value === '') return;
$op = in_array($operator, ['<', '>', '<=', '>=', '=', '!=']) ? $operator : '<';
$conditions[] = "$column $op " . doubleval($value);
}
// Optional: Extract your email logic into clean helpers
// ===================================================================
private function buildEmailCondition() {
$email_opt = trim($_REQUEST['email_opt'] ?? '');
$email = trim($_REQUEST['email'] ?? '');
if ($email === '') {
return '';
}
// Remove trailing comma if present
if (substr($email, -1) === ',') {
$email = substr($email, 0, -1);
}
$email_arr = explode(',', $email);
$email_arr = array_map('trim', $email_arr);
$email_arr = array_filter($email_arr); // remove empty values
if (empty($email_arr)) {
return '';
}
if ($email_opt === 'exclude') {
$email_opt = "Like '%{str}%@%'";
}
list($start, $end) = explode('{str}', $email_opt);
$parts = [];
foreach ($email_arr as $em) {
$escaped = $this->db->realEscapeString($em);
$parts[] = "email " . $start . $escaped . $end;
}
return "(" . implode(" OR ", $parts) . ")";
}
// ===================================================================
private function buildExcludeEmailCondition() {
$exclude_email_opt = trim($_REQUEST['exclude_email_opt'] ?? '');
$exclude_email = trim($_REQUEST['exclude_email'] ?? '');
if ($exclude_email === '') {
return '';
}
// Remove trailing comma
if (substr($exclude_email, -1) === ',') {
$exclude_email = substr($exclude_email, 0, -1);
}
$email_arr = explode(',', $exclude_email);
$email_arr = array_map('trim', $email_arr);
$email_arr = array_filter($email_arr);
if (empty($email_arr)) {
return '';
}
list($start, $end) = explode('{str}', $exclude_email_opt);
// Convert Like/= to NOT LIKE / !=
$start = str_ireplace(['Like', '='], ['NOT LIKE', '!='], $start);
$parts = [];
foreach ($email_arr as $em) {
$escaped = $this->db->realEscapeString($em);
$parts[] = "email " . $start . $escaped . $end;
}
return "(" . implode(" AND ", $parts) . ")";
}
//====================================================================
private function boHasSearchTermsFilter() {
$search_terms_input = $_REQUEST['search_terms'] ?? '';
if (is_array($search_terms_input)) {
foreach ($search_terms_input as $item) {
if (is_array($item)) {
foreach ($item as $nested) {
if (trim((string)$nested) !== '') { return true; }
}
} elseif (trim((string)$item) !== '') {
return true;
}
}
return false;
}
return trim((string)$search_terms_input) !== '';
}
//====================================================================
private function boRequiresScopedFilters() {
$opt_leads = doubleval($_REQUEST['opt_leads'] ?? 0);
return ($opt_leads != 3 && $opt_leads != 0);
}
//====================================================================
private function boRejectUnscopedSearch() {
if (!$this->boRequiresScopedFilters()) { return ''; }
// Exact/partial phone from Also-in deep links is selective enough
$phone = trim((string)($_REQUEST['phone'] ?? ''));
if ($phone !== '') { return ''; }
$st_code = trim($_REQUEST['st_code'] ?? '');
if ($st_code === '' || !$this->boHasSearchTermsFilter()) {
return json_encode(array(
'result' => array(array(
'error' => 'Select a business type and a specific state (not All States) before searching.'
)),
'seconds_taken' => '0.00'
));
}
return '';
}
//====================================================================
/** Cross-DB phone flags on google leads (Also in Insurance / Realtor / …). */
private function ensureCrossDbFlagColumns(){
static $done = false;
if($done){ return; }
$done = true;
$cols = array(
'is_insurance' => "CHAR(1) NOT NULL DEFAULT ''",
'is_agency' => "CHAR(1) NOT NULL DEFAULT ''",
'is_realtor' => "CHAR(1) NOT NULL DEFAULT ''",
'is_mortgage' => "CHAR(1) NOT NULL DEFAULT ''",
'is_ca_sales' => "CHAR(1) NOT NULL DEFAULT ''",
);
foreach($cols as $name => $def){
$chk = $this->db->query("SHOW COLUMNS FROM leads LIKE '".$name."'")->fetchArray();
if(empty($chk)){
$this->db->query("ALTER TABLE leads ADD COLUMN `".$name."` ".$def);
}
}
}
//====================================================================
private function getBusinessOwnerWh() {
$this->ensureCrossDbFlagColumns();
$opt_leads = doubleval($_REQUEST['opt_leads']);
$conditions = [];
$user_id = doubleval($_REQUEST['user_id']);
$is_website = trim($_REQUEST['is_website']);
// ====================== FOLDER MODE ======================
if ($opt_leads == 3) {
$conditions[] = "lds.id > 0";
$folder_id = doubleval($_REQUEST['folder_id'] ?? 0);
if ($folder_id > 0) {
$conditions[] = "lds.folder_id = " . $folder_id;
}
}
// ====================== HIGH SELECTIVITY FILTERS FIRST ======================
// Also-in other databases (OR)
$crossOrs = array();
foreach(array('is_insurance','is_agency','is_realtor','is_mortgage','is_ca_sales') as $cf){
$v = strtoupper(trim((string)($_REQUEST[$cf] ?? '')));
if($v === 'Y' || $v === '1'){ $crossOrs[] = "gl.`{$cf}`='Y'"; }
}
if(!empty($crossOrs)){
$conditions[] = '('.implode(' OR ', $crossOrs).')';
}
// Date Range (skip on phone / Also-in deep links)
$phoneLookup = trim((string)($_REQUEST['phone'] ?? '')) !== '' || !empty($crossOrs);
$insertion_date_from = trim($_REQUEST['insertion_date_from'] ?? '');
$insertion_date_to = trim($_REQUEST['insertion_date_to'] ?? '');
if (!$phoneLookup && $insertion_date_from && $insertion_date_to) {
$conditions[] = "gl.insertion_date BETWEEN '" .
$this->db->realEscapeString($insertion_date_from) . "' AND '" .
$this->db->realEscapeString($insertion_date_to) . "'";
}
// State
$st_code = trim($_REQUEST['st_code'] ?? '');
if ($st_code !== '') {
$conditions[] = "gl.state = '" . $this->db->realEscapeString($st_code) . "'";
}
// County
if (!empty($_REQUEST['county'])) {
$counties = is_array($_REQUEST['county'])
? $_REQUEST['county']
: explode(',', $_REQUEST['county']);
$counties = array_values(array_filter(array_map('trim', $counties), function ($c) {
return $c !== '';
}));
if (!empty($counties)) {
$conditions[] = "gl.county IN (" . $this->escapeArrString($counties) . ")";
}
}
// Zip Code
$zip_code = trim($_REQUEST['zip_code'] ?? '');
if ($zip_code !== '') {
$conditions[] = "gl.zip = '" . $this->db->realEscapeString($zip_code) . "'";
}
// Business Score
$business_score = trim($_REQUEST['business_score'] ?? '');
if ($business_score !== '') {
$conditions[] = "gl.business_score = '" . $this->db->realEscapeString($business_score) . "'";
}
// Numeric Filters
$this->addNumericCondition($conditions, 'gl.reviews_count',
$_REQUEST['reviews_count_opt1'] ?? '', $_REQUEST['reviews_count1'] ?? '');
$this->addNumericCondition($conditions, 'gl.reviews_count',
$_REQUEST['reviews_count_opt2'] ?? '', $_REQUEST['reviews_count2'] ?? '');
$this->addNumericCondition($conditions, 'gl.rating_stars',
$_REQUEST['rating_stars_opt1'] ?? '', $_REQUEST['rating_stars1'] ?? '');
$this->addNumericCondition($conditions, 'gl.rating_stars',
$_REQUEST['rating_stars_opt2'] ?? '', $_REQUEST['rating_stars2'] ?? '');
// ====================== TEXT FILTERS ======================
$name = trim($_REQUEST['name'] ?? '');
if ($name !== '') {
$conditions[] = "gl.name LIKE '%" . $this->db->realEscapeString($name) . "%'";
}
$city = trim($_REQUEST['city'] ?? '');
if ($city !== '') {
$conditions[] = "gl.city LIKE '%" . $this->db->realEscapeString($city) . "%'";
}
// $search_terms = trim($_REQUEST['search_terms'] ?? '');
// if ($search_terms !== '') {
// $conditions[] = "gl.search_terms LIKE '%" . $this->db->realEscapeString($search_terms) . "%'";
// }
// $search_terms = trim($_REQUEST['search_terms'] ?? '');
// if ($search_terms !== '') {
// // Split the string by one or more spaces
// $words = preg_split('/\s+/', $search_terms);
// $word_conditions = [];
// foreach ($words as $word) {
// // Sanitize each individual word
// $escaped_word = $this->db->realEscapeString($word);
// $word_conditions[] = "gl.search_terms LIKE '%" . $escaped_word . "%'";
// }
// if(strtolower($search_terms)=='mobile notary'){ $opt=' OR '; }else{ $opt=' AND '; }
// // Combine all word conditions with OR and wrap in parentheses
// $conditions[] = "(" . implode($opt, $word_conditions) . ")";
// }
$search_terms_input = $_REQUEST['search_terms'] ?? '';
if (!empty($search_terms_input)) {
// If it is an array, flatten it and clean empty elements
if (is_array($search_terms_input)) {
$raw_items = [];
array_walk_recursive($search_terms_input, function($val) use (&$raw_items) {
$trimmed = trim((string)$val);
if ($trimmed !== '') {
$raw_items[] = $trimmed;
}
});
// Convert to a single string if the array contains exactly one valid item
if (count($raw_items) === 1) {
$search_terms_input = $raw_items[0];
} else {
$search_terms_input = $raw_items; // Keep as flattened array for multi-item logic
}
}
// --- Execute Logic Based on Final Data Type ---
if (is_array($search_terms_input)) {
// --- CASE 1: Input is a multi-item array ---
if (!empty($search_terms_input)) {
$word_conditions = [];
foreach ($search_terms_input as $item) {
$escaped_item = $this->db->realEscapeString($item);
$word_conditions[] = "gl.search_terms LIKE '%" . $escaped_item . "%'";
}
// Use OR operator between distinct array entries
$conditions[] = "(" . implode(' OR ', $word_conditions) . ")";
}
} else {
// --- CASE 2: Input is a single string (or a single-item array) ---
$full_search_string = trim((string)$search_terms_input);
$words = preg_split('/\s+/', $full_search_string, -1, PREG_SPLIT_NO_EMPTY);
if (!empty($words)) {
$word_conditions = [];
foreach ($words as $word) {
$escaped_word = $this->db->realEscapeString($word);
$word_conditions[] = "gl.search_terms LIKE '%" . $escaped_word . "%'";
}
// 'mobile notary' override check works perfectly here now
$opt = (strtolower($full_search_string) === 'mobile notary') ? ' OR ' : ' AND ';
$conditions[] = "(" . implode($opt, $word_conditions) . ")";
}
}
}
$search_terms_city = trim($_REQUEST['search_terms_city'] ?? '');
if ($search_terms_city !== '') {
$conditions[] = "gl.search_terms LIKE '%in%" . $this->db->realEscapeString($search_terms_city) . "%'";
}
$search_terms_state = trim($_REQUEST['search_terms_state'] ?? '');
if ($search_terms_state !== '') {
$conditions[] = "gl.search_terms LIKE '%in%" . $this->db->realEscapeString($search_terms_state) . "%'";
}
// ====================== PHONE FILTER (Full Original Logic) ======================
$phone = $this->cleanPhone($_REQUEST['phone'] ?? '', false);
$phone_opt = trim($_REQUEST['phone_opt'] ?? '');
if ($phone_opt !== '' && strpos($phone_opt, '{str}') === false) {
$phone_opt = '';
}
if ($phone_opt === '') {
// Also-in / missing opt: any-part match
$phone_opt = "Like '%{str}%'";
}
if (strlen($phone) >= 10) {
$phone = $this->formatPhone($phone);
list($leftPhone) = explode("-", $phone);
list($npa, $nxx) = explode(") ", $leftPhone);
$npa = trim(str_ireplace("(", "", $npa));
} else {
$npa = '';
$nxx = '';
}
if ($phone != '') {
if ($phone_opt == "Like '{str}%'") {
if (strlen($phone) == 3) {
$npa = $phone;
$phone = "(" . $phone . ")";
} elseif (strlen($phone) == 6) {
$npa = $this->left($phone, 3);
$nxx = $this->right($phone, 3);
$phone = "(" . $npa . ") " . $nxx;
}
}
list($start, $end) = explode('{str}', $phone_opt);
$phone_condition = "gl.phone " . $start . $phone . $end;
$isPartial = (stripos($start, 'Like') !== false);
// Exact match only: npa/nxx gate. Skip for Also-in / partial lookups.
if (!$isPartial) {
if (!empty($nxx)) {
$phone_condition = "gl.nxx=" . $nxx . " and " . $phone_condition;
}
if (!empty($npa)) {
$phone_condition = "gl.npa=" . $npa . " and " . $phone_condition;
}
}
$conditions[] = $phone_condition;
}
// Only Mobiles
$only_mobiles = doubleval($_REQUEST['only_mobiles'] ?? 0);
if ($only_mobiles == 1) {
$conditions[] = "gl.ltype_id IN ('C','M')";
}
// ====================== EMAIL FILTERS ======================
$email_condition = $this->buildEmailCondition();
if ($email_condition !== '') {
$conditions[] = $email_condition;
}
$exclude_email_condition = $this->buildExcludeEmailCondition();
if ($exclude_email_condition !== '') {
$conditions[] = $exclude_email_condition;
}
if ($is_website !== '') {
if ($is_website==1){
//$conditions[] = "(NOT gl.website IS NULL AND gl.website!='')";
$conditions[] = "(gl.website Like '%//%')";
}elseif($is_website==0){
//$conditions[] = "(gl.website IS NULL OR gl.website='')";
$conditions[] = "(Not gl.website Like '%//%')";
}
}
// ====================== SUPPRESS PREVIOUS DOWNLOAD ======================
$suppress_old = doubleval($_REQUEST['suppress_old'] ?? 0);
if ($opt_leads != 3 && $suppress_old == 1) {
$conditions[] = "NOT EXISTS (
SELECT 1 FROM " . DB_PREFIX . "leads_sent_csv_gl lsc
WHERE lsc.user_id = $user_id AND lsc.lead_id = gl.id
)";
$conditions[] = "NOT EXISTS (
SELECT 1 FROM " . DB_PREFIX . "leads_sent_csv_gl lsc
LEFT JOIN leads ld ON ld.id = lsc.lead_id
WHERE lsc.user_id = $user_id AND ld.phone = gl.phone AND ld.phone IS NOT NULL
)";
}
$is_bad_number_wh = $this->getIsBadNumberCondition('gl');
if ($is_bad_number_wh !== '') {
$conditions[] = $is_bad_number_wh;
}
// ====================== FINAL WHERE ======================
if (!empty($conditions)) {
$wh = "WHERE " . implode(" AND ", $conditions);
} else {
$wh = "WHERE 1=1";
}
// Forbidden phone check (only for normal mode)
if ($opt_leads != 3) {
$wh .= " AND forbidden.phone IS NULL";
}
if ($_REQUEST['test'] == 1) {
echo "\n\n=== FINAL WHERE ===\n" . $wh . "\n\n";
}
return $wh;
}
//====================================================================
private function getBusinessOwnerArr($limit = '', $skipPhoneCount = 0) {
$this->ensureCrossDbFlagColumns();
$leads = array();
$opt_leads = doubleval($_REQUEST['opt_leads']);
$user_id = doubleval($_REQUEST['user_id']);
$team_wh = str_ireplace('"', '', $_REQUEST['team_wh'] ?? '');
$wh = $this->getBusinessOwnerWh();
// id DESC avoids expensive filesort on name across large result sets
$orderBy = ($opt_leads == 3) ? "lds.sent_on DESC, gl.id DESC" : "gl.id DESC";
// ====================== DEFERRED JOIN (Performance Optimized) ======================
if ($opt_leads != 3) {
// Do not join unused leads_sent_gl in the id-picker subquery (notes join happens outer)
$idSubQry = "
SELECT gl.id
FROM leads AS gl
LEFT JOIN (
SELECT DISTINCT ld.phone
FROM leads AS ld
INNER JOIN " . DB_PREFIX . "leads_sent_gl AS lds2
ON ld.id = lds2.lead_id
WHERE " . str_ireplace("lds.", "lds2.", $team_wh) . "
AND lds2.folder_id > 0
AND ld.phone IS NOT NULL
) AS forbidden ON forbidden.phone = gl.phone
" . $wh . "
ORDER BY " . $orderBy . " " . $limit;
} else {
$idSubQry = "
SELECT gl.id
FROM leads AS gl
LEFT JOIN " . DB_PREFIX . "leads_sent_gl AS lds
ON lds.lead_id = gl.id AND " . $team_wh . "
" . $wh . "
ORDER BY " . $orderBy . " " . $limit;
}
$qry = "SELECT
COALESCE(gl.name, '') as name, COALESCE(gl.first_name, '') as first_name, COALESCE(gl.last_name, '') as last_name,
COALESCE(gl.phone, '') as phone, COALESCE(gl.email, '') as email,
COALESCE(gl.street, '') as street, COALESCE(gl.city, '') as city, COALESCE(gl.state, '') as state,
COALESCE(gl.zip, '') as zip,
COALESCE(gl.business_score, '') as business_score, COALESCE(gl.reviews_count, '') as reviews_count,
COALESCE(gl.rating_stars, '') as rating_stars, gl.id, COALESCE(gl.search_terms, '') as search_terms,
COALESCE(gl.website, '') as website, COALESCE(gl.profile_link, '') as profile_link,
COALESCE(gl.review_1, '') as review_1, COALESCE(gl.review_2, '') as review_2, COALESCE(gl.review_3, '') as review_3,
COALESCE(gl.county, '') as county,
COALESCE(lds.user_notes, '') as user_notes,
COALESCE(lds.sent_id, '') as sent_id,
COALESCE(lds.folder_id, 0) as folder_id,
COALESCE(lds.id, 0) as lead_sent_id,
COALESCE(gl.ltype_id, '') as ltype_id,
COALESCE(np.name, '') as ltype,
COALESCE(gl.is_bad_number, 0) as is_bad_number,
COALESCE(gl.is_tcpa, 0) as is_tcpa,
COALESCE(gl.is_dnc, 0) as is_dnc,
COALESCE(gl.is_insurance, '') as is_insurance,
COALESCE(gl.is_agency, '') as is_agency,
COALESCE(gl.is_realtor, '') as is_realtor,
COALESCE(gl.is_mortgage, '') as is_mortgage,
COALESCE(gl.is_ca_sales, '') as is_ca_sales,
gl.insertion_date,
DATE_FORMAT(gl.insertion_date, '%b %D %Y %r') as added_on,
CASE WHEN lds.updated_folder_status > 0
THEN FROM_UNIXTIME(lds.updated_folder_status, '%b %D %Y %r')
ELSE '' END as updated_folder_status
FROM (" . $idSubQry . ") AS ids
INNER JOIN leads AS gl ON gl.id = ids.id
LEFT JOIN " . DB_PREFIX . "npa_ltype AS np ON np.id = gl.ltype_id
LEFT JOIN " . DB_PREFIX . "leads_sent_gl AS lds
ON lds.lead_id = gl.id AND " . $team_wh . "
ORDER BY " . $orderBy;
if ($_REQUEST['test'] == 1) {
echo "
" . ($qry) . "
";//htmlspecialchars($qry)
}
$res = $this->db->query($qry)->fetchAll();
if (count($res) > 0) {
foreach ($res as $row) {
if ($skipPhoneCount == 0) {
$row['called'] = 0;
$row['texted'] = 0;
$row['emailed'] = 0;
}
$leads[] = $row;
}
} else {
$leads[] = array('error' => 'There is no any data available!');
}
return $leads;
}
//====================================================================
function viewBusinessOwner() {
$this->urldecodeRequest();
$reject = $this->boRejectUnscopedSearch();
if ($reject !== '') { return $reject; }
$startSearch = microtime(true);
$opt_leads = doubleval($_REQUEST['opt_leads']);
$skipPhoneCount = 0;
$page_limit = 24; //PAGE_LIMIT
$page = max(1, (int)$_REQUEST['page'] ?? 1);
$limitOffset = ($page - 1) * $page_limit;
$data = $this->getBusinessOwnerArr("LIMIT $limitOffset, $page_limit", $skipPhoneCount);
$newArr['result'] = $data;
$tab_clicked = doubleval($_REQUEST['tab_clicked'] ?? 0);
$team_wh = str_ireplace('"', '', $_REQUEST['team_wh'] ?? '');
if (!array_key_exists("error", $data[0] ?? [])) {
// ====================== TOTAL COUNT ======================
$wh = $this->getBusinessOwnerWh();
$user_id = doubleval($_REQUEST['user_id']);
if ($opt_leads != 3) {
$subQry = " LEFT JOIN (
SELECT DISTINCT ld.phone
FROM leads AS ld
INNER JOIN " . DB_PREFIX . "leads_sent_gl AS lds2
ON ld.id = lds2.lead_id
WHERE " . str_ireplace("lds.", "lds2.", $team_wh) . "
AND lds2.folder_id > 0
AND ld.phone IS NOT NULL
) AS forbidden ON forbidden.phone = gl.phone ";
} else {
$subQry = " LEFT JOIN " . DB_PREFIX . "leads_sent_gl AS lds
ON lds.lead_id = gl.id AND " . $team_wh;
}
$tbl = "leads AS gl " . $subQry;
$total = $this->db->getRecordByID("COUNT(*) as total", $tbl, $wh, 1);
$paginationFunc = ($_REQUEST['new_ui'] == 1) ? 'get_pagination_new' : 'get_pagination';
$newArr['pagination'] = $this->$paginationFunc($page, PAGE_LIMIT, $total, 'viewBusinessOwner');
}
// Saved Leads Count
$user_id = doubleval($_REQUEST['user_id']);
$newArr['saved_leads'] = doubleval($this->db->getRecordByID(
"COUNT(*) as total",
"leads AS gl LEFT JOIN " . DB_PREFIX . "leads_sent_gl AS lds
ON lds.lead_id = gl.id AND lds.user_id = " . $user_id,
"WHERE lds.id > 0", 1
));
$endSearch = microtime(true);
$seconds_taken = number_format($endSearch - $startSearch, 2);
$newArr['seconds_taken'] = $seconds_taken;
return json_encode($newArr);
}
//====================================================================
function getBusinessOwnerIDs(){
$this->urldecodeRequest();
$reject = $this->boRejectUnscopedSearch();
if ($reject !== '') { return $reject; }
$user_id = doubleval($_REQUEST['user_id']);
$wh = $this->getBusinessOwnerWh();
$team_wh = $_REQUEST['team_wh'];
$any = "";/*"ANY_VALUE";*/ $groupBy = "";//"GROUP BY license_number";
$qry = "select ".$any."(gl.id) as data from leads as gl left join ".DB_PREFIX."npa_ltype as np on np.id=gl.ltype_id ".$wh." ".$groupBy;
if($_REQUEST['test']==1){ echo "
qry: ".$qry; }
$data = array();
$res = $this->db->query($qry)->fetchAll();
foreach($res as $row){
array_push($data, $row['data']);
}
if($_REQUEST['test']==1){ print_r($data); }
$newArr['result']['data'] = implode(",", $data);
return json_encode($newArr);
}
//====================================================================
function exportBusinessOwnerCSV(){
$this->urldecodeRequest();
$reject = $this->boRejectUnscopedSearch();
if ($reject !== '') { return $reject; }
$lst = array();
$opt_limit = doubleval($_REQUEST['opt_limit']);
$opt_limit_leads = doubleval($_REQUEST['opt_limit_leads']);
$selected_ids = trim($_REQUEST['selected_ids']);
if($opt_limit_leads==2){
if(empty($selected_ids)){
$lst[]=array("Error"=>"No any record was selected to export!");
//$this->generateCSV($lst, 'PropHog_BusinessOwners'.date('_m-d-Y_H-i-s'));
goto Finish;
}
$limit = count(explode(",", $selected_ids));
}else{ $limit = $opt_limit; }
$addLog = 1;
//if(SESSION_USER_ID==1){ $addLog = 0; }
$user_id = doubleval($_REQUEST['user_id']);
$usage_left = doubleval($_REQUEST['usage_left']);
if($usage_left<=0 && $addLog==1){
$lst[] = array("Error"=>'Your monthly usage limit exhausted!');
}else{
$skipFields = array("id", "sent_id", "lead_sent_id", "kixie_lead_id", "folder_id", "phone_count", "multi_phones", "email_count", "first_name", "last_name");//, "email"
if($usage_left<$limit && $addLog==1){ $limit=$usage_left; }
$lst = $this->getBusinessOwnerArr("limit 0,".$limit, 1);
$newLst = array();
$only_mobiles = doubleval($_REQUEST['only_mobiles']);
$is_experience_required = 1;//doubleval($_REQUEST['is_experience_required']);
$opt_leads = 8;
foreach($lst as $row){
if(($only_mobiles==1 && ($row['ltype_id']=='C' || $row['ltype_id']=='M')) || $only_mobiles==0){
if($addLog==1 && ($row['ltype_id']=='C' || $row['ltype_id']=='M')){
if($_REQUEST['test']==1){ echo "
row = ". json_encode($row); }
//if($opt_leads==3){
$isAlreadyExported = doubleval($this->db->getRecordByID("lead_id", "leads_sent_csv_gl", "where user_id=".$user_id." and lead_id=".doubleval($row['id'])));
//}else{ $isAlreadyExported=0; }
if($isAlreadyExported<=0){
$insData['lead_id'] = $row['id'];
$insData['user_id'] = $user_id;
$insData['client_ip'] = $this->getClientIP();
$this->db->insertData($insData, "leads_sent_csv_gl");
unset($insData);
}
}
$row['source'] = 'PropHog';
foreach($skipFields as $fld){ unset($row[$fld]); }
$newLst[] = $row;
}
}
if(empty($newLst)){ $newLst[]=array("Error"=>"Record not found!"); }
//elseif($opt_limit>$limit){ $newLst[]=array("full_name"=>"Your monthly usage limit exhausted!"); }
$lst = $newLst;
}
Finish:
return json_encode($lst);
}
//====================================================================
function getSentLeadID($lead_id, $user_id=0, $tbl_leads_sent="leads_sent_gl"){
$user_id = doubleval($user_id);
if($user_id<=0){ $user_id = doubleval($_REQUEST['user_id']); }
$id = doubleval($_REQUEST['lead_sent_id']);
if($id<=0){
$id = doubleval($this->db->getRecordByID("id", $tbl_leads_sent, "Where lead_id=".$lead_id." and user_id=".$user_id));
if($id<=0){
$folder_id = doubleval($_REQUEST['first_folder_id']);
$tags = $_REQUEST['user_tags'];
$insData['lead_id'] = $lead_id;
$insData['user_id'] = $user_id;
$insData['tags'] = $tags;
$insData['lead_alerts_id'] = doubleval($_REQUEST['lead_alerts_id']);
$insData['folder_id'] = $folder_id;
$this->db->insertData($insData, $tbl_leads_sent);
$id = doubleval($this->db->getRecordByID("id", $tbl_leads_sent, "Where lead_id=".$lead_id." and user_id=".$user_id));
}
}
return $id;
}
//====================================================================
function updateFolderStatus(){
$lead_id = doubleval($_REQUEST['id']);
$folder_id = doubleval($_REQUEST['folder_id']);
if($lead_id<=0 || $folder_id<=0){ return '0'; }
$tbl_leads_sent = "leads_sent_gl";
$user_id = doubleval($_REQUEST['user_id']);
$id = $this->getSentLeadID($lead_id, $user_id, $tbl_leads_sent);
list($tags, $contact_id, $user_id, $opt_crm) = explode('|||', $this->db->getRecordByID('concat(tags, "|||", sent_id, "|||", user_id, "|||", opt_crm) as data', $tbl_leads_sent, 'where id='.$id));
$tags_arr = json_decode($_REQUEST['tags_arr'], true);
//if($contact_id==''){ return '0'; }
$tags = $this->getTags($tags, $folder_id, $tags_arr);
$this->db->updateDataArr(array("tags"=>$tags, "folder_id"=>$folder_id, "updated_on"=>date('Y-m-d'), "updated_folder_status"=>time()), $tbl_leads_sent, "Where id=".$id);
//$sourceCustomFields = $this->getCustomFieldsFromAPI($row['api_key']);
// $api_link = $this->ghl_api_link.'contacts/';
// if($contact_id!=''){
// if($opt_crm==1){
// $api_key = $this->db->getRecordByID("api_key", "user", "where userid=".doubleval($user_id));
// //Send to the source portal, if required
// $POSTFIELDS = json_encode(array('tags'=>$tags));
// $res = $this->GoHighLevel->EditContact($api_key, $contact_id, $POSTFIELDS);
// $arr = json_decode($res, true); //echo "res = ".$res;
// }elseif($opt_crm==2){
// $api_key = $this->db->getRecordByID("less_annoying_api_key", "user", "where userid=".doubleval($user_id));
// $PostArr['ContactId'] = $contact_id;
// $PostArr['Tags'] = $tags;
// $res = $this->LessAnnoyingCRM->EditContact($PostArr);
// }
// }
return $tags;
}
//====================================================================
private function getTags($tags, $folder_id, $tags_arr=array()){
if(count($tags_arr)>0){
foreach($tags_arr as $row){
$tags = $this->removeTag($tags, $row['tags']);
if($row['id']==$folder_id){ if($tags!=''){ $tags .= ', '; } $tags .= $row['tags']; }
}
}
return $tags;
}
//====================================================================
function removeTag($tags, $remove_tag){
$str = explode(',', $tags); $str1=array();
foreach($str as $st){ if(strtolower(trim($st)) != strtolower(trim($remove_tag))){ $str1[]=trim($st); } }
return trim(implode(',', $str1));
}
function update_phone_status(){
$qry="select * from ".DB_PREFIX."leads_sent_gl order by id";
$res = $this->db->query($qry)->fetchAll();
if(count($res)>0){
foreach($res as $row){
$time = strtotime($row['updated_on']);
$this->db->updateData('leads_sent_gl', "updated_folder_status=". $time, "Where id=".$row['id']);
}
}
echo "done";
}
//====================================================================
function updateUserNotes(){
$lead_id = doubleval($_REQUEST['id']);
$user_notes = trim(urldecode($_REQUEST['user_notes']));
if($lead_id<=0){ return '0'; }
$test = doubleval($_REQUEST['test']);
$tbl_leads_sent = "leads_sent_gl";
$user_id = doubleval($_REQUEST['user_id']);
$id = doubleval($_REQUEST['lead_sent_id']);
if($id<=0){ $id = $this->getSentLeadID($lead_id, $user_id, $tbl_leads_sent); }
$this->db->updateData($tbl_leads_sent, "user_notes='".$this->db->realEscapeString($user_notes)."', updated_on='". date('Y-m-d') ."'", "Where id=".$id);
// list($contact_id, $user_id, $note_id, $opt_crm) = explode("|||", $this->db->getRecordByID('concat(sent_id, "|||", user_id, "|||", note_id, "|||", opt_crm) as data', $tbl_leads_sent, 'where id='.$id));
// $customFields = array();
// $api_link = $this->ghl_api_link.'contacts/';
// //if($contact_id==''){ return '0'; }
// if($test==1){ echo "=>>here1 contact_id = ".$contact_id; }
// if($contact_id!=''){
// if($opt_crm==1){
// $api_key = $this->db->getRecordByID("api_key", "user", "where userid=".doubleval($user_id));
// $sourceCustomFields = $this->GoHighLevel->getCustomFieldsFromAPI($api_key);
// foreach($sourceCustomFields['customFields'] as $cf){
// if(stripos($cf['fieldKey'], 'contact.user_notes')!==false || stripos($cf['name'], 'user notes')!==false && !empty($user_notes)){
// $customFields[$cf['id']] = $user_notes;
// }
// }
// if(!empty($customFields)){
// //Send to the source portal, if required
// $POSTFIELDS = json_encode(array('customField'=>$customFields));
// $res = $this->GoHighLevel->EditContact($api_key, $contact_id, $POSTFIELDS);
// $arr = json_decode($res, true); //echo "res = ".$res;
// if($test==1){ echo "=>>here2 res = ".$res; }
// }
// }elseif($opt_crm==2){
// $api_key = $this->db->getRecordByID("less_annoying_api_key", "user", "where userid=".doubleval($user_id));
// if($note_id!=''){
// $this->LessAnnoyingCRM->EditNote($note_id, $user_notes);
// }else{
// $arr = $this->LessAnnoyingCRM->CreateNote($contact_id, $user_notes, 1);
// $this->db->updateDataArr(array("note_id"=>$arr['NoteId']), $tbl_leads_sent, "Where id=".$id);
// }
// // $PostArr['ContactId'] = $contact_id;
// // $PostArr['User Notes'] = $user_notes;
// // $res = $this->LessAnnoyingCRM->EditContact($PostArr);
// }
// }
return $user_notes;
}
//====================================================================
function updateGoogleLineType(){
$leads_tbl = "leads"; $limit = 10000;
$id = doubleval($_REQUEST['id']);
$pause = doubleval($_REQUEST['pause']);
if($pause<=0){ $pause=5; } //pause is required in seconds
$timeout = $pause * 1000;
$max_id = doubleval($this->db->getRecordByID("max(id) as id", $leads_tbl, "Where phone!='' and is_wireless='n' order by id desc limit 0,1", 1));
if($max_id<=0){ $max_id=17261564; }
//$qry = "Select * from leads where phone!='' and (ltype_id IS NULL or ltype_id='') and id>".$id." order by id limit 0,".$limit;
$qry = "Select * from ".$leads_tbl." where phone!='' and is_wireless='n' and id>".$id." order by id limit 0,".$limit;
echo "
".$qry."
";
$res = $this->db->query($qry)->fetchAll();
if(count($res)>0){
foreach($res as $row){
$ph = trim($row['phone']);
if($ph!=''){
$ar = $this->getPhoneLineType($ph, 1);
if($_REQUEST['test']==1){ echo "
ar = "; print_r($ar); }
if($ar['npa']!='' && $ar['nxx']!=''){
$postArr['npa'] = $ar['npa'];
$postArr['nxx'] = $ar['nxx'];
$postArr['block_id'] = $ar['block_id'];
$postArr['ltype_id'] = $ar['line_type'];
$postArr['is_wireless'] = $ar['is_wireless'];
$wh = "Where phone='".$row['phone']."'";
$y += $this->db->updateDataArr($postArr, "leads", $wh, 1);
}
}
$id = $row['id'];
}
//$offset = $offset + $limit;
echo "Total ".$y." records updated successfully!\n";
}else{ echo "There is no any data available!\n"; goto Finish; }
echo "; id=".$id."\n
";
ob_flush(); flush();
//if($processed<100000){ goto RunScanning; }
if($id<$max_id){ echo "Refreshing in 5 seconds!
"; }// && $x>0
Finish:
echo "Done";
}
//====================================================================
function updateGoogleCounty(){
$x = 0; $leads_tbl = "leads";
$log_id = $this->app_dir . "log_files/log_id.txt";
$id = doubleval(file_get_contents($log_id));
if($id<=0){ $id = doubleval($_REQUEST['id']); }
$pause = doubleval($_REQUEST['pause']);
if($pause<=0){ $pause=5; } //pause is required in seconds
$timeout = $pause * 1000;
$max_id = doubleval($this->db->getRecordByID("max(id) as id", $leads_tbl, "Where LENGTH(state)=2 and county IS NULL order by id desc limit 0,1", 1));
if($max_id<=0){ $max_id=17261564; }
$qry = "SELECT ld.id, ld.state, ld.city, ld.zip, IFNULL((select uz.county_name FROM `uszips` as uz where (uz.city=ld.city or uz.zip like concat('%', ld.zip, '%')) and uz.state_id=ld.state limit 0,1), '') as county_name FROM ".$leads_tbl." as ld where ld.id>".$id." and LENGTH(ld.state)=2 and ld.county IS NULL order by id limit 10000";
//echo "\n".$qry."\n";
$res = $this->db->query($qry)->fetchAll();
if(count($res)>0){
foreach($res as $row){
$postArr = array();
$postArr['county'] = $row['county_name'];
if(!empty($postArr['county'])){
if(strlen(trim($row['zip']))>5){
$postArr['zip'] = $this->left(trim($row['zip']), 5);
}
$wh = "Where id = ".$row['id'];
$this->db->updateDataArr($postArr, $leads_tbl, $wh, 1); $x++;
}
$id = $row['id'];
}
echo "
Total ".$x." records updated successfully\n";
}else{ echo "
There is no any data available!\n"; goto Finish; }
echo "; id=".$id."\n
";
file_put_contents($log_id, $id);
ob_flush(); flush();
//if($processed<100000){ goto RunScanning; }
if($id<$max_id){ echo "Refreshing in 5 seconds!
"; }// && $x>0
Finish:
echo "Done\n";
}
//Business Owner programming ended
//Search Terms List programming started
//====================================================================
function updateSearchTermsList(){
$tbl = 'search_terms_gl'; $values = array(); $result = 0;
$name = trim(urldecode($_POST['search_terms_list']));
$arr = $this->sortSearchTermsList($name);
if(empty($arr)){return -1;}
$this->db->query("TRUNCATE ".DB_PREFIX.$tbl);
foreach($arr as $ar){
if(!empty($ar)){
$values[] = "('". $this->db->realEscapeString($ar) ."')";
}
}
if(!empty($values)){
$qry = "INSERT INTO " . DB_PREFIX.$tbl . " (name) VALUES " . implode(", ", $values);
$result = $this->db->query($qry)->affectedRows();
if($this->db->error_msg!=''){ echo $this->db->error_msg; }
}
if($result>0){
return $this->getSearchTermsList();
}else{ return 0; }
}
//====================================================================
private function sortSearchTermsList($str){
$arr = explode("\n", str_ireplace("\r", "", $str));
$arr = array_map('ucwords', array_map('trim', $arr));
sort($arr); $arr = array_unique($arr);
return $arr;
}
//====================================================================
function getSearchTermsList(){
$qry="select GROUP_CONCAT(name SEPARATOR '\n') as name from ".DB_PREFIX."search_terms_gl order by name";
$data = $this->db->query($qry)->fetchArray();
$newArr['result'] = $data;
return json_encode($newArr);
}
//Search Terms List programming ended
//Exclude Google Company names programming started
//====================================================================
function updateExcludeCompanyNames(){
$tbl = 'exclude_company_gl'; $values = array(); $result = 0;
$name = trim($_POST['company_name']);
$arr = $this->sortExcludeCompanyNames($name);
if(empty($arr)){return -1;}
$this->db->query("TRUNCATE ".DB_PREFIX.$tbl);
foreach($arr as $ar){
if(!empty($ar)){
$values[] = "('". $this->db->realEscapeString($ar) ."')";
}
}
if(!empty($values)){
$qry = "INSERT INTO " . DB_PREFIX.$tbl . " (name) VALUES " . implode(", ", $values);
$result = $this->db->query($qry)->affectedRows();
if($this->db->error_msg!=''){ echo $this->db->error_msg; }
else{
// $qry = "UPDATE `leads` SET exclude=1 where NOT first_name IN (select name from exclude_company_gl)";
// $this->db->query($qry)->affectedRows();
}
}
if($result>0){
return $this->getExcludeCompanyNames();
}else{ return 0; }
}
//====================================================================
private function sortExcludeCompanyNames($str){
$arr = explode("\n", str_ireplace("\r", "", $str));
$arr = array_map('ucwords', array_map('trim', $arr));
sort($arr); $arr = array_unique($arr);
return $arr;
}
//====================================================================
function deleteExcludeCompanyNames(){
return $this->db->query("TRUNCATE ".DB_PREFIX."exclude_company_gl");
}
//====================================================================
function getExcludeCompanyNames(){
$qry="select GROUP_CONCAT(name SEPARATOR '\n') as name from ".DB_PREFIX."exclude_company_gl order by name";
$data = $this->db->query($qry)->fetchArray();
$newArr['result'] = $data;
return json_encode($newArr);
}
//Exclude Google Company names programming ended
//====================================================================
//Sending emails very faster for all type of mail servers, like: gmail, yahoo, etc.
function sendMailer($from_email, $from_name, $to_email, $to_name, $subject, $message, $file_path='', $cc_email='', $replyTo='', $bcc_email=''){
require_once 'includes/PHPMailer/PHPMailerAutoload.php';
$mail = new PHPMailer();
try {
//$mail->SMTPDebug = 3; // Enable verbose debug output
// //If you wanna set your own smtp to send emails
// $mail->isSMTP(); // Set mailer to use SMTP
// $mail->Host = 'mail.victorymarketing.software'; // Specify main and backup SMTP servers
// $mail->Port = 465; // TCP port to connect to
// $mail->SMTPAuth = true; // Enable SMTP authentication
// $mail->SMTPSecure = 'tls'; // Enable TLS encryption, `ssl` also accepted
// $mail->Username = 'support@victorymarketing.software';// SMTP username
// $mail->Password = 'support@victory'; // SMTP password
// $mail->Host = 'smptp.office365.com'; // Specify main and backup SMTP servers
// $mail->Port = 587; // TCP port to connect to
// $mail->SMTPAuth = true; // Enable SMTP authentication
// $mail->SMTPSecure = 'starttls'; // Enable TLS encryption, `ssl` also accepted
// $mail->Username = 'leads@workwithrandy.info';// SMTP username
// $mail->Password = 'ELeads2021!@#'; // SMTP password
// //Settings to use gmail smtp to send emails
// $mail->Host = 'smtp.gmail.com';
// $mail->Port = 587;
// $mail->SMTPAuth = true;
// $mail->SMTPSecure = 'tls';
// $mail->Username = 'user@gmail.com';
// $mail->Password = 'secret';
//Recipients
$mail->setFrom($from_email, $from_name);
$mail->addAddress($to_email, $to_name); //Add a recipient
$mail->addReplyTo($replyTo);
$mail->addCC($cc_email);
$mail->addBCC($bcc_email);
if (!empty($file_path)){
$file_name = basename($file_path);
$mail->addAttachment($file_path, $file_name);
}
//Content
$mail->isHTML(true); //Set email format to HTML
$mail->Subject = $subject;
$mail->Body = $message;//'This is the HTML message body in bold!';
//$mail->AltBody = 'This is the body in plain text for non-HTML mail clients';
$mail->send();
return 1; //'Message has been sent';
} catch (Exception $e) {
return 0; //"Message could not be sent. Mailer Error: {$mail->ErrorInfo}";
}
}
function info(){
// Start timing
$start = microtime(true);
echo "Start: $start
\n";
// Create DB object
$db = $this->db; //new DB();
$db->showErrors(true);
$afterDB = microtime(true);
echo "After DB object: $afterDB
\n";
// Fetch 10 rows from leads
$rows = $db->query("SELECT id, name FROM leads LIMIT 10")->fetchAll();
$afterQuery = microtime(true);
echo "After query: $afterQuery
\n";
// Process rows and flush each to browser
$totalFetched = 0;
foreach($rows as $row){
$rowStart = microtime(true);
echo "Row ID {$row['id']}, Name: {$row['name']} processed
\n";
flush();
$totalFetched++;
$rowEnd = microtime(true);
echo "Time for this row: ".($rowEnd-$rowStart)." sec
\n";
}
// End timing
$end = microtime(true);
echo "Total rows processed: $totalFetched
\n";
echo "Total script time: ".($end-$start)." sec
\n";
$row = $db->query("Select * FROM leads where phone='(410) 515-3838'")->fetchArray();
print_r($row);
}
}
?>