MySQL操作程序二十八(mscenq1.pl,input_parts)
返回
<![CDATA[
[error] [client 127.0.0.1] Use of uninitialized value in numeric eq (==) at ./pro/mscenq1.pl line 726
# 生成新增加零件表
for $n ( 0 .. $#b ) {
726: if ( $t{enq1_ids}{$b[$n]} == 0 ) {
$t{idmax}++;
$t{enq1_ids}{$b[$n]} = $t{idmax};
push @diff, $b[$n];
}
}
分析:
1. $t{enq1_ids}{$b[$n]} 不存在?
2. unless ( $seen{$t{line1}} ) {
push @b, $t{line1};
}
mysql> select id,name,code,dwg_id,Nuid from a000719;
+----+--------+-------+--------+------+
| id | name | code | dwg_id | Nuid |
+----+--------+-------+--------+------+
| 1 | STATOR | 102 | 2 | 1 |
| 2 | STATOR | 102 | 2 | 1 |
| 3 | name3 | code3 | 1 | 1 |
+----+--------+-------+--------+------+
3 rows in set (0.00 sec)
# 把name,code,dwg_id组成一个单元
$t{line1} = $rec[1] . '____' . $rec[2] . '____' . $rec[3];
for $n ( 0 .. $#b ) {
if ( $b[$n] eq $t{line1} ) {
$t{enq1_ids}{$t{line1}} = $rec[0];
last;
}
}
$t{idmax} = $rec[0];
修改:
# if ( $t{enq1_ids}{$b[$n]} == 0 ) {
if ( !($t{enq1_ids}{$b[$n]}) ) {
]]>
用户数据和DB数据,code相同,name不同时的处理
- 输入完全相同的零件时DB不重复输入
2个连续相同时enq1不显示?=>解决
- 消除[enq1写入的错误结果]
- DB写入的sleep是否需要?==>不需要!删除
- enq1写入操作修改
零件表操作的key是line,不是code
DB数据库有的话,就用DB的旧id
DB数据库没有的话,就用新写入的id
和现有数据的合并操作时的注意点:取出partsid的最后一个字母
1:主机相同时的追加,最后一个字母不是'C',加'='后连接
2:主机不同时的追加,最后一个字母是'C',把C置换成'',然后连接
- DB写入判别修改完成
输入数据组成(设为m)name0,code0,dwg_id0,组成m个(name0,code0,dwg_id0)组
取出DB所有的(设为n)name1,code1,dwg_id1,组成n个(name1,code1,dwg_id1)组,和输入的逐个比较
(name,code,dwg_id)完全相同:DB不增加
其他的情况,DB增加
输入完全相同的零件时DB不重复输入
<![CDATA[
@diff = ();
if ( $t{count1} != 0 ) { # 只有在不是空表格时才进行操作
@{ $t{lines} } = ();
$t{sth} = $self->dbh->prepare("SELECT id,name,code,dwg_id,Nuid FROM $t{ptable}");
$t{sth}->execute;
while ( @rec = $t{sth}->fetchrow_array ) {
# 把name,code,dwg_id组成一个单元
$t{line1} = $rec[1] . '____' . $rec[2] . '____' . $rec[3];
for $n ( 0 .. $#b ) {
if ( $b[$n] eq $t{line1} ) {
$t{enq1_ids}{$t{line1}} = $rec[0];
last;
}
}
$t{idmax} = $rec[0];
}
$t{sth}->finish;
# 生成新增加零件表
for $n ( 0 .. $#b ) {
if ( $t{enq1_ids}{$b[$n]} == 0 ) {
$t{idmax}++;
$t{enq1_ids}{$b[$n]} = $t{idmax};
push @diff, $b[$n];
}
}
} else { # 空表格的情况
$t{idmax} = 0;
for $n ( 0 .. $#b ) {
if ( $t{enq1_ids}{$b[$n]} == 0 ) {
$t{idmax}++;
$t{enq1_ids}{$b[$n]} = $t{idmax};
push @diff, $b[$n];
}
}
}
]]>
修改后enq1写入没有问题,继续考虑各种不正规操作
<![CDATA[
if ( !($t{enq1_ids}{$t{line1}}) ) { # 不存在时赋值
$t{enq1_ids}{$t{line1}} = $t{idmax}+1;
}
]]>
enq1写入的错误结果
<![CDATA[
错误的enq1
*************************** 1. row ***************************
id: 4689
time: 2010-01-05
ourref: A010501
owner: 1
ownerno: NULL
hullnoid: 1
type1id: 4003
partsid: 120=120=120=120=120=120=120=120=120=120=120=120=120=120=120=120=120=
120=120=120=120=120=120=120=120=120=120=120=120=120=120=120=120=120=120=120=120=
120=120=120=120=120=120=120=120=120=120=120=120=120=120=120=120=120=120=120=120=
120=120=120=120=120=120=120=120=120=120=120=120=120=120=120=120=120=120=120=120=
120=120=120=120=120=120=120=120=120=120=120=120=120=120=120=120=120=120=120=120=
120=120=120=120=120=120=120=120=120=120=120=120=120=120=120=120=120=120=120=120=
120=120
QTY: 2=3=4=5=6=7=8=9=10=11=12=13=14=15=16=17=18=19=20=21=22=23=24=25=26=2
7=28=29=30=31=32=33=34=35=36=37=38=39=40=41=42=43=44=45=46=47=48=49=50=51=52=53=
54=55=56=57=58=59=60=61=62=63=64=65=66=67=68=69=70=71=72=73=74=75=76=77=78=79=80
=81=82=83=84=85=86=87=88=89=90=91=92=93=94=95=96=97=98=99=100=101=102=103=104=10
5=106=107=108=109=110=111=112=113=114=115=116=117=118=119=120
memo: NULL
LANGUAGEid: 1
makerid: 1
enq2s: 5305
seriesid: C
1 row in set (0.00 sec)
-------------------------------------------------------------------------
mysql> select * from enq1 where ourref = 'a010826'\G
*************************** 1. row ***************************
id: 7080
time: 2010-01-08
ourref: A010826
owner: 107
ownerno: 8-Jan-2010
hullnoid: 273
type1id: 1729==54==4004==1050==453==660==574==4005
partsid: 64=65=66=67=68=69=70=71=72=73=74=75=76=77=78=79=80=81=82=83=84=85=86
=87=88=89=90=92=121=122=123=124=125=126=127=128=129=130=131=132=133=134==134=135
=136=137=138=139=140==1=2=3==67=68=69=70=71=72=73=74=75=76=77=78=79=80=81=82==26
=27=28=29==34=34=36==135=136=137=138=139==4=4=4=1=2=3=1=2=3=1=2=3=1=2=3=1=2=3=1=
2=3=1=2=3=1=2=3=1=2=3
QTY: 3=30=3=3=3=3=4=5=5=2=8=30=30=20=4=2=20=20=20=2=12=20=20=12=3=3=20=5=
4=4=3=3=2=2=6=3=8=24=6=3=6=6==10=10=4=4=1=6=6==2=4=4==5=20=30=20=20=20=30=10=20=
2=12=6=10=2=4=2==1=2=2=4==1=1=5==1=1=2=2=2==3=3=4=3=3=4=3=3=4=3=3=4=3=3=4=3=3=4=
3=3=4=3=3=4=3=3=4=3=3=4
memo: NULL
LANGUAGEid: 1
makerid: 1
enq2s: 8025
seriesid: C
1 row in set (0.03 sec)
]]>
DB写入程序(不能忘了sleep)
<![CDATA[
# 把新增加的零件插入DB中
if ( $#diff >= 0 ) {
$t{OKs1} = $#diff + 1;
$t{OKs2} = 0;
for $n ( 0 .. $#diff ) {
$t{line1} = $diff[$n];
$t{n1} = $t{enq1_names}{$t{line1}};
$t{c1} = $t{enq1_codes}{$t{line1}};
$t{u1} = $t{enq1_units}{$t{line1}};
$t{sql} = "INSERT INTO $t{ptable} (name,code,dwg_id,Nuid,weight,price1,price2) ";
$t{sql} .= 'VALUES("' . $t{n1} . '","';
$t{sql} .= $t{c1} . '","';
$t{sql} .= $t{DWG0_id} . '","';
$t{sql} .= $t{u1} . '","1","0=100=1=0000-00-00=0000000=1=1","0=115=1=0000-00-00=1")';
$t{DO} = $self->dbh->do($t{sql});
$t{enq1_ids}{$t{line1}} = $t{idmax}+1;
sleep(1); # Need Time
if ( $t{DO} == 1 ) {
$t{OKs2}++;
}
}
}
]]>
enq1写入操作现有程序(大为简化)
<![CDATA[
# 把enq1的QTY等输入到对应的位置上(注意多主机的处理)
@{ $t{pids} } = ();
@{ $t{qs} } = ();
for $n ( 0 .. $t{length1} ) {
$t{id} = $t{enq1_ids}{$b[$n]};
$t{q1} = $t{qtys}[$n];
push(@{ $t{pids} },$t{id});
push(@{ $t{qs} },$t{q1});
}
$t{partsid1} = join("=",@{ $t{pids} });
$t{QTY1} = join("=",@{ $t{qs} });
# 取出现有的partsid/QTY
($t{partsid},$t{QTY}) = $self->dbh->selectrow_array("SELECT partsid,QTY FROM enq1 WHERE id = $t{enq1_id}");
$t{plast1} = substr($t{partsid},-1);
if ( $t{plast1} eq 'C' ) { # 主机不同
substr($t{partsid},-1) = '';
substr($t{QTY},-1) = '';
$t{partsid1} = $t{partsid} . $t{partsid1};
$t{QTY1} = $t{QTY} . $t{QTY1};
} else { # 主机相同
$t{partsid1} = $t{partsid} . '=' . $t{partsid1};
$t{QTY1} = $t{QTY} . '=' . $t{QTY1};
}
# 写入操作
$t{sql} = 'UPDATE enq1 SET partsid = "';
$t{sql} .= $t{partsid1} . '" WHERE id = ' . $t{enq1_id};
$t{DO} = $self->dbh->do($t{sql});
$t{sql} = 'UPDATE enq1 SET QTY = "';
$t{sql} .= $t{QTY1} . '" WHERE id = ' . $t{enq1_id};
$t{DO} = $self->dbh->do($t{sql});
]]>
enq1写入操作原有程序
<![CDATA[
# 把enq1的QTY等输入到对应的位置上(注意多主机的处理)
# 从零件表中抽出id放入enq1中
$t{cs} = '';
for $n ( 0 .. $t{length1} ) {
$t{c1} = $t{codes}[$n];
$t{cs} .= '_' . $t{c1};
}
$t{sth} = $self->dbh->prepare("SELECT id,code,dwg_id FROM $t{ptable}");
$t{sth}->execute;
while ( @rec = $t{sth}->fetchrow_array ) {
# 注意!除了code以外,DWG图纸号也要一致!
# if ( $t{cs} =~ /$rec[1]/ && $rec[2] == $t{DWG0_id} ) {
if ( $rec[2] == $t{DWG0_id} ) {
for $n ( 0 .. $t{length1} ) {
$t{c1} = $t{codes}[$n];
$t{q1} = $t{qtys}[$n];
if ( $t{c1} eq $rec[1] && !($t{oldlist}{$rec[1]}) ) {
$t{oldlist}{$rec[1]} = $rec[0];
$t{db_psid}{$rec[1]} = $rec[0];
}
}
}
}
$t{sth}->finish;
# 08/05/30: $t{pids}的顺序时取DB的ID时的顺序,必须恢复原来的顺序!
@{ $t{pids} } = ();
@{ $t{qs} } = ();
for $n ( 0 .. $t{length1} ) {
$t{c1} = $t{codes}[$n];
$t{id} = $t{db_psid}{$t{c1}},
$t{q1} = $t{qtys}[$n];
push(@{ $t{pids} },$t{id});
push(@{ $t{qs} },$t{q1});
}
$t{partsid1} = join("=",@{ $t{pids} });
$t{QTY1} = join("=",@{ $t{qs} });
# 取出现有的partsid/QTY
($t{partsid},$t{QTY}) = $self->dbh->selectrow_array("SELECT partsid,QTY FROM enq1 WHERE id = $t{enq1_id}");
@{ $t{partsids} } = split(/==/,$t{partsid});
@{ $t{partsidnews} } = ();
@{ $t{QTYs} } = split(/==/,$t{QTY});
@{ $t{QTYnews} } = ();
for $n ( 0 .. $#{ $t{partsids} } ) {
$t{NO} = $n + 1;
if ( $t{NO} == $t{NE1} ) { # 相同主机的情况
# 注意把老的也留下,C代表还没有输入一个零件
if ( $t{partsids}[$n] ne 'C' ) {
$t{partsid1} = $t{partsids}[$n] . '=' . $t{partsid1};
$t{QTY1} = $t{QTYs}[$n] . '=' . $t{QTY1};
# 相同项合并
@{ $t{ps} } = split(/=/,$t{partsid1});
@{ $t{qs} } = split(/=/,$t{QTY1});
%seen = ();
@{ $t{pss} } = ();
@{ $t{qss} } = ();
foreach $n1 ( 0 .. $#{ $t{ps} }) {
$t{ps1} = $t{ps}[$n1];
$t{qs1} = $t{qs}[$n1];
unless ( $seen{$t{ps1}} ) {
$seen{$t{ps1}} = 1;
push(@{ $t{pss} },$t{ps1});
push(@{ $t{qss} },$t{qs1});
}
}
$t{partsid1} = join("=",@{ $t{pss} });
$t{QTY1} = join("=",@{ $t{qss} });
}
push(@{ $t{partsidnews} }, $t{partsid1});
push(@{ $t{QTYnews} }, $t{QTY1});
} else { # 不同主机的情况
push(@{ $t{partsidnews} }, $t{partsids}[$n]);
push(@{ $t{QTYnews} }, $t{QTYs}[$n]);
}
}
$t{partsid1} = join("==",@{ $t{partsidnews} });
$t{sql} = 'UPDATE enq1 SET partsid = "';
$t{sql} .= $t{partsid1} . '" WHERE id = ' . $t{enq1_id};
$t{DO} = $self->dbh->do($t{sql});
$t{QTY1} = join("==",@{ $t{QTYnews} });
$t{sql} = 'UPDATE enq1 SET QTY = "';
$t{sql} .= $t{QTY1} . '" WHERE id = ' . $t{enq1_id};
$t{DO} = $self->dbh->do($t{sql});
]]>
DB写入判别现有程序
<![CDATA[
if ( $t{count1} != 0 ) { # 只有在不是空表格时才进行操作
@{ $t{lines} } = ();
$t{sth} = $self->dbh->prepare("SELECT id,name,code,dwg_id,Nuid FROM $t{ptable}");
$t{sth}->execute;
while ( @rec = $t{sth}->fetchrow_array ) {
# 把name,code,dwg_id组成一个单元
$t{line1} = $rec[1] . '____' . $rec[2] . '____' . $rec[3];
push @{ $t{lines} }, $t{line1};
$t{dbids}{$t{line1}} = $rec[0];
$t{dbnames}{$t{line1}} = $rec[1];
$t{dbcodes}{$t{line1}} = $rec[2];
$t{dbunits}{$t{line1}} = $rec[4];
$t{idmax} = $rec[0];
}
$t{sth}->finish;
# 同步作业
@a = @{ $t{lines} };
foreach $e (@a,@b) { $count{$e}++ };
@union = sort keys %count;
foreach $e ( keys %count ) {
# if ($count{$e} == 2 ) {
if ($count{$e} >= 2 ) {
$count2{$e}++;
}
}
for $n ( 0 .. $#b ) {
next if $count2{$b[$n]}; # 如果重复的话就放弃
$t{idmax}++;
push @diff, $b[$n];
$t{enq1_ids}{$b[$n]} = $t{idmax};
}
# @diff = sort {$a<=>$b} @diff;
# @diff = sort @diff;
} else { # 空表格的情况
]]>
DB写入判别原有程序
<![CDATA[
if ( $t{count1} != 0 ) { # 只有在不是空表格时才进行操作
@{ $t{dbcodes} } = ();
$t{sth} = $self->dbh->prepare("SELECT id,name,code,dwg_id,Nuid FROM $t{ptable}");
$t{sth}->execute;
while ( @rec = $t{sth}->fetchrow_array ) {
# 要考虑DWG不同,但是code相同的情况
$t{dbcode1} = $rec[2] . '===' . $rec[3]; # 这个操作合并code和DWG
push @{ $t{dbcodes} }, $t{dbcode1};
$t{dbids}{$t{dbcode1}} = $rec[0];
$t{dbnames}{$t{dbcode1}} = $rec[1];
$t{dbunits}{$t{dbcode1}} = $rec[4];
$t{idmax} = $rec[0];
}
$t{sth}->finish;
# 同步作业
@a = @{ $t{dbcodes} };
foreach $e (@a,@b) { $count{$e}++ };
@union = sort keys %count;
foreach $e ( keys %count ) {
# if ($count{$e} == 2 ) {
if ($count{$e} >= 2 ) {
$count2{$e}++;
}
}
for $n ( 0 .. $#b ) {
next if $count2{$b[$n]}; # 如果重复的话就放弃
$t{idmax}++;
push @diff, $b[$n];
$t{enq1_ids}{$b[$n]} = $t{idmax};
}
# @diff = sort {$a<=>$b} @diff;
# @diff = sort @diff;
} else { # 空表格的情况
]]>
返回