MySQL操作程序三十一(零件修改程序,mscparts1.pl,追加/切换零件)
返回
- 修改mscenq1_parts22.htm,已经出错,已输入的零件不用显示。不用修改!
- 还是在输入时把关,name-code组成一个单词,两个以上相同的输入出错(长度和QTY不一样!)
- 增加"输入时不能在同一张单子上同时登记两个以上name/code都相同的零件!"
mscenq1_parts2.pl,增加如果name/code都相同的话,不能同时输入
mscenq1.pl,pat=input_parts,"取出DB的Parts的codes"操作修改的可能性?
- 追加一个零件的程序
- 切换零件的程序
- 同一主机,输入和现有零件同样的零件问题没有解决!
<![CDATA[
# 增加"如果name/code都相同的话,不能输入的功能"
%seen = ();
$t{name_code_length} = -1;
for $n ( 0 .. $#{ $t{codes} } ) {
$t{name_code} = $t{names}[$n] . '-' . $t{codes}[$n];
unless ( $seen{$t{name_code}} ) {
$t{name_code_length}++;
$seen{$t{name_code}} = 1;#==>不能漏此行!
}
}
# 增加"如果name/code都相同的话,不能输入的功能"
%seen = ();
$t{codes_length} = -1;
for $n ( 0 .. $#{ $t{codes} } ) {
$t{name_code} = $t{names}[$n] . ' ' . $t{codes}[$n];
unless ( $seen{$t{name_code}} ) {
$t{codes_length}++;
}
}
# $t{codes_length} = $#{ $t{codes} };
# $t{names_length} = $#{ $t{names} };
$t{qtys_length} = $#{ $t{qtys} };
$t{length1} = $t{names_length};
if ( $t{codes_length} == $t{qtys_length} ) {
$t{mode} = 1;
} else { # 出错,返回修改
$t{mode} = 2;
}
]]>
<![CDATA[
问题没有解决!
mysql> select * from enq1 where id = 12393\G
*************************** 1. row ***************************
id: 12393
time: 2011-04-04
ourref: B040406
owner: 1
ownerno: NULL
hullnoid: 1
type1id: 7
partsid: 9=10=10
QTY: 20=30=15
memo: NULL
LANGUAGEid: 1
makerid: 1
enq2s: 13943
seriesid: C
tmp_pname:
tmp_pcode:
tmp_pqty:
1 row in set (0.00 sec)
unless ( $seen{$t{line1}} ) {
push @b, $t{line1};
}
my %seen = (); =>追加
for $n ( 0 .. $t{length1} ) {
$t{id} = $t{enq1_ids}{$b[$n]}; # 这个地方可能有问题。
$t{q1} = $t{qtys}[$n];
unless ( $seen{$t{id}} ) { =>追加
push(@{ $t{pids} },$t{id});
push(@{ $t{qs} },$t{q1});
}
}
第一步:输入相同的name/code时,enq1的零件表不追加
mysql> select * from enq1 where id = 12393\G
*************************** 1. row ***************************
id: 12393
time: 2011-04-04
ourref: B040406
owner: 1
ownerno: NULL
hullnoid: 1
type1id: 7
partsid: 9=9
QTY: 22=22
memo: NULL
LANGUAGEid: 1
makerid: 1
enq2s: 13943
seriesid: C
tmp_pname:
tmp_pcode:
tmp_pqty:
1 row in set (0.02 sec)
----------------------------------------------------------------------------
下面的partsid是4=4,也就是一个相同的parts放在了不用的地方,解决办法是
1.追加一个零件
2.把其中一个零件切换到该新加的零件上
mysql> select * from enq1 where id = 12387\G
*************************** 1. row ***************************
id: 12387
time: 2011-03-08
ourref: B030803
owner: 1
ownerno: NULL
hullnoid: 1
type1id: 17
partsid: 4=4
QTY: 10=20
memo: NULL
LANGUAGEid: 1
makerid: 1
enq2s: 13936
seriesid: C
tmp_pname:
tmp_pcode:
tmp_pqty:
1 row in set (0.00 sec)
]]>
- 每个零件使用的REF列表,把零件选择的mscnew_price.pl搬过来用,sub get_price附在mscshowparts.pl下面
- 检查每个零件的price1,price2的内容
- enq1.htm进去时,把该张ENQ用的Parts列在上面,其他所有的列在下面。只要上面即可!
- Name,Code,DWG的排序(暂时取消)
- mscshowparts.pl,mscshowpartsone.pl,mscpartsupdate.pl
取消DB
取消memo
原有操作PATTERN追加
排序操作PATTERN追加,id=1的memo用于排序LIST
- 取消ENQ1的零件选择?取消更新价格部分。
<![CDATA[
mscparts1.htm
mscparts2.htm
mscparts2.htm
取消
<TMPL_IF NAME="DB1">
<form action="" method="post">
<input type="submit" value="Equipment Types管理へ戻る"><p>
<input type="hidden" name="table" value="main_type1">
<input type="hidden" name="DB" value="<TMPL_VAR NAME="DB">">
<input type="hidden" name="mode" value="NO">
<input type="hidden" name="rm" value="modeshowtype1">
</form>
</TMPL_IF>
取消
<form action="" method="post">
<input type="submit" value="排序"><p>
<input type="hidden" name="mode" value="order1">
<input type="hidden" name="enq1_id" value="<TMPL_VAR NAME="enq1_id">">
<input type="hidden" name="rm" value="modeshowparts">
</form>
mscshowparts.pl
取消
if ( $t{DB} == 1 ) {
$template->param(DB1 => 1);
$template->param(ENQ1 => 0);
} else {
$t{enq1_id} = $t{q}->param("enq1_id");
$template->param(DB1 => 0);
$template->param(ENQ1 => 1);
$template->param(enq1_id => $t{enq1_id});
}
]]>
<![CDATA[
新
sub get_price {
my($self,$pref) = @_;
my(%t,$aref,$row,$pid,$eid);
# 检索所有enq2并写入ptable的price1
# 如果type1id=B,没有type1数据
# 取得含有type1id的enq2的id/makerid/type1id/partsid/price/discount/discount0/money
$t{id} = $$pref{id};
$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");
$$pref{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;
# $$pref{DWG} = $$pref{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});
# $$pref{DWG} = $$pref{DWG} . ',N1=>' . $t{N1} . ',N2=>' . $t{N2};
# $$pref{DWG} = $$pref{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}");
}
# $$pref{DWG} = $$pref{DWG} . $t{ourref1};
# 按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});
}
# $$pref{DWG} = $$pref{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 ' . $$pref{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} } ) {
# $$pref{DWG} = $$pref{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 ' . $$pref{ptable} . ' set price2 ="';
$t{sql} .= $t{one1} . '" where id = ' . $pid;
$t{DO} = $self->dbh->do($t{sql});
}
return($self,$pref);
}
]]>
<![CDATA[
旧
sub get_price {
my($self) = @_;
my(%t,$aref,$row,$pid);
# 检索所有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});
}
return($self);
}
]]>
返回