<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
class CR_model extends MY_Model{
	var $cr_temp_path = "assets\\cr_temp";
	
	function __construct(){
		parent::__construct();
		
		$this->table = "LogCrystalReport";
//		$this->join .= "LEFT JOIN WEB_GROUP ON WEB_GROUP.PKKEY = <T>.USER_GROUP ";
	}
	
	function setOrder($params=array(), $key,$creport,$crxDatabaseField, $sortnos, $filter10) 
	{
		$a = 'ORDERBY'.$key;
		$orderby3 = $params[$a];
		$b = 'ASC'.$key;
		$asc = $params[$b];
		
		if($orderby3 == "" ) return "";
		if($key>=2) {
			$orderby1 = $params['ORDERBY1'];
//			echo $orderby1."=".$orderby3;
			if(strcasecmp($orderby1, $orderby3)==0) return "";
		}
		if($key>=3) {
			$orderby2 = $params['ORDERBY2'];
			if(strcasecmp($orderby2, $orderby3)==0) return "";
		}
		
		for ($i=1 ; $i<=$sortnos; $i++) {

			if(strcasecmp($crxDatabaseField[$i]->Name(), $orderby3)) continue;
			
			$creport->RecordSortFields->Item($key)->Field = $crxDatabaseField[$i];
			
			$order = (strcmp($asc,"1")==0 || strcmp($asc,"")==0) ? 0 : 1;
			$creport->RecordSortFields->Item($key)->SortDirection = $order;

			$sql = "SELECT NAME FROM [WEB_RPTORDERBY] WHERE RPT = '".$orderby3."'";
			$query = $this->db->query($sql);
			$des = $query->row()->NAME;
			if($order) {
				$des = $des."(DESC)";
			}
			
			$query->free_result();
			if($filter10=="") {
				$filter10 = "Order By ".$des;
			}
			else {
				$filter10 = $filter10."/".$des;
			}
			break;
		} 			
		return $filter10;
	}

	function setFormula($params=array(),$data=array(),$SubReport,$filter10,$my_filter)
	{
				
		$ci = get_instance();
		
		$user = $this->fm->get_current_user();
		$no2 = $SubReport->FormulaFields->Count;
		for($i=1; $i<=$no2; $i++) {
			$fname = $SubReport->FormulaFields->Item($i)->Name;
			//echo $fname; 
			if( $fname== "{@CompanyName}") {
				$cname = $this->fm->decrypt($ci->company_name);
				$SubReport->FormulaFields->Item($i)->Text = ("'$cname'");
			}
			else if( $fname== "{@CompanyAddress1}") {
				$cname = $data['ADD1'];
				$SubReport->FormulaFields->Item($i)->Text = ("'$cname'");
			}
			else if( $fname== "{@CompanyAddress2}") {
				$cname = $data['ADD2'];
				$SubReport->FormulaFields->Item($i)->Text = ("'$cname'");
			}
			else if( $fname== "{@CompanyAddress3}") {
				$cname = $data['ADD3'];
				$SubReport->FormulaFields->Item($i)->Text = ("'$cname'");
			}
			else if( $fname== "{@CompanyAddress4}") {
				$cname = $data['ADD4'];
				$SubReport->FormulaFields->Item($i)->Text = ("'$cname'");
			}
			else if($fname == "{@CoNo}") {
				$cname = $data['CONO'];
				$cname = trim($cname);
				if( strlen( $cname) > 4) {
					$cname = "(".$cname.")";
					$SubReport->FormulaFields->Item($i)->Text = ("'$cname'");
				}
			}
			else if($fname == "{@UserID}") {
				$cname = $user->CODE;
				$SubReport->FormulaFields->Item($i)->Text = ("'$cname'");
			}
			else if($fname == "{@UserName}") {
				$cname = $user->NAME;
				$SubReport->FormulaFields->Item($i)->Text = ("'$cname'");
			}
			else if($fname == "{@RptSelectionTitle1}") {
				$SubReport->FormulaFields->Item($i)->Text = ("'$my_filter'");
			}
			else if($fname == "{@RptSelectionTitle2}") {
				$my_filter02 = $params['FILTER02'];
				$SubReport->FormulaFields->Item($i)->Text = ("'$my_filter02'");
			}
			else if($fname == "{@RptSelectionTitle3}") {
				$my_filter03 = $params['FILTER03'];
				$SubReport->FormulaFields->Item($i)->Text = ("'$my_filter03'");
			}
			else if($fname == "{@RptSelectionTitle3}") {
				$my_filter03 = $params['FILTER03'];
				$SubReport->FormulaFields->Item($i)->Text = ("'$my_filter03'");
			}
			else if($fname == "{@RptSelectionTitle4}") {
				$my_filter03 = $params['FILTER04'];
				$SubReport->FormulaFields->Item($i)->Text = ("'$my_filter03'");
			}
			else if($fname == "{@RptSelectionTitle5}") {
				$my_filter03 = $params['FILTER05'];
				$SubReport->FormulaFields->Item($i)->Text = ("'$my_filter03'");
			}
			else if($fname == "{@RptSelectionTitle6}") {
				$my_filter03 = $params['FILTER06'];
				$SubReport->FormulaFields->Item($i)->Text = ("'$my_filter03'");
			}
			else if($fname == "{@RptSelectionTitle7}") {
				$my_filter03 = $params['FILTER07'];
				$SubReport->FormulaFields->Item($i)->Text = ("'$my_filter03'");
			}
			else if($fname == "{@RptSelectionTitle8}") {
				$my_filter03 = $params['FILTER08'];
				$SubReport->FormulaFields->Item($i)->Text = ("'$my_filter03'");
			}
			else if($fname == "{@RptSelectionTitle9}") {
				$my_filter03 = $params['FILTER09'];
				$SubReport->FormulaFields->Item($i)->Text = ("'$my_filter03'");
			}
			else if($fname == "{@RptSelectionTitle10}") {
				$my_filter03 = $filter10;
				$SubReport->FormulaFields->Item($i)->Text = ("'$my_filter03'");
			}
			else if($fname == "{@d1}") {
				$d1 = $params['D1'];
				$SubReport->FormulaFields->Item($i)->Text = ("$d1");
			}
			else if($fname == "{@d2}") {
				$d2 = $params['D2'];
				$SubReport->FormulaFields->Item($i)->Text = ("$d2");
			}
		}
	}
	
