MySQL操作程序三十五(网页零件表生成程序,parts01.pl,parts03.pl,parts04.pl)
返回
- 增加下面两张表格
The Ship List
- make_ships.pl
ships.htm
- 注意:数据直接从中间文件XXXX_hull_no.txt读取!
parts04.pl
- 取消部分特定厂家的零件一览(应这些厂家的要求)
- 在parts03.pl的基础上修改
- 取出parts_nu的所有parts_Unit
- 在parts写HTML文件时,同时写入parts_Unit
parts01.pl
- 取出所有制造厂家并列表,main_maker1
英文名
中文名
不存在,1 NO INPUT,428 XX,564 11,612 111
- 取出所有TYPE并于制造厂家挂钩,main_type1
- 一个制造厂家一张零件表(此程序中止,不是代理店意思不大)
parts02.pl
- 取出所有机器名并列表,main_name1,1627个
英文名
不存在,1 NO INPUT
- 取出所有的TYPE并与机器名挂钩,main_type1
- 一个机器名一张零件表(此程序也中止,数量太多)
parts03.pl
- 取出所有的TYPE
处理关联name
处理关联maker
- TYPE分类,A..Z,1
- 处理所有零件,27张表
形成HTML网页,=1=A=B=C=D....=Z=,index.html,p1.htm,pa.htm,pb.htm,....,pz.htm
- 上传网页www.msckobe.com/parts/
<![CDATA[
mysql> select * from parts_nu;
+----+------------+
| id | parts_Unit |
+----+------------+
| 1 | PCS |
| 2 | SET |
| 3 | BOX |
| 4 | ROLL |
| 5 | PAIR |
| 6 | CAN |
| 7 | DOZ |
| 8 | KGS |
| 9 | MTR |
| 10 | SHT |
| 11 | TUBE |
| 12 | PKT |
+----+------------+
12 rows in set (0.03 sec)
mysql> show columns from main_maker1;
+-------+-----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(100) | YES | | NULL | |
| memo | text | YES | | NULL | |
| cname | char(100) | YES | | NULL | |
| cmemo | text | YES | | NULL | |
| jname | char(100) | YES | | NULL | |
| jmemo | text | YES | | NULL | |
+-------+-----------+------+-----+---------+----------------+
mysql> show columns from main_name1;
+-------+-----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(100) | YES | | NULL | |
| memo | text | YES | | NULL | |
| cname | char(100) | YES | | NULL | |
| cmemo | text | YES | | NULL | |
| jname | char(100) | YES | | NULL | |
| jmemo | text | YES | | NULL | |
+-------+-----------+------+-----+---------+----------------+
mysql> show columns from main_type1;
+----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(100) | YES | | NULL | |
| series | text | YES | | NULL | |
| gname_id | int(11) | YES | | NULL | |
| maker_id | int(11) | YES | | NULL | |
| GR | text | YES | | NULL | |
| DWG | text | YES | | NULL | |
| memo | text | YES | | NULL | |
+----------+--------------+------+-----+---------+----------------+
ptables.txt
C===file===id===name===code===dwg_id===Nuid===weight===price1===price2===memo
]]>
<![CDATA[
# parts03.pl
use strict;
use DBI;
use HTML::Template;
use utf8;
my(%t,$n,$n1,$n2,@fld,$pref,@rec);
# 连接数据库
$$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;
}
# 取出main_name1的数据
$t{sth} = $$pref{dbh}->prepare("SELECT id,name FROM main_name1");
$t{sth}->execute;
while ( @rec = $t{sth}->fetchrow_array ) {
$t{name0}{$rec[0]} = $rec[1];
}
$t{sth}->finish;
# 取出main_maker1的数据
$t{sth} = $$pref{dbh}->prepare("SELECT id,name FROM main_maker1");
$t{sth}->execute;
while ( @rec = $t{sth}->fetchrow_array ) {
$t{maker0}{$rec[0]} = $rec[1];
}
$t{sth}->finish;
# 取出main_type1的数据
$t{sth} = $$pref{dbh}->prepare("SELECT id,name,gname_id,maker_id FROM main_type1");
$t{sth}->execute;
while ( @rec = $t{sth}->fetchrow_array ) {
$t{type1}{$rec[0]} = $rec[1];
$t{name1}{$rec[0]} = $rec[2];
$t{maker1}{$rec[0]} = $rec[3];
}
$t{sth}->finish;
# 关闭数据库
$$pref{dbh}->disconnect;
#读零件表文件ptalbe.txt
open(IN,"../txt/ptables.txt") or die "Can't open the file ptables.txt\n";
while(<IN>){
if ( $. >= 3 ) {
@fld = split(/===/);
$fld[3] =~ s/==/\//g;
$fld[3] =~ s/^\'//;
$fld[3] =~ s/\'$//;
$t{p1} = '<LI>' . $fld[4] . '==>' . $fld[3];
push(@{ $t{parts}{$fld[1]} },$t{p1});
}
}
close(IN);
# 处理TYPE
for $n1 ( sort {$t{type1}{$a} cmp $t{type1}{$b}} %{ $t{type1} } ) {
$t{C1} = substr($t{type1}{$n1},0,1);
if ( $t{C1} =~ /\d/ or $t{C1} =~ /\W/ ) {
push(@{ $t{NO}{'1'} },1);
push(@{ $t{ID}{'1'} },$n1);
push(@{ $t{TYPE1}{'1'} },$t{type1}{$n1});
push(@{ $t{NAME1}{'1'} },$t{name0}{$t{name1}{$n1}});
push(@{ $t{MAKER1}{'1'} },$t{maker0}{$t{maker1}{$n1}});
# print "$n1==>$t{type1}{$n1},$t{name0}{$t{name1}{$n1}},$t{maker0}{$t{maker1}{$n1}}\n";
}
}
for $n ( "A".."Z" ) {
for $n1 ( sort {$t{type1}{$a} cmp $t{type1}{$b}} %{ $t{type1} } ) {
$t{C1} = substr($t{type1}{$n1},0,1);
$t{C1} = uc($t{C1});
if ( $t{C1} eq $n ) {
push(@{ $t{NO}{$n} },$n);
push(@{ $t{ID}{$n} },$n1);
push(@{ $t{TYPE1}{$n} },$t{type1}{$n1});
push(@{ $t{NAME1}{$n} },$t{name0}{$t{name1}{$n1}});
push(@{ $t{MAKER1}{$n} },$t{maker0}{$t{maker1}{$n1}});
# print "$n==>$t{type1}{$n1},$t{name0}{$t{name1}{$n1}},$t{maker0}{$t{maker1}{$n1}}\n";
}
}
}
@{ $t{list} } = ('1','A'..'Z');
for $n ( 0 .. $#{ $t{list} } ) {
$t{name} = $t{list}[$n];
$t{htmfile} = 'p' . lc($t{name}) . '.htm';
my $template = HTML::Template->new(filename => './parts/index.html');
my @loop = ();
for $n1 ( 0 .. $#{ $t{NO}{$t{name}} } ) {
$t{id1} = $t{ID}{$t{name}}[$n1];
$t{id1} = sprintf("%06s",$t{id1});
$t{id1} = 'a' . $t{id1};
if ( defined @{ $t{parts}{$t{id1}} } ) {
@{ $t{parts1} } = sort {$a cmp $b} @{ $t{parts}{$t{id1}} };
$t{p1} = '';
for $n2 ( 0 .. $#{ $t{parts1} } ) {
$t{p1} = $t{p1} . $t{parts1}[$n2];
}
} else {
$t{p1} = '<LI>PARTS';
}
my %row = (
TYPE1 => $t{TYPE1}{$t{name}}[$n1],
ID => $t{ID}{$t{name}}[$n1],
NAME1 => $t{NAME1}{$t{name}}[$n1],
MAKER1 => $t{MAKER1}{$t{name}}[$n1],
PARTS => $t{p1}
);
push(@loop, \%row);
}
# Output to a html file
$template->param(name => $t{name});
$template->param(dic_loop => \@loop);
open(OUT,">./parts/$t{htmfile}");
print OUT $template->output;
close(OUT);
print "It was written to $t{htmfile}\n";
}
exit;
__END__;
]]>
<![CDATA[
# parts04.pl
use strict;
use DBI;
use HTML::Template;
use utf8;
my(%t,$n,$n1,$n2,@fld,$pref,@rec);
# 连接数据库
$$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;
}
# 取出parts_nu的数据
$t{sth} = $$pref{dbh}->prepare("SELECT id,parts_Unit FROM parts_nu");
$t{sth}->execute;
while ( @rec = $t{sth}->fetchrow_array ) {
$t{unit1}{$rec[0]} = $rec[1];
}
$t{sth}->finish;
# 取出main_name1的数据
$t{sth} = $$pref{dbh}->prepare("SELECT id,name FROM main_name1");
$t{sth}->execute;
while ( @rec = $t{sth}->fetchrow_array ) {
$t{name0}{$rec[0]} = $rec[1];
}
$t{sth}->finish;
# 取出main_maker1的数据
$t{sth} = $$pref{dbh}->prepare("SELECT id,name FROM main_maker1");
$t{sth}->execute;
while ( @rec = $t{sth}->fetchrow_array ) {
$t{maker0}{$rec[0]} = $rec[1];
}
$t{sth}->finish;
# 取出main_type1的数据
$t{sth} = $$pref{dbh}->prepare("SELECT id,name,gname_id,maker_id FROM main_type1");
$t{sth}->execute;
while ( @rec = $t{sth}->fetchrow_array ) {
$t{type1}{$rec[0]} = $rec[1];
$t{name1}{$rec[0]} = $rec[2];
$t{maker1}{$rec[0]} = $rec[3];
}
$t{sth}->finish;
# 关闭数据库
$$pref{dbh}->disconnect;
#读零件表文件ptalbe.txt
open(IN,"../txt/ptables.txt") or die "Can't open the file ptables.txt\n";
while(<IN>){
if ( $. >= 3 ) {
@fld = split(/===/);
$fld[3] =~ s/==/\//g;
$fld[3] =~ s/^\'//;
$fld[3] =~ s/\'$//;
if ( $fld[6] ) {
chop($fld[6]); # 注意!$fld[6]带有回车键!
$t{p1} = '<LI>' . $fld[4] . '==>' . $fld[3] . ' (' . $t{unit1}{$fld[6]} . ')';
} else {
print $_;
exit;
}
push(@{ $t{parts}{$fld[1]} },$t{p1});
}
}
close(IN);
# 处理TYPE
for $n1 ( sort {$t{type1}{$a} cmp $t{type1}{$b}} %{ $t{type1} } ) {
$t{C1} = substr($t{type1}{$n1},0,1);
if ( $t{C1} =~ /\d/ or $t{C1} =~ /\W/ ) {
push(@{ $t{NO}{'1'} },1);
push(@{ $t{ID}{'1'} },$n1);
push(@{ $t{TYPE1}{'1'} },$t{type1}{$n1});
push(@{ $t{NAME1}{'1'} },$t{name0}{$t{name1}{$n1}});
push(@{ $t{MAKER1}{'1'} },$t{maker0}{$t{maker1}{$n1}});
# print "$n1==>$t{type1}{$n1},$t{name0}{$t{name1}{$n1}},$t{maker0}{$t{maker1}{$n1}}\n";
}
}
for $n ( "A".."Z" ) {
for $n1 ( sort {$t{type1}{$a} cmp $t{type1}{$b}} %{ $t{type1} } ) {
$t{C1} = substr($t{type1}{$n1},0,1);
$t{C1} = uc($t{C1});
if ( $t{C1} eq $n ) {
push(@{ $t{NO}{$n} },$n);
push(@{ $t{ID}{$n} },$n1);
push(@{ $t{TYPE1}{$n} },$t{type1}{$n1});
push(@{ $t{NAME1}{$n} },$t{name0}{$t{name1}{$n1}});
push(@{ $t{MAKER1}{$n} },$t{maker0}{$t{maker1}{$n1}});
# print "$n==>$t{type1}{$n1},$t{name0}{$t{name1}{$n1}},$t{maker0}{$t{maker1}{$n1}}\n";
}
}
}
@{ $t{list} } = ('1','A'..'Z');
for $n ( 0 .. $#{ $t{list} } ) {
$t{name} = $t{list}[$n];
$t{htmfile} = 'p' . lc($t{name}) . '.htm';
my $template = HTML::Template->new(filename => './parts/index.html');
my @loop = ();
for $n1 ( 0 .. $#{ $t{NO}{$t{name}} } ) {
$t{id1} = $t{ID}{$t{name}}[$n1];
$t{id1} = sprintf("%06s",$t{id1});
$t{id1} = 'a' . $t{id1};
if ( defined @{ $t{parts}{$t{id1}} } ) {
@{ $t{parts1} } = sort {$a cmp $b} @{ $t{parts}{$t{id1}} };
$t{p1} = '';
for $n2 ( 0 .. $#{ $t{parts1} } ) {
$t{p1} = $t{p1} . $t{parts1}[$n2];
}
} else {
$t{p1} = '<LI>PARTS';
}
my %row = (
TYPE1 => $t{TYPE1}{$t{name}}[$n1],
ID => $t{ID}{$t{name}}[$n1],
NAME1 => $t{NAME1}{$t{name}}[$n1],
MAKER1 => $t{MAKER1}{$t{name}}[$n1],
PARTS => $t{p1}
);
push(@loop, \%row);
}
# Output to a html file
$template->param(name => $t{name});
$template->param(dic_loop => \@loop);
open(OUT,">./parts/$t{htmfile}");
print OUT $template->output;
close(OUT);
print "It was written to $t{htmfile}\n";
}
]]>
返回