<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
 
	function dbCon () {
		$uid='sa';
		$pwd='P4ssword';
		$db='DB_PASI_NEW';

		$connectionInfo = array("UID" => $uid, "PWD" => $pwd, "Database" => $db);
		$serverName = "teana";
		$conn = sqlsrv_connect( $serverName, $connectionInfo);
/* 		
		if( $conn ) {
			echo "Connection established.<br />";
		}else{
			echo "Connection could not be established.<br />";
			die( print_r( sqlsrv_errors(), true));
		}
*/
		return $conn;
	}
	
	function exec_sp_array($XML,$storeProcedure, &$stsno,&$stsmsg,&$responce,&$detail){
		$conn=dbCon();
		if( $conn )
		{
			$tsql = "EXEC ".$storeProcedure." ?";
			/* Assign parameter values. */
			$param1 = $XML->saveXML(); 
			 // echo $param1;die();
			$params = array( $param1);
	 		//$stmt = sqlsrv_query($conn, $tsql, $params ,  array("Scrollable"=>"static"));
			$stmt = sqlsrv_query( $conn, $tsql, $params);
			if( $stmt === false ) {	 
				$stsno='90';
				$errorsql= sqlsrv_errors() ;
				$stsmsg=$errorsql[0][2];
	 		}else{
				if( sqlsrv_fetch( $stmt ) === false) {
					$stsno='91';
					$errorsql= sqlsrv_errors() ;
 					$stsmsg=$errorsql[0][2];
				}else
				{
 					$stsno = sqlsrv_get_field($stmt,0, SQLSRV_PHPTYPE_STRING('UTF-8') );
					$stsmsg = sqlsrv_get_field($stmt,1, SQLSRV_PHPTYPE_STRING('UTF-8') );
					$dataxml = sqlsrv_get_field($stmt, 2, SQLSRV_PHPTYPE_STRING('UTF-8') );
							
			 			if ($dataxml!=''){
						
						$doc_report = DOMDocument::loadXML($dataxml);
						$xpath_report = new DOMXpath($doc_report);
						$datarows = $xpath_report->query('//result/xrow');
						$i=0;
						$totalrows=0;
						if ($xpath_report->query('//result/detail')->item(0)!=null){
							foreach ($xpath_report ->query('//result/detail')->item(0)->childNodes as $field) {
							$detail[$field->nodeName]=$field->nodeValue;
							}
									$rowspage=$detail['rowspage'];
									$totalrows=$detail['rows'];
									$pages=$detail['pages'];
				
						}
						 
						foreach ($datarows as $per_row) {
							foreach ($per_row->childNodes as $field) {
							$rows['rows'][$i][$field->nodeName]=$field->nodeValue;
							}
							$i++;
						}
						
						}
		 			sqlsrv_free_stmt( $stmt);
 		 		}
			}
		//echo $json."\n";
		sqlsrv_close( $conn);
		}
		else
		{
				$stsno='99';
				$stsmsg='Connection could not be established.';	
 		}
	}

	function exec_sp_array_for_table($XML,$storeProcedure, &$stsno,&$stsmsg,&$responce,&$page){
		$conn=dbCon();
		
		/*
		if( $conn ) {
			echo "Connection established.<br />";
		}else{
			echo "Connection could not be established.<br />";
			die( print_r( sqlsrv_errors(), true));
		}
		*/
		if( $conn )
		{
			$tsql = "EXEC ".$storeProcedure." ?";
			/* Assign parameter values. */
			$param1 = $XML->saveXML(); 
			 // echo $param1;die();
			$params = array( $param1);
	 		//$stmt = sqlsrv_query($conn, $tsql, $params ,  array("Scrollable"=>"static"));
			$stmt = sqlsrv_query( $conn, $tsql, $params);
			
			//print_r ($stmt);die();
			if( $stmt === false ) {	
				//echo 'error';
				$stsno='90';
				$errorsql= sqlsrv_errors() ;
				$stsmsg=$errorsql[0][2];
	 		}else{
				if( sqlsrv_fetch( $stmt ) === false) {
					$stsno='91';
					$errorsql= sqlsrv_errors() ;
 					$stsmsg=$errorsql[0][2];
				}else
				{
					//echo 'masuk';
 					$stsno = sqlsrv_get_field($stmt,0, SQLSRV_PHPTYPE_STRING('UTF-8') );
					$stsmsg = sqlsrv_get_field($stmt,1, SQLSRV_PHPTYPE_STRING('UTF-8') );
					$dataxml = sqlsrv_get_field($stmt, 2, SQLSRV_PHPTYPE_STRING('UTF-8') );
							
			 			if ($dataxml!=''){
						
						$doc_report = DOMDocument::loadXML($dataxml);
						$xpath_report = new DOMXpath($doc_report);
						$datarows = $xpath_report->query('//result/xrow');
						$i=0;
						$totalrows=0;
						if ($xpath_report->query('//result/detail')->item(0)!=null){
							foreach ($xpath_report ->query('//result/detail')->item(0)->childNodes as $field) {
							$detail[$field->nodeName]=$field->nodeValue;
							}
									$rowspage=$detail['rowspage'];
									$totalrows=$detail['rows'];
									$pages=$detail['pages'];
				
						}
						$responce['page']=$page;
						$responce['total']=$pages;
						$responce['records']=$totalrows; 
						foreach ($datarows as $per_row) {
							foreach ($per_row->childNodes as $field) {
							$responce['rows'][$i][$field->nodeName]=$field->nodeValue;
							}
							$i++;
						}
						
						}
		 			sqlsrv_free_stmt( $stmt);
 		 		}
			}
		//echo $json."\n";
		sqlsrv_close( $conn);
		}
		else
		{
				$stsno='99';
				$stsmsg='Connection could not be established.';	
 		}
	}
	function exec_sp_xml($XML,$storeProcedure, &$stsno,&$stsmsg,&$rows,&$detail){
		$conn=dbCon();
		if( $conn )
		{
			$tsql = "EXEC ".$storeProcedure." ?";
			/* Assign parameter values. */
			$param1 = $XML->saveXML(); 
			$params = array( &$param1);
			$stmt = sqlsrv_query( $conn, $tsql, $params);
			if( $stmt === false ) {
				$stsno='90';
				$errorsql= sqlsrv_errors() ;
				$stsmsg=$errorsql[0][2];
			}else{
				if( sqlsrv_fetch( $stmt ) === false) {
					$stsno='91';
					$errorsql= sqlsrv_errors() ;
 					$stsmsg=$errorsql[0][2];
				}else
				{
 					$stsno = sqlsrv_get_field($stmt,0, SQLSRV_PHPTYPE_STRING('UTF-8') );
					$stsmsg = sqlsrv_get_field($stmt,1, SQLSRV_PHPTYPE_STRING('UTF-8') );
					$dataxml = sqlsrv_get_field($stmt, 2, SQLSRV_PHPTYPE_STRING('UTF-8') );
					
						
					if ($stsno=='0'){
						if ($dataxml!=''){
					
							$data = DOMDocument::loadXML($dataxml);
							$xpath_report = new DOMXpath($data);
							$rows = $xpath_report->query('//result/xrow');
							$detail=$xpath_report->query('//result/detail');
							}
						}
					sqlsrv_free_stmt( $stmt);
 		 		}
			}
		echo $json."\n";
		sqlsrv_close( $conn);
		}
		else
		{
				$stsno='99';
				$stsmsg='Connection could not be established.';
	
 		}
	}
	  
	// function add_car($doc,$car){
        // $node = $doc->getElementsByTagName('param')->item(0);
        // $field = $node->getElementsByTagName('field')->item(0);
        // if ($field->getElementsByTagName('car')->item(0)!=null){
            // $listfile = $field->getElementsByTagName('car')->item(0);
        // }
        // else {
            // $listfile = $doc->createElement('car', '');
            // $field->appendChild($listfile);
        // }
        // $l = $doc->createElement('l', '');
        // $listfile->appendChild($l);

        // $element = $doc->createElement('id', $car);
        // $l->appendChild($element);
    // }
	
	// function add_hdr($doc,$hdr){
        // $node = $doc->getElementsByTagName('param')->item(0);
        // $field = $node->getElementsByTagName('field')->item(0);
        // if ($field->getElementsByTagName('hdr')->item(0)!=null){
            // $listfile = $field->getElementsByTagName('hdr')->item(0);
        // }
        // else {
            // $listfile = $doc->createElement('hdr', '');
            // $field->appendChild($listfile);
        // }
        // $l = $doc->createElement('list', '');
        // $listfile->appendChild($l);

        // $element = $doc->createElement('id', $hdr);
        // $l->appendChild($element);
	// }
	