MySQL操作程序十六(生成HTML零件表)
返回
<![CDATA[
表格不能改行时,追加以下的CSS
<style type="text/css">
body { word-break:break-all;}
</style>
]]>
<![CDATA[
# obtain_parts.pl(要修改:分层,图号的输出)
# 取出数据
use strict;
use DBI;
my(%t,$n,@fld,@rec,$pref);
$$pref{dsn} = "DBI:mysql:host=localhost;database=cookbook";
$$pref{dbh} = DBI->connect($$pref{dsn}, "cbuser", "cbpass") or die "Cannot connect to server\n";
if(!$$pref{dbh}){
print "SQL read ERROR!\n";
exit;
}
#
print "Please input id of main_type1=";
chop($t{table1}=<STDIN>);
$t{type1} = $$pref{dbh}->selectrow_array("SELECT name FROM main_type1 WHERE id = $t{table1}");
$t{table1} = sprintf("%06d",$t{table1});
$t{outputf} = 'type_' . $t{table1} . '_org.txt';
$t{table1} = 'a' . $t{table1};
open(OUT,">./data/$t{outputf}");
print OUT "filename=$t{outputf}\n";
print OUT "TYPE===$t{type1}\n";
#
$t{sth} = $$pref{dbh}->prepare("SELECT name,code FROM $t{table1}");
$t{sth}->execute;
while ( @rec = $t{sth}->fetchrow_array ) {
print OUT 'PARTS===';
print OUT $rec[0],'===';
print OUT $rec[1],"\n";
}
$t{sth}->finish;
close(OUT);
#
$$pref{dbh}->disconnect;
]]>
<![CDATA[
# parts_html.pl
# 生成HTML文件(要修改:分层,图号的输出)
use strict;
use HTML::Template;
my(%t,@fld,$n,$template,@loop);
chop($t{table1}=<STDIN>);
$t{table1} = sprintf("%06d",$t{table1});
$t{inputf} = 'type_' . $t{table1} . '.txt';
$t{htmfile} = 'a' . $t{table1} . '_org.htm';
$template = HTML::Template->new(filename => "./data/parts.htm");
@loop = ();
open(IN,"./data/$t{inputf}") or die "Can't open the file $t{inputf}";
$t{NO} = 0;
while(<IN>){
next if $. == 1;
if ( $. == 2 ) {
chop;
@fld = split(/===/);
$t{title} = $fld[1];
next;
}
next if length($_) < 2;
chop;
@fld = split(/===/);
$t{NO}++;
my %row = (
NO => $t{NO},
N1 => $fld[1],
C1 => $fld[2]
);
push(@loop, \%row);
}
close(IN);
$template->param(title => $t{title});
$template->param(std_loop => \@loop);
open(OUT,">./partshtm/$t{htmfile}");
print OUT $template->output;
close(OUT);
]]>
<![CDATA[
# input_series_DWG.pl
use strict;
use DBI;
use HTML::Template;
my(%t,$n,@fld,@rec,$pref,$template,@loop);
$t{inputf} = 'main_type1.txt';
$t{table1} = 'main_type1';
# Read main_type1.txt
open(IN,"$t{inputf}") or die "Can't open the file $t{inputf}\n";
while(<IN>){
next if $. == 1;
chop;
@fld = split(/==>/);
push(@{ $t{list0} },$fld[3]);
$t{html_id}{$fld[3]} = $fld[2];
if ( $fld[4] ) {
$t{html_2id}{$fld[3]} = $fld[4];
}
}
close(IN);
$$pref{dsn} = "DBI:mysql:host=localhost;database=cookbook";
$$pref{dbh} = DBI->connect($$pref{dsn}, "cbuser", "cbpass") or die "Cannot connect to server\n";
$$pref{dbh}->do("SET NAMES utf8");
if(!$$pref{dbh}){
print "SQL read ERROR!\n";
exit;
}
# Read data from database
$t{sth} = $$pref{dbh}->prepare("SELECT id,name,series,DWG FROM $t{table1}");
$t{sth}->execute;
while ( @rec = $t{sth}->fetchrow_array ) {
$t{id1} = $rec[0];
$t{name1} = $rec[1];
$t{series} = $rec[2];
$t{DWG} = $rec[3];
# $t{name1} =~ s/\s+$//;
$t{name1} = uc($t{name1});
push(@{ $t{list1} },$t{name1});
if ( $t{name_id}{$t{name1}} ) {
print "ERROR,$rec[0],==>,$t{name1},$t{name_id}{$t{name1}}\n";
exit;
} else {
$t{id1} = sprintf("%06d",$t{id1});
$t{tb1} = 'a' . $t{id1};
$t{name_id}{$t{name1}} = $t{tb1};
$t{series_list}{$t{name1}} = $t{series};
$t{DWG_list}{$t{name1}} = $t{DWG};
# print "$t{name1}==>$t{series_list}{$t{name1}}\n";
}
}
$t{sth}->finish;
@{ $t{list} } = (@{ $t{list0} },@{ $t{list1} });
@{ $t{list} } = sort @{ $t{list} };
# 重複項目を削除
my(%seen,$item);
%seen = ();
foreach $item (@{ $t{list} }) {
push(@{ $t{list2} },$item) unless $seen{$item}++;
}
# SAVE
open(OUT,">tmp1.txt");
print OUT "Filename=$t{inputf}\n";
$t{NO} = 0;
for $n ( 1 .. $#{ $t{list2} } ) {
print OUT $t{table1};
print OUT '==>';
if ( $t{name_id}{$t{list2}[$n]} ) {
$t{tb1} = $t{name_id}{$t{list2}[$n]};
} else {
$t{tb1} = 'a000000';
}
$t{e1} = $t{list2}[$n];
print OUT $t{tb1};
print OUT '==>';
if ($t{html_id}{$t{e1}}) {
$t{h1} = 1;
} elsif ( $t{html_2id}{$t{e1}} ) {
$t{h1} = 1;
} else {
$t{h1} = 0;
}
print OUT $t{h1};
print OUT '==>';
print OUT $t{e1};
print OUT '==>';
print OUT $t{series_list}{$t{e1}};
print OUT '==>';
print OUT $t{DWG_list}{$t{e1}};
print OUT "\n";
}
close(OUT);
$$pref{dbh}->disconnect;
]]>
<![CDATA[
# make_type.pl
use strict;
use DBI;
use HTML::Template;
my(%t,$n,@fld,@rec,$pref,$template,@loop);
print "This is make_type.pl.\n";
$t{title}{type1} = 'Equipment Types';
$template = HTML::Template->new(filename => "type0.htm");
@loop = ();
$$pref{dsn} = "DBI:mysql:host=localhost;database=cookbook";
$$pref{dbh} = DBI->connect($$pref{dsn}, "cbuser", "cbpass") or die "Cannot connect to server\n";
$$pref{dbh}->do("SET NAMES utf8");
if(!$$pref{dbh}){
print "SQL read ERROR!\n";
exit;
}
#
$t{htmfile} = 'type1.htm';
$t{table1} = 'main_type1';
$t{inputf} = 'main_type1.txt';
# Read main_type1.txt
open(IN,"$t{inputf}") or die "Can't open the file $t{inputf}\n";
while(<IN>){
next if $. == 1;
chop;
@fld = split(/==>/);
push(@{ $t{list0} },$fld[3]);
push(@{ $t{series0} },$fld[4]);
push(@{ $t{DWG0} },$fld[5]);
$t{html_id}{$fld[3]} = $fld[2];
}
close(IN);
# Read data from database
$t{sth} = $$pref{dbh}->prepare("SELECT id,name,series,DWG FROM $t{table1}");
$t{sth}->execute;
while ( @rec = $t{sth}->fetchrow_array ) {
$t{id1} = $rec[0];
$t{name1} = $rec[1];
# $t{name1} =~ s/\s+$//;
$t{name1} = uc($t{name1});
$t{series} = $rec[2];
$t{DWG} = $rec[3];
push(@{ $t{list1} },$t{name1});
if ( $t{name_id}{$t{name1}} ) {
print "ERROR,$rec[0],==>,$t{name1},$t{name_id}{$t{name1}}\n";
exit;
} else {
$t{id1} = sprintf("%06d",$t{id1});
$t{tb1} = 'a' . $t{id1};
$t{name_id}{$t{name1}} = $t{tb1};
$t{series_list}{$t{name1}} = $t{series};
$t{DWG_list}{$t{name1}} = $t{DWG};
}
}
$t{sth}->finish;
@{ $t{list} } = (@{ $t{list0} },@{ $t{list1} });
@{ $t{list} } = sort @{ $t{list} };
# 重複項目を削除
my(%seen,$item);
%seen = ();
foreach $item (@{ $t{list} }) {
push(@{ $t{list2} },$item) unless $seen{$item}++;
}
$template->param(title => $t{title}{type1});
# SAVE
open(OUT,">$t{inputf}");
#open(OUT,">tmp1.txt");
print OUT "Filename=$t{inputf}\n";
$t{NO} = 0;
for $n ( 1 .. $#{ $t{list2} } ) {
print OUT $t{table1};
print OUT '==>';
if ( $t{name_id}{$t{list2}[$n]} ) {
$t{tb1} = $t{name_id}{$t{list2}[$n]};
} else {
$t{tb1} = 'a000000';
}
$t{e1} = $t{list2}[$n];
print OUT $t{tb1};
print OUT '==>';
if ($t{html_id}{$t{e1}}) {
$t{h1} = 1;
} else {
$t{h1} = 0;
}
print OUT $t{h1};
print OUT '==>';
print OUT $t{e1};
print OUT '==>';
print OUT $t{series_list}{$t{e1}};
print OUT '==>';
print OUT $t{DWG_list}{$t{e1}};
print OUT "\n";
if ( $t{html_id}{$t{e1}} ) {
$t{html1} = $t{name_id}{$t{e1}} . '.htm';
$t{e1} = '<a href=./partshtm/' . $t{html1} . '>' . $t{e1} . '</a>';
}
@{ $t{s1s} } = split(/=/,$t{series_list}{$t{e1}});
$t{s1} = join('<br>',@{ $t{s1s} }[1..$#{ $t{s1s} }]);
@{ $t{d1s} } = split(/=/,$t{DWG_list}{$t{e1}});
$t{d1} = join('<br>',@{ $t{d1s} }[1..$#{ $t{d1s} }]);
my %row = (
NO => $n,
E1 => $t{e1},
S1 => $t{s1},
D1 => $t{d1}
);
push(@loop, \%row);
}
close(OUT);
$template->param(std_loop => \@loop);
open(OUT,">$t{htmfile}");
print OUT $template->output;
close(OUT);
$$pref{dbh}->disconnect;
]]>
返回