LAMP開発者のためのWindows Azure講座

第8回Azure上にPHP+MySQLでアプリを作ってみよう[その1]

Excelでの経費精算を支援するPHP+MySQLアプリ

前回まででAzure上でPHPとMySQLを動作させる環境が整いましたので、今回はその環境を使って簡単なサービスを作成してみます。今回作成するのは、経費精算の経理業務を少しだけ楽にするアプリケーションです。具体的には、Excelで作成した経費精算書をWebサイトにアップロードし、そこから会計ソフトでインポートできるCSVファイルを出力するものです。

最近は、Webサイト上で経費精算するための便利なサービスが増えてきました。こうしたサービスを利用するメリットの1つは、データやファイルを一元的に管理できることです。ユーザが入力したデータは会計ソフトなどでそのまま利用できることも多く、事務の効率化につながります。とは言え「実際にそこまでは必要ない」という方も多いでしょう。

一方、Excelを使った経費精算のメリットは、入力、編集のしやすさ、印刷品質の高さなどです。特に、印刷と押印が必要なシーンに対しては、Web上のアプリケーションは十分に対応できていないのが現状だと思います。とは言え、Excelの場合、ファイルをどこで管理するかなど煩雑になりがちです。また、データ自体も会計ソフトで利用されることはほとんどありません。

これらを踏まえ、以下のようなざっくりとしたシナリオを想定してサービスを作ることにします。

  1. ユーザがExcelを使って経費精算書を作成(印刷、押印)
  2. 上長などがサイトにXLSファイルをアップロード(承認)
  3. 経理担当者がサイトからCSVファイルをダウンロード
  4. 経理担当者が会計ソフトにCSVファイルをインポート

アプリケーションとしては、PHPとMySQLを使ったアップロード掲示板になります。実際の画面は、図1のようになります。

図1 作成する掲示板の画面
図1 作成する掲示板の画面

アップロードしたXLSファイルからセルのデータを読み取ってデータベースに格納し、あわせてCSVファイルを作成して、リストとして表示させます。XLSファイルの読み込みには、PHPExcelライブラリを使っています。

今回は、PHPとMySQLの設定手順とソースコードを紹介します。もっとも、この辺りはLAMP環境のノウハウですので、最小限にとどめます。

Excelの経費精算書を元にsqlファイルを作成

まず、実際に利用している経費精算書を見て、MySQLで利用するデータのフィールドや型を決めましょう。経費精算書は、図2のようなExcelファイルとします。

図2 経費精算書のExcel画面
図2 経費精算書のExcel画面

また、会計ソフトとしては、弥生会計を利用することにします。図3のように、月末に未払費用として振替伝票を作成しています。

図3 経費精算書のExcel画面
図3 経費精算書のExcel画面

ここでは、科目ごとに仕訳しています。データとして必要になるのは、⁠日付」のほか、⁠交際費」⁠会議費」⁠旅費交通費」⁠通信費」⁠消耗品費」⁠新聞図書費」⁠その他」の各科目。加えて、サイト上でファイルのリンクを表示するために「xls」⁠csv」の各ファイル名です。

これをもとにsqlファイルを作成します。sample_db.sqlとして以下の内容で作成します。

リスト1 sample_db.sql
CREATE TABLE sample_table (
  id       INTEGER NOT NULL AUTO_INCREMENT,
  month    DATE,
  kosai    INTEGER,
  kaigi    INTEGER,
  kotsu    INTEGER,
  tusin    INTEGER,
  shomo    INTEGER,
  tosho    INTEGER,
  other    INTEGER,
  gokei    INTEGER,
  xlsfile  VARCHAR(20),
  csvfile  VARCHAR(20),
  primary key (id)
);

各科目はint型、日時はdate型、ファイル名はvarchar型にしています。

MySQLに登録するには、以下のようにします。データベース名はsample_db、テーブル名はsample_table、ユーザはsample_db_userです。

mysql -u sample_db_user -p sample_db 

なお、MySQLは、第6回第7回で行ったようにAzureドライブを利用することにします。ローカル環境でユーザ、データベース、データを作成し、仮想ディスク内に保存しておきます。

PHPアプリケーションの作成

では、次にLAMP環境で動作するPHPアプリケーションを作成しましょう。

使い勝手を考えると、XLSファイルをアップロードすると自動的にデータが一覧表示されたほうがいいと思います。また、画面遷移もできるだけ少ないほうがいいでしょう。ここでは、1つのファイル(index.php)として作成してみます。

必要な処理としては、次の5つがあります。

  1. ファイルのアップロードと保存
  2. MySQLへの接続
  3. XLSデータから内容の読み込みとMySQLへのデータ書き込み
  4. CSVファイルの作成
  5. MySQLからデータの読み込みと一覧表示

このうち、⁠3. XLSデータから内容の読み込み」と、⁠4. CSVファイルの作成」について、以下にコードを挙げておきます。

XLSデータから内容の読み込み

まず、PHPExcelを利用するので、最初に以下の記述が必要です。PHPExcelのClassesフォルダをルートディレクトリ配下に設置しておきます。

<?php
require_once 'Classes/PHPExcel.php';
require_once 'Classes/PHPExcel/IOFactory.php';
?>

XLSデータを読み込むには、以下のようにします。なお、$xlsfileはアップロードしたファイル、$updirはアップロードしたファイルの保存先です。

<?php
  ##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();

  ##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()); }
?>

CSVファイルの作成

アップロードしたファイル名をcsvに変えたうえで、弥生会計でインポート可能な形式(振替伝票)に書き換えて、保存しています。

<?php
  $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);
?>

部分的に見るとわかりにくいかもしれませんが、今回のアプリケーションで固有なのはこの辺りだけです。PHPでのファイルのアップロードやMySQLへの接続などは、少し修正すれば普通のLAMP環境と同じように動作させることができます。LAMP環境と明らかに異なると言えるのは、IISでのアクセス制限などでしょう。

次回は、作成したプログラム全体と、実際にAzureにデプロイしたうえで、IISでアクセス制限をどう行うかなどを紹介しようと思います。

おすすめ記事

記事・ニュース一覧