<?php 
  
 /**
 * @package Form Maker
 * @author Web-Dorado
 * @copyright (C) 2011 Web-Dorado. All rights reserved.
 * @license GNU/GPLv3 http://www.gnu.org/licenses/gpl-3.0.html
 **/

defined('_JEXEC') or die('Restricted access'); 
jimport( 'joomla.application.component.model' );
jimport('joomla.filesystem.file');
jimport('joomla.filesystem.folder');
class formmakerModelsubmissions extends JModelLegacy
{	
	function paypal_info()
	{
		$id 	= JRequest::getVar('id');
		$db = JFactory::getDBO();
		$query = "SELECT * FROM #__formmaker_sessions where group_id=".$db->escape((int)$id);
		$db->setQuery( $query);
		$row = $db->loadObject();
		if($db->getErrorNum()){
			echo $db->stderr();
			return false;
		}	
		
		if(!isset($row->ipn))
		{
		echo "<div style='width:100%; text-align:center; height: 100%; vertical-align:middle'><h1 style='top: 44%;position: absolute;left:38%; color:#000'>No information yet<p></h1>";
		return;
		}
	
		$paypal_info ='
			<h2>Payment Info</h2>
			<table class="admintable">
				<tr>
					<td class="key">Currency</td>
					<td>'.$row->currency.'</td>
				</tr>
				<tr>
					<td class="key">Last modified</td>
					<td>'.$row->ord_last_modified.'</td>
				</tr>
				<tr>
					<td class="key">Status</td>
					<td>'.$row->status.'</td>
				</tr>
				<tr>
					<td class="key">Full name</td>
					<td>'.$row->full_name.'</td>
				</tr>
				<tr>
					<td class="key">Email</td>
					<td>'.$row->email.'</td>
				</tr>
				<tr>
					<td class="key">Phone</td>
					<td>'.$row->phone.'</td>
				</tr>
				<tr>
					<td class="key">Mobile phone</td>
					<td>'.$row->mobile_phone.'</td>
				</tr>
				<tr>
					<td class="key">Fax</td>
					<td>'.$row->fax.'</td>
				</tr>
				<tr>
					<td class="key">Address</td>
					<td>'.$row->address.'</td>
				</tr>
				<tr>
					<td class="key">PayPal info</td>
					<td>'.$row->paypal_info.'</td>
				</tr>	
				<tr>
					<td class="key">IPN</td>
					<td>'.$row->ipn.'</td>
				</tr>
				<tr>
					<td class="key">Tax</td>
					<td>'.$row->tax.'%</td>
				</tr>
				<tr>
					<td class="key">Shipping</td>
					<td>'.$row->shipping.'</td>
				</tr>
				<tr>
					<td class="key">Total</td>
					<td><b>'.$row->total.'</b></td>
				</tr>
			</table>';
		echo $paypal_info;

	}
	

	function show_matrix()
	{
		$matrix_params 	= htmlspecialchars(JRequest::getVar('matrix_params'), ENT_QUOTES);
		$new_filename= str_replace("***matrix***",'', $matrix_params);	

		$new_filename=explode('***', $matrix_params);
		$mat_params=array_slice($new_filename,0, count($new_filename)-1);
		$mat_rows=$mat_params[0];
		$mat_columns=$mat_params[$mat_rows+1];
							
		$matrix="<table >";					
		$matrix .='<tr><td></td>';
			
		for( $k=1;$k<=$mat_columns;$k++)
			$matrix .='<td style="background-color:#BBBBBB; padding:5px;">'.$mat_params[$mat_rows+1+$k].'</td>';
	
		$matrix .='</tr>';
				
		$aaa=Array();
		$var_checkbox=1;
		for( $k=1;$k<=$mat_rows;$k++)
		{
			$matrix .='<tr><td style="background-color:#BBBBBB; padding:5px; ">'.$mat_params[$k].'</td>';
			if($mat_params[$mat_rows+$mat_columns+2]=="radio")
			{  
				if($mat_params[$mat_rows+$mat_columns+2+$k]==0)
				{
					$checked=0;
					$aaa[1]="";	
				}
				else
					$aaa=explode("_",$mat_params[$mat_rows+$mat_columns+2+$k]);
				
				for( $l=1;$l<=$mat_columns;$l++)
				{
					if($aaa[1]==$l)
						$checked="checked";
					else
						$checked="";
					$matrix .='<td style="text-align:center"><input  type="radio" '.$checked.' disabled /></td>';	
				}
								
			} 
			else
			{
				if($mat_params[$mat_rows+$mat_columns+2]=="checkbox")
				{						  
					for( $l=1;$l<=$mat_columns;$l++)
					{
						if($mat_params[$mat_rows+$mat_columns+2+$var_checkbox]=="1")
							$checked="checked";
						else
							$checked="";
	
						$matrix .='<td style="text-align:center"><input  type="checkbox" '.$checked.' disabled /></td>';
						$var_checkbox++;
					}
								
				}
				else
				{
					if($mat_params[$mat_rows+$mat_columns+2]=="text")
					{						  
						for( $l=1;$l<=$mat_columns;$l++)
						{
							$checked = $mat_params[$mat_rows+$mat_columns+2+$var_checkbox];
							$matrix .='<td style="text-align:center"><input  type="text" value="'.$checked.'" disabled /></td>';
							$var_checkbox++;
						}
					}
					else
					{
						for( $l=1;$l<=$mat_columns;$l++)
						{
							$checked = $mat_params[$mat_rows+$mat_columns+2+$var_checkbox];		
							$matrix .='<td style="text-align:center">'.$checked.'</td>';
							$var_checkbox++;
						}
					}			
				}					
			}
			
			$matrix .='</tr>';
		}
		
		$matrix .='</table>';
		echo $matrix;

	}