	function export_pdf($params=array()){
		$return = array(
			'DESTINATION' => "",
		);
		
		$query = $this->db->query("SELECT * FROM [LOGO]");
		foreach ($query->result() as $row)
		{
    	    $data['CONO'] = $row->CODE;
			$data['ADD1'] = $row->ADD1;
		    $data['ADD2'] = $row->ADD2;
    	    $data['ADD3'] = $row->ADD3;
    	    $data['ADD4'] = $row->ADD4;
    	    break;
		}
		
		if(isset($params['RPT'], $params['FILENAME']) && strlen($params['RPT']) > 0 && strlen($params['FILENAME']) > 0 ){
			if(! isset($params['PATH'])) $params['PATH'] = $this->cr_temp_path;
			if(! isset($params['FORMULA'])) $params['FORMULA'] = "";
			if(! isset($params['EXPORT_TYPE'])) $params['EXPORT_TYPE'] = "";

			$folder_separator = "\\";
			if(strlen($params['PATH']) > 0 && ! in_array(substr($params['PATH'], -1), array("/", "\\"))){
				$params['PATH'] .= $folder_separator;
			}
			$params['PATH'] = str_replace("/", $folder_separator, $params['PATH']);
			
			if(strlen($params['PATH']) > 0 && ! file_exists($params['PATH'])){
				mkdir($params['PATH']);
			}
			
			$server = $this->db->hostname;
			$db = $this->db->database;
			$userid = $this->db->username;
			$passid = $this->db->password;

			/*			
 			$server = "domainc1_chec";
			$db = "domainc1_chec";
			$userid = "domainc1_domain";
			$passid = "domain1816";
			$db = "";
			$userid = "";
			$passid = "";
			*/
			
			//- Variables - for your RPT and PDF
			$my_report = getcwd().$folder_separator."assets\\rpts\\".$params['RPT'];
			if(in_array($params['EXPORT_TYPE'], array("EXCEL", "CSV"))){
				$pi = pathinfo($params['RPT']);
				$excel_rpt_filename = substr($pi['basename'], 0, strlen($pi['basename']) - strlen($pi['extension']) - 1)."_EXCEL.".$pi['extension'];
				
				$excel_rpt_path = getcwd().$folder_separator."assets\\rpts\\".$excel_rpt_filename;
				
				if(file_exists($excel_rpt_path)){
					$my_report = $excel_rpt_path;
				}
			}
			
			$my_pdf = getcwd().$folder_separator.$params['PATH'].$params['FILENAME'];	// RPT export to pdf file
			$my_filter = $params['FILTER01'];
			//echo $d02;
			
			$crapp = new com("CrystalRuntime.Application") or die("Unable to Create Object");
			$creport = $crapp->OpenReport($my_report, 1);	// call rpt report
			
			$gn = $creport->GroupNameFields();
			//$gn->Reset();
			//$gn->Add("{WB.LORRY}",0);
			
			$creport->Database->Tables(1)->SetLogOnInfo($server, $db, $userid, $passid);
			//$creport->Database->LogOnServer('odbc', $server, $db, $userid, $passid);
			//$creport->Database->LogOnServer('oledb', $server, $db, $userid, $passid);
			
			$creport->EnableParameterPrompting = 0;
			
			$pa = $creport->RecordSelectionFormula;

			$formula = $params['FORMULA'];
			if( $formula != "") {
				$creport->RecordSelectionFormula = $formula;
			}
			$creport->DiscardSavedData;
			$creport->ReadRecords();
			
			$filter10 = "";
			$sortnos = $creport->RecordSortFields->Count();

			$crxDatabaseField = array ();
			for ($i=1 ; $i<=$sortnos; $i++) {
				$crxDatabaseField[$i] = $creport->RecordSortFields->Item($i)->Field;
			} 			
			
			for($i=1; $i<=3; $i++) {				
				$ret = $this->setOrder($params, $i,$creport,$crxDatabaseField, $sortnos, $filter10);
				if($ret == "") break;
				$filter10=$ret;
			}
			$crxDatabaseField = NULL;
			
			$Sections = $creport->Sections();
			$TotalSections = $Sections->Count();
			
			//$creport->Sections->Item(5)->Suppress = True; 
			for($j=1; $j<=$TotalSections; $j++) {
				try {
					$Sec1 = $Sections->Item($j);
					$c1 = $Sec1->ReportObjects();
					if($c1->Count() != 1) continue;		//subreport section 1 object only
					
					$SubReportObj = $c1->Item(1);
					$SubReport = $SubReportObj->OpenSubreport();
				} catch (Exception $e) {
				    echo 'Caught exception: ',  $e->getMessage(), "\n";
				    continue;
				}

				$this->setFormula($params,$data,$SubReport,$filter10,$my_filter);
	        	$SubReport = NULL;
	      		$SubReportObj = NULL;
				$c1 = NULL;
	        	$Sec1 = NULL;
			}
			
			$this->setFormula($params,$data,$creport,$filter10,$my_filter);
			
			$title = $params['TITLE'];
			if($title != "") {
				$creport->ReportTitle = $title;
			}
			//$creport->SelectPrinter("winspool", "Panasonic KX-P1121", "lpt1:");
			
			/*
			 * PaperOrientation
			 * 1: Portrait	(Default)
			 * 2: Landscape
			 */
			
			//$Ori = $creport->PaperOrientation;
			//echo "...".$Ori;
			
			//$creport->PaperOrientation = (isset($params['ORIENTATION']) && $params['ORIENTATION'] == "L")? 2:1;
			
			//$creport->PaperOrientation = $Ori;
			//$ps = $creport->PaperSize;
			//echo "...".$ps;
			//$creport->PaperSize = 9;  //0 default 1 letter 9 a4 (pls ref. crystalcommon.h line 204
			//$creport->PrintOut(0);
			
			$creport->ExportOptions->DiskFileName = $my_pdf;	//export to pdf
			$creport->ExportOptions->PDFExportAllPages = TRUE;
			$creport->ExportOptions->DestinationType = 1;	// export to file
			
			/*
			 * FormatType
			 * 4	rtf
			 * 31	pdf
			 * 30	xls
			 * 36	xls
			 * 14	doc
			 */
			switch($params['EXPORT_TYPE']){
				case "EXCEL":
					$creport->ExportOptions->FormatType = 29;
					break;
				case "CSV":
					$creport->ExportOptions->FormatType = 5;
					break;
				case "PDF":
				default:
					$creport->ExportOptions->FormatType = 31;
					break;
			}
			$creport->Export(FALSE);
			
			//------ Release the variables ------ 
			$creport = NULL;
			$crapp = NULL;
			$ObjectFactory = NULL;
			
			$result = $params['PATH'].$params['FILENAME'];
			
			$return['DESTINATION'] = $result;
			
			if($params['EXPORT_TYPE'] == "CSV"){
				$this->csv_fix($return['DESTINATION']);
			}
		}
		
		$this->cr_temp_housekeeping();
		
		return $return;
	}

