上記データベースから仕訳帳や決算報告などを見る ユーザーインタフェース: show.pl

#! /usr/bin/perl -w
use CGI;
use Pg;
use CGI::Carp qw(fatalsToBrowser);
use CGI qw/:standard/;
use strict;

my $db = 'balance';

# get key table
my ($tbl_i, $tbl_f, %titles, $qstr, $res, $n);
my $conn = Pg::connectdb("user=www dbname = $db");
if ($conn->status ne PGRES_CONNECTION_OK) {
    print "Cannot connect to database $db: $conn->errorMessage";
    exit;
}
$tbl_i = 'titles';
$qstr = "select * from $tbl_i;";
if (&submit_sql()) {
    for (my $i = 0; $i < $n; $i++) {
	my $key = $res->getvalue($i, 0);
	$titles{$key} = $res->getvalue($i, 1);
    }
}

print header;
print start_html(-title=>"Settlement",
		       -meta=>{'Content-Type'=>'text/html; charset=x-euc-jp'}
		       ),"\n";
print h2('財務諸表'),"\n";

# select year and output-sheet
my @years = ('1997','1998','1999','2000','2001','2002','2003');
my $year;
my @sheets = ('仕訳帳','期首貸借対照表・フロー計算書','貸借対照表・損益計算書');
my $sheet;
print
    start_form(),
    '年と表を選んでください ',
    popup_menu(-name=>'year',-values=>\@years,-defaults=>$years[$#years]),
    popup_menu(-name=>'sheet',-values=>\@sheets,-defaults=>$sheets[$#sheets]),
    submit('実行'),br(),
    end_form(),"\n";
if (!($year = param('year'))) {$year = $years[$#years];}
$tbl_i = 'i'.$year;
$tbl_f = 'f'.$year;
if (!($sheet = param('sheet'))) {$sheet = $sheets[$#sheets];}

my ($i, $amount, $debit, $credit, $profit);

# print journal
if ($sheet eq '仕訳帳') {
    print h3('仕訳帳'),"\n";
    print "<table border>\n";
    print "<tr><td colspan=3>&nbsp;</td>
	<td colspan=4 align=center>仕訳帳</td><td>&nbsp</td></tr>\n";
    printf("<tr><td>%s</td><td>%s</td><td>%s</td>
	<td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td></tr>\n",
	   'No.','年月日','摘要',
	   '左借方','金額','右貸方','金額','cash');
    $qstr = "select oid,* from $tbl_f;";
    if (&submit_sql()) {
	$debit = 0;
	for ($i = 0; $i < $n; $i++) {
	    $amount = $res->getvalue($i,2);
	    $debit += $amount;
	    printf("<tr><td>%s</td><td>%s</td><td>%s</td>
		<td>%s</td><td align=right>%s</td>
		<td>%s</td><td align=right>%s</td>
		<td>%s</td></tr>\n",
		   $res->getvalue($i,0),
		   $res->getvalue($i,5),&chksp($res->getvalue($i,6)),
		   &chksp($titles{$res->getvalue($i,1)}),$amount,
		   &chksp($titles{$res->getvalue($i,3)}),$amount,
		   $res->getvalue($i,4));
	}
	printf("<tr><td>%s</td><td>%s</td><td>%s</td>
	    <td>%s</td><td align=right>%s</td>
	    <td>%s</td><td align=right>%s</td><td>%s</td></tr>\n",
	       '&nbsp;','&nbsp;','&nbsp;',
	       '【To】',$debit,'【From】',$debit,'&nbsp;');
    }
    print "</table>\n";
}

#print flow statement (F/S)
if ($sheet eq '期首貸借対照表・フロー計算書') {
    print h3('期首貸借対照表・フロー計算書'),"\n";
    print "<table border>\n";
    print "<tr><td></td>
	<td colspan=4 align=center>期首貸借対照表</td><td></td></tr>\n";
    printf("<tr><td>%s</td><td>%s</td><td align=right>%s</td>
	<td>%s</td><td align=right>%s</td><td>%s</td></tr>\n",
	   'No.','左借方','金額','右貸方','金額','摘要');
    $qstr =
	"select oid,* from $tbl_i;";
    if (&submit_sql()) {
	my ($d_key, $c_key);
	$debit = 0;
	for (my $i = 0; $i < $n; $i++) {
	    $amount = $res->getvalue($i,2);
	    $d_key = $res->getvalue($i,1);
	    $c_key = $res->getvalue($i,3);
	    if ($d_key !~ /^0/ && $amount) {
		printf("<tr><td>%s</td><td>%s</td><td align=right>%s</td>
		    <td>%s</td><td align=right>%s</td><td>%s</td>\n",
		   $res->getvalue($i,0),$titles{$d_key},$amount,
		   '&nbsp;','&nbsp;',&chksp($res->getvalue($i,4)));
		$debit += $amount;
	    }
	    if ($c_key !~ /^0/ && $amount) {
		printf("<tr><td>%s</td><td>%s</td><td align=right>%s</td>
		    <td>%s</td><td align=right>%s</td><td>%s</td>\n",
		   $res->getvalue($i,0),'&nbsp;','&nbsp;',
		   $titles{$c_key},$amount,&chksp($res->getvalue($i,4)));
		$credit += $amount;
	    }
	}
	if ($debit != $credit) {print "<p>Unbalanced!</p>\n";}
	$profit = $debit - $credit;
	if ($profit) {
	    $credit += $profit;
	    printf("<tr><td>%s</td><td align=right>%s</td>
		<td>%s</td><td align=right>%s</td></tr>\n",
		   '&nbsp;','&nbsp;','*当期利益',&chksp($profit));
	}
	if ($debit != $credit) {print "<p>Unbalanced!</p>\n";}
	printf("<tr><td>%s</td>
	    <td>%s</td><td align=right>%s</td>
	    <td>%s</td><td align=right>%s</td><td>%s</td></tr>\n",
	       '&nbsp;','【資産】',$debit,'【負債資本】',$debit,'&nbsp;');
    }
    print "<tr><td></td></tr>\n";
    print "<tr><td></td>
	<td colspan=4 align=center>キャッシュフロー (cash)</td></tr>\n";
    printf("<tr><td>%s</td><td>%s</td><td align=right>%s</td>
	<td>%s</td><td align=right>%s</td></tr>\n",
	   '','左借方','金額','右貸方','金額');
    $qstr =
	"select * from (
	    select * from (
		select debit as key, sum(amount) as dsum from (
		    select * from $tbl_f where cash != 'n'
		)dc group by debit
	    )d full outer join (
	    	select credit as key, sum(amount) as csum from (
		    select * from $tbl_f where cash != 'n'
		)cc group by credit
	    )c using(key)
	)t join titles using(key);";
    if (&submit_sql()) {
	$debit = $credit = 0;
	for ($i = 0; $i < $n; $i++) {
	    if ($res->getvalue($i,4) eq 'rd') {
		$amount = $res->getvalue($i,1)-$res->getvalue($i,2);
		if ($amount) {
		    $debit += $amount;
		    printf("<tr><td>%s</td><td>%s</td><td align=right>%s</td>
			<td>%s</td><td>%s</td></tr>\n",
			   '',$res->getvalue($i,3),&chksp($amount),
			   '&nbsp;','&nbsp;');
		}
	    } elsif ($res->getvalue($i,4) eq 'rc') {
		$amount = $res->getvalue($i,2)-$res->getvalue($i,1);
		if ($amount) {
		    $credit += $amount;
		    printf("<tr><td>%s</td><td>%s</td><td>%s</td>
			<td>%s</td><td align=right>%s</td></tr>\n",
			   '','&nbsp;','&nbsp;',
			   $res->getvalue($i,3),&chksp($amount));
		}
	    } elsif ($res->getvalue($i,4) eq 'ic') {
		$amount = $res->getvalue($i,2)-$res->getvalue($i,1);
		if ($amount) {
		    $credit += $amount;
		    printf("<tr><td>%s</td><td>%s</td><td>%s</td>
			<td>%s</td><td align=right>%s</td></tr>\n",
			   '','&nbsp;','&nbsp;',
			   $res->getvalue($i,3),&chksp($amount));
		}
	    } elsif ($res->getvalue($i,4) eq 'id') {
		$amount = $res->getvalue($i,1)-$res->getvalue($i,2);
		if ($amount) {
		    $debit += $amount;
		    printf("<tr><td>%s</td><td>%s</td><td align=right>%s</td>
			<td>%s</td><td>%s</td></tr>\n",
			   '',$res->getvalue($i,3),&chksp($amount),
			   '&nbsp;','&nbsp;');
		}
	    }
	}
	if ($debit != $credit) {print "<p>Unbalanced!</p>\n";}
	printf("<tr><td>%s</td><td>%s</td><td align=right>%s</td>
	    <td>%s</td><td align=right>%s</td></tr>\n",
	       '','【To】',&chksp($debit),'【From】',&chksp($credit));
    }
    print "<tr></tr>\n";
    print "<tr><td></td>
	<td colspan=4 align=center>非キャッシュフロー (non-cash)</td></tr>\n";
    printf("<tr><td>%s</td><td>%s</td><td align=right>%s</td>
	<td>%s</td><td align=right>%s</td></tr>\n",
	   '','左借方','金額','右貸方','金額');
    $qstr =
	"select * from (
	    select * from (
		select debit as key, sum(amount) as dsum from (
		    select * from $tbl_f where cash = 'n'
		)dc group by debit
	    )d full outer join (
	    	select credit as key, sum(amount) as csum from (
		    select * from $tbl_f where cash = 'n'
		)cc group by credit
	    )c using(key)
	)t join titles using(key);";
    if (&submit_sql()) {
	$debit = $credit = 0;
	for ($i = 0; $i < $n; $i++) {
	    if ($res->getvalue($i,4) eq 'rd') {
		$amount = $res->getvalue($i,1)-$res->getvalue($i,2);
		if ($amount) {
		    $debit += $amount;
		    printf("<tr><td>%s</td><td>%s</td><td align=right>%s</td>
			<td>%s</td><td>%s</td></tr>\n",
			   '',$res->getvalue($i,3),&chksp($amount),
			   '&nbsp;','&nbsp;');
		}
	    } elsif ($res->getvalue($i,4) eq 'rc') {
		$amount = $res->getvalue($i,2)-$res->getvalue($i,1);
		if ($amount) {
		    $credit += $amount;
		    printf("<tr><td>%s</td><td>%s</td><td>%s</td>
			<td>%s</td><td align=right>%s</td></tr>\n",
			   '','&nbsp;','&nbsp;',
			   $res->getvalue($i,3),&chksp($amount));
		}
	    } elsif ($res->getvalue($i,4) eq 'ic') {
		$amount = $res->getvalue($i,2)-$res->getvalue($i,1);
		if ($amount) {
		    $credit += $amount;
		    printf("<tr><td>%s</td><td>%s</td><td>%s</td>
			<td>%s</td><td align=right>%s</td></tr>\n",
			   '','&nbsp;','&nbsp;',
			   $res->getvalue($i,3),&chksp($amount));
		}
	    } elsif ($res->getvalue($i,4) eq 'id') {
		$amount = $res->getvalue($i,1)-$res->getvalue($i,2);
		if ($amount) {
		    $debit += $amount;
		    printf("<tr><td>%s</td><td>%s</td><td align=right>%s</td>
			<td>%s</td><td>%s</td></tr>\n",
			   '',$res->getvalue($i,3),&chksp($amount),
			   '&nbsp;','&nbsp;');
		}
	    }
	}
	printf("<tr><td>%s</td><td>%s</td><td align=right>%s</td>
	    <td>%s</td><td align=right>%s</td></tr>\n",
	       '','【To】',&chksp($debit),'【From】',&chksp($credit));
	if ($debit != $credit) {print "<p>Unbalanced!</p>\n";}
    }
    print "</table>\n";
}

# print B/S and P/L
if ($sheet eq '貸借対照表・損益計算書') {
    print h3('貸借対照表・損益計算書'),"\n";
    print "<table border>\n";
    $qstr =
	"select * from (
	    select * from (
		select debit as key,sum(amount) as d_sum from (
		    select debit,amount from $tbl_i union all
		    select debit,amount from $tbl_f
		) d group by debit
	    ) ds full outer join (
		select credit as key,sum(amount) as c_sum from (
		    select credit,amount from $tbl_i union all
		    select credit,amount from $tbl_f
		) c group by credit
	    ) cs using(key)
	)t join titles using(key);";
    if (&submit_sql()) {
	print "<tr><td colspan=4 align=center>貸借対照表</td></tr>\n";
	printf("<tr><td>%s</td><td>%s</td><td>%s</td><td>%s</td></tr>\n",
	       '左借方','金額','右貸方','金額');
	$debit = $credit = 0;
	for ($i = 0; $i < $n; $i++) {
	    if ($res->getvalue($i,4) eq 'rd') {
		$amount = $res->getvalue($i,1)-$res->getvalue($i,2);
		if ($amount) {
		    $debit += $amount;
		    printf("<tr><td>%s</td><td align=right>%s</td>
			<td>%s</td><td align=right>%s</td></tr>\n",
			   $res->getvalue($i,3),&chksp($amount),
			   '&nbsp;','&nbsp;');
		}
	    } elsif ($res->getvalue($i,4) eq 'rc') {
		$amount = $res->getvalue($i,2)-$res->getvalue($i,1);
		if ($amount) {
		    $credit += $amount;
		    printf("<tr><td>%s</td><td align=right>%s</td>
			<td>%s</td><td align=right>%s</td></tr>\n",
			   '&nbsp;','&nbsp;',
			   $res->getvalue($i,3),&chksp($amount));
		}
	    }
	}
	$profit = $debit - $credit;
	if ($profit) {
	    $credit += $profit;
	    printf("<tr><td>%s</td><td align=right>%s</td>
		<td>%s</td><td align=right>%s</td></tr>\n",
		   '&nbsp;','&nbsp;','*当期利益',&chksp($profit));
	}
	printf("<tr><td>%s</td><td align=right>%s</td>
	    <td>%s</td><td align=right>%s</td></tr>\n",
	       '【資産】',$debit,'【負債資本】',$credit);
	if ($debit != $credit) {print "<p>Unbalanced!</p>\n";}
	print "<tr></tr>";
	print "<tr><td colspan=4 align=center>損益計算書</td></tr>\n";
	printf("<tr><td>%s</td><td>%s</td><td>%s</td><td>%s</td></tr>\n",
	       '左借方','金額','右貸方','金額');
	$debit = $credit = 0;
	for ($i = 0; $i < $n; $i++) {
	    if ($res->getvalue($i,4) eq 'ic') {
		$amount = $res->getvalue($i,2)-$res->getvalue($i,1);
		if ($amount) {
		    $credit += $amount;
		    printf("<tr><td>%s</td><td align=right>%s</td>
			<td>%s</td><td align=right>%s</td></tr>\n",
			   '&nbsp;','&nbsp;',
			   $res->getvalue($i,3),&chksp($amount));
		}
	    } elsif ($res->getvalue($i,4) eq 'id') {
		$amount = $res->getvalue($i,1)-$res->getvalue($i,2);
		if ($amount) {
		    $debit += $amount;
		    printf("<tr><td>%s</td><td align=right>%s</td>
			<td>%s</td><td align=right>%s</td></tr>\n",
			   $res->getvalue($i,3),&chksp($amount),
			   '&nbsp;','&nbsp;');
		}
	    }
	}
	$profit = $credit - $debit;
	if ($profit) {
	    $debit += $profit;
	    printf("<tr><td>%s</td><td align=right>%s</td>
		<td>%s</td><td align=right>%s</td></tr>\n",
		   '*!当期利益',$profit,'&nbsp;','&nbsp;');
	}
	printf("<tr><td>%s</td><td align=right>%s</td>
	   <td>%s</td><td align=right>%s</td></tr>\n",
	       '【費用】',&chksp($debit),'【収益】',&chksp($credit));
	if ($debit != $credit) {print "<p>Unbalanced!</p>\n";}
    }
    print "</table>\n";
}
print end_html;

sub submit_sql {
    $res = $conn->exec($qstr);
    if ($res->resultStatus ne PGRES_TUPLES_OK) {
        print "Query failed: $conn->errorMessage\n";
	return 0;
    }
    $n = $res->ntuples;
    if ($n == 0) {
        print '<p>No record in DB</p>',"\n";
	return 0;
    }
    return 1;
}

sub chksp {
    if ($_[0] eq '') {return '&nbsp;';}
    return $_[0];
}