<?php
include '../conn_obj/conn_param.php';
date_default_timezone_set ( "America/Panama" );
error_reporting ( E_ALL & ~ E_NOTICE & ~ E_WARNING );

//define ( "WEBDOMAIN", "172.17.0.1" );
//define ( "DEFDBNAME", "" );
//define ( "DEFUSERNAME", "cloudbay7" );
//define ( "DEFDBPWD", "*Bay1720.*" );
define ( "SW_LOGFILE", 1 );

/* CONNECT TO A DB */
function connectDb() {
	global $Srv_Info;
	if ($conn = @mysqli_connect($Srv_Info['hostname'], $Srv_Info['username'], $Srv_Info['password'])) {
		mysqli_select_db($conn, $Srv_Info['dbname']);
		mysqli_query("SET NAMES 'utf8mb4'");
		return $conn;
	} else
		return false;
}

function running_in_Win() {
	if (strtolower ( substr ( PHP_OS, 0, 3 ) ) === 'win' || PHP_SHLIB_SUFFIX == 'dll' || PATH_SEPARATOR == ';') {
		return true;
	} else {
		return false;
	}
}

function logs2($text, $Include_Time = true) {
	IF (SW_LOGFILE == 0) {
		exit ();
	}

	$hoynowf = getdate ();

	$hora = str_pad ( $hoynowf ["minutes"], 2, "0", STR_PAD_LEFT );
	$timeStampf = $hoynowf ["year"] . "-" . $hoynowf ["mon"] . "-" . $hoynowf ["mday"] . " " . $hoynowf ["hours"] . ":" . $hora . ":" . $hoynowf ["seconds"];

	if (is_readable ( "logphp1.txt" )) {
		// echo "Tienes permiso de lectura!";
	} else {
		// echo "NO Tienes permiso de lectura!";
	}

	// chmod ("logphp.txt", 0777);

	// Entrada
	if (running_in_Win () == true) {
		$FileLog = sys_get_temp_dir () . '\log_php2.txt';
		$FileLog = 'C:\xampp\htdocs\flam\log_php2.txt';
	} else {
		$FileLog = getenv ( "HOME" ) . '/log_php2.txt';
	}
	$fp = fopen ( $FileLog, "a" );
	fputs ( $fp, ($Include_Time == true ? $timeStampf . " : " : '') . $text . "\r\n" );
	fclose ( $fp );
}

/* RUN SCRIPT FOR REPAIR AND OPTIMIZE TABLES */
function fixTables($pCon, $dbname) {
	// search for all the tables of
	// a db and run repair and optimize
	// note: this can take a lot of time
	// if you have big/many tables.
	$sql = "SHOW TABLES FROM " . $dbname;
	if ($rs = mysqli_query ( $pCon, $sql )) {
		while ( $record = mysqli_fetch_array ( $rs ) ) {
			mysqli_query ( $pCon, "REPAIR TABLE " . $record [0] );
			mysqli_query ( $pCon, "OPTIMIZE TABLE " . $record [0] );
		}
		mysqli_free_result ( $rs );
	}
}

/* CONVERT A RECORDSET TO HTML TABLE */
function getHtmlTable($pCon, $rs) {
	// receive a record set and print
	// it into an html table
	$out = '<table>';
	while ( $field = mysqli_fetch_field ( $rs ) ) {
		$out .= "<th>" . $field->name . "</th>";
		while ( $linea = mysqli_fetch_row ( $rs ) ) {
			$out .= "<tr>";
			foreach ( $linea as $valor_col )
				$out .= '<td>' . $valor_col . '</td>';
			$out .= "</tr>";
		}
	}
	$out .= "</table>";
	return $out;
}

/* MAKE A STRING WITH FIELD NAMES */
function getCommaFields($pCon, $table, $excepts = "") {
	// get a string with the names of the fields of the $table,
	// except the onews listed in '$excepts' param
	$out = "";
	$sql = "SHOW COLUMNS FROM `$table`";
	if ($rs = mysqli_query ( $pCon, $sql )) {
		while ( $r = mysqli_fetch_array ( $rs ) ) {
			if (! stristr ( "," . $r ['Field'] . ",", $excepts ))
				$out .= ($out ? "," : "") . $r ['Field'];
		}
		mysqli_free_result ( $rs );
	} else
		die ( mysqli_error ( $pCon ) );
	return $out;
}