	function get_softfields($params=array()){
		$return = array(
		);
		
		$ci = get_instance();
		
		if(isset($params['RPT'], $params['FILENAME']) && strlen($params['RPT']) > 0 && strlen($params['FILENAME']) > 0 && file_exists($params['RPT'])){
			if(! isset($params['FORMULA'])) $params['FORMULA'] = "";

			$server = $this->db->hostname;
			$db = $this->db->database;
			$userid = $this->db->username;
			$passid = $this->db->password;
			
			//- Variables - for your RPT and PDF
			if(! isset($params['PATH'])) $params['PATH'] = $this->cr_temp_path;
			$folder_separator = "\\";
			if(strlen($params['PATH']) > 0 && ! in_array(substr($params['PATH'], -1), array("/", "\\"))){
				$params['PATH'] .= $folder_separator;
			}
			$my_report = getcwd().$folder_separator.$params['RPT'];
			
			$crapp = new com("CrystalRuntime.Application") or die("Unable to Create Object");
			$creport = $crapp->OpenReport($my_report, 1);	// call rpt report
			
			$creport->Database->Tables(1)->SetLogOnInfo($server, $db, $userid, $passid);
					
			$creport->EnableParameterPrompting = 0;
		
			$sortnos = $creport->RecordSortFields->Count();
			for ($i=1 ; $i<=$sortnos; $i++) {
				$crxDatabaseField = $creport->RecordSortFields->Item($i)->Field;
				$return[$i] = $crxDatabaseField->Name();
			} 			
			$return[$i] = "{}";
			//print_r($return);		
			
			//------ Release the variables ------ 
			$creport = NULL;
			$crapp = NULL;
			$ObjectFactory = NULL;
		}
		
		return $return;
	}
	
