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);
}
