MySQL操作程序九
返回
<![CDATA[
[Sun Sep 12 22:17:15 2010] [error] [client 127.0.0.1] Use of uninitialized value in concatenation (.) or string at ./pro/mscnew_price.pl line 151,
151: $t{one1} = $t{prices}[$n2] . '=' . $t{per1} . '=' . $t{money} . '=' . $t{time1} . '=' . $t{owner};
修改如下($t{per1}处理)
if ( $t{percents}[$n2] ) {
$t{per1} = $t{percents}[$n2];
} else {
$t{per1} = 100;
}
修改如下($t{prices}[$n2]处理)
if ($t{prices}[$n2]) {
$t{one1} = $t{prices}[$n2] . '=' . $t{per1} . '=' . $t{money} . '=' . $t{time1} . '=' . $t{owner};
} else {
$t{one1} = '0=100=JPY=0000-00-00=owner';
}
]]>
更新价格程序(Ver3)
<![CDATA[
sub mscnew_price {
my $self = shift;
my(%t,$n,$n1,$n2,@rec,@loop,$row_ref,$pref,$aref,$row,$pid,$eid);
# Get CGI query object
$t{q} = $self->query();
# データベースにアクセス
$t{tname} = $t{q}->param("tname");
$t{id} = $t{q}->param("id");
$t{NE1} = $t{q}->param("NE1");
$t{enq1_id} = $t{q}->param("enq1_id");
# 取出DWG图号
$t{DWG} = $self->dbh->selectrow_array("SELECT DWG FROM main_type1 WHERE id = $t{id}");
@{ $t{DWGs} } = split(/=/,$t{DWG});
$t{ptable} = sprintf("%06d",$t{id});
$t{ptable} = 'a' . $t{ptable};
# 检索所有enq2并写入ptable的price1
# 如果type1id=B,没有type1数据
# 取得含有type1id的enq2的id/makerid/type1id/partsid/price/discount/discount0/money
$aref = $self->dbh->selectall_arrayref("SELECT id,enq1id,type1id,partsid,price,discount,discount0
FROM enq2 WHERE type1id LIKE \'$t{id}\'
OR type1id LIKE \'\%\=\=$t{id}\'
OR type1id LIKE \'\%\=\=$t{id}\=\=\%\'
OR type1id LIKE \'$t{id}\=\=\%\'
ORDER BY time DESC");
$t{DWG} = ''; # Debug使用
for $row ( @$aref ) {
($t{id1},$t{enq1id1},$t{type1id1},$t{partsid1},$t{price1},$t{discount1},$t{discount01}) = @$row;
# 按partsid有否处理,如果partsid=C,没有parts数据,对enq2来说,似乎不会出现type1id=B和partsid=C的情况
# 按price有否处理,如果price=P,没有价格。按0处理
next if $t{price1} eq 'P';
# 按enq1id=0不用处理
# next if $t{enq1id1}==0;
# 分解partsid,price并赋值(要考虑多台主机的情况)
@{ $t{type1s} } = split(/==/,$t{type1id1});
@{ $t{partss} } = split(/==/,$t{partsid1});
@{ $t{prices_all} } = split(/=/,$t{price1});
@{ $t{discount1_all} } = split(/=/,$t{discount1});
$t{N1} = 0;
# $t{DWG} = $t{DWG} . $t{id1} . ',prices_all_length=>' . $#{ $t{prices_all} } . ',pppp,';
for $n ( 0 .. $#{ $t{type1s} } ) { # 处理相同type1的主机
if ( $t{type1s}[$n] == $t{id} ) {
$t{partsid1} = $t{partss}[$n];
@{ $t{partsids} } = split(/=/,$t{partsid1});
$t{N2} = 1 + $#{ $t{partsids} }; # 零件的数量
@{ $t{prices} } = splice(@{ $t{prices_all} },$t{N1},$t{N2});
# $t{DWG} = $t{DWG} . ',N1=>' . $t{N1} . ',N2=>' . $t{N2};
# $t{DWG} = $t{DWG} . ',prices_length=>' . $#{ $t{prices} } . '/////' . '<br>';
# 有的话:读取makerid/money
($t{time1},$t{enq1id1},$t{makerid1},$t{money1}) = $self->dbh->selectrow_array("SELECT time,enq1id,makerid,money FROM enq2 WHERE id = $t{id1}");
if ( $t{enq1id1} == 0 ) {
$t{ourref1} = 'XXXXXXX';
} else {
$t{ourref1} = $self->dbh->selectrow_array("SELECT ourref FROM enq1 WHERE id = $t{enq1id1}");
}
# 按discount处理,如果discount=D,没有个别discount,所有discount采用discount0的值
if ( $t{discount1} eq 'D' ) { # 所有的discount相同
for $n1 ( 0 .. $#{ $t{prices} } ) {
push(@{ $t{discounts} },$t{discount01});
}
} else {
@{ $t{discounts} } = splice(@{ $t{discount1_all} },$t{N1},$t{N2});
}
# 数据作成
for $n1 ( 0 .. $#{ $t{prices} } ) {
$pid = $t{partsids}[$n1];
$t{p1} = $t{prices}[$n1];
$t{d1} = $t{discounts}[$n1];
# 元件的一个价格生成
$t{one1} = $t{p1} . '=' . $t{d1} . '=' . $t{money1} . '=' . $t{time1} . '=' . $t{ourref1}
. '=' . $t{makerid1} . '=' . $t{id1} ;
push(@{ $t{price1s}{$pid} }, $t{one1});
if ( $t{enq1id1} != 0 ) {
push(@{ $t{enq1ids}{$pid} }, $t{enq1id1});
}
# $t{DWG} = $t{DWG} . 'n1=' . $n1 . ',' . $t{one1} . ';';
}
# $t{DWG} = $t{DWG} . '<br>';
# 不同主机的项目计算零件数量
} else {
@{ $t{partsids} } = split(/=/,$t{partss}[$n]);
$t{N1} = $t{N1} + $#{ $t{partsids} } + 1;
}
}
}
# 把所有的价格写入价格表
for $pid ( sort {$a<=>$b} keys %{ $t{price1s} } ) {
$t{one1} = join('==',@{ $t{price1s}{$pid} });
# $t{DWG} = $t{DWG} . 'pid=' . $pid . ',one1=' . $t{one1} . '<br>';
$t{sql} = 'UPDATE ' . $t{ptable} . ' set price1 ="';
$t{sql} .= $t{one1} . '" where id = ' . $pid;
$t{DO} = $self->dbh->do($t{sql});
}
# 检索所有quo2并写入ptable的price2
# 只检查已报过价(有enq2和enq2的价格表)的enq1的id(和quo2相同)
for $pid ( sort {$a<=>$b} keys %{ $t{enq1ids} } ) {
for $eid ( @{ $t{enq1ids}{$pid} } ) {
# $t{DWG} = $t{DWG} . 'pid==>' . $pid . ',enq1=>'. $eid . '/////' . '<br>';
($t{owner},$t{type1id1},$t{partsid1}) = $self->dbh->selectrow_array("select owner,type1id,partsid from enq1 where id = $eid");
($t{time1},$t{percent0},$t{percent1},$t{price1},$t{money}) = $self->dbh->selectrow_array("select
time,percent0,percent,price,money
from quo2 where id = $eid");
# 分解partsid,price并赋值(要考虑多台主机的情况)
@{ $t{type1s} } = split(/==/,$t{type1id1});
@{ $t{partss} } = split(/==/,$t{partsid1});
@{ $t{prices_all} } = split(/=/,$t{price1});
@{ $t{percent_all} } = split(/=/,$t{percent1});
$t{N1} = 0;
for $n1 ( 0 .. $#{ $t{type1s} } ) { # 处理相同type1的主机
if ( $t{type1s}[$n1] == $t{id} ) {
$t{partsid1} = $t{partss}[$n1];
@{ $t{partsids} } = split(/=/,$t{partsid1});
$t{N2} = 1 + $#{ $t{partsids} };
@{ $t{prices} } = splice(@{ $t{prices_all} },$t{N1},$t{N2});
@{ $t{percents} } = splice(@{ $t{percent_all} },$t{N1},$t{N2});;
# 检查已有报价的零件
for $n2 ( 0 .. $#{ $t{partsids} } ) {
next unless $t{partsids}[$n2] == $pid;
# $pid = $t{partsids}[$n2];
# quo2的percent=P1时,就把percent0的值代入各个零件的percent
if ($t{percent1} eq 'P1') {
$t{per1} = $t{percent0};
} else {
$t{per1} = $t{percents}[$n2];
}
$t{one1} = $t{prices}[$n2] . '=' . $t{per1} . '=' . $t{money} . '=' . $t{time1}
. '=' . $t{owner};
# . '=' . $t{makerid1} . '=' . $t{id1} ;
# $t{one1} = $t{p1} . '=' . $t{d1} . '=' . $t{money1} . '=' . $t{time1} . '=' . $t{ourref1}
# . '=' . $t{makerid1} . '=' . $t{id1} ;
push(@{ $t{price2s}{$pid} }, $t{one1});
}
# 不同主机的项目计算零件数量
} else {
@{ $t{partsids} } = split(/=/,$t{partss}[$n1]);
$t{N1} = $t{N1} + $#{ $t{partsids} } + 1;
}
}
}
}
# quo2的price是否是'P2',如果是P2,就认为价格还没有填入(price是最终价格,price0是厂家原价)
# 把所有的价格写入价格表
for $pid ( sort {$a<=>$b} keys %{ $t{price2s} } ) {
$t{one1} = join('==',@{ $t{price2s}{$pid} });
$t{sql} = 'UPDATE ' . $t{ptable} . ' set price2 ="';
$t{sql} .= $t{one1} . '" where id = ' . $pid;
$t{DO} = $self->dbh->do($t{sql});
}
# 显示price1和price2
@loop = ();
$t{sth} = $self->dbh->prepare("SELECT * FROM $t{ptable}");
$t{sth}->execute;
while ( @rec = $t{sth}->fetchrow_array ) {
my $row_ref = (); # この初期化はとても重要!
$t{NO} = 'select_' . $rec[0];
$$row_ref{NO} = $t{NO};
$$row_ref{NO_P1} = 'price1_' . $rec[0]; # PRICE1
$$row_ref{NO_P2} = 'price2_' . $rec[0]; # PRICE2
$$row_ref{name} = $rec[1];
$$row_ref{code} = $rec[2];
$$row_ref{dwg} = $t{DWGs}[$rec[3]-1];
$t{Nuid1} = $rec[4];
$$row_ref{Nuid} = $self->dbh->selectrow_array("select parts_Unit from parts_nu where id = $t{Nuid1}");
$$pref{p1} = $rec[6];
($row_ref,$self,$pref) = show_price1($row_ref,$self,$pref);
$$pref{p1} = $rec[7];
($row_ref,$self,$pref) = show_price2($row_ref,$self,$pref);
push(@loop, $row_ref);
}
$t{sth}->finish;
# HTMLファイルに出力
$t{template} = $self->load_tmpl("mscparts10.htm") || die "error loading tmpl";
$t{template}->param(enq1_id => $t{enq1_id});
$t{template}->param(ptable => $t{ptable});
$t{template}->param(tname => $t{tname});
$t{template}->param(THIS_LOOP => \@loop);
$t{template}->param(tid => $t{id});
$t{template}->param(pro => 'mscnew_price.pl');
# $t{template}->param(select => $t{select});
$t{template}->param(NE1 => $t{NE1});
$t{template}->param(DWG => $t{DWG});
return $t{template}->output;
}
1;
]]>
读取enq2的价格表时,必须检查一下enq1id,如果enq1id=0,该价格没有用
<![CDATA[
没有OURREF!
mysql> select * from enq2 where id = 1057\G
*************************** 1. row ***************************
id: 1057
time: 2008-08-18
enq1id: 0
LANGUAGEid: 1
ORIGINid: 1
PRICEid: 1
PAYMENTid: 1
makerid: 108
DELIVERY:
type1id: 821
partsid: 8=9=10=11=12=13=14=15=16=17=18=19=20=21=22=23=24=25=26=27=28=29=30=3
1
QTY: 6=6=24=24=2=2=2=2=2=4=4=4=6=6=24=24=2=2=2=2=2=8=8=8
memo:
price: 4440=480=9600=120=8640=2400=300=240=480=600=120=120=6000=480=12100=1
80=8640=2400=300=240=480=600=120=120
discount: D
discount0: 100
money: 1
price1: P1
makerref:
disc:
charges:
1 row in set (0.00 sec)
]]>
a000821的检查
<![CDATA[
----不能同时输入两个type1,821==821,须改成821
mysql> select id,type1id from enq2 where type1id like "%821==821%";
+------+----------+
| id | type1id |
+------+----------+
| 1896 | 821==821 |
+------+----------+
1 row in set (0.01 sec)
mysql> select * from enq2 where id = 1896\G
*************************** 1. row ***************************
id: 1896
time: 2008-11-07
enq1id: 1693
LANGUAGEid: 1
ORIGINid: 1
PRICEid: 1
PAYMENTid: 1
makerid: 108
DELIVERY:
type1id: 821==821
partsid: 52=53==54=55
QTY: 2=24==1=24
memo:
price: 6000=11700=4440=9600
discount: D
discount0: 100
money: 1
price1: P1
makerref:
disc:
charges:
1 row in set (0.00 sec)
mysql> select * from enq1 where id = 1693\G
*************************** 1. row ***************************
id: 1693
time: 2008-10-25
ourref: 8102525
owner: 1
ownerno: SA-2P-7G00K1
hullnoid: 270
type1id: 821==821
partsid: 52=53==54=55
QTY: 2=24==1=24
memo:
LANGUAGEid: 1
makerid: 108
enq2s: 1896
seriesid: C
1 row in set (0.00 sec)
mysql> select * from enq1 where id = 1693\G
*************************** 1. row ***************************
id: 1693
time: 2008-10-25
ourref: 8102525
owner: 1
ownerno: SA-2P-7G00K1
hullnoid: 270
type1id: 821
partsid: 52=53
QTY: 2=24
memo:
LANGUAGEid: 1
makerid: 108
enq2s: 1896
seriesid: C
1 row in set (0.00 sec)
]]>
更新价格(Ver2)
<![CDATA[
sub mscnew_price {
my $self = shift;
my(%t,$n,$n1,$n2,@rec,@loop,$row_ref,$pref,$aref,$row,$pid,$eid);
# Get CGI query object
$t{q} = $self->query();
# データベースにアクセス
$t{tname} = $t{q}->param("tname");
$t{id} = $t{q}->param("id");
$t{NE1} = $t{q}->param("NE1");
$t{enq1_id} = $t{q}->param("enq1_id");
# 取出DWG图号
$t{DWG} = $self->dbh->selectrow_array("SELECT DWG FROM main_type1 WHERE id = $t{id}");
@{ $t{DWGs} } = split(/=/,$t{DWG});
# HTMLファイルに出力
$t{template} = $self->load_tmpl("mscparts10.htm") || die "error loading tmpl";
$t{ptable} = sprintf("%06d",$t{id});
$t{ptable} = 'a' . $t{ptable};
# 检索所有enq2并写入ptable的price1
# 如果type1id=B,没有type1数据
# 取得含有type1id的enq2的id/makerid/type1id/partsid/price/discount/discount0/money
$aref = $self->dbh->selectall_arrayref("SELECT id,type1id,partsid,price,discount,discount0
FROM enq2 WHERE type1id LIKE \'$t{id}\'
OR type1id LIKE \'\%\=\=$t{id}\'
OR type1id LIKE \'\%\=\=$t{id}\=\=\%\'
OR type1id LIKE \'$t{id}\=\=\%\'
ORDER BY time DESC");
$t{DWG} = '';
for $row ( @$aref ) {
($t{id1},$t{type1id1},$t{partsid1},$t{price1},$t{discount1},$t{discount01}) = @$row;
# 按partsid有否处理,如果partsid=C,没有parts数据,对enq2来说,似乎不会出现type1id=B和partsid=C的情况
# 按price有否处理,如果price=P,没有价格。按0处理
next if $t{price1} eq 'P';
# 分解partsid,price并赋值(要考虑多台主机的情况)
@{ $t{type1s} } = split(/==/,$t{type1id1});
@{ $t{partss} } = split(/==/,$t{partsid1});
@{ $t{prices_all} } = split(/=/,$t{price1});
@{ $t{discount1_all} } = split(/=/,$t{discount1});
$t{N1} = 0;
for $n ( 0 .. $#{ $t{type1s} } ) { # 处理相同type1的主机
if ( $t{type1s}[$n] == $t{id} ) {
$t{partsid1} = $t{partss}[$n];
@{ $t{partsids} } = split(/=/,$t{partsid1});
$t{N2} = 1 + $#{ $t{partsids} };
@{ $t{prices} } = splice(@{ $t{prices_all} },$t{N1},$t{N2});
# $t{DWG} = $t{DWG} . $t{id1} . 'N1=>' . $t{N1} . 'N2=>' . $t{N2} . '/////' . '<br>';
# $t{DWG} = $t{DWG} . $t{id1} . 'prices_length' . $#{ $t{prices} } . 'prices_all_length=>' . $#{ $t{prices_all} } . '/////' . '<br>';
# 有的话:读取makerid/money
($t{time1},$t{enq1id1},$t{makerid1},$t{money1}) = $self->dbh->selectrow_array("SELECT time,enq1id,makerid,money FROM enq2 WHERE id = $t{id1}");
$t{ourref1} = $self->dbh->selectrow_array("SELECT ourref FROM enq1 WHERE id = $t{enq1id1}");
# 按discount处理,如果discount=D,没有个别discount,所有discount采用discount0的值
if ( $t{discount1} eq 'D' ) { # 所有的discount相同
for $n1 ( 0 .. $#{ $t{prices} } ) {
push(@{ $t{discounts} },$t{discount01});
}
} else {
@{ $t{discounts} } = splice(@{ $t{discount1_all} },$t{N1},$t{N2});
}
# 数据作成
for $n1 ( 0 .. $#{ $t{prices} } ) {
$pid = $t{partsids}[$n1];
$t{p1} = $t{prices}[$n1];
$t{d1} = $t{discounts}[$n1];
# 元件的一个价格生成
$t{one1} = $t{p1} . '=' . $t{d1} . '=' . $t{money1} . '=' . $t{time1} . '=' . $t{ourref1}
. '=' . $t{makerid1} . '=' . $t{id1} ;
push(@{ $t{price1s}{$pid} }, $t{one1});
push(@{ $t{enq1ids}{$pid} }, $t{enq1id1});
# $t{DWG} = $t{DWG} . '=' . $t{id1} . '=' . $n . '=p' . $t{one1} . '==';
}
# $t{DWG} = $t{DWG} . '/////' . '<br>';
# 不同主机的项目计算零件数量
} else {
@{ $t{partsids} } = split(/=/,$t{partss}[$n]);
$t{N1} = $t{N1} + $#{ $t{partsids} } + 1;
}
}
}
# 把所有的价格写入价格表
for $pid ( sort {$a<=>$b} keys %{ $t{price1s} } ) {
$t{one1} = join('==',@{ $t{price1s}{$pid} });
$t{sql} = 'UPDATE ' . $t{ptable} . ' set price1 ="';
$t{sql} .= $t{one1} . '" where id = ' . $pid;
$t{DO} = $self->dbh->do($t{sql});
}
# 检索所有quo2并写入ptable的price2
# 只检查已报过价(有enq2和enq2的价格表)的enq1的id(和quo2相同)
for $pid ( sort {$a<=>$b} keys %{ $t{enq1ids} } ) {
for $eid ( @{ $t{enq1ids}{$pid} } ) {
# $t{DWG} = $t{DWG} . 'pid==>' . $pid . ',enq1=>'. $eid . '/////' . '<br>';
($t{owner},$t{type1id1},$t{partsid1}) = $self->dbh->selectrow_array("select owner,type1id,partsid from enq1 where id = $eid");
($t{time1},$t{percent0},$t{percent1},$t{price1},$t{money}) = $self->dbh->selectrow_array("select
time,percent0,percent,price,money
from quo2 where id = $eid");
# 分解partsid,price并赋值(要考虑多台主机的情况)
@{ $t{type1s} } = split(/==/,$t{type1id1});
@{ $t{partss} } = split(/==/,$t{partsid1});
@{ $t{prices_all} } = split(/=/,$t{price1});
@{ $t{percent_all} } = split(/=/,$t{percent1});
$t{N1} = 0;
for $n1 ( 0 .. $#{ $t{type1s} } ) { # 处理相同type1的主机
if ( $t{type1s}[$n1] == $t{id} ) {
$t{partsid1} = $t{partss}[$n1];
@{ $t{partsids} } = split(/=/,$t{partsid1});
$t{N2} = 1 + $#{ $t{partsids} };
@{ $t{prices} } = splice(@{ $t{prices_all} },$t{N1},$t{N2});
@{ $t{percents} } = splice(@{ $t{percent_all} },$t{N1},$t{N2});;
# 检查已有报价的零件
for $n2 ( 0 .. $#{ $t{partsids} } ) {
next unless $t{partsids}[$n2] == $pid;
# $pid = $t{partsids}[$n2];
# quo2的percent=P1时,就把percent0的值代入各个零件的percent
if ($t{percent1} eq 'P1') {
$t{per1} = $t{percent0};
} else {
$t{per1} = $t{percents}[$n2];
}
$t{one1} = $t{prices}[$n2] . '=' . $t{per1} . '=' . $t{money} . '=' . $t{time1}
. '=' . $t{owner};
# . '=' . $t{makerid1} . '=' . $t{id1} ;
# $t{one1} = $t{p1} . '=' . $t{d1} . '=' . $t{money1} . '=' . $t{time1} . '=' . $t{ourref1}
# . '=' . $t{makerid1} . '=' . $t{id1} ;
push(@{ $t{price2s}{$pid} }, $t{one1});
}
}
}
}
}
# quo2的price是否是'P2',如果是P2,就认为价格还没有填入(price是最终价格,price0是厂家原价)
# 把所有的价格写入价格表
for $pid ( sort {$a<=>$b} keys %{ $t{price2s} } ) {
$t{one1} = join('==',@{ $t{price2s}{$pid} });
$t{sql} = 'UPDATE ' . $t{ptable} . ' set price2 ="';
$t{sql} .= $t{one1} . '" where id = ' . $pid;
$t{DO} = $self->dbh->do($t{sql});
}
# 显示price1和price2
@loop = ();
$t{sth} = $self->dbh->prepare("SELECT * FROM $t{ptable}");
$t{sth}->execute;
while ( @rec = $t{sth}->fetchrow_array ) {
my $row_ref = (); # この初期化はとても重要!
$t{NO} = 'select_' . $rec[0];
$$row_ref{NO} = $t{NO};
$$row_ref{name} = $rec[1];
$$row_ref{code} = $rec[2];
$$row_ref{dwg} = $t{DWGs}[$rec[3]-1];
$t{Nuid1} = $rec[4];
$$row_ref{Nuid} = $self->dbh->selectrow_array("select parts_Unit from parts_nu where id = $t{Nuid1}");
$$pref{p1} = $rec[6];
($row_ref,$self,$pref) = show_price1($row_ref,$self,$pref);
$$pref{p1} = $rec[7];
($row_ref,$self,$pref) = show_price2($row_ref,$self,$pref);
push(@loop, $row_ref);
}
$t{sth}->finish;
$t{template}->param(enq1_id => $t{enq1_id});
$t{template}->param(ptable => $t{ptable});
$t{template}->param(tname => $t{tname});
$t{template}->param(THIS_LOOP => \@loop);
$t{template}->param(tid => $t{id});
$t{template}->param(pro => 'mscnew_price.pl');
# $t{template}->param(select => $t{select});
$t{template}->param(NE1 => $t{NE1});
$t{template}->param(DWG => $t{DWG});
return $t{template}->output;
}
]]>
更新价格(Ver1)
<![CDATA[
sub mscnew_price {
my $self = shift;
my(%t,$n,$n1,$n2,@rec,@loop,$row_ref,$pref,$aref,$row);
# Get CGI query object
$t{q} = $self->query();
# データベースにアクセス
$t{tname} = $t{q}->param("tname");
$t{id} = $t{q}->param("id");
$t{NE1} = $t{q}->param("NE1");
$t{enq1_id} = $t{q}->param("enq1_id");
# 取出DWG图号
$t{DWG} = $self->dbh->selectrow_array("SELECT DWG FROM main_type1 WHERE id = $t{id}");
@{ $t{DWGs} } = split(/=/,$t{DWG});
# HTMLファイルに出力
$t{template} = $self->load_tmpl("mscparts10.htm") || die "error loading tmpl";
$t{ptable} = sprintf("%06d",$t{id});
$t{ptable} = 'a' . $t{ptable};
# 检索所有enq2并写入ptable的price1
# 如果type1id=B,没有type1数据
# 取得含有type1id的enq2的id/makerid/type1id/partsid/price/discount/discount0/money
$aref = $self->dbh->selectall_arrayref("SELECT id,type1id,partsid,price,discount,discount0
FROM enq2 WHERE type1id LIKE \'$t{id}\'
OR type1id LIKE \'\%\=\=$t{id}\'
OR type1id LIKE \'\%\=\=$t{id}\=\=\%\'
OR type1id LIKE \'$t{id}\=\=\%\'
ORDER BY time DESC");
$t{DWG} = '';
@{ $t{enq1list} } = ();
@{ $t{pppp} } = ();
for $row ( @$aref ) {
($t{id1},$t{type1id1},$t{partsid1},$t{price1},$t{discount1},$t{discount01}) = @$row;
# 按partsid有否处理,如果partsid=C,没有parts数据,对enq2来说,似乎不会出现type1id=B和partsid=C的情况
# 按price有否处理,如果price=P,没有价格。按0处理
next if $t{price1} eq 'P';
# 分解partsid,price并赋值(要考虑多台主机的情况)
@{ $t{type1s} } = split(/==/,$t{type1id1});
@{ $t{partss} } = split(/==/,$t{partsid1});
@{ $t{prices_all} } = split(/=/,$t{price1});
@{ $t{discount1_all} } = split(/=/,$t{discount1});
$t{N1} = 0;
for $n ( 0 .. $#{ $t{type1s} } ) { # 处理相同type1的主机
if ( $t{type1s}[$n] == $t{id} ) {
$t{partsid1} = $t{partss}[$n];
@{ $t{partsids} } = split(/=/,$t{partsid1});
$t{N2} = 1 + $#{ $t{partsids} };
@{ $t{prices} } = splice(@{ $t{prices_all} },$t{N1},$t{N2});
# $t{DWG} = $t{DWG} . $t{id1} . 'N1=>' . $t{N1} . 'N2=>' . $t{N2} . '/////' . '<br>';
# $t{DWG} = $t{DWG} . $t{id1} . 'prices_length' . $#{ $t{prices} } . 'prices_all_length=>' . $#{ $t{prices_all} } . '/////' . '<br>';
# 有的话:读取makerid/money
($t{time1},$t{enq1id1},$t{makerid1},$t{money1}) = $self->dbh->selectrow_array("SELECT time,enq1id,makerid,money FROM enq2 WHERE id = $t{id1}");
$t{ourref1} = $self->dbh->selectrow_array("SELECT ourref FROM enq1 WHERE id = $t{enq1id1}");
push(@{ $t{enq1list} },$t{enq1id1});
# 按discount处理,如果discount=D,没有个别discount,所有discount采用discount0的值
if ( $t{discount1} eq 'D' ) { # 所有的discount相同
for $n1 ( 0 .. $#{ $t{prices} } ) {
push(@{ $t{discounts} },$t{discount01});
}
} else {
@{ $t{discounts} } = splice(@{ $t{discount1_all} },$t{N1},$t{N2});
}
# 数据作成
for $n1 ( 0 .. $#{ $t{prices} } ) {
$t{pid1} = $t{partsids}[$n1];
$t{p1} = $t{prices}[$n1];
$t{d1} = $t{discounts}[$n1];
# 元件的一个价格生成
$t{one1} = $t{p1} . '=' . $t{d1} . '=' . $t{money1} . '=' . $t{time1} . '=' . $t{ourref1}
. '=' . $t{makerid1} . '=' . $t{id1} ;
push(@{ $t{price1s}{$t{pid1}} }, $t{one1});
push(@{ $t{pppp} } ,$t{pid1});
# $t{DWG} = $t{DWG} . '=' . $t{id1} . '=' . $n . '=p' . $t{one1} . '==';
}
# $t{DWG} = $t{DWG} . '/////' . '<br>';
# 不同主机的项目计算零件数量
} else {
@{ $t{partsids} } = split(/=/,$t{partss}[$n]);
$t{N1} = $t{N1} + $#{ $t{partsids} } + 1;
}
}
}
# 把所有的价格写入价格表
for $n ( 0 .. $#{ $t{pppp} } ) {
$t{pid1} = $t{pppp}[$n];
$t{one1} = join('==',@{ $t{price1s}{$t{pid1}} });
$t{sql} = 'UPDATE ' . $t{ptable} . ' set price1 ="';
$t{sql} .= $t{one1} . '" where id = ' . $t{pid1};
$t{DO} = $self->dbh->do($t{sql});
}
# 检索所有quo2并写入ptable的price2
# 只检查已报过价(有enq2和enq2的价格表)的enq1的id(和quo2相同)
for $n ( 0 .. $#{ $t{enq1list} } ) {
# $t{DWG} = $t{DWG} . 'n==>' . $n . 'enq1=>'. $t{enq1list}[$n] . '/////' . '<br>';
$t{enq1id1} = $t{enq1list}[$n];
($t{owner},$t{type1id1},$t{partsid1}) = $self->dbh->selectrow_array("select owner,type1id,partsid from enq1 where id = $t{enq1id1}");
($t{time1},$t{percent0},$t{percent1},$t{price1},$t{money}) = $self->dbh->selectrow_array("select
time,percent0,percent,price,money
from quo2 where id = $t{enq1id1}");
# 分解partsid,price并赋值(要考虑多台主机的情况)
@{ $t{type1s} } = split(/==/,$t{type1id1});
@{ $t{partss} } = split(/==/,$t{partsid1});
@{ $t{prices_all} } = split(/=/,$t{price1});
@{ $t{percent_all} } = split(/=/,$t{percent1});
$t{N1} = 0;
for $n1 ( 0 .. $#{ $t{type1s} } ) { # 处理相同type1的主机
if ( $t{type1s}[$n1] == $t{id} ) {
$t{partsid1} = $t{partss}[$n1];
@{ $t{partsids} } = split(/=/,$t{partsid1});
$t{N2} = 1 + $#{ $t{partsids} };
@{ $t{prices} } = splice(@{ $t{prices_all} },$t{N1},$t{N2});
@{ $t{percents} } = splice(@{ $t{percent_all} },$t{N1},$t{N2});;
# 检查已有报价的零件
for $n2 ( 0 .. $#{ $t{partsids} } ) {
$t{pid1} = $t{partsids}[$n2];
# quo2的percent=P1时,就把percent0的值代入各个零件的percent
if ($t{percent1} eq 'P1') {
$t{per1} = $t{percent0};
} else {
$t{per1} = $t{percents}[$n2];
}
$t{one1} = $t{prices}[$n2] . '=' . $t{per1} . '=' . $t{money} . '=' . $t{time1}
. '=' . $t{owner};
# . '=' . $t{makerid1} . '=' . $t{id1} ;
# $t{one1} = $t{p1} . '=' . $t{d1} . '=' . $t{money1} . '=' . $t{time1} . '=' . $t{ourref1}
# . '=' . $t{makerid1} . '=' . $t{id1} ;
push(@{ $t{price2s}{$t{pid1}} }, $t{one1});
}
}
}
}
# quo2的price是否是'P2',如果是P2,就认为价格还没有填入(price是最终价格,price0是厂家原价)
# 把所有的价格写入价格表
for $n ( 0 .. $#{ $t{pppp} } ) {
$t{pid1} = $t{pppp}[$n];
$t{one1} = join('==',@{ $t{price2s}{$t{pid1}} });
$t{sql} = 'UPDATE ' . $t{ptable} . ' set price2 ="';
$t{sql} .= $t{one1} . '" where id = ' . $t{pid1};
$t{DO} = $self->dbh->do($t{sql});
}
# 显示price1和price2
@loop = ();
$t{sth} = $self->dbh->prepare("SELECT * FROM $t{ptable}");
$t{sth}->execute;
while ( @rec = $t{sth}->fetchrow_array ) {
my $row_ref = (); # この初期化はとても重要!
$t{NO} = 'select_' . $rec[0];
$$row_ref{NO} = $t{NO};
$$row_ref{name} = $rec[1];
$$row_ref{code} = $rec[2];
$$row_ref{dwg} = $t{DWGs}[$rec[3]-1];
$t{Nuid1} = $rec[4];
$$row_ref{Nuid} = $self->dbh->selectrow_array("select parts_Unit from parts_nu where id = $t{Nuid1}");
$$pref{p1} = $rec[6];
($row_ref,$self,$pref) = show_price1($row_ref,$self,$pref);
$$pref{p1} = $rec[7];
($row_ref,$self,$pref) = show_price2($row_ref,$self,$pref);
push(@loop, $row_ref);
}
$t{sth}->finish;
$t{template}->param(enq1_id => $t{enq1_id});
$t{template}->param(ptable => $t{ptable});
$t{template}->param(tname => $t{tname});
$t{template}->param(THIS_LOOP => \@loop);
$t{template}->param(tid => $t{id});
$t{template}->param(pro => 'mscnew_price.pl');
# $t{template}->param(select => $t{select});
$t{template}->param(NE1 => $t{NE1});
$t{template}->param(DWG => $t{DWG});
return $t{template}->output;
}
]]>
旧程序保存
<![CDATA[
# 检索所有enq2并写入ptable的price1
$t{sth} = $self->dbh->prepare("SELECT * FROM enq2 ORDER BY time DESC");
$t{sth}->execute;
@{ $t{pppp} } = ();
while ( @rec = $t{sth}->fetchrow_array ) {
@{ $t{prices} } = split(/=/,$rec[13]);
next unless ( $t{prices}[0] ne 'P' );
@{ $t{type1ids} } = split(/==/,$rec[9]);
@{ $t{partsids} } = split(/==/,$rec[10]);
@{ $t{discounts} } = split(/=/,$rec[14]);
$t{enq1id} = $rec[2];
$t{makerid} = $rec[7];
$t{moneyid} = $rec[16];
$t{time1} = $rec[1];
# $t{time1} = $self->dbh->selectrow_array("SELECT time FROM enq1 WHERE id = $t{enq1id}");
$t{ourref1} = $self->dbh->selectrow_array("SELECT ourref FROM enq1 WHERE id = $t{enq1id}");
$t{money} = $self->dbh->selectrow_array("SELECT English FROM money WHERE id = $t{moneyid}");
$t{maker} = $self->dbh->selectrow_array("SELECT company FROM makers WHERE id = $t{makerid}");
$t{NO} = -1;
for $n ( 0 .. $#{ $t{type1ids} } ) {
@{ $t{pps1} } = split(/=/,$t{partsids}[$n]);
if ( $t{type1ids}[$n] == $t{id} ) {
for $n1 ( 0 .. $#{ $t{pps1} } ) {
$t{NO}++;
# 取的parts的ID
$t{pps2} = $t{pps1}[$n1];
# 取得价格/DISC/MONEY/TIME/maker
$t{price} = $t{prices}[$t{NO}];
$t{one1} = $t{price} . '=';
if ( $t{discounts}[0] eq 'D' ) {
$t{discount} = '0';
} else {
$t{discount} = $t{discounts}[$t{NO}];
}
$t{one1} .= $t{discount} . '=';
$t{one1} .= $t{moneyid} . '=';
$t{one1} .= $t{time1} . '=';
$t{one1} .= $t{ourref1} . '=';
$t{one1} .= $t{makerid} . '=';
$t{one1} .= $rec[0];
push(@{ $t{price1s}{$t{pps2}} },$t{one1});
push(@{ $t{pppp} },$t{pps2});
}
} else {
for $n1 ( 0 .. $#{ $t{pps1} } ) {
$t{NO}++;
}
}
}
}
$t{sth}->finish;
for $n ( 0 .. $#{ $t{pppp} } ) {
$t{pps2} = $t{pppp}[$n];
$t{price1} = join('==',@{ $t{price1s}{$t{pps2}} });
$t{sql1} = 'UPDATE ' . $t{ptable} . ' set price1 = "';
$t{sql1} .= $t{price1} . '" where id = ' . $t{pps2};
$t{DO1} = $self->dbh->do($t{sql1});
}
----------------------------------------------------------
# 检索所有quo2并写入ptable的price2
$t{sth} = $self->dbh->prepare("SELECT * FROM quo2 ORDER BY time DESC");
$t{sth}->execute;
@{ $t{pppp} } = ();
while ( @rec = $t{sth}->fetchrow_array ) {
@{ $t{prices} } = split(/=/,$rec[11]);
next unless ( $t{prices}[0] ne 'P2' );
($t{owner},$t{type1id},$t{partsid}) = $self->dbh->selectrow_array("SELECT owner,type1id,partsid FROM enq1 WHERE id = $rec[0]");
@{ $t{type1ids} } = split(/==/,$t{type1id});
@{ $t{partsids} } = split(/==/,$t{partsid});
@{ $t{prices} } = split(/=/,$rec[11]);
@{ $t{percents} } = split(/=/,$rec[9]);
$t{moneyid} = $rec[6];
$t{time1} = $rec[1];
$t{ourref1} = $self->dbh->selectrow_array("SELECT ourref FROM enq1 WHERE id = $rec[0]");
$t{money} = $self->dbh->selectrow_array("SELECT English FROM money WHERE id = $t{moneyid}");
# $t{owner} = $self->dbh->selectrow_array("SELECT company FROM owners WHERE id = $t{owner}");
$t{NO} = -1;
for $n ( 0 .. $#{ $t{type1ids} } ) {
@{ $t{pps1} } = split(/=/,$t{partsids}[$n]);
if ( $t{type1ids}[$n] == $t{id} ) {
for $n1 ( 0 .. $#{ $t{pps1} } ) {
$t{NO}++;
# 取的parts的ID
$t{pps2} = $t{pps1}[$n1];
# 取得价格/DISC/MONEY/TIME/maker
$t{price} = $t{prices}[$t{NO}];
if ( $t{percents}[0] eq 'P1' ) {
$t{percent} = '0';
} else {
$t{percent} = $t{percents}[$t{NO}];
}
$t{one1} = $t{price} . '=';
$t{one1} .= $t{percent} . '=';
$t{one1} .= $t{moneyid} . '=';
$t{one1} .= $t{time1} . '=';
$t{one1} .= $t{ourref1} . '=';
$t{one1} .= $t{owner} . '=';
$t{one1} .= $rec[0];
push(@{ $t{price2s}{$t{pps2}} },$t{one1});
push(@{ $t{pppp} },$t{pps2});
}
} else {
for $n1 ( 0 .. $#{ $t{pps1} } ) {
$t{NO}++;
}
}
}
}
$t{sth}->finish;
for $n ( 0 .. $#{ $t{pppp} } ) {
$t{pps2} = $t{pppp}[$n];
$t{price2} = join('==',@{ $t{price2s}{$t{pps2}} });
$t{sql1} = 'UPDATE ' . $t{ptable} . ' set price2 = "';
$t{sql1} .= $t{price2} . '" where id = ' . $t{pps2};
$t{DO1} = $self->dbh->do($t{sql1});
}
]]>
返回