/* MAKE A STRING WITH COMMA SEPARATED VALUES */
function getCommaValues($pCon, $sql) {
	// execute a $sql query and return
	// all the first value of the rows in
	// a comma separated string
	$out = "";
	if ($rs = mysqli_query ( $pCon, $sql )) {
		while ( $r = mysqli_fetch_row ( $rs ) ) {
			$out .= ($out ? "," : "") . $r [0];
		}
		mysqli_free_result ( $rs );
	} else
		die ( mysqli_error ( $pCon ) );
	return $out;
}

/* GET VALUES OF A ENUM/SET FIELD */
function getScalar($pCon, $sql, $def = "") {
	if ($rs = mysqli_query ( $pCon, $sql )) {
		$r = mysqli_fetch_array ( $rs );
		mysqli_free_result ( $rs );
		return $r [0];
	}
	return $def;
}

/* RUN A QUERY AND GET THE FIRST VALUE */
function getRow2($pCon, $sql, &$rec_cnt, $asjson = false) {
	$rec_cnt = 0;
	if ($rs = mysqli_query ( $pCon, $sql )) {
		$r = mysqli_fetch_array ( $rs, MYSQLI_ASSOC );
		$rec_cnt = mysqli_num_rows ( $rs );
		mysqli_free_result ( $rs );
		if ($asjson == true) {
			return json_encode ( $r );
		} else {
			return $r;
		}
	}
	return "";
}

/* RUN A QUERY AND GET THE ALL VALUES */
function getRowAll2($pCon, $sql, &$rec_cnt, $asjson = false) {
	$rec_cnt = 0;
	if ($rs = mysqli_query ( $pCon, $sql )) {
		$rec_cnt = mysqli_num_rows ( $rs );
		while ( $record = mysqli_fetch_array ( $rs, MYSQLI_ASSOC ) ) {
			$r [] = $record;
		}
		mysqli_free_result ( $rs );

		if ($asjson == true) {
			return json_encode ( $r );
		} else {
			return $r;
		}
	}
	return "";
}

/* RUN A QUERY AND GET THE FIRST VALUE */
function getRow($conn, $sql, &$rec_cnt, $asjson = false) {
	mysqli_set_charset ( $conn, "utf8" );
	$rec_cnt = 0;
	if ($rs = $conn->query ( $sql )) {
		$rec_cnt = $rs->num_rows;
		$r = $rs->fetch_array ();
		// $rec_cnt = count($r);
		$rs->free ();

		if ($asjson == true) {
			$output = json_encode ( $r, JSON_UNESCAPED_UNICODE );
			return $output;
		} else {
			return $r;
		}
	}
	return "";
}

/* RUN A QUERY AND GET THE ALL VALUES */
function getRowAll($conn, $sql, &$rec_cnt, $asjson = false) {
	mysqli_set_charset ( $conn, "utf8" );
	$rec_cnt = 0;
	if ($rs = $conn->query ( $sql )) {
		$rec_cnt = $rs->num_rows;
		while ( $record = $rs->fetch_Assoc () ) {
			$r [] = $record;
		}
		$rs->free ();

		if ($asjson == true) {
			$output = json_encode ( $r, JSON_UNESCAPED_UNICODE );
			return $output;
		} else {
			return $r;
		}
	}
	return "";
}

function update_Sql($conn, $sql, &$pAffected_rows) {
	mysqli_set_charset ( $conn, "utf8" );
	if (! mysqli_query( $conn, $sql ) ) {
		$pAffected_rows = - 1;
		return false;
	} else {
		$pAffected_rows = $conn->affected_rows;
		return true;
	}
}

function update_Sql_Multi($pCon, $sql, &$pAffected_rows) {
	mysqli_set_charset ( $conn, "utf8" );
	if ($pCon->multi_query ( $sql )) {
		$pAffected_rows = $pCon->affected_rows;
		return true;
	} else {
		$pAffected_rows = - 1;
		return false;
	}
}