	function show_map()
	{
	
		$long 	= htmlspecialchars(JRequest::getVar('long'), ENT_QUOTES);
		$lat 	= htmlspecialchars(JRequest::getVar('lat'), ENT_QUOTES);
		
		$document = JFactory::getDocument();
		$document->addScript(JURI::root(true).'/administrator/components/com_formmaker/js/if_gmap.js');
		$document->addScript('http://maps.google.com/maps/api/js?sensor=false');
		?>
		<table style="margin:0px; padding:0px">
			<tr>
				<td>
					<b>Address:</b>
				</td>
				<td>
					<input type="text" id="addrval0" style="border:0px; background:none" size="100" readonly />
				</td>
			</tr>
			<tr>
				<td>
					<b>Longitude:</b>
				</td> 
				<td>
					<input type="text" id="longval0" style="border:0px; background:none" size="100" readonly />
				</td>
			</tr>
			<tr>
				<td>
					<b>Latitude:</b>
				</td>
				<td>
					<input type="text" id="latval0" style="border:0px; background:none" size="100" readonly /> 
				</td>
			</tr>
		</table>
			
		<div id="0_elementform_id_temp" long="<?php echo $long ?>" center_x="<?php echo $long ?>" center_y="<?php echo $lat ?>" lat="<?php echo $lat ?>" zoom="8" info="" style="width:600px; height:500px; "></div>
		<script>
			if_gmap_init("0");
			add_marker_on_map(0, 0, "<?php echo $long ?>", "<?php echo $lat ?>", '');
		</script>
		<?php		
	}

