<?php
defined('_JEXEC') or die('Direct Access to this location is not allowed.');

function is_ip($ip) {
$valid = true;

$ip = explode(".", $ip);
   foreach($ip as $block) {
       if(!is_numeric($block)) {
           $valid = false;
       }
   }
return $valid;
}

require_once($ff_admpath.'/admin/recordmanagement.html.php');

class bfRecordManagement
{
	/**
	 * @var JDatabase
	 */
	private $db = null;
	
	function __construct()
	{
		$this->db = JFactory::getDBO();
	}
	
	public function editRecord()
	{ 
		$ids = JRequest::getVar('ids', array());
		if(is_array($ids) && count($ids) != 0)
		{
			$this->db->setQuery("Select * From #__facileforms_records As records Where records.id = " . intval($ids[0]));
			$head = $this->db->loadObjectList();
			
			$this->db->setQuery("Select * From #__facileforms_subrecords As subrecords Where subrecords.record = " . intval($ids[0]));
			$entries = $this->db->loadObjectList();
			
			if(count($head) && count($entries))
			{
				$this->db->setQuery("Update #__facileforms_records Set viewed = 1 Where id = " . intval($ids[0]));
				$this->db->query();
                                
				bfRecordManagementHtml::record($head[0], $entries);
			}
			else
			{
				bfRecordManagementHtml::backjump();
			}
		}
	}
	
