はてなブログに引っ越ししました
アプリからのテストです
いままではてなダイアリーは写真埋め込みがクソの山だったけれど改善されたかな?
# ip lan1 address 192.168.1.1/24 # ip lan2 address dhcp # nat descriptor type 1 masquerade # nat descriptor address outer 1 primary # ip lan2 nat descriptor 1 # ip route default gateway dhcp lan2 # dhcp service server # dhcp scope 1 192.168.1.10-192.168.1.254/24
CREATE TABLE `Labo2016` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `Department` varchar(20) DEFAULT NULL, `PatID` varchar(13) DEFAULT NULL, `PatName` varchar(30) DEFAULT NULL, `PatAge` int(11) DEFAULT NULL, `PatSex` varchar(4) DEFAULT NULL, `TestDate` varchar(12) DEFAULT NULL, `AST` float DEFAULT NULL, `ALT` float DEFAULT NULL, `LDH` float DEFAULT NULL, `Na` float DEFAULT NULL, `K` float DEFAULT NULL, `BUN` float DEFAULT NULL, `Cr` float DEFAULT NULL, `BNP` float DEFAULT NULL, `NTproBNP` float DEFAULT NULL, `WBC` float DEFAULT NULL, `Hb` float DEFAULT NULL, `Ht` float DEFAULT NULL, `Plt` float DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=2982 DEFAULT CHARSET=utf8;
#!/usr/bin/perl -w use Spreadsheet::ParseExcel; use strict 'refs'; use utf8; use Encode; use DBI; binmode STDOUT, ":utf8"; binmode STDIN, ":utf8"; { my $FileName = $ARGV[0];; # Excel(xls)ファイル名 my $ExcelObj = Spreadsheet::ParseExcel->new(); # ParseExcelのオブジェクト定義 my $Book = $ExcelObj->parse($FileName); # xlsファイル読み込み/book扱い our $DB_NAME = "databasename"; our $DB_USER = "username"; our $DB_PASS = "password"; $dbh = DBI->connect("dbi:mysql:dbname=$DB_NAME","$DB_USER","$DB_PASS") or die "$!\n Error: failed to connect to DB.\n"; for my $Sheet ($Book->worksheets()) { # worksheetオブジェクト取得 &GetValuesFromSheet($Sheet); # worksheetデータ取得へ last; # Sheet1で終了 } $dbh->disconnect; } sub updateDB { my ($patID, $testDate, $item, $value) = @_; $itemCol = $item; $itemCol =~ tr/0-9A-Za-z/0-9A-Za-z/; $itemCol =~ s/HCT/Ht/; $itemCol =~ s/HGB/Hb/; $itemCol =~ s/PLT/Plt/; $itemCol =~ s/CRE/Cr/; $sql = sprintf("UPDATE Labo2016 SET %s=? WHERE PatID=? AND TestDate=?", $itemCol); $sth = $dbh->prepare($sql); $sth->execute($value,$patID,$testDate); } sub GetValuesFromSheet { # worksheetデータ取得 my ($Sheet) = @_; my ($Rmin, $Rmax) = $Sheet->row_range(); # 行のデータ範囲(最小,最大) my ($Cmin, $Cmax) = $Sheet->col_range(); # 列のデータ範囲(最小,最大) for($i=$Rmin+1;$i<$Rmax;$i++) { $cell = $Sheet->get_cell($i, 2); if (defined($cell)) { $patID = substr($cell->value(),2); $cell = $Sheet->get_cell($i, 7); $testDate = $cell->value(); $rc = $dbh->do("SELECT PatName FROM Labo2016 WHERE PatID=? AND TestDate=?",undef,$patID,$testDate); if($rc eq "0E0") { # not defined in DB. register it. $cell = $Sheet->get_cell($i, 1); $patDep = $cell->value(); $cell = $Sheet->get_cell($i, 3); $patName = $cell->value(); $cell = $Sheet->get_cell($i, 4); $patSex = $cell->value(); $cell = $Sheet->get_cell($i, 5); $patAge = $cell->value(); $sql = "INSERT INTO Labo2016 (`Department`, `PatID`, `PatName`, `PatAge`, `PatSex`, `TestDate`) "; $sql = $sql . "VALUES (?, ?, ?, ?, ?, ?)"; # placeholders $sth = $dbh->prepare($sql); $sth->execute($patDep, $patID, $patName, $patAge,$patSex, $testDate); $cell = $Sheet->get_cell($i, 9); $itemName = $cell->value(); $cell = $Sheet->get_cell($i, 10); $itemValue = $cell->value(); &updateDB($patID, $testDate, $itemName, $itemValue); } else { # patient exist. update item $cell = $Sheet->get_cell($i, 9); $itemName = $cell->value(); $cell = $Sheet->get_cell($i, 10); $itemValue = $cell->value(); &updateDB($patID, $testDate, $itemName, $itemValue); } } } }