(作業メモ 20240408) ポータルサイトのコンテンツを移動する

教材のバージョンを 5.10 にあげたので，ポータルサイトのコンテンツを移動します。そうは言っても，College_Physics の分だけを移動します。以下にそのコードを残しておきます。これは直接データベースを操作するものです。

<?php

// College Physics のデータを、NLportal4.11 から NLportal5.1用に修正するプログラム

  // データベースに接続
  $DB = mysqli_connect( 'localhost', 'username', 'password', 'NLportal41') or die(mysqli_connect_error()); 
  
  mysqli_set_charset($DB, 'utf8');

  $DB2 = mysqli_connect( 'localhost', 'username', 'password', 'NLportal510') or die(mysqli_connect_error());
  
  mysqli_set_charset($DB2, 'utf8');


// コピー先($DB2)のデータベースの内容を削除する

$sql = "DROP TABLE `table_College_Physics`;";

mysqli_query($DB2, $sql) or die(mysqli_error($DB2));

echo "DROP table_College_Physics\n";


//  コピーする

$sql = "CREATE TABLE NLportal510.table_College_Physics LIKE NLportal41.table_College_Physics";

mysqli_query($DB2, $sql) or die(mysqli_error($DB2));

$sql = "INSERT INTO NLportal510.table_College_Physics SELECT * FROM NLportal41.table_College_Physics";

mysqli_query($DB2, $sql) or die(mysqli_error($DB2));

echo "copy table_College_Physics\n";



// 作業フォルダーを用意する

$timestamp = timestamp( );

$head = "tmp".$timestamp;

$foldername = mb_convert_encoding($head, "UTF-8", "auto");

passthru("mkdir -p /var/www/html/temporary/$foldername/tmp/");

echo "作業フォルダー $foldername を作成\n";



// key の用意

$file = '/xxxx/xxxx/xxxx/key.txt';

$keyxml = simplexml_load_string(file_get_contents($file));



// 以前、それぞれのコンテンツの正解を復号する　コードが　此処にあった。現在は暗号化されていない



$a_table_name = "`table_College_Physics`";

$sql = "SELECT id, title, zip FROM $a_table_name;";

$result = mysqli_query($DB2, $sql) or die(mysqli_error($DB2));

$rows = mysqli_num_rows($result);

echo "table_College_Physics has ".$rows." rows.\n";


for ($j = 0; $j < $rows; $j = $j + 1){

	$row = mysqli_fetch_array($result);

	$id = $row['id'];

	$a_file = fopen("/var/www/html/temporary/$foldername/tmp.zip","w");

	fwrite($a_file, $row['zip']);

	fclose($a_file);

	passthru("cd /var/www/html/temporary/$foldername/; unzip -q /var/www/html/temporary/$foldername/tmp.zip -d /var/www/html/temporary/$foldername/tmp/");






	// 画像や動画などの media ファイルをリストアップする

	$fullpath="/var/www/html/temporary/$foldername/tmp/html/";

	$mediaFileArray = array();

	if ($handle = opendir($fullpath)) {

		//    echo "Directory handle: $handle\n";
		//    echo "Entries:\n";

		/* ディレクトリをループする際の正しい方法です */
		
		while (false !== ($entry = readdir($handle))) {

			if (preg_match('/gif/i',$entry) || preg_match('/jpg/i',$entry) || preg_match('/png/i',$entry) || preg_match('/svg/i',$entry) || preg_match('/mp3/i',$entry) || preg_match('/mp4/i',$entry)) {

				$mediaFileArray[] = $entry;
			}

		}

		closedir($handle);
	}







	// htmlフォルダー内の index.html を更新する

	passthru("cp -f ./simple_question/html/index.html /var/www/html/temporary/$foldername/tmp/html/");
	



	$config_text = file_get_contents("/var/www/html/temporary/$foldername/tmp/configfile.xml");

	$config_xml = simplexml_load_string($config_text);



	$xmlstr = reConfig($config_xml, $mediaFileArray);
	


	// configfile.xml を置き換える

	$configfile = fopen("/var/www/html/temporary/$foldername/tmp/configfile.xml","w");

	fputs($configfile,$xmlstr);

	fclose($configfile);


	passthru("cd /var/www/html/temporary/$foldername/tmp ; zip -q -r /var/www/html/temporary/$foldername/new.zip ./configfile.xml ./html");

	$zipbinary = file_get_contents("/var/www/html/temporary/$foldername/new.zip");

	$zipbinary = mysqli_real_escape_string($DB2, $zipbinary);


	$sql = "UPDATE $a_table_name SET zip='$zipbinary' WHERE id='$id'";

	$result_UPDATE = mysqli_query($DB2, $sql) or die(mysqli_error($DB));

	echo "changed ".$row['title']."\n";


	// 作業ファイルを消す

	passthru("rm -r /var/www/html/temporary/$foldername/*");

}