	public function listRecords()
	{
		JToolBarHelper::custom('exportPdf',    'ff_download',             'ff_download_f2',             BFText::_('PDF'),    false);
		JToolBarHelper::custom('exportCsv',    'ff_download',             'ff_download_f2',             BFText::_('CSV'),    false);
		JToolBarHelper::custom('exportXml',    'ff_download',             'ff_download_f2',             BFText::_('XML'),    false);
		JToolBarHelper::custom('remove',    'delete.png',       'delete_f2.png',    BFText::_('TOOLBAR_DELETE'),    false);
		JToolBarHelper::custom('all',    'ff_switch',             'ff_switch_f2',             BFText::_('ALL'),    false);
		JToolBarHelper::custom('viewed',    'ff_switch',             'ff_switch_f2',             BFText::_('TOOLBAR_VIEWED'),    false);
		JToolBarHelper::custom('exported',  'ff_switch',             'ff_switch_f2',             BFText::_('TOOLBAR_EXPORTED'),  false);
		JToolBarHelper::custom('archived',  'ff_switch',             'ff_switch_f2',             BFText::_('TOOLBAR_ARCHIVED'),  false);
		
		JFactory::getDocument()->addStyleDeclaration(
			'
			
			.icon-32-ff_switch {
				background-image:url(components/com_facileforms/images/icons/switch.png);
			}
			
			.icon-32-ff_switch_f2 {
				background-image:url(components/com_facileforms/images/icons/switch_f2.png);
			}
			
			.icon-32-ff_download {
				background-image:url(components/com_facileforms/images/icons/download.png);
			}
			
			.icon-32-ff_download_f2 {
				background-image:url(components/com_facileforms/images/icons/download_f2.png);
			}
			
			'
		);
		
		//print_r($_REQUEST);
		$ids = JRequest::getVar('ids', array());
		$offset = JRequest::getInt('limitstart', 0);
		$limit  = JRequest::getInt('mylimit', 20);
		
		if(JRequest::getVar('task','') == 'all')
		{
			JFactory::getSession()->set('bfStatus', '');
		} 
		else if(JRequest::getVar('task','')=='exported')
		{
			JFactory::getSession()->set('bfStatus', 'exported');
		}
		else if(JRequest::getVar('task','')=='archived')
		{
			JFactory::getSession()->set('bfStatus', 'archived');
		}
		else if(JRequest::getVar('task','')=='viewed')
		{
			JFactory::getSession()->set('bfStatus', 'viewed');
		}
		else if(JRequest::getVar('task','')=='remove')
		{
			$size = count($ids);
			for($i = 0; $i < $size; $i++)
			{
				$this->db->setQuery("Delete From #__facileforms_records Where id = " . $this->db->Quote($ids[$i]));
				$this->db->query();
				$this->db->setQuery("Delete From #__facileforms_subrecords Where record = " . $this->db->Quote($ids[$i]));
				$this->db->query();
				//echo $this->db->getQuery() . '<br/>';
			}
		}
		else if(JRequest::getVar('task','')=='save')
		{
			$id = JRequest::getInt('record_id', 0);
			$updates = JRequest::getVar('update', array());
			foreach($updates As $update)
			{
				$this->db->setQuery("Update #__facileforms_subrecords Set value = ".$this->db->Quote(JRequest::getVar('ff_nm_'.$update,''))." Where name = ".$this->db->Quote($update)." And record = " . $id);
				$this->db->query();
			}
		}
		else if(JRequest::getVar('task','')=='exportXml' && JRequest::getInt('exportt',0) == 1)
		{
			$this->expxml($ids);
		}
		else if(JRequest::getVar('task','')=='exportCsv' && JRequest::getInt('exportt',0) == 1)
		{
			$this->expcsv($ids);
		}
		else if(JRequest::getVar('task','')=='exportPdf' && JRequest::getInt('exportt',0) == 1)
		{
			$this->exppdf($ids);
		}
		
		if(JRequest::getInt('status_update',0) == 1)
		{
			$offset = 0;
			$limit = 20;
			JRequest::setVar('limitstart', 0);
			JRequest::setVar('mylimit', 20);
		}
		
		if(JRequest::getInt('id', 0) != '' && JRequest::getInt('viewed', -1) != -1)
		{
			$value = 1;
			if(JRequest::getInt('viewed', -1) == 1)
			{
				$value = 0;
			}
			$this->db->setQuery("Update #__facileforms_records Set viewed = ".$value." Where id = " . JRequest::getInt('id', 0));
			$this->db->query();
		}
		
		if(JRequest::getInt('id', 0) != '' && JRequest::getInt('exported', -1) != -1)
		{
			$value = 1;
			if(JRequest::getInt('exported', -1) == 1)
			{
				$value = 0;
			}
			$this->db->setQuery("Update #__facileforms_records Set exported = ".$value." Where id = " . JRequest::getInt('id', 0));
			$this->db->query();
		}
		
		if(JRequest::getInt('id', 0) != '' && JRequest::getInt('archived', -1) != -1)
		{
			$value = 1;
			if(JRequest::getInt('archived', -1) == 1)
			{
				$value = 0;
			}
			$this->db->setQuery("Update #__facileforms_records Set archived = ".$value." Where id = " . JRequest::getInt('id', 0));
			$this->db->query();
		}
		
		if(JRequest::getInt('status_update',0) == 1 && JRequest::getVar('write_status','') != '')
		{
			$in = '';
			
			if(is_array($ids) && count($ids) != 0)
			{
				$status = '';
				if(JFactory::getSession()->get('bfStatus', '') == 'exported')
				{
					$status = "exported = ".(JRequest::getVar('write_status','') == 'set' ? 1 : 0);
				}
				else
				if(JFactory::getSession()->get('bfStatus', '') == 'archived')
				{
					$status = "archived = ".(JRequest::getVar('write_status','') == 'set' ? 1 : 0);
				}
				else
				if(JFactory::getSession()->get('bfStatus', '') == 'viewed')
				{
					$status = "viewed = ".(JRequest::getVar('write_status','') == 'set' ? 1 : 0);
				}
							
				if($status != '' )
				{
					$size = count($ids);
					for($i = 0; $i < $size; $i++)
					{
						$this->db->setQuery("Update #__facileforms_records Set $status Where id = " . $this->db->Quote($ids[$i]));
						$this->db->query();
						//echo $this->db->getQuery() . '<br/>';
					}
				}
			}
		}
		
		$ands = '';
		$subs = '';
		
		if(JRequest::getVar('search','') != '')
		{
			//echo JRequest::getVar('txtsearch','false');
			if(JRequest::getVar('txtsearch','false')=='true')
			{
				$subs .= ', #__facileforms_subrecords As subrecord';
				$ands .= 'subrecord.value Like ' . $this->db->Quote('%'.JRequest::getVar('search','').'%') . ' And record.id = subrecord.record And ';
			}
			
			$headerSearch = '';
			if(is_numeric(JRequest::getVar('search','')))
			{
				$headerSearch .= 'record.id = ' . intval(JRequest::getVar('search','')) . ' Or ';
			}
			
			$ex = explode('-', JRequest::getVar('search',''));
			//print_r($ex);
			if(count($ex) == 3 && checkdate($ex[1], $ex[2], $ex[0]))
			{
				$headerSearch .= "record.submitted Between '" . $ex[0] . '-' . $ex[1] . '-' . $ex[2] . " 00:00:00' And '" . $ex[0] . '-' . $ex[1] . '-' . $ex[2] . " 23:59:59' Or ";
				$headerSearch .= "record.paypal_payment_date Between '" . $ex[0] . '-' . $ex[1] . '-' . $ex[2] . " 00:00:00' And '" . $ex[0] . '-' . $ex[1] . '-' . $ex[2] . " 23:59:59' Or ";
			}
			
			if(is_ip(JRequest::getVar('search','')))
			{
				$headerSearch .= 'record.ip = ' . $this->db->Quote(JRequest::getVar('search','')) . ' Or ';
			}
			
			if(substr(trim(JRequest::getVar('search','')), 0,4) == 'tx: ')
			{
				$text = trim(JRequest::getVar('search',''));
				$text = substr($text, 3,strlen($text));
				$headerSearch .= 'record.paypal_tx_id Like ' . $this->db->Quote('%'.$text) . ' Or ';
			}
			
			if($headerSearch == '' && JRequest::getVar('txtsearch','false')!='true')
			{
				$headerSearch .= 'record.`name` Like ' . $this->db->Quote('%'.trim(JRequest::getVar('search','')).'%') . ' Or ';
			}
			
			if($headerSearch != '')
			{
				$headerSearch = substr($headerSearch,0,strlen($headerSearch)-4);
				$ands .= "(".$headerSearch.") And ";
			}
		}
		
		if(JRequest::getInt('form',0) != 0)
		{
			$ands .= 'record.form = ' . $this->db->Quote(JRequest::getInt('form',0)) . ' And ';
		}
		
		if(JFactory::getSession()->get('bfStatus', '') == 'exported')
		{
			$ands .= "record.exported = 1 And";
		}
		else
		if(JFactory::getSession()->get('bfStatus', '') == 'archived')
		{
			$ands .= "record.archived = 1 And";
		}
		else
		if(JFactory::getSession()->get('bfStatus', '') == 'viewed')
		{
			$ands .= "record.viewed = 1 And";
		}
		
		if($ands != '')
		{
			$ands = 'Where ' . substr($ands,0,strlen($ands)-4);
		}
		
		$limiter = " Limit $offset, $limit";
		if($limit == 0)
		{
			$limiter = '';
		}
		
		$this->db->setQuery("Select Distinct SQL_CALC_FOUND_ROWS record.* From #__facileforms_records As record $subs $ands Order By record.".JRequest::getWord('orderBy','submitted')." ".(JRequest::getWord('order','DESC') == 'DESC' ? 'ASC' : 'DESC').$limiter);
		$rows = $this->db->loadObjectList();
		//echo $this->db->getQuery();
		$this->db->setQuery("SELECT FOUND_ROWS();");
		$foundRows = $this->db->loadResult();
                jimport('joomla.html.pagination');
		$pagination = new JPagination($foundRows, $offset, $limit);
		
		$this->db->setQuery("Select Distinct form As id, `name`, title From #__facileforms_records Order By title");
		$forms = $this->db->loadObjectList();
		$size = count($forms);
		$formsArray = array();
		for($i = 0;$i < $size;$i++)
		{
			if(!isset($formsArray['_'.$forms[$i]->id])){
				$formsArray['_'.$forms[$i]->id] = $forms[$i];
			}
		}
		
		bfRecordManagementHtml::records($rows, $pagination, $formsArray);
	}
	
