リスト3 システムのトップページ(index.php)
<html>
<head>
<meta http-equiv="Content-Type" content="text/html;charset=utf-8">
<title>経費精算サンプル</title>
</head>
<body>
<h1>経費精算サンプル</h1>
<?php
//PHPAzureのライブラリの読み込み
set_include_path(ini_get('include_path') . PATH_SEPARATOR . './lib');
require_once 'Microsoft/WindowsAzure/Storage/Blob.php';
require_once 'Microsoft/WindowsAzure/RetryPolicy/NoRetry.php';
//PHPAzureで利用するストレージアカウント情報の記述
$storageUrl = Microsoft_WindowsAzure_Storage::URL_CLOUD_BLOB;
$storageAccount = 'samplestorage001';
$storageKey = '3YrKN*************==';
//PHPExcelで利用するライブラリの読み込み
require_once 'Classes/PHPExcel.php';
require_once 'Classes/PHPExcel/IOFactory.php';
//MySQLにアクセスするための情報
$hostname = "localhost";
$database = "sample_db";
$username = "sample_db_user";
$password = "*********";
//ファイルをアップロードするディレクトリ
$updir = "./files/";
//Blobインスタンスの初期化とストリームラッパの登録
$blob_client = new Microsoft_WindowsAzure_Storage_Blob(
$storageUrl,
$storageAccount,
$storageKey,
false,
Microsoft_WindowsAzure_RetryPolicy_RetryPolicyAbstract::retryN(10, 250)
);
$blob_client->registerStreamWrapper();
//MySQLに接続
$link = mysql_connect($hostname, $username, $password);
if (!$link) { die(mysql_error()); }
//ファイルのアップロード処理
if (is_uploaded_file($_FILES["uploadfile"]["tmp_name"])) {
if (move_uploaded_file($_FILES["uploadfile"]["tmp_name"], $updir.$_FILES["uploadfile"]["name"])) {
echo "アップロードしました: ".$_FILES["uploadfile"]["name"];
} else {
echo "アップロードできませんでした";
}
}
//XLSからデータ取得、CSVに加工
$xlsfile = $_FILES["uploadfile"]["name"];
if ($xlsfile == "") {
echo "経費精算書をアップロードしてください";
} else {
//Excel5(Excel97-2003形式)の読み込み
$xlsReader = PHPExcel_IOFactory::createReader('Excel5');
$xlsObject = $xlsReader->load($updir.$xlsfile);
//アクティブなシートを選択
$xlsObject->setActiveSheetIndex(0);
$sheet = $xlsObject->getActiveSheet();
//経費精算書のセルからデータを取得
$month = strftime("%Y/%m/%d",PHPExcel_Shared_Date::ExcelToPHP($sheet->getCell('A4') ->getValue()));
$kosai = $sheet->getCell('C23')->getCalculatedValue();
$kaigi = $sheet->getCell('D23')->getCalculatedValue();
$kotsu = $sheet->getCell('E23')->getCalculatedValue();
$tusin = $sheet->getCell('F23')->getCalculatedValue();
$shomo = $sheet->getCell('G23')->getCalculatedValue();
$tosho = $sheet->getCell('H23')->getCalculatedValue();
$other = $sheet->getCell('I23')->getCalculatedValue();
$gokei = $sheet->getCell('C24')->getCalculatedValue();
//CSVファイルに加工
$csvfile = basename($xlsfile,"xls")."csv";
$fp = fopen($updir.$csvfile,"w");
$str =
'"2110",,"","'.$month.'","","","","対象外",0,0,"未払費用","立替経費","","対象外","'.$gokei.'",0,"","","",3,"","","0","0","no"'."\n".
'"2100",,"","'.$month.'","交際費","","","課対仕入込","'.$kosai.'",0,"","","","対象外",0,0,"","","",3,"","","0","0","no"'."\n".
'"2100",,"","'.$month.'","会議費","","","課対仕入込","'.$kaigi.'",0,"","","","対象外",0,0,"","","",3,"","","0","0","no"'."\n".
'"2100",,"","'.$month.'","旅費交通費","","","課対仕入込","'.$kotsu.'",0,"","","","対象外",0,0,"","","",3,"","","0","0","no"'."\n".
'"2100",,"","'.$month.'","通信費","","","課対仕入込","'.$tusin.'",0,"","","","対象外",0,0,"","","",3,"","","0","0","no"'."\n".
'"2100",,"","'.$month.'","消耗品費","","","課対仕入込","'.$shomo.'",0,"","","","対象外",0,0,"","","",3,"","","0","0","no"'."\n".
'"2100",,"","'.$month.'","新聞図書費","","","課対仕入込","'.$tosho.'",0,"","","","対象外",0,0,"","","",3,"","","0","0","no"'."\n".
'"2101",,"","'.$month.'","雑費","","","課対仕入込","'.$other.'",0,"","","","対象外",0,0,"","","",3,"","","0","0","no"'."\n";
$str = mb_convert_encoding($str,"SJIS","UTF-8");
fwrite($fp,$str);
fclose($fp);
//MySQLにデータ登録
$result = mysql_db_query($database, "INSERT INTO sample_table (
month, kosai, kaigi, kotsu, tusin, shomo, tosho, other, gokei, xlsfile, csvfile
) VALUES (
'$month','$kosai','$kaigi','$kotsu','$tusin','$shomo','$tosho','$other','$gokei','$xlsfile','$csvfile'
)" );
if (!$result) { die(mysql_error()); }
//Blobストレージに保存
$blob_client->putBlob('files', $xlsfile, $updir.$xlsfile);
$blob_client->putBlob('files', $csvfile, $updir.$csvfile);
}
//テーブル作成
$result = mysql_db_query($database, "SELECT id FROM sample_table", $link);
$numrows = mysql_numrows($result);
echo "<table border='1'>\n";
echo "<tr><th>月</th><th>交際費</th><th>会議費</th><th>交通費</th><th>通信費</th><th>消耗品費</th><th>図書費</th><th>その他</th><th>合計</th><th>xls</th><th>csv</th></tr>\n";
$result = mysql_db_query($database, "SELECT
month, kosai, kaigi, kotsu, tusin, shomo, tosho, other, gokei, xlsfile, csvfile
FROM sample_table", $link);
for ($i=0; $i<$numrows; $i++) {
$row = mysql_fetch_array($result);
$_month = $row["month"];
$_kosai = $row["kosai"];
$_kaigi = $row["kaigi"];
$_kotsu = $row["kotsu"];
$_tusin = $row["tusin"];
$_shomo = $row["shomo"];
$_tosho = $row["tosho"];
$_other = $row["other"];
$_gokei = $row["gokei"];
$_xlsfile = $row["xlsfile"];
$_csvfile = $row["csvfile"];
echo "<tr><td>$_month</td><td>$_kosai</td><td>$_kaigi</td><td>$_kotsu</td><td>$_tusin</td><td>$_shomo</td><td>$_tosho</td><td>$_other</td><td>$_gokei</td><td><a href='$updir$_xlsfile'>$_xlsfile</a></td><td><a href='$updir$_csvfile'>$_csvfile</a></td></tr>";
}
echo "</table>\n";
?>
<!-- //アップロードフォーム -->
<br />
<form action="index.php" method="post" enctype="multipart/form-data">
<input type="file" name="uploadfile" size="20" />
<input type="submit" value="upload" />
</form>
</body>
</html>
Web.configだけでベーシック認証などを行うことはできません。ただ、IPやホスト名によるアクセス制限は可能です。ここでは、以下のようにして、特定のIP(111.222.333.444と555.666.777.888)以外からのアクセスは、403エラーが表示されるようにリライトしています。