	function generate_csv()
	{

		$mainframe = JFactory::getApplication();
		$user = JFactory::getUser();
		$db		= JFactory::getDBO();
		$id 	= JRequest::getVar('id');
		
		$db->setQuery("SELECT `user_id` FROM #__formmaker WHERE id=".$db->escape((int)$id)); 
		$userGroups = $db->loadResult();
		if ($db->getErrorNum())	{echo $db->stderr(); return false;}	
		
		$users = explode(',', $userGroups);
		$users 	= array_slice($users,0, count($users)-1); 

		$allow_export = false;
		$msg = 'You have no permissions to download csv';
		if($user->guest)
		{
			if(!in_array('9',$users))
			{
				$mainframe->redirect($_SERVER["HTTP_REFERER"], $msg, 'error');
			}
		}
		else
		{
			foreach($user->groups as $user_id)
			{
				if(in_array($user_id, $users))
					$allow_export = true;
			}
			
			if(!$allow_export)
			{
				$mainframe->redirect($_SERVER["HTTP_REFERER"], $msg, 'error');
			}
		}
				
		$checked_ids = $db->escape(JRequest::getVar('checked_ids'));
		$dates_range = $db->escape(JRequest::getVar('dates_range'));
		if($dates_range)
		{
			$dates_range =explode('***',$dates_range);
			$from = $dates_range[0];
			$to = $dates_range[1];
		}
		else
		{
			$from = '';
			$to = '';
		}
		
		$form_id=$id;
		$paypal_info_fields=array('currency', 'ord_last_modified', 'status', 'full_name', 'fax', 'mobile_phone', 'email', 'phone', 'address', 'paypal_info',  'ipn', 'tax', 'shipping');
		$paypal_info_labels = array( 'Currency', 'Last modified', 'Status', 'Full Name', 'Fax', 'Mobile phone', 'Email', 'Phone', 'Address', 'Paypal info', 'IPN', 'Tax', 'Shipping');
		
		$where_range = ' ';
		$labels= array();
		
		if($from)
			$where_range .= "AND DATE_FORMAT(date,'%Y-%m-%d') >= '".$db->escape($from)."'";
		if($to)
			$where_range .= "AND DATE_FORMAT(date,'%Y-%m-%d') <= '".$db->escape($to)."'";
	
		if($checked_ids)
		{		
			$labels = explode(',',$checked_ids);
			$labels = array_slice($labels,0, count($labels)-1);   
			
			$query = "SELECT id FROM #__formmaker_submits where form_id=".$db->escape((int)$form_id).$where_range;
			$db->setQuery( $query);
			$rows = $db->loadObject();
			if($db->getErrorNum()){
				echo $db->stderr();
				return false;
			}
		}
		else
			$rows = '';

		$query_lable = "SELECT label_order,title FROM #__formmaker where id=".$db->escape((int)$form_id);
		$db->setQuery( $query_lable);
		$rows_lable = $db->loadObjectList();

		if($db->getErrorNum()){
			echo $db->stderr();
			return false;
		}
		
		$ptn = "/[^a-zA-Z0-9_]/";
				$rpltxt = "";
			
		
		$title=preg_replace($ptn, $rpltxt, $rows_lable[0]->title);

		$sorted_labels_id= array();
		$sorted_labels= array();
		$label_titles=array();
		if($labels)
		{
		
			$label_id= array();
			$label_order= array();
			$label_order_original= array();
			$label_type= array();

			$label_all	= explode('#****#',$rows_lable[0]->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_oder_each=explode('#**label**#', $label_id_each[1]);		
				array_push($label_order_original, $label_oder_each[0]);
			
				$ptn = "/[^a-zA-Z0-9_]/";
				$rpltxt = "";
				$label_temp=preg_replace($ptn, $rpltxt, $label_oder_each[0]);
				array_push($label_order, $label_temp);		
				array_push($label_type, $label_oder_each[1]);	
			}
		
			foreach($label_id as $key => $label) 
				if(in_array($label, $labels))
				{
					array_push($sorted_labels, $label_order[$key]);
					array_push($sorted_labels_id, $label);
					array_push($label_titles, $label_order_original[$key]);
				}
		}

		$m=count($sorted_labels);
		$group_id_s= array();

		if(count($rows)>0 and $checked_ids)
		{		
			$query = "SELECT distinct group_id FROM #__formmaker_submits where form_id=".$db->escape((int)$form_id);
			$db->setQuery( $query);
			$group_id_s = $db->loadColumn();
			if($db->getErrorNum()){echo $db->stderr();return false;}
		}

		$data=array();
		for($www=0;  $www < count($group_id_s); $www++)
		{
			$data_temp=array();
			$i=$group_id_s[$www];
			
			$query = "SELECT `date`, `ip`, `user_id` FROM #__formmaker_submits where group_id=$i".$where_range;
			$db->setQuery( $query);
			$f = $db->loadObject();
			
			$date=$f->date;
			$ip = $f->ip;
			$user_id = JFactory::getUser($f->user_id);
			$user_name = $user_id->username;
			$user_email= $user_id->email;
			if(in_array('submit_id', $labels))
				$data_temp['ID']=$i;
			if(in_array('submit_date', $labels))
				$data_temp['Submit date']=$date;
			if(in_array('submitter_ip', $labels))
				$data_temp['Ip']=$ip;
			if(in_array('username', $labels))
				$data_temp['Submitter\'s Username']=$user_name;
			if(in_array('useremail', $labels))
				$data_temp['Submitter\'s Email Address']=$user_email;
			for($h=0; $h < $m ; $h++)
			{		
				if(isset($data_temp[$label_titles[$h]]))
					$label_titles[$h] .= '(1)';
					
				$query = "SELECT * FROM #__formmaker_submits where group_id=$i AND element_label=".$sorted_labels_id[$h];
				$db->setQuery( $query);
				$t = $db->loadObject();
				
				if($t)
				{	
					if(strpos($t->element_value,"*@@url@@*"))
					{
						$new_file=str_replace("*@@url@@*",'', $t->element_value);
						$new_filename=explode('/', $new_file);
						$data_temp[$label_titles[$h]]=$new_file;
					}
					else
						if(strpos($t->element_value,"***br***"))					
						{						
							$element_value = str_replace("***br***",', ', $t->element_value);
							
							if(strpos($element_value,"***quantity***"))
							$element_value = str_replace("***quantity***",'', $element_value);
							
							if(strpos($element_value,"***property***"))
							$element_value = str_replace("***property***",'', $element_value);

							if(substr($element_value, -2) == ', ')
								$data_temp[$label_titles[$h]]= substr($element_value, 0, -2);
							else
								$data_temp[$label_titles[$h]]= $element_value;		
						}					
						else					
							if(strpos($t->element_value,"***map***"))		
							{								
								$data_temp[$label_titles[$h]]= 'Longitude:'.substr(str_replace("***map***",', Latitude:', $t->element_value), 0, -2);		
							}
							else
								if(strpos($t->element_value,"@@@")>-1 || $t->element_value=="@@@" || $t->element_value=="@@@@@@@@@" ||$t->element_value=="::" || $t->element_value==":" || $t->element_value=="--")
								{
									$data_temp[$label_titles[$h]]= str_replace(array("@@@",":","-"),' ', $t->element_value);
								}
								else
									if(strpos($t->element_value,"***grading***"))
									{	
										$element = str_replace("***grading***",'', $t->element_value);
										
										$grading = explode(":",$element);                        
										$items_count = sizeof($grading)-1;
										$items = "";
										$total = "";
										for($k=0;$k<$items_count/2;$k++)
										{
										$items .= $grading[$items_count/2+$k].": ".$grading[$k].", ";
										$total += $grading[$k];
										}
										$items .="Total: ".$total;

										$data_temp[$label_titles[$h]]= $items;
									}
									else
										if(strpos($t->element_value,"***matrix***"))
										{	
											$element = str_replace("***matrix***",'', $t->element_value);

											$matrix_value=explode('***', $element);
											$matrix_value = array_slice($matrix_value,0, count($matrix_value)-1);   
																		
											$mat_rows=$matrix_value[0];
											$mat_columns=$matrix_value[$mat_rows+1];
														
											$matrix="";
											$aaa=Array();
											$var_checkbox=1;
											$selected_value="";
											$selected_value_yes="";
											$selected_value_no="";
							   
											for( $k=1;$k<=$mat_rows;$k++)
											{
												if($matrix_value[$mat_rows+$mat_columns+2]=="radio")
												{
													if($matrix_value[$mat_rows+$mat_columns+2+$k]==0)
													{
														$checked="0";
														$aaa[1]="";
													}
													else
														$aaa=explode("_",$matrix_value[$mat_rows+$mat_columns+2+$k]);

													for( $l=1;$l<=$mat_columns;$l++)
													{
														if($aaa[1]==$l)
															$checked='1';
														else
															$checked="0";
																				
														$matrix .= '['.$matrix_value[$k].','.$matrix_value[$mat_rows+1+$l].']='.$checked."; ";
													}
									
												} 
												else
												{
													if($matrix_value[$mat_rows+$mat_columns+2]=="checkbox")
													{  
														for( $l=1;$l<=$mat_columns;$l++)
														{
															if( $matrix_value[$mat_rows+$mat_columns+2+$var_checkbox]==1)
																$checked ='1';
															else
																$checked ='0';
									
															$matrix .= '['.$matrix_value[$k].','.$matrix_value[$mat_rows+1+$l].']='.$checked."; ";
															$var_checkbox++;
														}
								
													}
													else
													{
														if($matrix_value[$mat_rows+$mat_columns+2]=="text")
														{
															for( $l=1;$l<=$mat_columns;$l++)
															{
																$text_value = $matrix_value[$mat_rows+$mat_columns+2+$var_checkbox];	
																
																$matrix .='['.$matrix_value[$k].','.$matrix_value[$mat_rows+1+$l].']='.$text_value."; ";
																$var_checkbox++;
															}
								
														}
														else
														{
															for( $l=1;$l<=$mat_columns;$l++)
															{
																$selected_text = $matrix_value[$mat_rows+$mat_columns+2+$var_checkbox];
						
																$matrix .='['.$matrix_value[$k].','.$matrix_value[$mat_rows+1+$l].']='.$selected_text."; ";
																$var_checkbox++;
															}
														}
													}
								
												}
								
											}	
											$data_temp[$label_titles[$h]]= $matrix;
										}							
										else				
											$data_temp[$label_titles[$h]]= ' '.$t->element_value;
				}	
				else
					$data_temp[$label_titles[$h]]= '';	
			}
			
			if(in_array('item_total', $labels))
			{
				$query = "SELECT `element_value` FROM #__formmaker_submits where group_id=$i AND element_label='item_total'".$where_range;
				$db->setQuery( $query);
				$item_total = $db->loadResult();	
				$data_temp['Item Total'] = $item_total;
			}
			
			if(in_array('total', $labels))
			{
				$query = "SELECT `element_value` FROM #__formmaker_submits where group_id=$i AND element_label='total'".$where_range;
				$db->setQuery( $query);
				$total = $db->loadResult();
				$data_temp['Total'] = $total;
			}
			
			if(in_array('0', $labels))
			{
				$query = "SELECT `element_value` FROM #__formmaker_submits where group_id=$i AND element_label='0'".$where_range;
				$db->setQuery( $query);
				$payment_status = $db->loadResult();
				$data_temp['Payment Status'] = $payment_status;
			}
			
			if(in_array('payment_info', $labels))
			{
				$query = "SELECT * FROM #__formmaker_sessions where group_id=".$db->escape($i).$where_range;
				$db->setQuery( $query);
				$paypal_info = $db->loadObject();
				if($db->getErrorNum()){		echo $db->stderr();		return false;	}

				foreach($paypal_info_fields as $key=>$paypal_info_field)
				{
					if($paypal_info)
						$data_temp['PAYPAL_'.$paypal_info_labels[$key]]=$paypal_info->$paypal_info_field;
					else
						$data_temp['PAYPAL_'.$paypal_info_labels[$key]]='';
				}
			}
			$data[]=$data_temp;
		}

		$filename = $title."_" . date('Ymd') . ".csv";
	 
		header('Content-Encoding: Windows-1252');
		header('Content-type: text/csv; charset=Windows-1252');
		header("Content-Disposition: attachment; filename=\"$filename\"");

		$flag = false;
		$text = '';
		foreach($data as $row)
		{
			if(!$flag) 
			{
				//  echo "sep=,\r\n";
				$text .= '"'.implode('","', str_replace('PAYPAL_', '', array_keys($row)));
				$text .= "\"\r\n";
				$flag = true;
			}
			
			array_walk($row, array($this, 'cleanData'));
			$text .= '"'.implode('","',array_values($row))."\"\r\n";
		}
		echo $text;
		exit;
	}