	function expxml($ids)
	{
		global $database, $ff_admsite, $ff_compath, $ff_version, $mosConfig_fileperms;
		$database = JFactory::getDBO();
		$xmlname = $ff_compath.'/exports/ffexport-'.date('YmdHis').'.xml';

		$ids = implode(',', $ids);
		$database->setQuery(
			"select * from #__facileforms_records where id in ($ids) order by id"
		);
		$recs = $database->loadObjectList();
		if ($database->getErrorNum()) {
			echo $database->stderr();
			return false;
		} // if

		$xml  = '<?xml version="1.0" encoding="utf-8" ?>'.nl().
				'<FacileFormsExport type="records" version="'.$ff_version.'">'.nl().
				indent(1).'<exportdate>'.date('Y-m-d H:i:s').'</exportdate>'.nl();

		$form = '';
		for($r = 0; $r < count($recs); $r++) {
			$rec = $recs[$r];
			$xml .= indent(1).'<record id="'.$rec->id.'">'.nl().
					indent(2).'<submitted>'.$rec->submitted.'</submitted>'.nl().
					indent(2).'<user_id>'.$rec->user_id.'</user_id>'.nl().
					indent(2).'<username>'.htmlspecialchars($rec->username).'</username>'.nl().
					indent(2).'<user_full_name>'.htmlspecialchars($rec->user_full_name).'</user_full_name>'.nl().
					indent(2).'<form>'.$rec->form.'</form>'.nl().
					indent(2).'<title>'.htmlspecialchars($rec->title).'</title>'.nl().
					indent(2).'<name>'.$rec->name.'</name>'.nl().
					indent(2).'<ip>'.$rec->ip.'</ip>'.nl().
					indent(2).'<browser>'.htmlspecialchars($rec->browser).'</browser>'.nl().
					indent(2).'<opsys>'.htmlspecialchars($rec->opsys).'</opsys>'.nl().
					indent(2).'<provider>'.$rec->provider.'</provider>'.nl().
					indent(2).'<viewed>'.$rec->viewed.'</viewed>'.nl().
					indent(2).'<exported>'.$rec->exported.'</exported>'.nl().
					indent(2).'<archived>'.$rec->archived.'</archived>'.nl().
					indent(2).'<pptxid>'.$rec->paypal_tx_id.'</pptxid>'.nl().
					indent(2).'<pppdate>'.$rec->paypal_payment_date.'</pppdate>'.nl().
					indent(2).'<pptestacc>'.$rec->paypal_testaccount.'</pptestacc>'.nl().
					indent(2).'<ppdltries>'.$rec->paypal_download_tries.'</ppdltries>'.nl();
			$database->setQuery(
				"select * from #__facileforms_subrecords where record = $rec->id order by id"
			);
			$subs = $database->loadObjectList();
			for($s = 0; $s < count($subs); $s++) {
				$sub = $subs[$s];
				$xml .= indent(2).'<subrecord id="'.$sub->id.'">'.nl().
						indent(3).'<element>'.$sub->element.'</element>'.nl().
						indent(3).'<name>'.$sub->name.'</name>'.nl().
						indent(3).'<title>'.htmlspecialchars($sub->title).'</title>'.nl().
						indent(3).'<type>'.$sub->type.'</type>'.nl().
						indent(3).'<value>'.htmlspecialchars($sub->value).'</value>'.nl().
						indent(2).'</subrecord>'.nl();
			} // for
			$xml .= indent(1).'</record>'.nl();
		} // for
		$xml .= '</FacileFormsExport>'.nl();
		
		$xmlname = JFile::makeSafe($xmlname);
		if (!JFile::write($xmlname,$xml)) {
			echo "<script> alert('".addslashes(BFText::_('RECORDS_XMLNORWRTBL'))."'); window.history.go(-1);</script>\n";
			exit();
		} // if
		
		$database->setQuery(
			"update #__facileforms_records set exported=1 where id in ($ids)"
		);
		if (!$database->query()) {
			echo "<script> alert('".$database->getErrorMsg()."'); window.history.go(-1); </script>\n";
			exit();
		} // if
		else
		{
			@ob_end_clean();
			$_size = filesize($xmlname);
			$_name = basename($xmlname);
			@ini_set("zlib.output_compression", "Off");
			header("Pragma: public");
			header("Expires: 0");
			header("Cache-Control: private");
			header("Content-Type: application/octet-stream");
			header("Content-Disposition: attachment; filename=$_name");
			header("Accept-Ranges: bytes");
			header("Content-Length: $_size");
			readfile($xmlname);
			exit;
		}
		
	} // expxml
	