/* DUPLICATE A SPECIFIED ROW */
function duplicateRow($pCon, $table, $primaryField, $primaryIDvalue) {
	// duplicate one record in a table
	// and return the id
	$fields = getCommaFields ( $pCon, $table, $primaryField );
	$sql = "INSERT INTO " . $table . " (" . $fields . ") SELECT " . $fields . " FROM " . $table . " WHERE " . $primaryField . "='" . $pCon->escape_string ( $primaryIDvalue ) . "' LIMIT 0,1";
	if (! mysqli_query ( $pCon, $sql )) {
		die ( mysqli_error ( $pCon ) );
	}
	return mysqli_insert_id ( $pCon );
}

/* CONVERT A RECORDSET TO JSON AND CSV */
function convertResult($rs, $type, $jsonmain = "") {
	// receive a recordset and convert it to csv
	// or to json based on "type" parameter.
	$jsonArray = array ();
	$csvString = "";
	$csvcolumns = "";
	$count = 0;
	while ( $r = mysqli_affected_rows ( $rs ) ) {
		for($k = 0; $k < count ( $r ); $k ++) {
			mysqli_field_seek ( $rs, $k );
			$finfo = mysqli_fetch_field ( $rs );
			$jsonArray [$count] [$finfo->name] = $r [$k];
			$csvString .= ",\"" . $r [$k] . "\"";
			if (! $count)
				$csvcolumns .= ($csvcolumns ? "," : "") . $finfo->name;
		}
		$csvString .= "\n";
		$count ++;
	}
	$jsondata = "{\"$jsonmain\":" . json_encode ( $jsonArray ) . "}";
	$csvdata = str_replace ( "\n,", "\n", $csvcolumns . "\n" . $csvString );
	return ($type == "csv" ? $csvdata : $jsondata);
}

function clean($string) {
	$string = str_replace ( ' ', '-', $string ); // Replaces all spaces with hyphens.
	$string = preg_replace ( '/[^A-Za-z0-9\-]/', '', $string ); // Removes special chars.

	return preg_replace ( '/-+/', '-', $string ); // Replaces multiple hyphens with single one.
}

function isUTF8($encoding, $value) {
	return (($encoding === 'UTF-8') && (utf8_encode ( utf8_decode ( $value ) ) === $value));
}

function utf8tify(&$value) {
	$encodings = [
			'UTF-8',
			'ISO-8859-1',
			'ASCII'
	];

	mb_internal_encoding ( 'UTF-8' );
	mb_substitute_character ( 0xfffd ); // REPLACEMENT CHARACTER
	mb_detect_order ( $encodings );

	$stringEncoding = mb_detect_encoding ( $value, $encodings, true );

	if (! $stringEncoding) {
		$value = null;
		throw new \RuntimeException ( "Unable to identify character encoding in sanitizer." );
	}

	if (isUTF8 ( $stringEncoding, $value )) {
		return;
	} else {
		$value = mb_convert_encoding ( $value, 'UTF-8', $stringEncoding );
		$stringEncoding = mb_detect_encoding ( $value, $encodings, true );

		if (isUTF8 ( $stringEncoding, $value )) {
			return;
		} else {
			$value = null;
			throw new \RuntimeException ( "Unable to convert character encoding from ISO-8859-1, or ASCII, to UTF-8 in sanitizer." );
		}
	}

	return;
}

