FMModelForm_submissions.php

/wp-content/plugins/form-maker/frontend/models/FMModelForm_submissions.php

https://gitlab.com/hunt9310/ras
PHP | 296 lines | 240 code | 32 blank | 24 comment | 58 complexity | 06cede83011100edd3c8106908d12882 MD5 | raw file

<?php



class FMModelForm_submissions {

  ////////////////////////////////////////////////////////////////////////////////////////

  // Events                                                                             //

  ////////////////////////////////////////////////////////////////////////////////////////

  ////////////////////////////////////////////////////////////////////////////////////////

  // Constants                                                                          //

  ////////////////////////////////////////////////////////////////////////////////////////

  ////////////////////////////////////////////////////////////////////////////////////////

  // Variables                                                                          //

  ////////////////////////////////////////////////////////////////////////////////////////

  ////////////////////////////////////////////////////////////////////////////////////////

  // Constructor & Destructor                                                           //

  ////////////////////////////////////////////////////////////////////////////////////////

  public function __construct() {

  }

  ////////////////////////////////////////////////////////////////////////////////////////

  // Public Methods                                                                     //

  ////////////////////////////////////////////////////////////////////////////////////////

  function showsubmissions($form_id, $startdate, $enddate, $submit_date, $submitter_ip, $username, $useremail, $form_fields, $csv, $xml, $title, $search, $ordering, $entries, $views, $conversion_rate, $pagination, $stats) {

		global $wpdb;

	

		$user = wp_get_current_user();

		$userGroups = $wpdb->get_var($wpdb->prepare("SELECT `user_id_wd` FROM " . $wpdb->prefix . "formmaker WHERE id='%d'", $form_id));    

		$users = explode(',', $userGroups);

		$users = array_slice($users, 0, count($users) - 1); 

		$show_submits = false;

		if(!is_user_logged_in()) {

			if(!in_array('guest', $users))

				return false;

		}

		else {

			foreach($user->roles as $user_role) {

				if(in_array($user_role, $users))

					$show_submits = true;

			}

			if(!$show_submits)

				return false;

		}

		$from = $startdate;

		$to = $enddate;

		

		$filter_order = ((isset($_POST['order_by']) && esc_html(stripslashes($_POST['order_by'])) != '') ? esc_html(stripslashes($_POST['order_by'])) : 'group_id');

		$filter_order_Dir = ((isset($_POST['asc_or_desc']) && ($_POST['asc_or_desc'] == 'asc' || $_POST['asc_or_desc'] == 'desc')) ? esc_html($_POST['asc_or_desc']) : 'asc');

		

		$ip_search = $submitter_ip;

		$username_search = strtolower($username);

		$useremail_search = strtolower($useremail);



		$where = array();

		$lists['startdate'] = ((isset($_POST['startdate'])) ? esc_html(stripslashes($_POST['startdate'])) : '');

		$lists['enddate'] = ((isset($_POST['enddate'])) ? esc_html(stripslashes($_POST['enddate'])) : '');

		$lists['hide_label_list'] = ((isset($_POST['hide_label_list'])) ? esc_html(stripslashes($_POST['hide_label_list'])) : '');

    

		$lists['ip_search'] = ((isset($_POST['ip_search'])) ? esc_html(stripslashes($_POST['ip_search'])) : '');

		$lists['username_search'] = ((isset($_POST['username_search'])) ? esc_html(stripslashes($_POST['username_search'])) : '');

		$lists['useremail_search'] = ((isset($_POST['useremail_search'])) ? esc_html(stripslashes($_POST['useremail_search'])) : '');

    $limit = ((isset($_POST['page_number'])) ? ((int) $_POST['page_number'] - 1) * 20 : 0);

		

		if ( $lists['ip_search'] ) {

			$where[] = 'ip LIKE "%' . esc_sql($lists['ip_search']) . '%"';

		}		

		if ( $lists['username_search'] ) {

			$where[] = 'user_id_wd IN (SELECT `id` FROM `' . $wpdb->prefix . 'users` WHERE `display_name` LIKE "%' . esc_sql($lists['username_search']) . '%")';

		}

		if ( $lists['useremail_search'] ) {

			$where[] = 'user_id_wd IN (SELECT `id` FROM `' . $wpdb->prefix . 'users` WHERE `user_email` LIKE "%' . esc_sql($lists['useremail_search']) . '%")';

		}

		

		if($from) {

			if($lists['startdate'] != '') {

				if(strtotime($from) > strtotime($lists['startdate']))

					$where[] = "`date` >= '" . $from . " 00:00:00'";

				else

					$where[] ="`date`>='" . $lists['startdate'] . " 00:00:00' ";

			}

			else

				$where[] = "`date` >= '" . $from . " 00:00:00'";

		}

		else {

			if($lists['startdate']!='')

        $where[] ="  `date`>='".$lists['startdate']." 00:00:00' ";

		}

		if($to) {

			if($lists['enddate']!='') {

				if(strtotime($to) < strtotime($lists['enddate']))

					$where[] = "`date` <= '".$to." 23:59:59'";

				else

					$where[] ="`date`<='".$lists['enddate']." 23:59:59' ";

			}

			else

				$where[] = "`date` <= '".$to." 23:59:59'";

		}

		else {

			if($lists['enddate']!='')

        $where[] ="`date`<='".$lists['enddate']." 23:59:59' ";

		}

					

		$form_title = $wpdb->get_var($wpdb->prepare("SELECT `title` FROM " . $wpdb->prefix . "formmaker WHERE id='%d'", $form_id));



		$where[] = 'form_id="' . (int)$form_id . '"';

		$where 		= ( count( $where ) ? '  ' . implode( ' AND ', $where ) : '' );

		$orderby 	= ' ';

		if ($filter_order == 'id' or $filter_order == 'title' or $filter_order == 'mail') {

			$orderby 	= ' ORDER BY `date` desc';

		}

		else {

			if ($filter_order == 'group_id' or $filter_order == 'date' or $filter_order == 'ip') {

					$orderby 	= ' ORDER BY '.$filter_order .' '. $filter_order_Dir .'';

			} 

			else {

				if ($filter_order == 'display_name' or $filter_order == 'user_email') {

					$orderby 	= ' ORDER BY (SELECT `'.$filter_order.'` FROM `' . $wpdb->prefix . 'users` WHERE id=user_id_wd) '. $filter_order_Dir .'';

				}

      }

		}

		$query = "SELECT distinct element_label FROM " . $wpdb->prefix . "formmaker_submits WHERE ". $where;

		$labels = $wpdb->get_col($query);



		$query = $wpdb->prepare("SELECT id FROM " . $wpdb->prefix . "formmaker_submits WHERE form_id='%d' and element_label=0 limit 0, 1", $form_id);    

		$ispaypal = $wpdb->get_var($query);



		$query = $wpdb->prepare('SELECT count(distinct group_id) FROM ' . $wpdb->prefix . 'formmaker_submits where form_id ="%d"', $form_id);    

		$total_entries = $wpdb->get_var($query);

	

		$sorted_labels_type= array();

		$sorted_labels_id= array();

		$sorted_labels= array();

		$label_titles=array();

		$rows_ord = array();

		$rows = array();

		$total = 0;	

		$join_count='';

    $checked_ids = '';

    $stats_fields = '';

    

		if($labels) {

			$label_id= array();

			$label_order= array();

			$label_order_original= array();

			$label_type= array();			

			$this_form = $wpdb->get_row($wpdb->prepare("SELECT * FROM " . $wpdb->prefix . "formmaker WHERE id='%d'", $form_id));

      $checked_ids = $this_form->frontend_submit_fields;

      $stats_fields = $this_form->frontend_submit_stat_fields;

			

			if(strpos($this_form->label_order, 'type_paypal_')) {

				$this_form->label_order=$this_form->label_order."item_total#**id**#Item Total#**label**#type_paypal_payment_total#****#total#**id**#Total#**label**#type_paypal_payment_total#****#0#**id**#Payment Status#**label**#type_paypal_payment_status#****#";

			}

			$label_all	= explode('#****#',$this_form->label_order);

			$label_all 	= array_slice($label_all,0, count($label_all)-1);

			foreach($label_all as $key => $label_each) {

				$label_id_each=explode('#**id**#',$label_each);

				array_push($label_id, $label_id_each[0]);

				

				$label_order_each=explode('#**label**#', $label_id_each[1]);	

				array_push($label_order_original, $label_order_each[0]);

				

				$ptn = "/[^a-zA-Z0-9_]/";

				$rpltxt = "";

				$label_temp=preg_replace($ptn, $rpltxt, $label_order_each[0]);

				array_push($label_order, $label_temp);		

				array_push($label_type, $label_order_each[1]);

			}

			

			$join_query=array();

			$join_where=array();

			$join='';

			$is_first=true;

			

			foreach($label_id as $key => $label) {

				if(in_array($label, $labels)) {

					array_push($sorted_labels_type, $label_type[$key]);

					array_push($sorted_labels, $label_order[$key]);

					array_push($sorted_labels_id, $label);

					array_push($label_titles, $label_order_original[$key]);

					$search_temp = isset($_POST[$form_id.'_'.$label.'_search']) ? $_POST[$form_id.'_'.$label.'_search'] : '';

					$search_temp = strtolower( $search_temp );

					$lists[$form_id.'_'.$label.'_search']	 = $search_temp;					

					if ( $search_temp ) {

						$join_query[]	='search';

						$join_where[]	=array('label'=>$label, 'search'=>esc_sql($search_temp));

					}

				}

      }

			if(strpos($filter_order,"_field")) {

				if (in_array(str_replace("_field", "", $filter_order), $labels)) {

					$join_query[]	='sort';

					$join_where[]	=array('label'=>str_replace("_field", "", $filter_order));

				}

			}

			$cols 	= 'group_id';

			if ($filter_order == 'date' or $filter_order == 'ip') {

				$cols 	= 'group_id, date, ip';

			}

		

			switch(count($join_query)) {

				case 0:

					$join='SELECT distinct group_id FROM ' . $wpdb->prefix . 'formmaker_submits WHERE '. $where;

          break;

				case 1:

					if($join_query[0]=='sort') {

						$join		=	'SELECT group_id FROM ' . $wpdb->prefix . 'formmaker_submits WHERE '.$where.' AND element_label="'.$join_where[0]['label'].'" ';

						$join_count	=	'SELECT count(group_id) FROM ' . $wpdb->prefix . 'formmaker_submits WHERE form_id="'.esc_sql((int)$form_id).'" AND element_label="'.$join_where[0]['label'].'" ';

						$orderby 	= 	' ORDER BY `element_value` '. $filter_order_Dir .'';

					}

					else

						$join='SELECT group_id FROM ' . $wpdb->prefix . 'formmaker_submits WHERE element_label="'.$join_where[0]['label'].'" AND  element_value LIKE "%'.$join_where[0]['search'].'%" AND '. $where;

          break;						

				default:

					$join='SELECT t.group_id FROM (SELECT '.$cols.'  FROM ' . $wpdb->prefix . 'formmaker_submits WHERE '.$where.' AND element_label="'.$join_where[0]['label'].'" AND  element_value LIKE "%'.$join_where[0]['search'].'%" ) as t ';					

					for($key=1; $key< count($join_query); $key++) {

						if($join_query[$key]=='sort') {

							$join.='LEFT JOIN (SELECT group_id as group_id'.$key.', element_value   FROM ' . $wpdb->prefix . 'formmaker_submits WHERE '.$where.' AND element_label="'.$join_where[$key]['label'].'") as t'.$key.' ON t'.$key.'.group_id'.$key.'=t.group_id ';

							$orderby 	= 	' ORDER BY t'.$key.'.`element_value` '. $filter_order_Dir .'';

						}

						else

							$join.='INNER JOIN (SELECT group_id as group_id'.$key.' FROM ' . $wpdb->prefix . 'formmaker_submits WHERE '.$where.' AND element_label="'.$join_where[$key]['label'].'" AND  element_value LIKE "%'.$join_where[$key]['search'].'%" ) as t'.$key.' ON t'.$key.'.group_id'.$key.'=t.group_id ';

          }

          break;

			}

			

			$pos = strpos($join, 'SELECT t.group_id');		

			if ($pos === false) 

				$query = str_replace(array('SELECT group_id','SELECT distinct group_id'), array('SELECT count(distinct group_id)','SELECT count(distinct group_id)'),  $join);

			else

				$query = str_replace('SELECT t.group_id', 'SELECT count(t.group_id)',  $join);

			$total = $wpdb->get_var($query);

			

			$query = $join.' '.$orderby . ($pagination ? ' limit ' . $limit . ', 20 ' : '') . ' ';

			

			$rows_ord = $wpdb->get_col($query);

			

			$where2 = array();

				$where2 [] ="group_id='0'";			

			foreach($rows_ord as $rows_ordd) {

				$where2 [] ="group_id='".esc_sql($rows_ordd)."'";

			}

			$where2 = ( count( $where2 ) ? ' WHERE ' . implode( ' OR ', $where2 ).'' : '' );

			$query = "SELECT * FROM " . $wpdb->prefix . "formmaker_submits ".$where2.'';



			$rows = $wpdb->get_results($query);

			

			if($join_count) {

				$total_sort = $wpdb->get_var($join_count);

				if($total_sort != $total_entries)

					$join_count = $total_sort;

				else

					$join_count = '';

			}

		}

		

		$query = $wpdb->prepare('SELECT views FROM ' . $wpdb->prefix . 'formmaker_views WHERE form_id="%d"', $form_id);    

		$total_views = $wpdb->get_var($query);



		$pageNav = "";// $pageNav = new JPagination( $total, $limitstart, $limit );	



		$lists['order_Dir']	= $filter_order_Dir;

		$lists['order']		= $filter_order;

    $lists['total'] = $total;

    $lists['limit'] = (int) ($limit / 20 + 1);

		return array("rows" => $rows, "lists" => $lists, "pageNav" => $pageNav, "sorted_labels" => $sorted_labels, "label_titles" => $label_titles, "rows_ord" => $rows_ord, "sorted_labels_id" => $sorted_labels_id, "sorted_labels_type" => $sorted_labels_type, "total_entries" => $total_entries, "total_views" => $total_views, "join_count" => $join_count, "form_title" => $form_title, "checked_ids" => $checked_ids, "stats_fields" => $stats_fields);

	}

  