	function exppdf($ids)
	{
		global $ff_compath;
		
		$file = JPATH_SITE.'/administrator/components/com_facileforms/pdftpl/export_custom_pdf.php';
		if(!JFile::exists($file)){
			$file = JPATH_SITE.'/administrator/components//com_facileforms/pdftpl/export_pdf.php';
		}
		
		$ids = implode(',', $ids);
		$this->db->setQuery(
			"select * from #__facileforms_records where id in ($ids) order by submitted Desc"
		);
		$recs = $this->db->loadObjectList();
		
		ob_end_clean();
		ob_start();
		require_once($file);
		$c = ob_get_contents();
		ob_end_clean();
		
		require_once(JPATH_SITE.'/administrator/components/com_facileforms/libraries/tcpdf/tcpdf.php');
		
		$pdf = new TCPDF();
		$pdf->AddPage();
		$pdf->writeHTML($c);
		$pdfname = $ff_compath.'/exports/ffexport-pdf-'.date('YmdHis').'.pdf';
		$pdf->lastPage();
		$pdf->Output($pdfname, "F");
		$pdf->Output(basename($pdfname), "D");
		exit;
	}
	
	function getSubrecords($recordId)
	{
		$this->db->setQuery(
				"select Distinct * from #__facileforms_subrecords where record = ".intval($recordId)." order by title"
			);
		return $this->db->loadObjectList();
	}
	