	function generate_xml()
	{
		$mainframe = JFactory::getApplication();
		$user = JFactory::getUser();
		$db		= JFactory::getDBO();
		$id 	= JRequest::getVar('id');
		$db->setQuery("SELECT `user_id` FROM #__formmaker WHERE id=".$db->escape((int)$id)); 
		$userGroups = $db->loadResult();
		if ($db->getErrorNum())	{echo $db->stderr(); return false;}	
		
		$users = explode(',', $userGroups);
		$users 	= array_slice($users,0, count($users)-1); 

		$allow_export = false;
		$msg = 'You have no permissions to download xml';
		if($user->guest)
		{
			if(!in_array('9',$users))
			{
				$mainframe->redirect($_SERVER["HTTP_REFERER"], $msg, 'error');
			}
		}
		else
		{
			foreach($user->groups as $user_id)
			{
				if(in_array($user_id, $users))
					$allow_export = true;
			}
			
			if(!$allow_export)
			{
				$mainframe->redirect($_SERVER["HTTP_REFERER"], $msg, 'error');
			}
		}

		$checked_ids = $db->escape(JRequest::getVar('checked_ids'));
		$dates_range = $db->escape(JRequest::getVar('dates_range'));
		if($dates_range)
		{
			$dates_range =explode('***',$dates_range);
			$from = $dates_range[0];
			$to = $dates_range[1];
		}
		else
		{
			$from = '';
			$to = '';
		}
		
		$form_id=$id;
		$paypal_info_fields=array('currency', 'ord_last_modified', 'status', 'full_name', 'fax', 'mobile_phone', 'email', 'phone', 'address', 'paypal_info',  'ipn', 'tax', 'shipping');
		$paypal_info_labels = array( 'Currency', 'Last modified', 'Status', 'Full Name', 'Fax', 'Mobile phone', 'Email', 'Phone', 'Address', 'Paypal info', 'IPN', 'Tax', 'Shipping');

		$where_range = ' ';
		$labels= array();
		
		if($from)
			$where_range .= "AND DATE_FORMAT(date,'%Y-%m-%d') >= '".$db->escape($from)."'";
		if($to)
			$where_range .= "AND DATE_FORMAT(date,'%Y-%m-%d') <= '".$db->escape($to)."'";
		if($checked_ids)
		{

			$labels = explode(',',$checked_ids);
			$labels = array_slice($labels,0, count($labels)-1);  
				
			$query = "SELECT id FROM #__formmaker_submits where form_id=".$db->escape((int)$form_id).$where_range;
			$db->setQuery( $query);
			$rows = $db->loadObject();
			if($db->getErrorNum()){ echo $db->stderr(); return false;}
		}
		else
			$rows = '';
		
		$query_lable = "SELECT label_order,title FROM #__formmaker where id=".$db->escape((int)$form_id);
		$db->setQuery( $query_lable);
		$rows_lable = $db->loadObjectList();

		if($db->getErrorNum()){
			echo $db->stderr();
			return false;
		}
				
		$ptn = "/[^a-zA-Z0-9_]/";
				$rpltxt = "";
				
		$form_title	= $rows_lable[0]->title;	
		$title=preg_replace($ptn, $rpltxt, $rows_lable[0]->title);
		
		$sorted_labels_id= array();
		$sorted_labels= array();
		$label_titles=array();
		if($labels)
		{		
			$label_id= array();
			$label_order= array();
			$label_order_original= array();
			$label_type= array();
			
			$label_all	= explode('#****#',$rows_lable[0]->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_oder_each=explode('#**label**#', $label_id_each[1]);			
				array_push($label_order_original, $label_oder_each[0]);
				
				$ptn = "/[^a-zA-Z0-9_]/";
				$rpltxt = "";
				$label_temp=preg_replace($ptn, $rpltxt, $label_oder_each[0]);
				array_push($label_order, $label_temp);			
				array_push($label_type, $label_oder_each[1]);

			}
			
			foreach($label_id as $key => $label) 
				if(in_array($label, $labels))
				{
					array_push($sorted_labels, $label_order[$key]);
					array_push($sorted_labels_id, $label);
					array_push($label_titles, $label_order_original[$key]);
				}
		}
		
		$m=count($sorted_labels);
		$group_id_s= array();

		if(count($rows)>0 and $checked_ids)
		{
			$query = "SELECT distinct group_id FROM #__formmaker_submits where form_id=".$db->escape((int)$form_id);
			$db->setQuery( $query);
			$group_id_s = $db->loadColumn();
			if($db->getErrorNum()){echo $db->stderr();return false;}
		}

		$data=array();
		for($www=0;  $www < count($group_id_s); $www++)
		{	
			$data_temp=array();
			$i=$group_id_s[$www];
		
			$query = "SELECT `date`, `ip`, `user_id` FROM #__formmaker_submits where group_id=$i".$where_range;
			$db->setQuery( $query);
			$f = $db->loadObject();
			
			$date=$f->date;
			$ip = $f->ip;
			$user_id = JFactory::getUser($f->user_id);
			$user_name = $user_id->username;
			$user_email= $user_id->email;
			if(in_array('submit_id', $labels))
				$data_temp['ID']=$i;
			if(in_array('submit_date', $labels))
				$data_temp['Submit date']=$date;
			if(in_array('submitter_ip', $labels))
				$data_temp['Ip']=$ip;
			if(in_array('username', $labels))
				$data_temp['Submitter\'s Username']=$user_name;
			if(in_array('useremail', $labels))
				$data_temp['Submitter\'s Email Address']=$user_email;
				
			for($h=0; $h < $m ; $h++)
			{		
				if(isset($data_temp[$label_titles[$h]]))
					$label_titles[$h] .= '(1)';
					
				$query = "SELECT * FROM #__formmaker_submits where group_id=$i AND element_label=".$sorted_labels_id[$h];
				$db->setQuery( $query);
				$t = $db->loadObject();
				if($t)
				{	
					if(strpos($t->element_value,"*@@url@@*"))
					{
						$new_file=str_replace("*@@url@@*",'', $t->element_value);
						$new_filename=explode('/', $new_file);
						$data_temp[$label_titles[$h]]=$new_file;
					}
					else
						if(strpos($t->element_value,"***br***"))					
						{						
							$element_value = str_replace("***br***",', ', $t->element_value);
							
							if(strpos($element_value,"***quantity***"))
							$element_value = str_replace("***quantity***",'', $element_value);
							
							if(strpos($element_value,"***property***"))
							$element_value = str_replace("***property***",'', $element_value);

							if(substr($element_value, -2) == ', ')
								$data_temp[$label_titles[$h]]= substr($element_value, 0, -2);
							else
								$data_temp[$label_titles[$h]]= $element_value;		
						}					
						else					
							if(strpos($t->element_value,"***map***"))		
							{								
								$data_temp[$label_titles[$h]]= 'Longitude:'.substr(str_replace("***map***",', Latitude:', $t->element_value), 0, -2);		
							}
							else
								if(strpos($t->element_value,"@@@")>-1 || $t->element_value=="@@@" || $t->element_value=="@@@@@@@@@" ||$t->element_value=="::" || $t->element_value==":" || $t->element_value=="--")
								{
									$data_temp[$label_titles[$h]]= str_replace(array("@@@",":","-"),' ', $t->element_value);
								}
								else
									if(strpos($t->element_value,"***grading***"))
									{	
										$element = str_replace("***grading***",'', $t->element_value);
										
										$grading = explode(":",$element);                        
										$items_count = sizeof($grading)-1;
										$items = "";
										$total = "";
										for($k=0;$k<$items_count/2;$k++)
										{
										$items .= $grading[$items_count/2+$k].": ".$grading[$k].", ";
										$total += $grading[$k];
										}
										$items .="Total: ".$total;

										$data_temp[$label_titles[$h]]= $items;
									}
									else
										if(strpos($t->element_value,"***matrix***"))
										{	
											$element = str_replace("***matrix***",'', $t->element_value);

											$matrix_value=explode('***', $element);
											$matrix_value = array_slice($matrix_value,0, count($matrix_value)-1);   
																		
											$mat_rows=$matrix_value[0];
											$mat_columns=$matrix_value[$mat_rows+1];
														
											$matrix="";
											$aaa=Array();
											$var_checkbox=1;
											$selected_value="";
											$selected_value_yes="";
											$selected_value_no="";
							   
											for( $k=1;$k<=$mat_rows;$k++)
											{
												if($matrix_value[$mat_rows+$mat_columns+2]=="radio")
												{
													if($matrix_value[$mat_rows+$mat_columns+2+$k]==0)
													{
														$checked="0";
														$aaa[1]="";
													}
													else
														$aaa=explode("_",$matrix_value[$mat_rows+$mat_columns+2+$k]);

													for( $l=1;$l<=$mat_columns;$l++)
													{
														if($aaa[1]==$l)
															$checked='1';
														else
															$checked="0";
																				
														$matrix .= '['.$matrix_value[$k].','.$matrix_value[$mat_rows+1+$l].']='.$checked."; ";
													}
									
												} 
												else
												{
													if($matrix_value[$mat_rows+$mat_columns+2]=="checkbox")
													{  
														for( $l=1;$l<=$mat_columns;$l++)
														{
															if( $matrix_value[$mat_rows+$mat_columns+2+$var_checkbox]==1)
																$checked ='1';
															else
																$checked ='0';
									
															$matrix .= '['.$matrix_value[$k].','.$matrix_value[$mat_rows+1+$l].']='.$checked."; ";
															$var_checkbox++;
														}
								
													}
													else
													{
														if($matrix_value[$mat_rows+$mat_columns+2]=="text")
														{
															for( $l=1;$l<=$mat_columns;$l++)
															{
																$text_value = $matrix_value[$mat_rows+$mat_columns+2+$var_checkbox];	
																
																$matrix .='['.$matrix_value[$k].','.$matrix_value[$mat_rows+1+$l].']='.$text_value."; ";
																$var_checkbox++;
															}
								
														}
														else
														{
															for( $l=1;$l<=$mat_columns;$l++)
															{
																$selected_text = $matrix_value[$mat_rows+$mat_columns+2+$var_checkbox];
						
																$matrix .='['.$matrix_value[$k].','.$matrix_value[$mat_rows+1+$l].']='.$selected_text."; ";
																$var_checkbox++;
															}
														}
													}
								
												}
								
											}	
											$data_temp[$label_titles[$h]]= $matrix;
										}							
										else				
											$data_temp[$label_titles[$h]]= ' '.$t->element_value;
				}	
				else
					$data_temp[$label_titles[$h]]= '';	
			}
			
			if(in_array('item_total', $labels))
			{
				$query = "SELECT `element_value` FROM #__formmaker_submits where group_id=$i AND element_label='item_total'".$where_range;
				$db->setQuery( $query);
				$item_total = $db->loadResult();	
				$data_temp['Item Total'] = $item_total;
			}
			
			if(in_array('total', $labels))
			{
				$query = "SELECT `element_value` FROM #__formmaker_submits where group_id=$i AND element_label='total'".$where_range;
				$db->setQuery( $query);
				$total = $db->loadResult();
				$data_temp['Total'] = $total;
			}
			
			if(in_array('0', $labels))
			{
				$query = "SELECT `element_value` FROM #__formmaker_submits where group_id=$i AND element_label='0'".$where_range;
				$db->setQuery( $query);
				$payment_status = $db->loadResult();
				$data_temp['Payment Status'] = $payment_status;
			}
			
			if(in_array('payment_info', $labels))
			{
				$query = "SELECT * FROM #__formmaker_sessions where group_id=".$db->escape($i);
				$db->setQuery( $query);
				$paypal_info = $db->loadObject();
				if($db->getErrorNum()){		echo $db->stderr();		return false;	}
				if($paypal_info)
				{
					foreach($paypal_info_fields as $key=>$paypal_info_field)
						$data_temp['PAYPAL_'.$paypal_info_labels[$key]]=$paypal_info->$paypal_info_field;
				}  
			} 

			$data[$i]=$data_temp;
		}
		
		define('PHP_TAB', "\t");

		// file name for download
		$filename = $title."_" . date('Ymd') . ".xml";

		header("Content-Disposition: attachment; filename=\"$filename\"");
		header("Content-Type:text/xml,  charset=utf-8");

		$text = '<?xml version="1.0" encoding="utf-8"?>'.PHP_EOL;
		$text.= '<form id="'.$form_id.'" title="'.$form_title.'">'.PHP_EOL;

		foreach ($data as $key1 => $value1)
		{
			$text .=PHP_TAB.'<submission id="'.$key1.'">'.PHP_EOL;	 
			foreach ($value1 as $key => $value)
			{
				$text .=PHP_TAB.PHP_TAB.'<field title="'.$key.'">'.PHP_EOL;
				$text .=PHP_TAB.PHP_TAB.PHP_TAB.'<![CDATA['.$value.']]>'.PHP_EOL;
				$text .=PHP_TAB.PHP_TAB.'</field>'.PHP_EOL;
			}  
			$text .=PHP_TAB.'</submission>'.PHP_EOL;
		}
		$text .='</form>';
		
		echo $text;
		exit;

	}
	