	function csv_fix($file=""){
		$row_delimiter = "\r\n";
		$delimiter = ",";
		
		if(file_exists($file)){
			$content = file_get_contents($file);
			
			$content_array = explode($row_delimiter, $content);
			
			if(count($content_array) > 0){
				$result_array = array();
				
				//Get Header---
//				$c_array = explode(",", $content_array[0]);
//				
//				$value_array = array();
//				for($x=0; $x<(int)(count($c_array) / 2); $x++){
//					array_push($value_array, $c_array[$x]);
//				}
//				
//				array_push($result_array, $value_array);
				//-------------
				
				//Get Content---
				$grand_all_content = array();
				foreach($content_array as $content){
					$c_array = explode(",", $content);
					
					$value_array = array();
					$grand_content = array();
					for($x=(int)(count($c_array) / 2); $x<count($c_array); $x++){
						if (preg_match('/\bGrand Total\b/',$c_array[$x])){
							// Requested on 26/9/2018 - Remove Grand Total from CSV format
							/*for($y=$x; $y<count($c_array); $y++){
								array_push($grand_content,$c_array[$y]);
							}*/
							break;
						}else{
							array_push($value_array, $c_array[$x]);
						}
					}
					array_push($result_array, $value_array);
					array_push($grand_all_content, $grand_content);
				}
				array_push($result_array, $grand_all_content[0]);
				//--------------

				//Write to File---
				$result = "";
				foreach($result_array as $value_array){
					$sub_result = "";
					foreach($value_array as $value){
						$sub_result = $this->fm->append($sub_result, $value, $delimiter);
					}
					
					$result = $this->fm->append($result, $sub_result, $row_delimiter);
				}
				
				file_put_contents($file, $result);
				//----------------
			}
		}
	}
	
	function cr_temp_housekeeping(){
		$file_lifespan = 1;	//Hours
		
		if(file_exists($this->cr_temp_path)){
			$dir_array = scandir($this->cr_temp_path);
			foreach($dir_array as $filename){
				if(! in_array($filename, array(".", "..", ".htaccess"))){
					$file_w_path = $this->cr_temp_path."\\".$filename;
					$last_modified_date = date("Y-m-d H:i:s", filemtime($file_w_path));
					
					if($last_modified_date < date("Y-m-d H:i:s", strtotime("-".$file_lifespan." hours"))){
						unlink($file_w_path);
					}
				}
			}
		}
	}
}