	function expcsv(array $ids)
	{
                global $ff_config;

                $csvdelimiter = stripslashes($ff_config->csvdelimiter);
                $csvquote = stripslashes($ff_config->csvquote);
                $cellnewline = $ff_config->cellnewline == 0 ? "\n" : "\\n";

		$fields = array();
		$lines = array();
		$ids = implode(',', $ids);
		$this->db->setQuery(
			"select * from #__facileforms_records where id in ($ids) order by submitted Desc"
		);
		$recs = $this->db->loadObjectList();
		$recsSize = count($recs);
		for($r = 0; $r < $recsSize; $r++) {

                        $rec = $recs[$r];

			$lineNum = count($lines);
			
			$fields['ZZZ_A_ID'] = true;
			$fields['ZZZ_B_SUBMITTED'] = true;
			$fields['ZZZ_C_USER_ID'] = true;
			$fields['ZZZ_D_USERNAME'] = true;
			$fields['ZZZ_E_USER_FULL_NAME'] = true;
			$fields['ZZZ_F_TITLE'] = true;
			$fields['ZZZ_G_IP'] = true;
			$fields['ZZZ_H_BROWSER'] = true;
			$fields['ZZZ_I_OPSYS'] = true;
			$fields['ZZZ_J_TRANSACTION_ID'] = true;
			$fields['ZZZ_K_DATE'] = true;
			$fields['ZZZ_L_TEST_ACCOUNT'] = true;
			$fields['ZZZ_M_DOWNLOAD_TRIES'] = true;
			
			$lines[$lineNum]['ZZZ_A_ID'][] = $rec->id;
			$lines[$lineNum]['ZZZ_B_SUBMITTED'][] = $rec->submitted;
			$lines[$lineNum]['ZZZ_C_USER_ID'][] = $rec->user_id;
			$lines[$lineNum]['ZZZ_D_USERNAME'][] = $rec->username;
			$lines[$lineNum]['ZZZ_E_USER_FULL_NAME'][] = $rec->user_full_name;
			$lines[$lineNum]['ZZZ_F_TITLE'][] = $rec->title;
			$lines[$lineNum]['ZZZ_G_IP'][] = $rec->ip;
			$lines[$lineNum]['ZZZ_H_BROWSER'][] = $rec->browser;
			$lines[$lineNum]['ZZZ_I_OPSYS'][] = $rec->opsys;
			$lines[$lineNum]['ZZZ_J_TRANSACTION_ID'][] = $rec->paypal_tx_id;
			$lines[$lineNum]['ZZZ_K_DATE'][] = $rec->paypal_payment_date;
			$lines[$lineNum]['ZZZ_L_TEST_ACCOUNT'][] = $rec->paypal_testaccount;
			$lines[$lineNum]['ZZZ_M_DOWNLOAD_TRIES'][] = $rec->paypal_download_tries;
			
			$rec = $recs[$r];
			$this->db->setQuery(
				"select Distinct * from #__facileforms_subrecords where record = $rec->id order by id"
			);
			$subs = $this->db->loadObjectList();
			$subsSize = count($subs);
			for($s = 0; $s < $subsSize; $s++) {
				$sub = $subs[$s];
				if($sub->name != 'bfFakeName' && $sub->name != 'bfFakeName2' && $sub->name != 'bfFakeName3' && $sub->name != 'bfFakeName4'){
					if(!isset($fields[strtoupper($sub->name)]))
					{
						$fields[strtoupper($sub->name)] = true;
					}
					$lines[$lineNum][strtoupper($sub->name)][] = $sub->value;
				}
			}
		}

		$head = '';
		ksort($fields);
		$lineLength = count($lines);
		foreach($fields As $fieldName => $null)
		{
			$head .= $csvquote.$fieldName.$csvquote.$csvdelimiter;
			for($i = 0; $i < $lineLength;$i++)
			{
				if(!isset($lines[$i][$fieldName]))
				{
					$lines[$i][$fieldName] = array();
				}
			}
		}
		
		$head = substr($head,0,strlen($head)-1) . nl();
		
		$out = '';
		for($i = 0; $i < $lineLength;$i++)
		{
			ksort($lines[$i]);
			foreach($lines[$i] As $line){
				$out .= $csvquote.str_replace($csvquote,$csvquote.$csvquote,str_replace("\n",$cellnewline,str_replace("\r","",implode(',',$line)))).$csvquote.$csvdelimiter;
			}
			$out = substr($out,0,strlen($out)-1);
			$out .= nl();
		}
		
		$csvname = JPATH_SITE.'/components/com_facileforms/exports/ffexport-'.date('YmdHis').'.csv';
		JFile::makeSafe($csvname);
		if (!JFile::write($csvname,$head.$out)) {
			echo "<script> alert('".addslashes(BFText::_('RECORDS_XMLNORWRTBL'))."'); window.history.go(-1);</script>\n";
			exit();
		} // if
		/*
		$data = JFile::read($csvname);
		$files[] = array('name' => basename($csvname), 'data' => $data);
		
		$zip = JArchive::getAdapter('zip');
		$path = JPATH_SITE.'/components/com_facileforms/exports/ffexport-csv-'.date('YmdHis').'.zip';
		$zip->create($path, $files);
		JFile::delete($csvname);
		*/
		@ob_end_clean();
		$_size = filesize($csvname);
		$_name = basename($csvname);
		@ini_set("zlib.output_compression", "Off");

                Header("Content-Type: text/comma-separated-values; charset=utf-8");
                Header("Content-Disposition: attachment;filename=\"$_name\"");
                Header("Content-Transfer-Encoding: 8bit");

		header("Pragma: public");
		header("Expires: 0");
		header("Cache-Control: private");
		//header("Content-Type: application/octet-stream");
		//header("Content-Disposition: attachment; filename=$_name");
		//header("Accept-Ranges: bytes");
		//header("Content-Length: $_size");
                ob_start();
		readfile($csvname);
                $c = ob_get_contents();
                ob_end_clean();
                echo chr(255).chr(254).mb_convert_encoding( $c, 'UTF-16LE', 'UTF-8');
		exit;
	}
}