// example to json string: $StrJson3='{"regs_cnt":"1","operacion_cxc":[{"autoin":"220000009","usermodify":"1","useroption":"1"}]}';
// PARAMETERS:
// $JsonStr= json string,
// $pObjCon = connection object;
// $pDataba = database name,
// $TablName = tablename,
// $pSaveLog: savelog
function json_insert_to_mysql($pObjCon, $pJsonStr, $pDataba, $TablName, $pSaveLog) {
	$JsonAr = json_decode ( $pJsonStr, TRUE );
	// $JsonAr = $pJsonStr;
	print_r ( $JsonAr [$TablName] );

	$TableN = $JsonAr [$TablName];
	$regcnt = $JsonAr ['regs_cnt'];

	$ib = 0;

	while ( $ib < $regcnt ) {

		$opfinal = $TableN [$ib];

		$str_fields = '';
		$str_values = '';
		$str_update = '';
		$i = 0;
		$valuekey = '';
		$fieldkey = '';
		foreach ( $opfinal as $field => $value ) {

			$fieldkey = $field;
			$valuekey = $value;
			// if($i<9999) {
			if ($i > 0) {
				$field = ',' . $field;
				$fieldkey = ',' . $fieldkey;

				if ($value == '') {
					$value = $value . ',null';
					$valuekey = 'null';
				} else {
					$value = ',"' . $value . '"';
					$valuekey = '"' . $valuekey . '"';
				}
			} else {
				$value = '"' . $value . '"';
			}

			$str_update = $str_update . $fieldkey . '=' . $valuekey;
			$str_fields = $str_fields . $field;
			$str_values = $str_values . $value;
			$i = $i + 1;
		}

		$sql = "INSERT INTO " . $pDataba . "." . $TablName . " (" . $str_fields . ") VALUES (" . $str_values . ") ON DUPLICATE KEY UPDATE " . $str_update . "";

		if ($pSaveLog == '1') {
			LogsFile ( 'SQL: ' . $sql );
		}

		$SQLupdate = $sql;

		if ($pObjCon->query ( $SQLupdate ) === TRUE) {

			$result = 'true';
		} else {

			$result = 'false';
		}

		$ib ++;
	}
	if ($pSaveLog == '1') {
		LogsFile ( 'RESP: ' . $result );
	}

	return $result;
}

function json_insert_to_sqlite($pDB_Sqlite, $pJsonStr, $TablName, $pSaveLog) {
	$JsonAr = json_decode ( $pJsonStr, TRUE );
	$TableN = $JsonAr [$TablName];
	$regcnt = count ( $TableN );

	$SqlInsert = '';
	$SqlValues = '';
	$Sql_Segm = '';

	$ib = 0;

	while ( $ib < $regcnt ) {
		if ($ib == 0) {
			$DeleteTable = 'DELETE FROM ' . ($pDB_Sqlite == '' ? '' : $pDB_Sqlite . '.') . $TablName . ';' . "\n";
		}
		;

		$opfinal = $TableN [$ib];

		$str_fields = '';
		$str_values = '';
		$str_update = '';
		$i = 0;
		$valuekey = '';
		$fieldkey = '';
		foreach ( $opfinal as $field => $value ) {

			$fieldkey = $field;
			$value = str_replace ( array (
					"\r\n",
					"\n",
					"\r"
			), ' ', $value );
			$valuekey = $value;
			// if($i<9999) {
			if ($i > 0) {
				$field = ',' . $field;
				$fieldkey = ',' . $fieldkey;

				if ($value == '') {
					$value = $value . ',null';
					$valuekey = 'null';
				} else {
					$value = ',"' . $value . '"';
					$valuekey = '"' . $valuekey . '"';
				}
			} else {
				$value = '"' . $value . '"';
			}
			;

			$str_update = $str_update . $fieldkey . '=' . $valuekey;
			$str_fields = $str_fields . $fieldkey;
			$str_values = $str_values . $value;
			$i = $i + 1;
		}
		;

		$Frac = Div_result_Decimal ( $ib / 20 );

		if ($SqlInsert == '') {

			$SqlInsert = $SqlInsert . "INSERT INTO " . ($pDB_Sqlite == '' ? '' : $pDB_Sqlite . '.') . $TablName . " (" . $str_fields . ") VALUES \n";
		}
		$ib ++;

		$SqlValues = $SqlValues . "(" . $str_values . ")";

		$Frac = Div_result_Decimal ( $ib / 20 );
		if ($Frac == 0) {
			$Sql_Segm = $Sql_Segm . $SqlInsert . $SqlValues . ';' . "\n";
			$SqlInsert = '';
			$SqlValues = '';
		} else {

			$SqlValues = $SqlValues . ($ib == $regcnt ? ";" : ",") . "\n";
		}
	}
	$value = str_replace ( array (
			"\r\n",
			"\n",
			"\r"
	), ' ', $value );

	$sql = $DeleteTable . $Sql_Segm . $SqlInsert . $SqlValues;
	if ($pSaveLog == '1') {
		LogsFile ( 'SQL: ' . $sql );
	}

	$result = $sql;

	if ($pSaveLog == '1') {
		LogsFile ( 'RESP: ' . $result );
	}

	return $result;
}