  function show_stats() {

		global $wpdb;

		$form_id = (isset($_POST['form_id']) ? esc_html(stripslashes($_POST['form_id'])) : 0);

		$id = (isset($_POST['stat_id']) ? esc_html(stripslashes($_POST['stat_id'])) : 0);

		$from = (isset($_POST['from']) ? esc_html(stripslashes($_POST['from'])) : 0);

		$to = (isset($_POST['to']) ? esc_html(stripslashes($_POST['to'])) : 0);

		$where = ' AND form_id=' . $form_id;		

		if($from != '')

			$where .= " AND `date`>='".$from." 00:00:00' ";

		if($to != '')

			$where .= " AND `date`<='".$to." 23:59:59' ";

		$query = "SELECT element_value FROM " . $wpdb->prefix . "formmaker_submits WHERE element_label='" . $id . "'" . $where;

		$choices = $wpdb->get_col($query);

		return $choices;

	}

  

  function paypal_info($id) {

    global $wpdb;

		$query = $wpdb->prepare("SELECT * FROM " . $wpdb->prefix . "formmaker_sessions where group_id='%d'", $id);    

		return $wpdb->get_row($query);

  }



  ////////////////////////////////////////////////////////////////////////////////////////

  // Getters & Setters                                                                  //

  ////////////////////////////////////////////////////////////////////////////////////////

  ////////////////////////////////////////////////////////////////////////////////////////

  // Private Methods                                                                    //

  ////////////////////////////////////////////////////////////////////////////////////////

  ////////////////////////////////////////////////////////////////////////////////////////

  // Listeners                                                                          //

  ////////////////////////////////////////////////////////////////////////////////////////

}