You cannot select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
559 lines
15 KiB
PHTML
559 lines
15 KiB
PHTML
8 months ago
|
<?php
|
||
|
// Exit if accessed directly
|
||
|
if( !defined( 'ABSPATH' ) ) exit;
|
||
|
|
||
|
|
||
|
if( !class_exists( 'vxcf_hubspot_data' ) ) {
|
||
|
|
||
|
/**
|
||
|
* since 1.0
|
||
|
*/
|
||
|
class vxcf_hubspot_data extends vxcf_hubspot{
|
||
|
/**
|
||
|
* creates or updates tables
|
||
|
*
|
||
|
*/
|
||
|
public function update_table(){
|
||
|
global $wpdb;
|
||
|
|
||
|
$wpdb->hide_errors();
|
||
|
$table_name = $this->get_crm_table_name();
|
||
|
|
||
|
require_once(ABSPATH . '/wp-admin/includes/upgrade.php');
|
||
|
|
||
|
if ( ! empty($wpdb->charset) )
|
||
|
$charset_collate = "DEFAULT CHARACTER SET $wpdb->charset";
|
||
|
if ( ! empty($wpdb->collate) )
|
||
|
$charset_collate .= " COLLATE $wpdb->collate";
|
||
|
|
||
|
$sql = "CREATE TABLE $table_name (
|
||
|
id int(11) unsigned not null auto_increment,
|
||
|
form_id varchar(60) not null,
|
||
|
account mediumint(8) not null,
|
||
|
is_active tinyint(1) not null default 1,
|
||
|
sort mediumint(8) not null default 0,
|
||
|
name varchar(240) not null,
|
||
|
object varchar(200) not null,
|
||
|
meta longtext,
|
||
|
data longtext,
|
||
|
`time` datetime null,
|
||
|
PRIMARY KEY (id),
|
||
|
KEY form_id (form_id)
|
||
|
)$charset_collate; ";
|
||
|
|
||
|
dbDelta($sql);
|
||
|
|
||
|
$table_name = $this->get_crm_table_name('log');
|
||
|
|
||
|
$sql= "CREATE TABLE $table_name (
|
||
|
id int(11) unsigned not null auto_increment,
|
||
|
entry_id int(11) not null,
|
||
|
form_id varchar(60) not null,
|
||
|
feed_id int(11) not null,
|
||
|
parent_id int(11) not null,
|
||
|
crm_id varchar(200) not null,
|
||
|
object varchar(200) not null,
|
||
|
link varchar(250) not null,
|
||
|
meta varchar(250) not null,
|
||
|
event varchar(200) not null,
|
||
|
data text,
|
||
|
response text,
|
||
|
extra text,
|
||
|
`status` tinyint(1) not null default 1,
|
||
|
`time` datetime null,
|
||
|
PRIMARY KEY (id),
|
||
|
KEY entry_id (entry_id)
|
||
|
)$charset_collate;";
|
||
|
|
||
|
dbDelta($sql);
|
||
|
|
||
|
$table_name = $this->get_crm_table_name('accounts');
|
||
|
|
||
|
$sql= "CREATE TABLE $table_name (
|
||
|
id int(11) unsigned not null auto_increment,
|
||
|
name varchar(250) not null,
|
||
|
data longtext,
|
||
|
meta longtext,
|
||
|
`status` int(1) not null default 0,
|
||
|
`time` datetime null,
|
||
|
`updated` datetime null,
|
||
|
PRIMARY KEY (id)
|
||
|
)$charset_collate;";
|
||
|
|
||
|
dbDelta($sql);
|
||
|
|
||
|
}
|
||
|
/**
|
||
|
* Get tables names
|
||
|
*
|
||
|
* @param mixed $table
|
||
|
*/
|
||
|
public function get_crm_table_name($table=""){
|
||
|
global $wpdb;
|
||
|
if(!empty($table))
|
||
|
return $wpdb->prefix . $this->id."_".$table;
|
||
|
else
|
||
|
return $wpdb->prefix . $this->id;
|
||
|
}
|
||
|
|
||
|
/**
|
||
|
* get crm feeds
|
||
|
*
|
||
|
*/
|
||
|
public function get_feeds(){
|
||
|
global $wpdb;
|
||
|
$table_name = $this->get_crm_table_name();
|
||
|
|
||
|
$sql = "SELECT s.id, s.is_active,s.object,s.name,s.data,s.time, s.form_id, s.meta
|
||
|
FROM $table_name s where time is not NULL
|
||
|
ORDER BY s.sort";
|
||
|
|
||
|
$results = $wpdb->get_results($sql, ARRAY_A);
|
||
|
|
||
|
$count = sizeof($results);
|
||
|
/* for($i=0; $i<$count; $i++){
|
||
|
$results[$i]["meta"] = maybe_unserialize($results[$i]["meta"]);
|
||
|
}*/
|
||
|
|
||
|
return $results;
|
||
|
}
|
||
|
/**
|
||
|
* get log from database
|
||
|
*
|
||
|
*/
|
||
|
public function get_log(){
|
||
|
global $wpdb;
|
||
|
$sql_end=$this->get_log_query();
|
||
|
$sql_t="select count(s.id) as total $sql_end";
|
||
|
$result= $wpdb->get_results($sql_t);
|
||
|
$items=$result[0]->total;
|
||
|
$per_page = 20;
|
||
|
$start = 0;
|
||
|
$pages = ceil($items/$per_page);
|
||
|
if(isset($_GET['page_id']))
|
||
|
{
|
||
|
$page=(int)$this->post('page_id');
|
||
|
$start = $page-1;
|
||
|
$start = $start*$per_page;
|
||
|
}
|
||
|
$start=max($start,0);
|
||
|
$sql = "SELECT s.id, s.status,s.parent_id,s.object, s.form_id,s.link,s.feed_id,s.meta as error,s.entry_id,s.crm_id,s.time
|
||
|
$sql_end
|
||
|
limit $start , $per_page";
|
||
|
$results = $wpdb->get_results($sql, ARRAY_A);
|
||
|
|
||
|
$page_id=isset($_REQUEST['page_id'])&& $_REQUEST['page_id'] !="" ? $this->post('page_id') : "1";
|
||
|
$range_min=(int)($per_page*($page_id-1))+1;
|
||
|
$range_max=(int)($per_page*($page_id-1))+count($results);
|
||
|
unset($_GET['page_id']);
|
||
|
$query_h=$this->clean($_GET);$query_h=http_build_query($query_h);
|
||
|
$page_links = paginate_links( array(
|
||
|
'base' => admin_url("admin.php")."?".$query_h."&%_%" ,
|
||
|
'format' => 'page_id=%#%',
|
||
|
'prev_text' =>'«',
|
||
|
'next_text' =>'»',
|
||
|
'total' => $pages,
|
||
|
'current' => $page_id,
|
||
|
'show_all' => false
|
||
|
));
|
||
|
|
||
|
return array("min"=>$range_min,"max"=>$range_max,"items"=>$items,"links"=>$page_links,"feeds"=>$results);
|
||
|
}
|
||
|
/**
|
||
|
* get logs query string
|
||
|
*
|
||
|
*/
|
||
|
public function get_log_query(){
|
||
|
$search="";
|
||
|
$table_name = $this->get_crm_table_name('log');
|
||
|
$sql_end="FROM $table_name s";
|
||
|
// handle search
|
||
|
$time_key=$this->post('time');
|
||
|
$time=current_time('timestamp');
|
||
|
|
||
|
$offset = $this->time_offset();
|
||
|
$start_date=""; $end_date="";
|
||
|
switch($time_key){
|
||
|
case"today": $start_date=strtotime('today',$time); break;
|
||
|
case"this_week": $start_date=strtotime('last sunday',$time); break;
|
||
|
case"last_7": $start_date=strtotime('-7 days',$time); break;
|
||
|
case"last_30": $start_date=strtotime('-30 days',$time); break;
|
||
|
case"this_month": $start_date=strtotime('first day of 0 month',$time); break;
|
||
|
case"yesterday":
|
||
|
$start_date=strtotime('yesterday',$time);
|
||
|
$end_date=strtotime('today',$time);
|
||
|
|
||
|
break;
|
||
|
case"last_month":
|
||
|
$start_date=strtotime('first day of -1 month',$time);
|
||
|
$end_date=strtotime('last day of -1 month',$time);
|
||
|
|
||
|
break;
|
||
|
case"custom":
|
||
|
|
||
|
if(!empty($_GET['start_date'])){
|
||
|
$start_date=strtotime($this->post('start_date').' 00:00:00');
|
||
|
}
|
||
|
if(!empty($_GET['end_date'])){
|
||
|
$end_date=strtotime($this->post('end_date').' 23:59:59');
|
||
|
}
|
||
|
break;
|
||
|
}
|
||
|
|
||
|
if($start_date!=""){
|
||
|
$start_date-=$offset;
|
||
|
$search.=' and s.time >="'.date('Y-m-d H:i:s',$start_date).'"';
|
||
|
}
|
||
|
if($end_date!=""){
|
||
|
$end_date-=$offset;
|
||
|
if($time_key == "yesterday"){
|
||
|
$search.=' and s.time <"'.date('Y-m-d H:i:s',$end_date).'"';
|
||
|
}else{
|
||
|
$search.=' and s.time <="'.date('Y-m-d H:i:s',$end_date).'"';
|
||
|
}
|
||
|
}
|
||
|
if($this->post('object')!=""){
|
||
|
$search.=' and object ="'.esc_sql($this->post('object')).'"';
|
||
|
}
|
||
|
if($this->post('status')!=""){
|
||
|
$status=$this->post('status');
|
||
|
if($status == "all"){$status="0";}
|
||
|
$search.=' and status ="'.esc_sql($status).'"';
|
||
|
}
|
||
|
if($this->post('search')!=""){
|
||
|
$search_s=esc_sql($this->post('search'));
|
||
|
$search.=' and (object like "'.$search_s.'" or crm_id="'.$search_s.'" or entry_id="'.$search_s.'")';
|
||
|
}
|
||
|
if(isset($_GET['log_id']) && !empty($_GET['log_id'])){
|
||
|
$log_id=esc_sql($this->post('log_id'));
|
||
|
$search.=' and id="'.$log_id.'"';
|
||
|
}
|
||
|
if($this->post('id')!=""){
|
||
|
$form_id=esc_sql($this->post('id'));
|
||
|
$search.=' and form_id="'.$form_id.'"';
|
||
|
}
|
||
|
if($this->post('entry_id')!=""){
|
||
|
$entry_id=esc_sql($this->post('entry_id'));
|
||
|
$search.=' and entry_id="'.$entry_id.'"';
|
||
|
}
|
||
|
if($this->post('feed_id')!=''){
|
||
|
$feed_id=esc_sql($this->post('feed_id'));
|
||
|
$search.=' and feed_id="'.$feed_id.'"';
|
||
|
}
|
||
|
if($search!=""){
|
||
|
$sql_end.=" where ".substr($search,4);
|
||
|
}
|
||
|
if($this->post('orderby')!=""){
|
||
|
$sql_end.=' order by '.esc_sql($this->post('orderby'));
|
||
|
if($this->post('order')!="" && in_array($this->post('order'),array("asc","desc"))){
|
||
|
$sql_end.=' '.$this->post('order');
|
||
|
}
|
||
|
}else{
|
||
|
$sql_end.=" order by s.id desc";
|
||
|
}
|
||
|
return $sql_end;
|
||
|
}
|
||
|
/**
|
||
|
* insert log in database
|
||
|
*
|
||
|
* @param mixed $arr
|
||
|
*/
|
||
|
public function insert_log($arr,$log_id=""){
|
||
|
global $wpdb;
|
||
|
if(!is_array($arr) || count($arr) == 0)
|
||
|
return;
|
||
|
$wpdb->show_errors();
|
||
|
$table_name = $table_name = $this->get_crm_table_name('log');
|
||
|
$sql_arr=array();
|
||
|
foreach($arr as $k=>$v){
|
||
|
$sql_arr[$k]=is_array($v) ? json_encode($v) : $v;
|
||
|
}
|
||
|
//var_dump($sql_arr); die();
|
||
|
$log_id=(int)$log_id;
|
||
|
$res=false;
|
||
|
if(!empty($log_id)){
|
||
|
// update
|
||
|
$sql='update '.$table_name.' set '.implode('=%s, ',array_keys($sql_arr)).'=%s where id=%d';
|
||
|
$sql_arr['id']=$log_id;
|
||
|
}else{
|
||
|
$sql='insert into '.$table_name.'('.implode(',',array_keys($sql_arr)).') values('.implode(',',array_fill(1,count($sql_arr),'%s')).')';
|
||
|
}
|
||
|
|
||
|
$sql=$wpdb->prepare($sql,$sql_arr);
|
||
|
$res=$wpdb->query($sql);
|
||
|
if(!empty($wpdb->insert_id)){
|
||
|
$log_id=$wpdb->insert_id;
|
||
|
}
|
||
|
return $log_id;
|
||
|
}
|
||
|
/**
|
||
|
* clear logs
|
||
|
*
|
||
|
*/
|
||
|
public function clear_logs(){
|
||
|
global $wpdb;
|
||
|
$table_name = $this->get_crm_table_name('log');
|
||
|
// update
|
||
|
return $wpdb->query("truncate table `".$table_name."`");
|
||
|
}
|
||
|
/**
|
||
|
* delete feed
|
||
|
*
|
||
|
* @param mixed $id
|
||
|
*/
|
||
|
public function delete_feed($id){
|
||
|
global $wpdb;
|
||
|
$table_name = $this->get_crm_table_name();
|
||
|
$wpdb->query($wpdb->prepare("DELETE FROM $table_name WHERE id=%s", $id));
|
||
|
}
|
||
|
/**
|
||
|
* delete log entries
|
||
|
*
|
||
|
* @param mixed $id
|
||
|
*/
|
||
|
public function delete_log($log_ids){
|
||
|
global $wpdb;
|
||
|
$table=$this->get_crm_table_name('log');
|
||
|
$count=0;
|
||
|
foreach($log_ids as $id){
|
||
|
$del=$wpdb->delete($table,array('id'=>$id),array( '%d' ));
|
||
|
if($del){$count++;}
|
||
|
}
|
||
|
return $count;
|
||
|
}
|
||
|
/**
|
||
|
* get form
|
||
|
*
|
||
|
* @param mixed $form_id
|
||
|
* @param mixed $only_active
|
||
|
*/
|
||
|
public function get_feed_by_form($form_id, $only_active = false){
|
||
|
if(empty($form_id)){
|
||
|
return array();
|
||
|
}
|
||
|
global $wpdb;
|
||
|
$table_name = $this->get_crm_table_name();
|
||
|
$active_clause = $only_active ? " AND is_active=1" : "";
|
||
|
$sql = $wpdb->prepare("SELECT * FROM $table_name WHERE form_id=%s $active_clause ORDER BY sort", $form_id);
|
||
|
$results = $wpdb->get_results($sql, ARRAY_A);
|
||
|
if(empty($results))
|
||
|
return array();
|
||
|
|
||
|
return $results;
|
||
|
}
|
||
|
/**
|
||
|
* get object feeds
|
||
|
*
|
||
|
* @param mixed $form_id
|
||
|
* @param mixed $only_active
|
||
|
*/
|
||
|
public function get_object_feeds($form_id, $account , $object , $skip=''){
|
||
|
global $wpdb;
|
||
|
$table_name = $this->get_crm_table_name();
|
||
|
$sql = $wpdb->prepare("SELECT * FROM $table_name WHERE id!=%d and form_id=%s and object = %s and account=%d and is_active=1 ORDER BY sort", $skip,$form_id,$object, $account);
|
||
|
$results = $wpdb->get_results($sql, ARRAY_A);
|
||
|
if(empty($results))
|
||
|
return array();
|
||
|
|
||
|
return $results;
|
||
|
}
|
||
|
/**
|
||
|
* Get log of order and feed
|
||
|
*
|
||
|
* @param mixed $feed_id
|
||
|
* @param mixed $order_id
|
||
|
*/
|
||
|
public function get_feed_log($feed_id,$order_id,$object,$parent_id=""){
|
||
|
global $wpdb;
|
||
|
$table = $this->get_crm_table_name('log');
|
||
|
$sql= $wpdb->prepare('SELECT * FROM '.$table.' where entry_id = %s and feed_id = %d and crm_id!="" and object=%s and parent_id=%d order by id desc limit 1',$order_id,$feed_id,$object,$parent_id);
|
||
|
$results = $wpdb->get_row( $sql ,ARRAY_A );
|
||
|
return $results;
|
||
|
}
|
||
|
/**
|
||
|
* get single feed entry
|
||
|
*
|
||
|
* @param mixed $id
|
||
|
*/
|
||
|
public function get_feed($id){
|
||
|
global $wpdb;
|
||
|
$table= $this->get_crm_table_name();
|
||
|
if((string)$id =='new_form'){
|
||
|
$sql='SELECT * FROM '.$table.' where `time` is NULL limit 1';
|
||
|
$results = $wpdb->get_results( $sql,ARRAY_A );
|
||
|
|
||
|
if(count($results) == 0){
|
||
|
$wpdb->insert($table,array("is_active"=>"1"));
|
||
|
|
||
|
return array("id"=>$wpdb->insert_id,"is_active"=>1);
|
||
|
}else{
|
||
|
return $results[0];
|
||
|
}
|
||
|
}
|
||
|
$results = $wpdb->get_results( 'SELECT * FROM '.$table.' where id='.$id.' limit 1',ARRAY_A );
|
||
|
if(count($results) == 0){
|
||
|
return array();
|
||
|
}
|
||
|
$feed=$results[0];
|
||
|
$fields=json_decode($feed['data'],true);
|
||
|
$meta=json_decode($feed['meta'],true);
|
||
|
$feed['meta']=is_array($meta) ? $meta : array();
|
||
|
$feed['data']=is_array($fields) ? $fields : array();
|
||
|
return $feed;
|
||
|
}
|
||
|
/**
|
||
|
* get log by id
|
||
|
*
|
||
|
* @param mixed $id
|
||
|
*/
|
||
|
public function get_log_by_id($log_id){
|
||
|
global $wpdb;
|
||
|
$table= $this->get_crm_table_name('log');
|
||
|
|
||
|
$sql = $wpdb->prepare("SELECT * FROM $table WHERE id=%d limit 1", $log_id);
|
||
|
$log = $wpdb->get_row($sql, ARRAY_A);
|
||
|
return $log;
|
||
|
}
|
||
|
/**
|
||
|
* get log by id
|
||
|
*
|
||
|
* @param mixed $id
|
||
|
*/
|
||
|
public function get_log_by_lead($lead_id,$form_id,$parent_logs=true,$limit=1){
|
||
|
global $wpdb;
|
||
|
$table= $this->get_crm_table_name('log');
|
||
|
$sql="SELECT * FROM $table WHERE ";
|
||
|
if($parent_logs){
|
||
|
$sql.='parent_id=0 and ';
|
||
|
}
|
||
|
$sql.='entry_id=%d and form_id=%s order by id DESC limit %d';
|
||
|
$sql = $wpdb->prepare($sql, $lead_id,$form_id,$limit);
|
||
|
$log = $wpdb->get_row($sql, ARRAY_A);
|
||
|
return $log;
|
||
|
}
|
||
|
/**
|
||
|
* update feed
|
||
|
*
|
||
|
* @param mixed $arr
|
||
|
* @param mixed $id
|
||
|
*/
|
||
|
public function update_feed($arr,$id){
|
||
|
global $wpdb;
|
||
|
if(!is_array($arr) || count($arr) == 0)
|
||
|
return;
|
||
|
foreach($arr as $k=>$v){
|
||
|
if(is_array($v)){
|
||
|
$arr[$k]=json_encode($v);
|
||
|
}
|
||
|
}
|
||
|
$table_name = $this->get_crm_table_name();
|
||
|
// update
|
||
|
// $wpdb->show_errors();
|
||
|
return $wpdb->update($table_name,$arr, array('id' => $id));
|
||
|
}
|
||
|
|
||
|
/**
|
||
|
* fields sorting order
|
||
|
*
|
||
|
* @param mixed $data
|
||
|
*/
|
||
|
public function update_feed_order($data){
|
||
|
global $wpdb;
|
||
|
$table_name = $this->get_crm_table_name();
|
||
|
|
||
|
if(!empty($data)) {
|
||
|
foreach($data as $order=>$id) {
|
||
|
$u=$wpdb->update($table_name,
|
||
|
array('sort' => $order),
|
||
|
array('id' => $id),
|
||
|
array('%d'),
|
||
|
array('%d')
|
||
|
);
|
||
|
/// var_dump($u);
|
||
|
}
|
||
|
}
|
||
|
|
||
|
return true;
|
||
|
}
|
||
|
/**
|
||
|
* Get New Settings Id
|
||
|
* @return int Settings id
|
||
|
*/
|
||
|
public function get_new_account() {
|
||
|
global $wpdb;
|
||
|
$table= $this->get_crm_table_name('accounts');
|
||
|
$results = $wpdb->get_results( 'SELECT * FROM '.$table.' where status=9 limit 1',ARRAY_A );
|
||
|
$id=0;
|
||
|
if(count($results) == 0){
|
||
|
$wpdb->insert($table,array("status"=>"9"));
|
||
|
$id=$wpdb->insert_id;
|
||
|
}else{
|
||
|
$id=$results[0]['id'];
|
||
|
}
|
||
|
return $id;
|
||
|
}
|
||
|
/**
|
||
|
* delete account
|
||
|
*
|
||
|
* @param mixed $id
|
||
|
*/
|
||
|
public function del_account($id) {
|
||
|
global $wpdb;
|
||
|
$table= $this->get_crm_table_name('accounts');
|
||
|
$res=$wpdb->delete( $table, array('id'=>$id) , array('%d'));
|
||
|
return $res;
|
||
|
}
|
||
|
/**
|
||
|
* get account by id
|
||
|
*
|
||
|
* @param mixed $id
|
||
|
*/
|
||
|
public function get_account($id) {
|
||
|
global $wpdb;
|
||
|
$table= $this->get_crm_table_name('accounts');
|
||
|
$sql = $wpdb->prepare('SELECT * FROM '.$table.' where id=%d limit 1', $id);
|
||
|
$res=$wpdb->get_row( $sql ,ARRAY_A );
|
||
|
return $res;
|
||
|
}
|
||
|
/**
|
||
|
* update account
|
||
|
*
|
||
|
* @param mixed $id
|
||
|
*/
|
||
|
public function update_info_data($sql, $id) {
|
||
|
global $wpdb;
|
||
|
$table= $this->get_crm_table_name('accounts');
|
||
|
$res=$wpdb->update( $table, $sql,array('id'=>$id));
|
||
|
return $res;
|
||
|
}
|
||
|
|
||
|
/**
|
||
|
* Get all accounts
|
||
|
*/
|
||
|
public function get_accounts($verified=false) {
|
||
|
global $wpdb;
|
||
|
$table= $this->get_crm_table_name('accounts');
|
||
|
$sql='SELECT * FROM '.$table.' where';
|
||
|
if($verified){
|
||
|
$sql.=' status =1';
|
||
|
}else{
|
||
|
$sql.=' status !=9';
|
||
|
}
|
||
|
$sql.=' limit 100';
|
||
|
$results = $wpdb->get_results( $sql ,ARRAY_A );
|
||
|
return $results;
|
||
|
}
|
||
|
/**
|
||
|
* drop tables
|
||
|
*
|
||
|
*/
|
||
|
public function drop_tables(){
|
||
|
global $wpdb;
|
||
|
$wpdb->query("DROP TABLE IF EXISTS " . $this->get_crm_table_name());
|
||
|
$wpdb->query("DROP TABLE IF EXISTS " . $this->get_crm_table_name('accounts'));
|
||
|
$wpdb->query("DROP TABLE IF EXISTS " . $this->get_crm_table_name('log'));
|
||
|
delete_option($this->type."_version");
|
||
|
}
|
||
|
}
|
||
|
}
|
||
|
?>
|