function Div_result_Decimal($pNumber) {
	$whole = floor ( $pNumber ); // 1
	$fraction = $pNumber - $whole; // .25
	return $fraction;

}

function return_field_type_value($field_type) {
	switch ($field_type) {
		case MYSQLI_TYPE_DECIMAL :
			return 0.00;
			break;
		case MYSQLI_TYPE_NEWDECIMAL :
			return 0.00;
			break;
		case MYSQLI_TYPE_FLOAT :
			return 0.00;
			break;
		case MYSQLI_TYPE_DOUBLE :
			return 0.00;
			break;
		case MYSQLI_TYPE_BIT :
			return 0;
			break;
		case MYSQLI_TYPE_TINY :
			return 0;
			break;
		case MYSQLI_TYPE_SHORT :
			return 0;
			break;
		case MYSQLI_TYPE_LONG :
			return 0;
			break;
		case MYSQLI_TYPE_LONGLONG :
			return 0;
			break;
		case MYSQLI_TYPE_INT24 :
			return 0;
			break;
		case MYSQLI_TYPE_YEAR :
			return 0;
			break;
		// case MYSQLI_TYPE_ENUM: ;
		case MYSQLI_TYPE_TIMESTAMP :
			return '2009-01-01 01:00:00';
			break;
		case MYSQLI_TYPE_DATE :
			return '2009-01-01';
			break;
		case MYSQLI_TYPE_TIME :
			return '01:00:00';
			break;
		case MYSQLI_TYPE_DATETIME :
			return '2009-01-01 01:00:00';
			break;
		case MYSQLI_TYPE_NEWDATE :
			return '2009-01-01';
			break;
		// case MYSQLI_TYPE_INTERVAL:
		// case MYSQLI_TYPE_SET:
		case MYSQLI_TYPE_VAR_STRING :
			return '';
			break;
		case MYSQLI_TYPE_STRING :
			return '';
			break;
		case MYSQLI_TYPE_CHAR :
			return '';
			break;
		// case MYSQLI_TYPE_GEOMETRY: return 's';

		case MYSQLI_TYPE_TINY_BLOB :
			return null;
			break;
		case MYSQLI_TYPE_MEDIUM_BLOB :
			return null;
			break;
		case MYSQLI_TYPE_LONG_BLOB :
			return null;
			break;
		case MYSQLI_TYPE_BLOB :
			return null;
			break;
		default :
			trigger_error ( "unknown type: $field_type" );
			return null;
			break;
	}
	
}

function randStrGen($len) {
	$result = "";
	$chars = "abcdefghijklmnopqrstuvwxyz$_?!-0123456789";
	$charArray = str_split ( $chars );
	for($i = 0; $i < $len; $i ++) {
		$randItem = array_rand ( $charArray );
		$result .= "" . $charArray [$randItem];
	}
	return $result;
}

function randomNumber($length) {
	$min = str_repeat ( 0, $length - 1 ) . 1;
	$max = str_repeat ( 9, $length );
	return mt_rand ( $min, $max );
}

function check_field_type($connection, $schemaName, $TableName, $FieldName) {
	$regs = 0;
	$Sql_Line = "SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema = '" . $schemaName . "' AND TABLE_NAME = '" . $TableName . "' AND COLUMN_NAME = '" . $FieldName . "'";
	$data_flds = getRow ( $connection, $Sql_Line, $regs, false );
	$value = $data_flds ['DATA_TYPE'];
	$data_flds = null;
	return $value;
}