	function cleanData(&$str)
	{
		$str = preg_replace("/\t/", "\\t", $str);
		$str = preg_replace("/\r?\n/", "\\n", $str);
		if ( strstr($str, '"') ) {
		  $str = '"' . str_replace('"', '""', $str) . '"';
		}
		$str = ltrim($str, '@+=-');
	}
	
	function showsubmissions()
	{

		jimport('joomla.html.pagination');
		$mainframe = JFactory::getApplication();
		$user = JFactory::getUser();
		$form_id=JRequest::getVar('id',0);
		
		$db = JFactory::getDBO();
		$db->setQuery("SELECT `user_id` FROM #__formmaker WHERE id=".$db->escape((int)$form_id)); 
		$userGroups = $db->loadResult();
		if ($db->getErrorNum())	{echo $db->stderr(); return false;}	

		$users = explode(',', $userGroups);
		$users 	= array_slice($users,0, count($users)-1); 
	
		$show_submits = false;
		if($user->guest)
		{
			if(!in_array('9',$users))
				return false;
		}
		else
		{
			foreach($user->groups as $user_id)
			{
				if(in_array($user_id, $users))
					$show_submits = true;
			}
			
			if(!$show_submits)
				return false;
		}
	

		$option='com_formmaker';

		$checked_ids =$db->escape(JRequest::getVar('checked_ids'));	
		$dates_range = $db->escape(JRequest::getVar('dates_range'));
		$params = explode(',',$db->escape(JRequest::getVar('params')));
		$params = array_slice($params,0, count($params)-1);  

		if($dates_range)
		{
			$dates_range = explode('***',$dates_range);
			$from = $dates_range[0];
			$to = $dates_range[1];
		}
		else
		{
			$from = '';
			$to = '';
		}
		
		$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 );
		if(in_array('pagination',$params))
		$limit= $mainframe-> getUserStateFromRequest('global.list.limit', 'limit', $mainframe->getCfg('list_limit'), 'int');
		else
		$limit = '';
		$limitstart= $mainframe-> getUserStateFromRequest($option.'.limitstart', 'limitstart', 0, 'int');
		

