MySQL操作程序十三(操作开始画面的操作)
返回
- quo2,PRICEid改为4(FCA)
- $t{mode}没有赋值?
<![CDATA[
# $t{sql} .= ',"1","1","1","1","115","100","P1","D","P2","100","D0","P0")';
# $t{sql} .= ',"1","1","5","1","115","100","P1","D","P2","100","D0","P0")';
$t{sql} .= ',"1","4","5","1","115","100","P1","D","P2","100","D0","P0")';
]]>
<![CDATA[
Use of uninitialized value in string eq at ./pro/mscenq1_start.pl line 37
==> if ( $t{mode} eq 'new' or $t{mode} eq 'copy' or $t{mode} eq 'copy2' ) {
]]>
<![CDATA[
sub mscenq1_start {
my $self = shift;
my(%t,@rec,$n,$a_ref,$enq1ref,$aref,%seen,$item,$row);
# 读取数据
$t{q} = $self->query();
$t{mode0} = $t{q}->param("mode0");
$t{word1} = $t{q}->param("word1");
if ( !($t{word1}) ) {
$t{word1} = '9999999999999999';
} else {
$t{word1} = lc($t{word1});
}
# 最大IDを読む
$t{enq1_maxid} = $self->dbh->selectrow_array("SELECT MAX(id) FROM enq1");
$t{enq1_id} = $t{enq1_maxid} + 1;
$t{mode} = $t{q}->param("mode");
# 生成新的enq1项目
if ( $t{mode} eq 'new' or $t{mode} eq 'copy' or $t{mode} eq 'copy2' ) {
$t{oldid} = $t{q}->param("oldid");
# 取得时间
$a_ref = obtain_time($a_ref);
$t{time1} = $$a_ref{time1};
# 读取表格enq1最大号码的ourref
$t{ourref1} = $self->dbh->selectrow_array("SELECT ourref FROM enq1 WHERE id = $t{enq1_maxid}");
# 自动生成一个ourref
$t{ourref2} = $$a_ref{ymd} . '01';
$t{tmp1} = substr($t{ourref1},-2);
if ( $t{tmp1} eq '99' ) { # 最大は99
$t{ourref0} = $t{ourref1};
$t{ourref0_OK} = 'NG';
} elsif ( ($t{ourref1} - $t{ourref2}) >= 0 ) {
$t{ourref0} = $t{ourref1} + 1;
$t{ourref0_OK} = 'OK';
} else {
$t{ourref0} = $t{ourref2};
$t{ourref0_OK} = 'OK';
}
# enq2の最大id
$t{enq2_maxid} = $self->dbh->selectrow_array("SELECT MAX(id) FROM enq2");
$t{enq2_id} = $t{enq2_maxid} + 1;
if ( $t{mode} eq 'new' or $t{mode} eq 'copy2') { # 新的情况
# 插入enq2的新项目
$t{sql} = "INSERT INTO enq2 (";
$t{sql} .= 'time,enq1id,LANGUAGEid,ORIGINid,PRICEid,PAYMENTid,makerid,DELIVERY,type1id,partsid,QTY,price,discount,discount0,money,price1) VALUES(';
$t{sql} .= '"' . $t{time1} . '","';
$t{sql} .= $t{enq1_id} . '","1","1","1","1","1","","B","C","C","P","D","100","1","P1")';
$t{insert} = $self->dbh->do("$t{sql}");
# enq2の最大idをenq1に追加する
# 写入enq1操作
if ( $t{mode} eq 'new' ) {
$t{sql} = "INSERT INTO enq1 (";
$t{sql} .= 'time,ourref,owner,hullnoid,type1id,partsid,QTY,LANGUAGEid,makerid,enq2s,seriesid) VALUES("';
$t{sql} .= $t{time1} . '","';
$t{sql} .= $t{ourref0} . '",1,1,"B","C","C","1","1","' . $t{enq2_id} . '","C")';
$t{insert} = $self->dbh->do("$t{sql}");
} else { # COPY enq1的owner,hullnoid
($t{owner1},$t{ownerno1},$t{hullnoid1}) = $self->dbh->selectrow_array("SELECT owner,ownerno,hullnoid FROM enq1 WHERE id = $t{oldid}");
$t{sql} = "INSERT INTO enq1 (";
$t{sql} .= 'time,ourref,owner,ownerno,hullnoid,type1id,partsid,QTY,LANGUAGEid,makerid,enq2s,seriesid) VALUES("';
$t{sql} .= $t{time1} . '","';
$t{sql} .= $t{ourref0} . '","'. $t{owner1} . '","' . $t{ownerno1} . '","' . $t{hullnoid1} . '","B","C","C","1","1","' . $t{enq2_id} . '","C")';
$t{insert} = $self->dbh->do("$t{sql}");
}
# 写入quo2操作
$t{sql} = "INSERT INTO quo2 (";
$t{sql} .= 'time,ORIGINid,PRICEid,PAYMENTid,money,percent0,discount0,percent,discount,price,disc0,disc,price0) VALUES(';
$t{sql} .= '"' . $t{time1} . '"';
$t{sql} .= ',"1","1","1","1","115","100","P1","D","P2","100","D0","P0")';
$t{insert} = $self->dbh->do("$t{sql}");
# 写入order1操作
$t{sql} = "INSERT INTO order1 (";
$t{sql} .= 'time,ORIGINid,PRICEid,PAYMENTid,consignee,changeprice,LANGUAGEid) VALUES(';
$t{sql} .= '"' . $t{time1} . '",';
$t{sql} .= '"1","1","1","1","1","2")';
$t{insert} = $self->dbh->do("$t{sql}");
} elsif ( $t{mode} eq 'copy' ) { # COPY1的情况
# COPY enq1
$$a_ref{table} = 'enq1';
$$a_ref{oldid} = $t{oldid};
$$a_ref{time1} = $t{time1};
$$a_ref{ourref0} = $t{ourref0};
($a_ref,$self) = copy_one2($a_ref,$self);
# COPY enq2
# 首先取得enq2的id一览
$t{enq2s} = $self->dbh->selectrow_array("SELECT enq2s FROM enq1 WHERE id = $t{oldid}");
@{ $t{enq2ss} } = split(/=/,$t{enq2s});
# COPY各个enq2,同时增加enq2的符号
$t{enq2_id} = $t{enq2_id} - 1;
@{ $t{enq2list} } = ();
for $n ( 0 .. $#{ $t{enq2ss} } ) {
$t{enq2_id}++;
push(@{ $t{enq2list} },$t{enq2_id});
$t{enq2id1} = $t{enq2ss}[$n];
$$a_ref{table} = 'enq2';
$$a_ref{oldid} = $t{enq2id1};
$$a_ref{enq1id} = $t{enq1_id};
($a_ref,$self) = copy_one22($a_ref,$self);
}
# 更新enq1的enq2s项目
$t{enq2s} = join('=',@{ $t{enq2list} });
$t{sql} = 'UPDATE enq1 set enq2s = "';
$t{sql} .= $t{enq2s} . '" WHERE id = "' . $t{enq1_id} . '"';
$t{insert} = $self->dbh->do("$t{sql}");
# COPY quo2
$$a_ref{table} = 'quo2';
$$a_ref{oldid} = $t{oldid};
($a_ref,$self) = copy_one1($a_ref,$self);
# COPY order1
$$a_ref{table} = 'order1';
$$a_ref{oldid} = $t{oldid};
($a_ref,$self) = copy_one1($a_ref,$self);
if ( $t{mode0} eq 'work1' ) {
$t{enq1s1} = $self->dbh->selectrow_array("SELECT enq1s1 FROM enq1list");
$t{enq1s1} = $t{enq1_id} . '=' . $t{enq1s1};
$t{sql1} = 'UPDATE enq1list set enq1s1 = "';
$t{sql1} .= $t{enq1s1} . '" where id = "1"';
$t{DO1} = $self->dbh->do($t{sql1});
} elsif ( $t{mode0} eq 'work2' ) {
$t{enq1s2} = $self->dbh->selectrow_array("SELECT enq1s2 FROM enq1list");
$t{enq1s2} = $t{enq1_id} . '=' . $t{enq1s2};
$t{sql1} = 'UPDATE enq1list set enq1s2 = "';
$t{sql1} .= $t{enq1s2} . '" where id = "1"';
$t{DO1} = $self->dbh->do($t{sql1});
}
}
}
# mode0 mode
$t{enq1_maxid} = $self->dbh->selectrow_array("SELECT MAX(id) FROM enq1");
$t{enq1_id} = $t{enq1_maxid} + 1;
$t{SEARCH_OK} = 0;
@{ $t{list_id} } = ();
@{ $t{list_time} } = ();
@{ $t{list_ourref} } = ();
@{ $t{list_ownerno} } = ();
@{ $t{list_shipname} } = ();
# start mode
if ( $t{mode0} eq 'start' ) {
$t{end1} = 20; # 显示项目的最大数量
$aref = $self->dbh->selectall_arrayref("SELECT id,time,ourref,ownerno,hullnoid from enq1 ORDER BY id DESC LIMIT $t{end1}");
for $row ( @$aref ) {
($t{id1},$t{time1},$t{ourref1},$t{ownerno1},$t{hullnoid1}) = @$row;
$t{shipname} = $self->dbh->selectrow_array("SELECT name FROM hull_no WHERE id = $t{hullnoid1}");
$t{orderno} = $self->dbh->selectrow_array("SELECT orderno FROM order1 WHERE id = $t{id1} and orderno is not NULL");
$t{post} = $self->dbh->selectrow_array("SELECT post FROM order1 WHERE id = $t{id1} and post is not NULL");
push(@{ $t{list_id} }, $t{id1});
push(@{ $t{list_time} }, $t{time1});
push(@{ $t{list_ourref} }, $t{ourref1});
push(@{ $t{list_ownerno} }, $t{ownerno1});
push(@{ $t{list_shipname} }, $t{shipname});
push(@{ $t{list_orderno} }, $t{orderno});
push(@{ $t{list_post} }, $t{post});
}
# work1 mode
} elsif ( $t{mode0} eq 'work1' ) {
$t{enq1s1} = $self->dbh->selectrow_array("SELECT enq1s1 FROM enq1list WHERE enq1s1 != 'NO'");
if ( $t{enq1s1} ) {
$t{enq1s1_word} = $self->dbh->selectrow_array("SELECT enq1s1_word FROM enq1list");
# @{ $$enq1ref{list} } = split(/=/,$t{enq1s1});
@{ $t{enq1s} } = split(/=/,$t{enq1s1});
$t{end1} = $#{ $t{enq1s} } + 1;
for $n ( 0 .. $#{ $t{enq1s} } ) {
$t{e1_id} = $t{enq1s}[$n];
@rec = $self->dbh->selectrow_array("SELECT * FROM enq1 WHERE id = $t{e1_id}");
$t{shipname} = $self->dbh->selectrow_array("SELECT name FROM hull_no WHERE id = $rec[5]");
$t{orderno} = $self->dbh->selectrow_array("SELECT orderno FROM order1 WHERE id = $rec[0]");
$t{post} = $self->dbh->selectrow_array("SELECT post FROM order1 WHERE id = $rec[0]");
push(@{ $t{list_id} }, $rec[0]);
push(@{ $t{list_time} }, $rec[1]);
push(@{ $t{list_ourref} }, $rec[2]);
push(@{ $t{list_ownerno} }, $rec[4]);
push(@{ $t{list_shipname} }, $t{shipname});
push(@{ $t{list_orderno} }, $t{orderno});
push(@{ $t{list_post} }, $t{post});
}
}
# work2 mode
} elsif ( $t{mode0} eq 'work2' ) {
$t{enq1s2} = $self->dbh->selectrow_array("SELECT enq1s2 FROM enq1list WHERE enq1s2 != 'NO'");
if ( $t{enq1s2} ) {
$t{enq1s2_word} = $self->dbh->selectrow_array("SELECT enq1s2_word FROM enq1list");
# @{ $$enq1ref{list} } = split(/=/,$t{enq1s2});
@{ $t{enq1s} } = split(/=/,$t{enq1s2});
$t{end1} = $#{ $t{enq1s} } + 1;
for $n ( 0 .. $#{ $t{enq1s} } ) {
$t{e1_id} = $t{enq1s}[$n];
@rec = $self->dbh->selectrow_array("SELECT * FROM enq1 WHERE id = $t{e1_id}");
$t{shipname} = $self->dbh->selectrow_array("SELECT name FROM hull_no WHERE id = $rec[5]");
$t{orderno} = $self->dbh->selectrow_array("SELECT orderno FROM order1 WHERE id = $rec[0]");
$t{post} = $self->dbh->selectrow_array("SELECT post FROM order1 WHERE id = $rec[0]");
push(@{ $t{list_id} }, $rec[0]);
push(@{ $t{list_time} }, $rec[1]);
push(@{ $t{list_ourref} }, $rec[2]);
push(@{ $t{list_ownerno} }, $rec[4]);
push(@{ $t{list_shipname} }, $t{shipname});
push(@{ $t{list_orderno} }, $t{orderno});
push(@{ $t{list_post} }, $t{post});
}
}
# search mode
} elsif ( $t{mode0} eq 'search' ) {
# search ourref and ENQ的编号
$aref = $self->dbh->selectcol_arrayref("SELECT id FROM enq1 WHERE ourref LIKE \'\%$t{word1}\%\'
OR ownerno LIKE \'\%$t{word1}\%\'
ORDER BY id DESC");
@{ $t{list1} } = @$aref;
$t{enq1_word} .= 'Search_word=>';
# search 船名
$aref = $self->dbh->selectcol_arrayref("SELECT enq1.id FROM enq1,hull_no WHERE hull_no.name LIKE \'\%$t{word1}\%\'
AND hull_no.id = enq1.hullnoid
ORDER BY enq1.id DESC");
@{ $t{list2} } = @$aref;
# search ORDER NO
$aref = $self->dbh->selectcol_arrayref("SELECT id FROM order1 WHERE orderno LIKE \'\%$t{word1}\%\'
ORDER BY id DESC");
@{ $t{list3} } = @$aref;
# 两个搜索结果合并(可能有相同id的情况)
@{ $t{list3} } = (@{ $t{list1} },@{ $t{list2} },@{ $t{list3} });
@{ $t{list4} } = grep { ! $seen{$_} ++ } @{ $t{list3} };
# 排序
@{ $t{enq1s} } = reverse sort {$a<=>$b} @{ $t{list4} };
# enq1 の出力
if ( $t{enq1s}[0] ) {
for $n ( 0 .. $#{ $t{enq1s} } ) {
$t{e1_id} = $t{enq1s}[$n];
@{ $t{one1} } = $self->dbh->selectrow_array("SELECT * FROM enq1 WHERE id = $t{e1_id}");
$t{shipname} = $self->dbh->selectrow_array("SELECT name FROM hull_no WHERE id = $t{one1}[5]");
$t{orderno} = $self->dbh->selectrow_array("SELECT orderno FROM order1 WHERE id = $t{e1_id} and orderno is not NULL");
$t{post} = $self->dbh->selectrow_array("SELECT post FROM order1 WHERE id = $t{e1_id} and post is not NULL");
push(@{ $t{list_id} }, $t{e1_id});
push(@{ $t{list_time} }, $t{one1}[1]);
push(@{ $t{list_ourref} }, $t{one1}[2]);
push(@{ $t{list_ownerno} }, $t{one1}[4]);
push(@{ $t{list_shipname} }, $t{shipname});
push(@{ $t{list_orderno} }, $t{orderno});
push(@{ $t{list_post} }, $t{post});
}
$t{end1} = $#{ $t{list_id} } + 1;
$t{SEL1} = $t{q}->param("SEL1");
if ( $t{SEL1} == 1 ) {
$t{enq1s1} = join('=',@{ $t{list_id} });
$t{sql1} = 'UPDATE enq1list set enq1s1 = "';
$t{sql1} .= $t{enq1s1} . '" where id = "1"';
$t{DO1} = $self->dbh->do($t{sql1});
$t{word1} = uc($t{word1});
$t{sql1} = 'UPDATE enq1list set enq1s1_word = "';
$t{sql1} .= $t{word1} . '" where id = "1"';
$t{DO1} = $self->dbh->do($t{sql1});
$t{enq1s1_word} = $t{enq1_word} . $t{word1};
$t{mode0} = 'work1';
} elsif ( $t{SEL1} == 2 ) {
$t{enq1s2} = join('=',@{ $t{list_id} });
$t{sql1} = 'UPDATE enq1list set enq1s2 = "';
$t{sql1} .= $t{enq1s2} . '" where id = "1"';
$t{DO1} = $self->dbh->do($t{sql1});
$t{word1} = uc($t{word1});
$t{sql1} = 'UPDATE enq1list set enq1s2_word = "';
$t{sql1} .= $t{word1} . '" where id = "1"';
$t{DO1} = $self->dbh->do($t{sql1});
$t{enq1s2_word} = $t{enq1_word} . $t{word1};
$t{mode0} = 'work2';
}
}
}
# 输出enq1
for $n (1 .. $t{end1} ) {
$t{NO} = $n - 1;
my %row = (
enq1_id => $t{enq1_id},
id => $t{list_id}[$t{NO}],
# time => $t{list_time}[$t{NO}],
ourref => $t{list_ourref}[$t{NO}],
ownerno => $t{list_ownerno}[$t{NO}],
shipname => $t{list_shipname}[$t{NO}],
orderno => $t{list_orderno}[$t{NO}],
post => $t{list_post}[$t{NO}]
);
push(@loop, \%row);
}
# Get CGI query object
$t{q} = $self->query();
if ( $t{mode0} eq 'work1' ) {
$t{template} = $self->load_tmpl('mscenq1_start1.htm') || die "error loading tmpl";
$t{template}->param(enq1s1_word => $t{enq1s1_word});
} elsif ( $t{mode0} eq 'work2' ) {
$t{template} = $self->load_tmpl('mscenq1_start2.htm') || die "error loading tmpl";
$t{template}->param(enq1s2_word => $t{enq1s2_word});
} else {
$t{template} = $self->load_tmpl('mscenq1_start.htm') || die "error loading tmpl";
}
# $t{template}->param(pro => "mscenq1_start.pl");
$t{template}->param(pro => $t{ourref1});
$t{template}->param(enq1_id => $t{enq1_id});
$t{template}->param(mode0 => $t{mode0});
$t{template}->param(word1 => $t{word1});
$t{template}->param(THIS_LOOP => \@loop);
return $t{template}->output;
}
# 取得现在时间的子程序
sub obtain_time {
my($a_ref) = @_;
my(%t);
($t{day},$t{month},$t{year1}) = (localtime)[3,4,5];
$t{year} = substr($t{year1},-1);
$t{month} = $t{month} + 1;
$t{month} = sprintf("%02d",$t{month});
$t{day} = sprintf("%02d",$t{day});
$t{year1} = $t{year1} + 1900;
$t{time1} = $t{year1} . '-' . $t{month} . '-' . $t{day};
$$a_ref{time1} = $t{time1};
$$a_ref{ymd} = $t{year} . $t{month} . $t{day};
return($a_ref);
}
# COPY一个项目(time一项也COPY)
sub copy_one1 {
my($a_ref,$self) = @_;
my(%t,@rec,$n);
# 取出COLUMNS
$t{sth} = $self->dbh->prepare("SHOW COLUMNS FROM $$a_ref{table}");
$t{sth}->execute;
while ( @rec = $t{sth}->fetchrow_array ) {
push(@{ $t{columns} },$rec[0]);
}
$t{sth}->finish;
# 取出数据(同时记住不是NULL的项目)
@{ $t{one} } = $self->dbh->selectrow_array("SELECT * FROM $$a_ref{table} WHERE id = $$a_ref{oldid}");
for $n ( 1 .. $#{ $t{one} } ) {
$t{name} = $t{columns}[$n];
$t{value} = $t{one}[$n];
if ( $t{value} ) {
$t{value} = '"' . $t{value} . '"';
push(@{ $t{names} },$t{name});
push(@{ $t{values} },$t{value});
}
}
$t{name1} = join(',',@{ $t{names} });
$t{value1} = join(',',@{ $t{values} });
# 插入新项目
$t{sql} = 'INSERT INTO ' . $$a_ref{table} . '(';
$t{sql} .= $t{name1} . ') VALUES(';
$t{sql} .= $t{value1} . ')';
$t{DO} = $self->dbh->do($t{sql});
# print "DO=$t{DO}\n";
return($a_ref,$self);
}
# COPY一个项目(time/ourref两项不COPY)
sub copy_one22 {
my($a_ref,$self) = @_;
my(%t,@rec,$n);
# 取出COLUMNS
$t{sth} = $self->dbh->prepare("SHOW COLUMNS FROM $$a_ref{table}");
$t{sth}->execute;
while ( @rec = $t{sth}->fetchrow_array ) {
push(@{ $t{columns} },$rec[0]);
}
$t{sth}->finish;
# 取出数据(同时记住不是NULL的项目)
@{ $t{one} } = $self->dbh->selectrow_array("SELECT * FROM $$a_ref{table} WHERE id = $$a_ref{oldid}");
$t{names}[0] = 'time';
$t{values}[0] = '"' . $t{one}[1] . '"';
for $n ( 3 .. $#{ $t{one} } ) {
$t{name} = $t{columns}[$n];
$t{value} = $t{one}[$n];
if ( $t{value} ) {
$t{value} = '"' . $t{value} . '"';
push(@{ $t{names} },$t{name});
push(@{ $t{values} },$t{value});
}
}
$t{name1} = join(',',@{ $t{names} });
$t{value1} = join(',',@{ $t{values} });
# 插入新项目
$t{sql} = 'INSERT INTO ' . $$a_ref{table} . '(enq1id,';
$t{sql} .= $t{name1} . ') VALUES("';
$t{sql} .= $$a_ref{enq1id} . '",';
$t{sql} .= $t{value1} . ')';
$t{DO} = $self->dbh->do($t{sql});
# print "DO=$t{DO}\n";
return($a_ref,$self);
}
# COPY一个项目(time/ourref两项不COPY)
sub copy_one2 {
my($a_ref,$self) = @_;
my(%t,@rec,$n);
# 取出COLUMNS
$t{sth} = $self->dbh->prepare("SHOW COLUMNS FROM $$a_ref{table}");
$t{sth}->execute;
while ( @rec = $t{sth}->fetchrow_array ) {
push(@{ $t{columns} },$rec[0]);
}
$t{sth}->finish;
# 取出数据(同时记住不是NULL的项目)
@{ $t{one} } = $self->dbh->selectrow_array("SELECT * FROM $$a_ref{table} WHERE id = $$a_ref{oldid}");
for $n ( 3 .. $#{ $t{one} } ) {
$t{name} = $t{columns}[$n];
$t{value} = $t{one}[$n];
if ( $t{value} ) {
$t{value} = '"' . $t{value} . '"';
push(@{ $t{names} },$t{name});
push(@{ $t{values} },$t{value});
}
}
$t{name1} = join(',',@{ $t{names} });
$t{value1} = join(',',@{ $t{values} });
# 插入新项目
$t{sql} = 'INSERT INTO ' . $$a_ref{table} . '(time,ourref,';
$t{sql} .= $t{name1} . ') VALUES("' . $$a_ref{time1} . '","';
$t{sql} .= $$a_ref{ourref0} . '",';
$t{sql} .= $t{value1} . ')';
$t{DO} = $self->dbh->do($t{sql});
# print "DO=$t{DO}\n";
return($a_ref,$self);
}
1;
__END__
]]>
旧程序(start mode)这部分效率较低,读取所有的项目,新程序用"LIMIT",只读"20次"
<![CDATA[
$t{sth} = $self->dbh->prepare("SELECT * FROM enq1 ORDER BY id DESC");
$t{sth}->execute;
while ( @rec = $t{sth}->fetchrow_array ) {
$t{shipname} = $self->dbh->selectrow_array("SELECT name FROM hull_no WHERE id = $rec[5]");
$t{orderno} = $self->dbh->selectrow_array("SELECT orderno FROM order1 WHERE id = $rec[0]");
$t{post} = $self->dbh->selectrow_array("SELECT post FROM order1 WHERE id = $rec[0]");
push(@{ $t{list_id} }, $rec[0]);
push(@{ $t{list_time} }, $rec[1]);
push(@{ $t{list_ourref} }, $rec[2]);
push(@{ $t{list_ownerno} }, $rec[4]);
push(@{ $t{list_shipname} }, $t{shipname});
push(@{ $t{list_orderno} }, $t{orderno});
push(@{ $t{list_post} }, $t{post});
}
$t{sth}->finish;
$t{end} = $#{ $t{list_id} } + 1;
if ( $t{end} < $t{max} ) {
$t{end1} = $t{end};
} else {
$t{end1} = $t{max};
}
]]>
返回