function jsonmysql_insert_result_str($tabla, $reg_p, $nombre, $connection1, $database, $database_dest, $Excl_Field, $On_Dlupl, $Execute)
{
	$bresult = false;
	if (($Excl_Field == '') or ($Excl_Field == 'NULL') or ($Excl_Field == 'null')) {
		$Excl_Field = '-NONE-';
	}

	if (($On_Dlupl == '') or ($On_Dlupl == 'NULL') or ($On_Dlupl == 'null')) {
		$On_Dlupl = '-NONE-';
	}

	$Excl_Key = '';
	if (strtoupper($nombre) == strtoupper('operacion')) {
		$Excl_Key = 'id_op';
	}

	if (($Execute == '') or ($Execute == 'NULL') or ($Execute == 'null')) {
		$Execute = 0;
	}

	$ib = 0;
	$Sql_Insert_Res = '';
	while ($ib < $reg_p) {
		$opfinal = $tabla[$ib];

		$cadena = '';
		$cadena2 = '';
		$cadena3 = '';
		$i = 0;
		$valuekey = '';
		$fieldkey = '';
		foreach ($opfinal as $field => $value) {
			#if (check_field_type($connection1, $database, $nombre, $field )=="decimal"){
			#	$value=str_replace(",", ".", $value);
			#}
			if ($field != $Excl_Field) {
				$fieldkey = $field;
				$valuekey = $value;
				if ($i > 0) {
					$field = ',' . $field;
					$fieldkey = ',' . $fieldkey;

					if ( ($value != 0) and ($value != '') and  $value == null) {
						$value = ',null';
						$valuekey = 'null';
					} else {
						$value = ',"' . $value . '"';
						$valuekey = '"' . $valuekey . '"';
					}
				} else {
					$value = '"' . $value . '"';
				}

				if ($On_Dlupl != '-NONE-') {
					if ($fieldkey != $Excl_Key) {
						$cadena3 = $cadena3 . $fieldkey . '=VALUES(' . str_replace(',', '', $fieldkey) . ') ';
					}
				}

				$cadena = $cadena . $field;
				$cadena2 = $cadena2 . $value;
				$i = $i + 1;
			}
		}

		// $sql = "INSERT INTO " . $database_dest . "." . $nombre . " (" . $cadena . ") VALUES (" . $cadena2 . ") ON DUPLICATE KEY UPDATE " . $cadena3 . "";

		$sql = "INSERT INTO " . $database_dest . "." . $nombre . " (" . $cadena . ") VALUES (" . $cadena2 . ") ";

		if ($On_Dlupl != '-NONE-') {
			IF (trim($cadena3) != '') {
				$sql = $sql . " ON DUPLICATE KEY UPDATE " . $cadena3 . "";
			}
		}

		$Sql_Insert_Res = $Sql_Insert_Res . $sql . ';' . CHR(13);
		$Sql_Insert_Res = str_replace("0000-00-00", "2009-01-01", $Sql_Insert_Res);
		$Sql_Insert_Res = str_replace("0000-00-00 00:00:00", "2009-01-01 01:01:01", $Sql_Insert_Res);
		$Sql_Insert_Res = str_replace("UPDATE ,", "UPDATE ", $Sql_Insert_Res);
		$ib ++;
	}
	return $Sql_Insert_Res;
}

function mysql_to_json($conn, $cdatabase,$sql_ln) {
mysqli_select_db($conn, $cdatabase);
// Get the data
$result = mysqli_query($conn, $sql_ln);
$row    = mysqli_fetch_assoc($result);

// Fix the types    
$fixed = array();
foreach ($row as $key => $value) {
    $info = mysqli_fetch_field($result);
    if (in_array($info->type, array(
            MYSQLI_TYPE_TINY, MYSQLI_TYPE_SHORT, MYSQLI_TYPE_INT24,    
            MYSQLI_TYPE_LONG, MYSQLI_TYPE_LONGLONG)))
	{
        $fixed[$key] =intval($value);
	} 
	else 
	{
		if (in_array($info->type, array(
            MYSQLI_TYPE_DECIMAL, 
            MYSQLI_TYPE_FLOAT, MYSQLI_TYPE_DOUBLE))) 
		{
			$fixed[$key] =doubleval($value);
		}
		else 
		{		
			$fixed[$key] = utf8_encode($value);
		}
    }
}
echo json_encode($fixed,JSON_UNESCAPED_UNICODE)."\n";
}

function cast_query_results($rs) {
    $fields = mysqli_fetch_fields($rs);
    $data = array();
    $types = array();
    foreach($fields as $field) {
        switch($field->type) {
            case 3:
                $types[$field->name] = 'int';
                break;
            case 4:
                $types[$field->name] = 'float';
                break;
            default:
                $types[$field->name] = 'string';
                break;
        }
    }
    while($row=mysqli_fetch_assoc($rs)) array_push($data,$row);
    for($i=0;$i<count($data);$i++) {
        foreach($types as $name => $type) {
            settype($data[$i][$name], $type);
        }
    }
    return $data;
}
?>