		$where = array();
		$lists['startdate']= $db->escape(JRequest::getVar('startdate', ""));
		$lists['enddate']= $db->escape(JRequest::getVar('enddate', ""));
		$lists['hide_label_list']= $db->escape(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($from)
		{
			if($lists['startdate']!='')
			{
				if(strtotime($from) > strtotime($lists['startdate']))
					$where[] = "`date` >= '".$db->escape($from)." 00:00:00'";
				else
					$where[] ="`date`>='".$db->escape($lists['startdate'])." 00:00:00' ";
			}
			else
				$where[] = "`date` >= '".$db->escape($from)." 00:00:00'";
		
		}
		else
		{
			if($lists['startdate']!='')
			$where[] ="  `date`>='".$db->escape($lists['startdate'])." 00:00:00' ";
		}
		
		
		if($to)
		{
			if($lists['enddate']!='')
			{
				if(strtotime($to) < strtotime($lists['enddate']))
					$where[] = "`date` <= '".$db->escape($to)." 23:59:59'";
				else
					$where[] ="`date`<='".$db->escape($lists['enddate'])." 23:59:59' ";
			}
			else
				$where[] = "`date` <= '".$db->escape($to)." 23:59:59'";
		}
		else
		{
			if($lists['enddate']!='')
			$where[] ="`date`<='".$db->escape($lists['enddate'])." 23:59:59' ";
		}
		
		if ($form_id=='')
			if($forms)
			$form_id=$forms[0]->id;
			
		$query = "SELECT title FROM #__formmaker WHERE id=".$db->escape((int)$form_id);
		$db->setQuery( $query);
		$form_title = $db->loadResult();
		if($db->getErrorNum()){echo $db->stderr();return false;}
		
		$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=".$db->escape($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 ="'.$db->escape((int)$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="'.$db->escape($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($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

		return array($rows, $lists, $pageNav, $sorted_labels, $label_titles, $rows_ord, $sorted_labels_id, $sorted_labels_type, $total_entries, $total_views, $join_count,$form_title);
	}


}
	?>
