MySQL操作程序三十一(零件修改程序,mscparts1.pl,追加/切換零件)
返回
- 修改mscenq1_parts22.htm,已經(jīng)出錯,已輸入的零件不用顯示。不用修改!
- 還是在輸入時把關(guān),name-code組成一個單詞,兩個以上相同的輸入出錯(長度和QTY不一樣!)
- 增加"輸入時不能在同一張單子上同時登記兩個以上name/code都相同的零件!"
mscenq1_parts2.pl,增加如果name/code都相同的話,不能同時輸入
mscenq1.pl,pat=input_parts,"取出DB的Parts的codes"操作修改的可能性?
- 追加一個零件的程序
- 切換零件的程序
- 同一主機,輸入和現(xiàn)有零件同樣的零件問題沒有解決!
<![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的內(nèi)容
- 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數(shù)據(jù)
# 取得含有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數(shù)據(jù),對enq2來說,似乎不會出現(xiàn)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} }; # 零件的數(shù)量
@{ $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});
}
# 數(shù)據(jù)作成
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>';
# 不同主機的項目計算零件數(shù)量
} 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});
}
# 不同主機的項目計算零件數(shù)量
} 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數(shù)據(jù)
# 取得含有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數(shù)據(jù),對enq2來說,似乎不會出現(xiàn)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} }; # 零件的數(shù)量
@{ $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});
}
# 數(shù)據(jù)作成
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>';
# 不同主機的項目計算零件數(shù)量
} 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});
}
# 不同主機的項目計算零件數(shù)量
} 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);
}
]]>
返回
厦门市|
遂溪县|
新兴县|
微博|
河北区|
辰溪县|
平远县|
岳阳县|
岳普湖县|
陇南市|
柳林县|
双鸭山市|
邓州市|
兴宁市|
济宁市|
海南省|
迁西县|
嘉兴市|
闽侯县|
湘乡市|
浏阳市|
柘城县|
固阳县|
嘉黎县|
达尔|
哈巴河县|
绥江县|
化隆|
克什克腾旗|
海林市|
娱乐|
内江市|
德安县|
伊吾县|
宜宾县|
南城县|
华阴市|
闵行区|
开远市|
太谷县|
闽清县|