function show_submits() { $mainframe = JFactory::getApplication(); $user = JFactory::getUser(); $db = JFactory::getDBO(); $where = array(); $own_manage = false; if (!$user->authorise('core.manage.submits', 'com_formmaker')) { if ($user->authorise('core.manage.submits.own', 'com_formmaker')) { $own_manage = true; } else { $mainframe->redirect("index.php?option=com_formmaker", JText::_('JACCESS_NOT_PERMITTED'), 'error'); } } jimport('joomla.html.pagination'); if ($own_manage) { $query = "SELECT id, title FROM #__formmaker WHERE created_by = '" . $user->id . "' order by title"; } else { $query = "SELECT id, title FROM #__formmaker order by title"; } $db->setQuery($query); $forms = $db->loadObjectList(); if ($db->getErrorNum()) { echo $db->stderr(); return false; } $option = 'com_formmaker'; $task = JRequest::getCmd('task'); $form_id = $mainframe->getUserStateFromRequest($option . 'form_id', 'form_id', 'id', 'cmd'); if ($form_id) { if ($own_manage) { $query = "SELECT id FROM #__formmaker where created_by = '" . $user->id . "' AND id=" . $db->escape((int) $form_id); } else { $query = "SELECT id FROM #__formmaker where id=" . $db->escape((int) $form_id); } $db->setQuery($query); $exists = $db->LoadResult(); if (!$exists) { $form_id = 0; } } $filter_order = $mainframe->getUserStateFromRequest($option . 'filter_order2', 'filter_order2', 'id', 'cmd'); $filter_order_Dir = $mainframe->getUserStateFromRequest($option . 'filter_order_Dir2', 'filter_order_Dir2', '', 'word'); $id_search = $mainframe->getUserStateFromRequest($option . 'id_search', 'id_search', '', 'string'); $id_search = JString::strtolower($id_search); $ip_search = $mainframe->getUserStateFromRequest($option . 'ip_search', 'ip_search', '', 'string'); $ip_search = JString::strtolower($ip_search); $username_search = $mainframe->getUserStateFromRequest($option . 'username_search', 'username_search', '', 'string'); $username_search = JString::strtolower($username_search); $useremail_search = $mainframe->getUserStateFromRequest($option . 'useremail_search', 'useremail_search', '', 'string'); $useremail_search = JString::strtolower($useremail_search); $limit = $mainframe->getUserStateFromRequest('global.list.limit', 'limit', $mainframe->getCfg('list_limit'), 'int'); $limitstart = $mainframe->getUserStateFromRequest($option . '.limitstart', 'limitstart', 0, 'int'); $lists['startdate'] = JRequest::getVar('startdate', ""); $lists['enddate'] = JRequest::getVar('enddate', ""); $lists['hide_label_list'] = JRequest::getVar('hide_label_list', ""); if ($id_search) { $where[] = 'group_id =' . $db->escape($id_search); } if ($ip_search) { $where[] = 'ip LIKE "%' . $db->escape($ip_search) . '%"'; } if ($username_search) { $where[] = 'user_id IN (SELECT `id` FROM `#__users` WHERE `username` LIKE "%' . $db->escape($username_search) . '%")'; } if ($useremail_search) { $where[] = 'user_id IN (SELECT `id` FROM `#__users` WHERE `email` LIKE "%' . $db->escape($useremail_search) . '%")'; } if ($lists['startdate'] != '') { $where[] = " `date`>='" . $lists['startdate'] . " 00:00:00' "; } if ($lists['enddate'] != '') { $where[] = " `date`<='" . $lists['enddate'] . " 23:59:59' "; } if ($form_id == '') { if ($forms) { $form_id = $forms[0]->id; } } $where[] = 'form_id="' . $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 == 'username' or $filter_order == 'email') { $orderby = ' ORDER BY (SELECT `' . $filter_order . '` FROM `#__users` WHERE id=user_id) ' . $filter_order_Dir . ''; } } } $query = "SELECT distinct element_label FROM #__formmaker_submits WHERE " . $where; $db->setQuery($query); $labels = $db->loadColumn(); if ($db->getErrorNum()) { echo $db->stderr(); return false; } $query = "SELECT id FROM #__formmaker_submits WHERE form_id=" . $form_id . " and element_label=0 limit 0, 1"; $db->setQuery($query); $ispaypal = $db->loadResult(); if ($db->getErrorNum()) { echo $db->stderr(); return false; } $query = 'SELECT count(distinct group_id) FROM #__formmaker_submits where form_id ="' . $form_id . '"'; $db->setQuery($query); $total_entries = $db->loadResult(); if ($db->getErrorNum()) { echo $db->stderr(); return false; } $sorted_labels_type = array(); $sorted_labels_id = array(); $sorted_labels = array(); $label_titles = array(); $rows_ord = array(); $rows = array(); $total = 0; $join_count = ''; if ($labels) { $label_id = array(); $label_order = array(); $label_order_original = array(); $label_type = array(); $this_form = JTable::getInstance('formmaker', 'Table'); $this_form->load($form_id); 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 = $mainframe->getUserStateFromRequest($option . $form_id . '_' . $label . '_search', $form_id . '_' . $label . '_search', '', 'string'); $search_temp = JString::strtolower($search_temp); $lists[$form_id . '_' . $label . '_search'] = $search_temp; if ($search_temp) { $join_query[] = 'search'; $join_where[] = array('label' => $label, 'search' => $db->escape($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 #__formmaker_submits WHERE ' . $where; break; case 1: if ($join_query[0] == 'sort') { $join = 'SELECT group_id FROM #__formmaker_submits WHERE ' . $where . ' AND element_label="' . $join_where[0]['label'] . '" '; $join_count = 'SELECT count(group_id) FROM #__formmaker_submits WHERE form_id="' . $form_id . '" AND element_label="' . $join_where[0]['label'] . '" '; $orderby = ' ORDER BY `element_value` ' . $filter_order_Dir . ''; } else { $join = 'SELECT group_id FROM #__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 #__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 #__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 #__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); } $db->setQuery($query); $total = $db->loadResult(); $pageNav = new JPagination($total, $limitstart, $limit); $query = $join . ' ' . $orderby . ' '; $db->setQuery($query, $pageNav->limitstart, $pageNav->limit); $rows_ord = $db->loadColumn(); if ($db->getErrorNum()) { echo $db->stderr(); return false; } $where2 = array(); $where2[] = "group_id='0'"; foreach ($rows_ord as $rows_ordd) { $where2[] = "group_id='" . $rows_ordd . "'"; } $where2 = count($where2) ? ' WHERE ' . implode(' OR ', $where2) . '' : ''; $query = "SELECT * FROM #__formmaker_submits " . $where2 . ''; $db->setQuery($query); $rows = $db->loadObjectList(); if ($db->getErrorNum()) { echo $db->stderr(); return false; } if ($join_count) { $db->setQuery($join_count); $total_sort = $db->loadResult(); if ($total_sort != $total_entries) { $join_count = $total_sort; } else { $join_count = ''; } } } $query = 'SELECT views FROM #__formmaker_views WHERE form_id="' . $db->escape((int) $form_id) . '"'; $db->setQuery($query); $total_views = $db->loadResult(); $pageNav = new JPagination($total, $limitstart, $limit); $lists['order_Dir'] = $filter_order_Dir; $lists['order'] = $filter_order; // search filter $lists['id_search'] = $id_search; $lists['ip_search'] = $ip_search; $lists['username_search'] = $username_search; $lists['useremail_search'] = $useremail_search; // display function HTML_contact::show_submits($rows, $forms, $lists, $pageNav, $sorted_labels, $label_titles, $rows_ord, $form_id, $sorted_labels_id, $sorted_labels_type, $total_entries, $total_views, $join_count); }