// 以下、関数

function timestamp() {

	$stamp =  microtime();

	list($msec, $sec) = explode(" ", $stamp);

	$msec = $msec + 1;

	$msec = (string)$msec*1000000;

	$msec = substr($msec,1);

	$timestamp = $sec.$msec;

	return $timestamp;

}

function makePhpseclibKey($timestamp) {

	global $keyxml;

	$keynum = intval(substr($timestamp,-2,2));
	
	$keytxt = (string) $keyxml->key[$keynum]->public;

	return $keytxt;

}


function reConfig($xml, $mediaFileArray) {

	$timestamp = timestamp( );
	
	$xml_title = $xml->title;
	
	$xml_keyword = $xml->keyword;	
	
	$xml_edit_where = "kanaike.susi.oita-u.ac.jp";
		
	$xml_edit_when = $timestamp;	
	
	$keytxt = makePhpseclibKey($timestamp);
		
	$xml_TextHeight = $xml->TextHeight;



	$examinationSentenceStr = "";

	$examinationSentenceStr = $examinationSentenceStr . "<texmain></texmain>\n";
	
	$examinationSentenceStr = $examinationSentenceStr . "<addFile>\n";
	

	foreach ($mediaFileArray as $node) {
	
		$examinationSentenceStr = $examinationSentenceStr . "<node><label>" . htmlspecialchars($node) . "</label><base64></base64></node>\n";
			
	}

	$examinationSentenceStr = $examinationSentenceStr . "</addFile>\n\n";







$configstr = <<< end_of_quote
<?xml version="1.0" encoding="utf-8"?>

<root>

<version>5.10</version>

<server>
<url>https://kanaike.susi.oita-u.ac.jp</url>
</server>

<scriptFolder>/phpSimpleQuestionNL5.10</scriptFolder>

<cgi>
<url>/phpSimpleQuestionNL5.10/answertest_for_NL.php</url>
</cgi>

<tex>
<url>/phpSimpleQuestionNL5.10/fortex_xml_space.php</url>
</tex>

<refer>
<url>/phpSimpleQuestionNL5.10/refer_status.php</url>
</refer>

<LMSserver></LMSserver>

<UserID></UserID>

<realName></realName>

<record></record>


<q_ID>$timestamp</q_ID>
<q_ID_e></q_ID_e>

<author_name>College_Physics</author_name>

<title>$xml_title</title>

<keyword>$xml_keyword</keyword>

<edit>
<where>$xml_edit_where</where>
<when>$xml_edit_when</when>
</edit>

<phpseclibKey>$keytxt</phpseclibKey>

<TextHeight>$xml_TextHeight</TextHeight>

<examinationSentence>
$examinationSentenceStr
</examinationSentence>

<common>
<random>
<state>off</state>
</random>
<languageType>python</languageType>
<src></src>
</common>


end_of_quote;



$operation = <<< end_of_quote
# You can use base64, np and sympy.
rawmark = 'null'
feedback = 'null'
tips = []
#tips.append('Hello!')
status = {}
#status['statistics'] = 'no'
if tans == sans:
    status['coincide'] = 'yes'
end_of_quote;


	$i = 0;
	$str = '';


	foreach ($xml->question as $q) {


		$i = $i + 1;

		$str = $str."<question>\n\n<num>".$i."</num>\n";		
		$str = $str."<server>http://egret.susi.oita-u.ac.jp</server>\n";
		$str = $str."<AnswerTest>".$q->AnswerTest."</AnswerTest>\n";
		$str = $str."<searchForPastAnswers>yes</searchForPastAnswers>\n";		
		$str = $str."<Allotment>".$q->Allotment."</Allotment>\n";
		$str = $str.'<size>'.$q->size."</size>\n";
		$str = $str."<verticalSize>".$q->verticalSize."</verticalSize>\n";
		$str = $str.'<x>'.$q->x."</x>\n";
		$str = $str.'<y>'.$q->y."</y>\n";
		$str = $str."<SAns></SAns>\n";
		$str = $str.'<TAns>'.htmlentities((string)$q->TAns)."</TAns>\n";
		$str = $str.'<AnsTestOpt>'.$q->AnsTestOpt."</AnsTestOpt>\n";
		$str = $str.'<operation><text>' . htmlentities($operation) . "</text></operation>\n";
		$str = $str."</question>\n\n";

	}

	$configstr = $configstr.$str."</root>";

	return $configstr;
}