cf7-hubspot / includes / data.php

data.php in Integration for HubSpot and Contact Form 7, WPForms, Elementor, Ninja Forms trunk, at includes/data.php

559 lines 14.8 KB

Raw Download Zip

1	<?php
2	// Exit if accessed directly
3	if( !defined( 'ABSPATH' ) ) exit;
4
5
6	if( !class_exists( 'vxcf_hubspot_data' ) ) {
7
8	/**
9	* since 1.0
10	*/
11	class vxcf_hubspot_data extends vxcf_hubspot{
12	/**
13	* creates or updates tables
14	*
15	*/
16	public function update_table(){
17	global $wpdb;
18
19	$wpdb->hide_errors();
20	$table_name = $this->get_crm_table_name();
21
22	require_once(ABSPATH . '/wp-admin/includes/upgrade.php');
23
24	if ( ! empty($wpdb->charset) )
25	$charset_collate = "DEFAULT CHARACTER SET $wpdb->charset";
26	if ( ! empty($wpdb->collate) )
27	$charset_collate .= " COLLATE $wpdb->collate";
28
29	$sql = "CREATE TABLE $table_name (
30	id int(11) unsigned not null auto_increment,
31	form_id varchar(60) not null,
32	account mediumint(8) not null,
33	is_active tinyint(1) not null default 1,
34	sort mediumint(8) not null default 0,
35	name varchar(240) not null,
36	object varchar(200) not null,
37	meta longtext,
38	data longtext,
39	`time` datetime null,
40	PRIMARY KEY (id),
41	KEY form_id (form_id)
42	)$charset_collate; ";
43
44	dbDelta($sql);
45
46	$table_name = $this->get_crm_table_name('log');
47
48	$sql= "CREATE TABLE $table_name (
49	id int(11) unsigned not null auto_increment,
50	entry_id int(11) not null,
51	form_id varchar(60) not null,
52	feed_id int(11) not null,
53	parent_id int(11) not null,
54	crm_id varchar(200) not null,
55	object varchar(200) not null,
56	link varchar(250) not null,
57	meta varchar(250) not null,
58	event varchar(200) not null,
59	data text,
60	response text,
61	extra text,
62	`status` tinyint(1) not null default 1,
63	`time` datetime null,
64	PRIMARY KEY (id),
65	KEY entry_id (entry_id)
66	)$charset_collate;";
67
68	dbDelta($sql);
69
70	$table_name = $this->get_crm_table_name('accounts');
71
72	$sql= "CREATE TABLE $table_name (
73	id int(11) unsigned not null auto_increment,
74	name varchar(250) not null,
75	data longtext,
76	meta longtext,
77	`status` int(1) not null default 0,
78	`time` datetime null,
79	`updated` datetime null,
80	PRIMARY KEY (id)
81	)$charset_collate;";
82
83	dbDelta($sql);
84
85	}
86	/**
87	* Get tables names
88	*
89	* @param mixed $table
90	*/
91	public function get_crm_table_name($table=""){
92	global $wpdb;
93	if(!empty($table))
94	return $wpdb->prefix . $this->id."_".$table;
95	else
96	return $wpdb->prefix . $this->id;
97	}
98
99	/**
100	* get crm feeds
101	*
102	*/
103	public function get_feeds(){
104	global $wpdb;
105	$table_name = $this->get_crm_table_name();
106
107	$sql = "SELECT s.id, s.is_active,s.object,s.name,s.data,s.time, s.form_id, s.meta
108	FROM $table_name s where time is not NULL
109	ORDER BY s.sort";
110
111	$results = $wpdb->get_results($sql, ARRAY_A);
112
113	$count = sizeof($results);
114	/* for($i=0; $i<$count; $i++){
115	$results[$i]["meta"] = maybe_unserialize($results[$i]["meta"]);
116	}*/
117
118	return $results;
119	}
120	/**
121	* get log from database
122	*
123	*/
124	public function get_log(){
125	global $wpdb;
126	$sql_end=$this->get_log_query();
127	$sql_t="select count(s.id) as total $sql_end";
128	$result= $wpdb->get_results($sql_t);
129	$items=$result[0]->total;
130	$per_page = 20;
131	$start = 0;
132	$pages = ceil($items/$per_page);
133	if(isset($_GET['page_id']))
134	{
135	$page=(int)$this->post('page_id');
136	$start = $page-1;
137	$start = $start*$per_page;
138	}
139	$start=max($start,0);
140	$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
141	$sql_end
142	limit $start , $per_page";
143	$results = $wpdb->get_results($sql, ARRAY_A);
144
145	$page_id=isset($_REQUEST['page_id'])&& $_REQUEST['page_id'] !="" ? $this->post('page_id') : "1";
146	$range_min=(int)($per_page*($page_id-1))+1;
147	$range_max=(int)($per_page*($page_id-1))+count($results);
148	unset($_GET['page_id']);
149	$query_h=$this->clean($_GET);$query_h=http_build_query($query_h);
150	$page_links = paginate_links( array(
151	'base' => admin_url("admin.php")."?".$query_h."&%_%" ,
152	'format' => 'page_id=%#%',
153	'prev_text' =>'«',
154	'next_text' =>'»',
155	'total' => $pages,
156	'current' => $page_id,
157	'show_all' => false
158	));
159
160	return array("min"=>$range_min,"max"=>$range_max,"items"=>$items,"links"=>$page_links,"feeds"=>$results);
161	}
162	/**
163	* get logs query string
164	*
165	*/
166	public function get_log_query(){
167	$search="";
168	$table_name = $this->get_crm_table_name('log');
169	$sql_end="FROM $table_name s";
170	// handle search
171	$time_key=$this->post('time');
172	$time=current_time('timestamp');
173
174	$offset = $this->time_offset();
175	$start_date=""; $end_date="";
176	switch($time_key){
177	case"today": $start_date=strtotime('today',$time); break;
178	case"this_week": $start_date=strtotime('last sunday',$time); break;
179	case"last_7": $start_date=strtotime('-7 days',$time); break;
180	case"last_30": $start_date=strtotime('-30 days',$time); break;
181	case"this_month": $start_date=strtotime('first day of 0 month',$time); break;
182	case"yesterday":
183	$start_date=strtotime('yesterday',$time);
184	$end_date=strtotime('today',$time);
185
186	break;
187	case"last_month":
188	$start_date=strtotime('first day of -1 month',$time);
189	$end_date=strtotime('last day of -1 month',$time);
190
191	break;
192	case"custom":
193
194	if(!empty($_GET['start_date'])){
195	$start_date=strtotime($this->post('start_date').' 00:00:00');
196	}
197	if(!empty($_GET['end_date'])){
198	$end_date=strtotime($this->post('end_date').' 23:59:59');
199	}
200	break;
201	}
202
203	if($start_date!=""){
204	$start_date-=$offset;
205	$search.=' and s.time >="'.date('Y-m-d H:i:s',$start_date).'"';
206	}
207	if($end_date!=""){
208	$end_date-=$offset;
209	if($time_key == "yesterday"){
210	$search.=' and s.time <"'.date('Y-m-d H:i:s',$end_date).'"';
211	}else{
212	$search.=' and s.time <="'.date('Y-m-d H:i:s',$end_date).'"';
213	}
214	}
215	if($this->post('object')!=""){
216	$search.=' and object ="'.esc_sql($this->post('object')).'"';
217	}
218	if($this->post('status')!=""){
219	$status=$this->post('status');
220	if($status == "all"){$status="0";}
221	$search.=' and status ="'.esc_sql($status).'"';
222	}
223	if($this->post('search')!=""){
224	$search_s=esc_sql($this->post('search'));
225	$search.=' and (object like "'.$search_s.'" or crm_id="'.$search_s.'" or entry_id="'.$search_s.'")';
226	}
227	if(isset($_GET['log_id']) && !empty($_GET['log_id'])){
228	$log_id=esc_sql($this->post('log_id'));
229	$search.=' and id="'.$log_id.'"';
230	}
231	if($this->post('id')!=""){
232	$form_id=esc_sql($this->post('id'));
233	$search.=' and form_id="'.$form_id.'"';
234	}
235	if($this->post('entry_id')!=""){
236	$entry_id=esc_sql($this->post('entry_id'));
237	$search.=' and entry_id="'.$entry_id.'"';
238	}
239	if($this->post('feed_id')!=''){
240	$feed_id=esc_sql($this->post('feed_id'));
241	$search.=' and feed_id="'.$feed_id.'"';
242	}
243	if($search!=""){
244	$sql_end.=" where ".substr($search,4);
245	}
246	if($this->post('orderby')!=""){
247	$sql_end.=' order by '.sanitize_sql_orderby($this->post('orderby'));
248	if($this->post('order')!="" && in_array($this->post('order'),array("asc","desc"))){
249	$sql_end.=' '.$this->post('order');
250	}
251	}else{
252	$sql_end.=" order by s.id desc";
253	}
254	return $sql_end;
255	}
256	/**
257	* insert log in database
258	*
259	* @param mixed $arr
260	*/
261	public function insert_log($arr,$log_id=""){
262	global $wpdb;
263	if(!is_array($arr) \|\| count($arr) == 0)
264	return;
265	$wpdb->show_errors();
266	$table_name = $table_name = $this->get_crm_table_name('log');
267	$sql_arr=array();
268	foreach($arr as $k=>$v){
269	$sql_arr[$k]=is_array($v) ? json_encode($v) : $v;
270	}
271	//var_dump($sql_arr); die();
272	$log_id=(int)$log_id;
273	$res=false;
274	if(!empty($log_id)){
275	// update
276	$sql='update '.$table_name.' set '.implode('=%s, ',array_keys($sql_arr)).'=%s where id=%d';
277	$sql_arr['id']=$log_id;
278	}else{
279	$sql='insert into '.$table_name.'('.implode(',',array_keys($sql_arr)).') values('.implode(',',array_fill(1,count($sql_arr),'%s')).')';
280	}
281
282	$sql=$wpdb->prepare($sql,$sql_arr);
283	$res=$wpdb->query($sql);
284	if(!empty($wpdb->insert_id)){
285	$log_id=$wpdb->insert_id;
286	}
287	return $log_id;
288	}
289	/**
290	* clear logs
291	*
292	*/
293	public function clear_logs(){
294	global $wpdb;
295	$table_name = $this->get_crm_table_name('log');
296	// update
297	return $wpdb->query("truncate table `".$table_name."`");
298	}
299	/**
300	* delete feed
301	*
302	* @param mixed $id
303	*/
304	public function delete_feed($id){
305	global $wpdb;
306	$table_name = $this->get_crm_table_name();
307	$wpdb->query($wpdb->prepare("DELETE FROM $table_name WHERE id=%s", $id));
308	}
309	/**
310	* delete log entries
311	*
312	* @param mixed $id
313	*/
314	public function delete_log($log_ids){
315	global $wpdb;
316	$table=$this->get_crm_table_name('log');
317	$count=0;
318	foreach($log_ids as $id){
319	$del=$wpdb->delete($table,array('id'=>$id),array( '%d' ));
320	if($del){$count++;}
321	}
322	return $count;
323	}
324	/**
325	* get form
326	*
327	* @param mixed $form_id
328	* @param mixed $only_active
329	*/
330	public function get_feed_by_form($form_id, $only_active = false){
331	if(empty($form_id)){
332	return array();
333	}
334	global $wpdb;
335	$table_name = $this->get_crm_table_name();
336	$active_clause = $only_active ? " AND is_active=1" : "";
337	$sql = $wpdb->prepare("SELECT * FROM $table_name WHERE form_id=%s $active_clause ORDER BY sort", $form_id);
338	$results = $wpdb->get_results($sql, ARRAY_A);
339	if(empty($results))
340	return array();
341
342	return $results;
343	}
344	/**
345	* get object feeds
346	*
347	* @param mixed $form_id
348	* @param mixed $only_active
349	*/
350	public function get_object_feeds($form_id, $account , $object , $skip=''){
351	global $wpdb;
352	$table_name = $this->get_crm_table_name();
353	$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);
354	$results = $wpdb->get_results($sql, ARRAY_A);
355	if(empty($results))
356	return array();
357
358	return $results;
359	}
360	/**
361	* Get log of order and feed
362	*
363	* @param mixed $feed_id
364	* @param mixed $order_id
365	*/
366	public function get_feed_log($feed_id,$order_id,$object,$parent_id=""){
367	global $wpdb;
368	$table = $this->get_crm_table_name('log');
369	$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);
370	$results = $wpdb->get_row( $sql ,ARRAY_A );
371	return $results;
372	}
373	/**
374	* get single feed entry
375	*
376	* @param mixed $id
377	*/
378	public function get_feed($id){
379	global $wpdb;
380	$table= $this->get_crm_table_name();
381	if((string)$id =='new_form'){
382	$sql='SELECT * FROM '.$table.' where `time` is NULL limit 1';
383	$results = $wpdb->get_results( $sql,ARRAY_A );
384
385	if(count($results) == 0){
386	$wpdb->insert($table,array("is_active"=>"1"));
387
388	return array("id"=>$wpdb->insert_id,"is_active"=>1);
389	}else{
390	return $results[0];
391	}
392	}
393	$results = $wpdb->get_results( 'SELECT * FROM '.$table.' where id='.$id.' limit 1',ARRAY_A );
394	if(count($results) == 0){
395	return array();
396	}
397	$feed=$results[0];
398	$fields=json_decode($feed['data'],true);
399	$meta=json_decode($feed['meta'],true);
400	$feed['meta']=is_array($meta) ? $meta : array();
401	$feed['data']=is_array($fields) ? $fields : array();
402	return $feed;
403	}
404	/**
405	* get log by id
406	*
407	* @param mixed $id
408	*/
409	public function get_log_by_id($log_id){
410	global $wpdb;
411	$table= $this->get_crm_table_name('log');
412
413	$sql = $wpdb->prepare("SELECT * FROM $table WHERE id=%d limit 1", $log_id);
414	$log = $wpdb->get_row($sql, ARRAY_A);
415	return $log;
416	}
417	/**
418	* get log by id
419	*
420	* @param mixed $id
421	*/
422	public function get_log_by_lead($lead_id,$form_id,$parent_logs=true,$limit=1){
423	global $wpdb;
424	$table= $this->get_crm_table_name('log');
425	$sql="SELECT * FROM $table WHERE ";
426	if($parent_logs){
427	$sql.='parent_id=0 and ';
428	}
429	$sql.='entry_id=%d and form_id=%s order by id DESC limit %d';
430	$sql = $wpdb->prepare($sql, $lead_id,$form_id,$limit);
431	$log = $wpdb->get_row($sql, ARRAY_A);
432	return $log;
433	}
434	/**
435	* update feed
436	*
437	* @param mixed $arr
438	* @param mixed $id
439	*/
440	public function update_feed($arr,$id){
441	global $wpdb;
442	if(!is_array($arr) \|\| count($arr) == 0)
443	return;
444	foreach($arr as $k=>$v){
445	if(is_array($v)){
446	$arr[$k]=json_encode($v);
447	}
448	}
449	$table_name = $this->get_crm_table_name();
450	// update
451	// $wpdb->show_errors();
452	return $wpdb->update($table_name,$arr, array('id' => $id));
453	}
454
455	/**
456	* fields sorting order
457	*
458	* @param mixed $data
459	*/
460	public function update_feed_order($data){
461	global $wpdb;
462	$table_name = $this->get_crm_table_name();
463
464	if(!empty($data)) {
465	foreach($data as $order=>$id) {
466	$u=$wpdb->update($table_name,
467	array('sort' => $order),
468	array('id' => $id),
469	array('%d'),
470	array('%d')
471	);
472	/// var_dump($u);
473	}
474	}
475
476	return true;
477	}
478	/**
479	* Get New Settings Id
480	* @return int Settings id
481	*/
482	public function get_new_account() {
483	global $wpdb;
484	$table= $this->get_crm_table_name('accounts');
485	$results = $wpdb->get_results( 'SELECT * FROM '.$table.' where status=9 limit 1',ARRAY_A );
486	$id=0;
487	if(count($results) == 0){
488	$wpdb->insert($table,array("status"=>"9"));
489	$id=$wpdb->insert_id;
490	}else{
491	$id=$results[0]['id'];
492	}
493	return $id;
494	}
495	/**
496	* delete account
497	*
498	* @param mixed $id
499	*/
500	public function del_account($id) {
501	global $wpdb;
502	$table= $this->get_crm_table_name('accounts');
503	$res=$wpdb->delete( $table, array('id'=>$id) , array('%d'));
504	return $res;
505	}
506	/**
507	* get account by id
508	*
509	* @param mixed $id
510	*/
511	public function get_account($id) {
512	global $wpdb;
513	$table= $this->get_crm_table_name('accounts');
514	$sql = $wpdb->prepare('SELECT * FROM '.$table.' where id=%d limit 1', $id);
515	$res=$wpdb->get_row( $sql ,ARRAY_A );
516	return $res;
517	}
518	/**
519	* update account
520	*
521	* @param mixed $id
522	*/
523	public function update_info_data($sql, $id) {
524	global $wpdb;
525	$table= $this->get_crm_table_name('accounts');
526	$res=$wpdb->update( $table, $sql,array('id'=>$id));
527	return $res;
528	}
529
530	/**
531	* Get all accounts
532	*/
533	public function get_accounts($verified=false) {
534	global $wpdb;
535	$table= $this->get_crm_table_name('accounts');
536	$sql='SELECT * FROM '.$table.' where';
537	if($verified){
538	$sql.=' status =1';
539	}else{
540	$sql.=' status !=9';
541	}
542	$sql.=' limit 100';
543	$results = $wpdb->get_results( $sql ,ARRAY_A );
544	return $results;
545	}
546	/**
547	* drop tables
548	*
549	*/
550	public function drop_tables(){
551	global $wpdb;
552	$wpdb->query("DROP TABLE IF EXISTS " . $this->get_crm_table_name());
553	$wpdb->query("DROP TABLE IF EXISTS " . $this->get_crm_table_name('accounts'));
554	$wpdb->query("DROP TABLE IF EXISTS " . $this->get_crm_table_name('log'));
555	delete_option($this->type."_version");
556	}
557	}
558	}
559	?>