检查enq1的partsid程序;把改行符号'='改成'=='的有关程序,check_partsid.pl
返回
<![CDATA[
partsid有重复的并且有ownerno的列出具体内容
---------------------------
NG=1 enq1id= 490 outref=8071104 orderno=JTG-SP-016-08
===types==273
===parts==32=11=12=13=2=14=15=16=17=34=32=19=20
NG=1 enq1id= 995 outref=8081913 orderno=GBS-SP-037-08
===types==1083
===parts==1=2=3=4=5=6=7=8=9=10=11=12=13=13=15=16=17=18=19=20=21=22=23=24=25
NG=1 enq1id= 8035 outref=A031237 orderno= A030229
===types==1804
===parts==128=129=130=131=132=133=133=134=135=136
NG=1 enq1id= 9006 outref=A051812 orderno=9-JUN-2010
===types==275
===parts==214=215=216=217=218=219=220=221=222=223=224=225=226=227=228=229=230=23
1=232=233=234=235=236=237=238=239=240=241=242=243=244=245=246=247=248=249=250=25
1=232=252=253=254=255=256=257=258=259=260=261=262
NG=1 enq1id= 9382 outref=A060909 orderno= A051812
===types==275
===parts==214=215=216=217=218=219=220=221=222=223=224=225=226=227=228=229=230=23
1=232=233=234=235=236=237=238=239=240=241=242=243=244=245=246=247=248=249=250=25
1=232=252=253=254=255=256=257=258=259=260=261=262
NG=1 enq1id= 9642 outref=A062516 orderno=SA-2P-BH9067
===types==5141
===parts==1=2=3=4=5=6=7=8=9=10=8=9=11=12
NG=1 enq1id=10363 outref=A080916 orderno= 9AUG-10
===types==5226
===parts==5=6=6=8=9=10=12=13=14=15
NG=1 enq1id=10662 outref=A083113 orderno=SP-HAI-1031
===types==173
===parts==155=156=157=158=159=160=161=162=163=164=165=166=167=168=169=170=171=17
2=173=174=175=176=177=178=179=180=181=182=183=184=185=186=187=188=189=173=190=19
1=136
NG=1 enq1id=10953 outref=A091615 orderno=SP-HAR-1018
===types==2164
===parts==64=65=66=67=68=69=70=71=72=73=74=75=74=76=77=78=79=80=81=82
NG=1 enq1id=11126 outref=A093006 orderno= SSP-FM01
===types==3602
===parts==19=20=21=22=23=24=25=26=27=23=28=30=31=32=33=34=35=15=36=37=38=39=40=4
1=42=43=44=45=46=47=48=49=50=51=52=53=54=55=56=57=58=59=60=61=37=62=63=64=65=66=
67=68=69=70=71=72=73=74=75=76=77=78=79=80=81=82=83=31=84=85=86
NG=1 enq1id=11140 outref=A100107 orderno=SA-2P-BH80A8
===types==1206
===parts==2=5=2=5
NG=1 enq1id=11847 outref=A111518 orderno= A110108
===types==4231
===parts==26=27=28=29=30=31=32=33=34=29=35=36=37
NG=1 enq1id=12419 outref=A122115 orderno=SGESP-1032
===types==6150
===parts==17=18=19=20=21=22=23=23=24=25=26=27
NG=1 enq1id=12849 outref=B011834 orderno= S11014S
===types==1775
===parts==10=11=12=12=13
NG=3 enq1id=13561 outref=B031111 orderno=16MAR-11 TAN
===types==5291==4755==276
===parts==1=2=3=4=5=6=7=8=7=9=10=11=12=13=14=15=16==53=1=2=54=55=56=57=58=57=3=5
9=35==384=299=295=290=385=277=291=296=386=297=292=293=292=302=294=387=388=286=28
7=389=390=391
NG=1 enq1id=13584 outref=B031408 orderno= U11-0370
===types==2460
===parts==84=85=86=87=88=89=90=86=91=87=88=92=93=94=95=96=97=98=99=100=101=102=1
03=104=105=106=107
NG=1 enq1id=13690 outref=B032107 orderno=COMPRESSOR
===types==6150
===parts==43=44=45=46=47=48=49=50=51=52=53=54=55=54=56=57=58=59=59=60=61=62=63=6
4=65=66
查出如下项目的partsid有重复
---------------------------
NG=1 enq1id= 10 outref=8052303 orderno= NO
NG=1 enq1id= 138 outref=8060602 orderno= NO
NG=1 enq1id= 366 outref=8070105 orderno= NO
NG=1 enq1id= 437 outref=8070815 orderno= NO
NG=1 enq1id= 466 outref=8071012 orderno= NO
NG=1 enq1id= 490 outref=8071104 orderno=JTG-SP-016-08
NG=1 enq1id= 995 outref=8081913 orderno=GBS-SP-037-08
NG=1 enq1id= 1595 outref=8101701 orderno= NO
NG=1 enq1id= 7110 outref=A011123 orderno= NO
NG=1 enq1id= 7414 outref=A020113 orderno= NO
NG=1 enq1id= 7421 outref=A020120 orderno= NO
NG=3 enq1id= 7711 outref=A022406 orderno= NO
NG=1 enq1id= 7900 outref=A030604 orderno= NO
NG=1 enq1id= 8035 outref=A031237 orderno= A030229
NG=1 enq1id= 8192 outref=A032425 orderno= NO
NG=1 enq1id= 8325 outref=A040604 orderno= NO
NG=1 enq1id= 8500 outref=A041616 orderno= NO
NG=1 enq1id= 8571 outref=A042113 orderno= NO
NG=1 enq1id= 8700 outref=A043010 orderno= NO
NG=1 enq1id= 8847 outref=A051015 orderno= NO
NG=1 enq1id= 9006 outref=A051812 orderno=9-JUN-2010
NG=1 enq1id= 9036 outref=A052011 orderno= NO
NG=1 enq1id= 9050 outref=A052105 orderno= NO
NG=1 enq1id= 9084 outref=A052422 orderno= NO
NG=1 enq1id= 9120 outref=A052513 orderno= NO
NG=1 enq1id= 9132 outref=A052602 orderno= NO
NG=1 enq1id= 9382 outref=A060909 orderno= A051812
NG=1 enq1id= 9473 outref=A061437 orderno= NO
NG=1 enq1id= 9474 outref=A061438 orderno= NO
NG=1 enq1id= 9558 outref=A061802 orderno= NO
NG=1 enq1id= 9559 outref=A061803 orderno= NO
NG=1 enq1id= 9642 outref=A062516 orderno=SA-2P-BH9067
NG=1 enq1id= 9688 outref=A062907 orderno= NO
NG=1 enq1id= 9748 outref=A070203 orderno= NO
NG=1 enq1id= 9779 outref=A070519 orderno= NO
NG=1 enq1id= 9791 outref=A070604 orderno= NO
NG=1 enq1id=10030 outref=A072033 orderno= NO
NG=1 enq1id=10120 outref=A072318 orderno= NO
NG=1 enq1id=10163 outref=A072704 orderno= NO
NG=1 enq1id=10325 outref=A080530 orderno= NO
NG=1 enq1id=10360 outref=A080913 orderno= NO
NG=1 enq1id=10363 outref=A080916 orderno= 9AUG-10
NG=1 enq1id=10472 outref=A081809 orderno= NO
NG=1 enq1id=10493 outref=A081830 orderno= NO
NG=1 enq1id=10662 outref=A083113 orderno=SP-HAI-1031
NG=1 enq1id=10765 outref=A090614 orderno= NO
NG=1 enq1id=10832 outref=A091004 orderno= NO
NG=1 enq1id=10953 outref=A091615 orderno=SP-HAR-1018
NG=1 enq1id=11126 outref=A093006 orderno= SSP-FM01
NG=1 enq1id=11140 outref=A100107 orderno=SA-2P-BH80A8
NG=1 enq1id=11198 outref=A100606 orderno= NO
NG=1 enq1id=11529 outref=A102109 orderno= NO
NG=1 enq1id=11542 outref=A102122 orderno= NO
NG=1 enq1id=11590 outref=A102616 orderno= NO
NG=1 enq1id=11847 outref=A111518 orderno= A110108
NG=1 enq1id=11934 outref=A112213 orderno= NO
NG=1 enq1id=12419 outref=A122115 orderno=SGESP-1032
NG=1 enq1id=12475 outref=A122408 orderno= NO
NG=1 enq1id=12849 outref=B011834 orderno= S11014S
NG=1 enq1id=13217 outref=B021514 orderno= NO
NG=1 enq1id=13285 outref=B022124 orderno= NO
NG=1 enq1id=13383 outref=B022806 orderno= NO
NG=1 enq1id=13393 outref=B030102 orderno= NO
NG=3 enq1id=13561 outref=B031111 orderno=16MAR-11 TAN
NG=1 enq1id=13584 outref=B031408 orderno= U11-0370
NG=1 enq1id=13690 outref=B032107 orderno=COMPRESSOR
NG=1 enq1id=13791 outref=B032508 orderno= NO
NG=1 enq1id=13871 outref=B033110 orderno= NO
NG=1 enq1id=16553 outref=B090727 orderno= NO
查出两个长度不对的项目
---------------------------
enq1id=5880,ourref=9101709
enq1id=6023,ourref=9102623
mysql> select * from enq1 where id = 5880\G
*************************** 1. row ***************************
id: 5880
time: 2009-10-17
ourref: 9101709
owner: 70
ownerno: GPS-SP-AE-3E-09-10-09
hullnoid: 940
type1id: 551
partsid: 103=104=105=106=107=108=109=110=111=112=113=114=115=116=117=118=119=
120=121=122=123=124=126=127=132=133=134=135=136=137=138=139=140=141=142=143=144=
145=146=147=148=149=150=151=152=157=158=159=160=161=162=163=164=165=166=167=168=
169=170=171=172=173=174=175==155=156
QTY: 2=30=26=26=15=30=30=15=60=60=15=15=15=30=20=10=10=30=20=10=10=5=2=10
=2=4=5=30=30=30=30=15=40=40=50=50=40=20=10=15=10=15=14=3=15=15=15=30=3=15=4=13=1
5=30=33=1=1=12=10=15=2=2=3=6
memo:
LANGUAGEid: 1
makerid: 1
enq2s: 6698=6797
seriesid: 2
tmp_pname: NULL
tmp_pcode: NULL
tmp_pqty: NULL
1 row in set (0.17 sec)
用此语句删除多余的Parts
update enq1 set partsid = '103=104=105=106=107=108=109=110=111=112=113=114=115=116=117=118=119=120=121=122
=123=124=126=127=132=133=134=135=136=137=138=139=140=141=142=143=144=145=146=147
=148=149=150=151=152=157=158=159=160=161=162=163=164=165=166=167=168=169=170=171
=172=173=174=175' where id = 5880;
mysql> select * from enq1 where id = 5880\G
*************************** 1. row ***************************
id: 5880
time: 2009-10-17
ourref: 9101709
owner: 70
ownerno: GPS-SP-AE-3E-09-10-09
hullnoid: 940
type1id: 551
partsid: 103=104=105=106=107=108=109=110=111=112=113=114=115=116=117=118=119=
120=121=122=123=124=126=127=132=133=134=135=136=137=138=139=140=141=142=143=144=
145=146=147=148=149=150=151=152=157=158=159=160=161=162=163=164=165=166=167=168=
169=170=171=172=173=174=175
QTY: 2=30=26=26=15=30=30=15=60=60=15=15=15=30=20=10=10=30=20=10=10=5=2=10
=2=4=5=30=30=30=30=15=40=40=50=50=40=20=10=15=10=15=14=3=15=15=15=30=3=15=4=13=1
5=30=33=1=1=12=10=15=2=2=3=6
memo:
LANGUAGEid: 1
makerid: 1
enq2s: 6698=6797
seriesid: 2
tmp_pname: NULL
tmp_pcode: NULL
tmp_pqty: NULL
1 row in set (0.00 sec)
mysql> select * from enq1 where id = 6023\G
*************************** 1. row ***************************
id: 6023
time: 2009-10-26
ourref: 9102623
owner: 1
ownerno: SA-AEG090
hullnoid: 777
type1id: 2700
partsid:
QTY:
memo:
LANGUAGEid: 1
makerid: 1
enq2s: 6857
seriesid: C
tmp_pname: NULL
tmp_pcode: NULL
tmp_pqty: NULL
1 row in set (0.03 sec)
===>用主机删除可解决
]]>
<![CDATA[
# check_partsid2.pl
# 检查enq1的partsid
use strict;
my(%t,$n,$n1,@fld,$pref,@rec,%seen);
# 打开文件
open(IN,"../txt/enq1.txt") or die "Can't open the file enq1.txt\n";
$t{NO1} = 0;
while(<IN>){
if ( /^enq1/ && $t{NO1} == 0 ) {
$t{NO1} = 1;
@{ $t{names} } = split(/===/,$_);
$t{id_name} = $t{names}[1];
$t{ourref_name} = $t{names}[3];
$t{type1id_name} = $t{names}[7];
$t{partsid_name} = $t{names}[8];
print $_;
print "@{ $t{names} }\n";
print "id_name=$t{id_name}\n";
print "ourref_name=$t{ourref_name}\n";
print "type1id_name=$t{type1id_name}\n";
print "partsid_name=$t{partsid_name}\n";
} elsif ( /^enq1/ && $t{NO1} == 1 ) {
@{ $t{data} } = split(/===/,$_);
$t{id_data} = $t{data}[1];
$t{ourref_data} = $t{data}[3];
$t{type1id_data} = $t{data}[7];
$t{partsid_data} = $t{data}[8];
$$pref{enq1id} = $t{id_data};
$$pref{ourref} = $t{ourref_data};
$$pref{types1} = $t{type1id_data};
$$pref{parts1} = $t{partsid_data};
# 检查type1id和partsid的数量是否一样?
# ($pref) = check1($pref);
# 检查同一个type下的partid是否有重复
($pref) = check2($pref);
}
}
close(IN);
@{ $t{lists} } = @{ $$pref{enq1s} };
#print "list1=$t{list1}\n";
$t{list1} = shift(@{ $t{lists} });
$t{listNO} = 0;
open(IN,"../txt/order1.txt") or die "Can't open the file order1.txt\n";
$t{NO1} = 0;
while(<IN>){
if ( /^order1/ && $t{NO1} == 0 ) {
$t{NO1} = 1;
@{ $t{names} } = split(/===/,$_);
$t{id_name} = $t{names}[1];
$t{orderno_name} = $t{names}[3];
} elsif ( /^order1/ && $t{NO1} == 1 ) {
@{ $t{data} } = split(/===/,$_);
$t{id_data} = $t{data}[1];
$t{orderno_data} = $t{data}[3];
if ( $t{list1} == $t{id_data} ){
if ( !($t{orderno_data}) ) {
$t{orderno_data} = 'NO';
}
$t{NG} = $$pref{NG}[$t{listNO}];
$t{enq1id} = $$pref{enq1s}[$t{listNO}];
$t{ourref} = $$pref{ourrefs}[$t{listNO}];
$t{t1} = $$pref{t1}[$t{listNO}];
$t{p1} = $$pref{p1}[$t{listNO}];
if ( $t{orderno_data} ne 'NO' ) {
printf ("NG=%1s enq1id=%5s outref=%7s orderno=%10s\n",$t{NG},$t{enq1id},$t{ourref},$t{orderno_data});
print "===$t{t1}\n";
print "===$t{p1}\n";
}
$t{list1} = shift(@{ $t{lists} });
$t{listNO}++;
}
}
}
close(IN);
sub check2 {
my($pref) = @_;
my(%t,$n,%seen);
@{ $t{types} } = split(/==/,$$pref{types1});
@{ $t{parts} } = split(/==/,$$pref{parts1});
$t{NG} = 0;
$t{t1} = 'types';
$t{p1} = 'parts';
for $n ( 0 .. $#{ $t{parts} } ) {
$t{types1} = $t{types}[$n];
$t{parts1} = $t{parts}[$n];
@{ $t{parts1s} } = split(/=/,$t{parts1});
%seen = ();
@{ $t{uniq} } = grep { ! $seen{$_} ++ } @{ $t{parts1s} };
if ( $#{ $t{uniq} } != $#{ $t{parts1s} } ) {
$t{NG}++;
$t{t1} = $t{t1} . '==' . $t{types1};
$t{p1} = $t{p1} . '==' . $t{parts1};
}
}
if ( $t{NG} ) {
push( @{ $$pref{NG} } , $t{NG});
push( @{ $$pref{enq1s} },$$pref{enq1id});
push( @{ $$pref{ourrefs} },$$pref{ourref});
push( @{ $$pref{t1} },$t{t1});
push( @{ $$pref{p1} },$t{p1});
}
return($pref);
}
sub check1 {
my($pref) = @_;
my(%t,$n,$n1);
@{ $t{types} } = split(/==/,$$pref{types1});
@{ $t{parts} } = split(/==/,$$pref{parts1});
if ( $#{ $t{types} } != $#{ $t{parts} } ) {
print "enq1id=$$pref{enq1id},ourref=$$pref{ourref}\n";
print "types=@{ $t{types} }\n";
print "parts=@{ $t{parts} }\n";
# exit;
}
return($pref);
}
1;
]]>
<![CDATA[
# check_partsid.pl
# 检查enq1的partsid
use strict;
use DBI;
my(%t,$n,$n1,@fld,$pref,@rec,%seen);
# 连接数据库
$$pref{dsn} = "DBI:mysql:host=localhost;database=cookbook";
$$pref{dbh} = DBI->connect($$pref{dsn}, "cbuser", "cbpass") or die "Cannot connect to server\n";
$$pref{dbh}->do("SET NAMES utf8");
if(!$$pref{dbh}){
print "SQL read ERROR!\n";
exit;
}
# 取出id,partsid
$t{sth} = $$pref{dbh}->prepare("SELECT id,ourref,partsid FROM enq1");
$t{sth}->execute;
while ( @rec = $t{sth}->fetchrow_array ) {
$t{id} = $rec[0];
@{ $t{p1s} } = split(/==/,$rec[2]);
for $n ( 0 .. $#{ $t{p1s} } ) {
@{ $t{p2s} } = split(/=/,$t{p1s}[$n]);
%seen = ();
for $n1 ( 0 .. $#{ $t{p2s} } ) {
if ( $seen{$t{p2s}[$n1]} ) {
printf ("id=%5s, ref=%7s, partsid=>",$t{id},$rec[1]);
print $rec[2],"\n";
last;
} else {
$seen{$t{p2s}[$n1]} = 1;
}
}
}
}
$t{sth}->finish;
# 关闭数据库
$$pref{dbh}->disconnect;
非常危险,有这么多!
------------------------------------------------
id=11590, ref=A102616, partsid=>1=2=3=4=5=6=7==1=2=3=4=5=6=7==7=8=9=10=11=12=13=
13=14=15=16=17=18=19=20=21=22=23=24=25=22=26==43=44=45=46=47
id=11847, ref=A111518, partsid=>286=287=177=288=238=239=289=290=291=292=293=294=
295=296=297==1=2==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==2=3=4=5=6=7=8==26=27=28=29=30=31=
32=33=34=29=35=36=37==145=146=128=147=129=148=130=149=131=150=151=152=153==2==36
==72=73=74
id=11934, ref=A112213, partsid=>1=2=3=4=5=6=7=8=9=10=11=12=13=14=15=16=17=16=18=
19=20=21=22
id=12387, ref=B030803, partsid=>4=4=5=6
]]>
<![CDATA[
# pro_check.pl
# 检查·管理CGI的perl程序
use strict;
my(%t,@fld,$n);
open(IN,"pro_check.txt") or die "Can't open the file pro_check.txt\n";
while(<IN>){
chop;
if( /^file=/ ) {
@fld = split(/==>/);
push(@{ $t{files} },$fld[1]);
} elsif ( /^WORD/ ) {
@fld = split(/,/);
$t{WORD1} = $fld[1];
}
}
close(IN);
for $n ( 0 .. $#{ $t{files} } ) {
$t{file1} = $t{files}[$n];
open(IN,".\\cgi-bin\\pro\\$t{file1}") or die "Can't open the file $t{file1}.\n";
while(<IN>){
if ( $_ =~ /$t{WORD1}/ ) {
print "file=$t{file1}\n";
print "$.==>$_";
}
}
close(IN);
# exit;
}
]]>
<![CDATA[
filename=pro_check.txt
WORD,=\~ \/=\/
file=mscwrite_excel.pl
464==> if ( $t{name1} =~ /=/ ) {
1028==> if ( $t{name1} =~ /=/ ) {
1207==> if ( $t{memo} =~ /=/ ) {
1381==> if ( $t{name1} =~ /=/ ) {
------------------------------------------
WORD,=\~ s\/=\/\\\<br\\\>\/g
file=mscenq1.pl
1273==> $t{name1} =~ s/=/\<br\>/g; # 换行
file=mscenq1_parts2.pl
69==> $t{n1} =~ s/=/\<br\>/g; #多行显示
file=mscenq1_parts3.pl
127==> $t{name1} =~ s/=/\<br\>/g; # 多行显示
file=mscenq1_parts3.pl
263==> $t{n1} =~ s/=/\<br\>/g; #多行显示
file=mscorder1.pl
633==> $t{name1} =~ s/=/\<br\>/g; # 换行
file=mscqtn_input.pl
630==> $t{name1} =~ s/=/\<br\>/g; # 换行
file=mscquo2.pl
640==> $t{name1} =~ s/=/\<br\>/g; # 换行
file=mscquo2.pl
795==> $t{name1} =~ s/=/\<br\>/g; # 换行
]]>
<![CDATA[
# change_equ.pl
# 把所有零件表的name的'='置换成'=='
use strict;
use DBI;
my(%t,$n,@fld,@rec,$pref);
# 连接数据库
$$pref{dsn} = "DBI:mysql:host=localhost;database=cookbook";
$$pref{dbh} = DBI->connect($$pref{dsn}, "cbuser", "cbpass") or die "Cannot connect to server\n";
$$pref{dbh}->do("SET NAMES utf8");
if(!$$pref{dbh}){
print "SQL read ERROR!\n";
exit;
}
# 取出所有表格名
@{ $t{tables} } = $$pref{dbh}->tables;
$t{all_tables} = join(' ',@{ $t{tables} });
# 取出main_type1的编号,同时生成零件表名
@{ $t{ptables} } = ();
$t{sth} = $$pref{dbh}->prepare("SELECT id FROM main_type1");
$t{sth}->execute;
while ( @rec = $t{sth}->fetchrow_array ) {
$t{ptable1} = sprintf("%06d",$rec[0]);
$t{ptable1} = 'a' . $t{ptable1};
next unless $t{all_tables} =~ /$t{ptable1}/;
push(@{ $t{ptables} },$t{ptable1});
}
$t{sth}->finish;
# 把所有零件表的name的'='置换成'=='
for $n ( 0 .. $#{ $t{ptables} } ) {
$$pref{ptable1} = $t{ptables}[$n];
($pref) = change_ptable($pref);
}
# 关闭数据库
$$pref{dbh}->disconnect;
sub change_ptable {
my($pref) = @_;
my (%t,@rec,$aref,$row);
# 读零件表
$aref = $$pref{dbh}->selectall_arrayref("SELECT id,name from $$pref{ptable1} where name LIKE \'\%\=\%\'");
for $row ( @$aref ) {
($t{id1},$t{name1}) = @$row;
$t{name1} =~ s/=/==/g;
$t{sql} = 'UPDATE ' . $$pref{ptable1} . ' set name = "';
$t{sql} .= $t{name1} . '" where id = ';
$t{sql} .= $t{id1};
$t{DO} = $$pref{dbh}->do($t{sql});
print "ptable1=$$pref{ptable1},$t{id1}===>$t{DO}\n";
}
return($pref);
}
-----------------------------------------
enq1_ID 1671
Date 2008-10-25
OURREF 8102503
'PORTABLE TYPE WATER PROOF SPEKAER MODEL:NVS-400RB ,CAP:10W,L=15M LINE IMP : 150 OHM, JRC CODE NO: NVS-400B'
]]>
返回