有关series的程序
返回
<![CDATA[
恢复enq1的seriesid更新!(只要seriesid的顺序对,该程序没有问题!)
seriesid的DEFAULT是否要改为'1'
==========================================================
SERIES追加时不要同时写enq1!这样可避免写入出错!
# enq1的seriesid更新
# ($t{type1id},$t{sid}) = $self->dbh->selectrow_array("select type1id,seriesid from enq1 where id = $t{enq1_id}");
# @{ $t{ssids} } = ();
# @{ $t{type1ids} } = split(/==/,$t{type1id});
# $t{type1id_length} = $#{ $t{type1ids} } + 1;
# if ( $t{sid} eq 'C' ) {
# for $n ( 1 .. $t{type1id_length} ) {
# if ( $t{NO1} == $n ) {
# push(@{ $t{ssids} },$t{SERIES1});
# } else {
# push(@{ $t{ssids} },'1');
# }
# }
# } else {
# @{ $t{sids} } = split(/==/,$t{sid});
# for $n ( 0 .. $#{ $t{sids} } ) {
# $t{NO} = $n + 1;
# if ( $t{NO1} == $t{NO} ) {
# push(@{ $t{ssids} },$t{SERIES1});
# } else {
# push(@{ $t{ssids} },$t{sids}[$n]);
# }
# }
# }
# $t{seriesid} = join('==',@{ $t{ssids} });
# $t{sql} = 'UPDATE enq1 set seriesid = "';
# $t{sql} .= $t{seriesid} . '" where id = ' . $t{enq1_id};
# $t{DO} = $self->dbh->do($t{sql});
用ENQ可自由自在地改seriesid
mysql> select * from enq1 where id = 2707\G
*************************** 1. row ***************************
id: 2707
time: 2009-02-12
ourref: 9021201
owner: 53
ownerno: 090130-YTP9670
hullnoid: 408
type1id: 1337==785==803==1686
partsid: 12=9=17==8==5=23=24=22=4=3==6=11=16=17=14=15
QTY: 3=1=1==4==4=3=1=1=2=2==4=1=2=2=2=4
memo:
LANGUAGEid: 1
makerid: 1
enq2s: 3041
seriesid: 2==5==2==1
tmp_pname: NULL
tmp_pcode: NULL
tmp_pqty: NULL
1 row in set (0.00 sec)
mysql> select * from enq1 where id = 2707\G
*************************** 1. row ***************************
id: 2707
time: 2009-02-12
ourref: 9021201
owner: 53
ownerno: 090130-YTP9670
hullnoid: 408
type1id: 1337==785==803==1686
partsid: 12=9=17==8==5=23=24=22=4=3==6=11=16=17=14=15
QTY: 3=1=1==4==4=3=1=1=2=2==4=1=2=2=2=4
memo:
LANGUAGEid: 1
makerid: 1
enq2s: 3041
seriesid: 1==1==1==1
tmp_pname: NULL
tmp_pcode: NULL
tmp_pqty: NULL
1 row in set (0.00 sec)
mysql> UPDATE enq1 SET seriesid = '1==1==1==1' WHERE id = 2707;
Query OK, 1 row affected (0.09 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from enq1 where id = 2707\G
*************************** 1. row ***************************
id: 2707
time: 2009-02-12
ourref: 9021201
owner: 53
ownerno: 090130-YTP9670
hullnoid: 408
type1id: 1337==785==803==1686
partsid: 12=9=17==8==5=23=24=22=4=3==6=11=16=17=14=15
QTY: 3=1=1==4==4=3=1=1=2=2==4=1=2=2=2=4
memo:
LANGUAGEid: 1
makerid: 1
enq2s: 3041
seriesid: 2
tmp_pname: NULL
tmp_pcode: NULL
tmp_pqty: NULL
1 row in set (0.06 sec)
C:\database\perl>perl check_enq1_series.pl
2707,1337==785==803==1686,1==1==1==1,2
]]>
检查enq1的series的程序(check_enq1_series.pl)
enq1的seriesid不是C的时候,其个数必须跟type1id的个数对应
<![CDATA[
use strict;
use DBI;
my(%t,$n,@fld,$pref,@rec);
print "This is check_enq1_series.pl.\n";
# 连接数据库
$$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;
}
# 取出main_type1的数据
$t{sth} = $$pref{dbh}->prepare("SELECT id,type1id,seriesid FROM enq1");
$t{sth}->execute;
while ( @rec = $t{sth}->fetchrow_array ) {
if ( $rec[2] ne 'C' ) {
@{ $t{type1ids} } = split(/==/,$rec[1]);
@{ $t{seriesids} } = split(/==/,$rec[2]);
if ( $#{ $t{type1ids} } != $#{ $t{seriesids} } ) {
@{ $t{series1s} } = ();
for $n ( 0 .. $#{ $t{type1ids} } ) {
push(@{ $t{series1s} },'1');
}
$t{series1} = join('==',@{ $t{series1s} });
print "$rec[0],$rec[1],$t{series1},$rec[2]\n";
push(@{ $t{ids} },$rec[0]);
push(@{ $t{series_new} },$t{series1});
}
}
}
$t{sth}->finish;
# 更新seriesid
for $n (0 .. $#{ $t{ids} } ) {
$t{id1} = $t{ids}[$n];
$t{series1} = $t{series_new}[$n];
$t{sql} = 'UPDATE enq1 set seriesid = "';
$t{sql} .= $t{series1} . '" where id = ';
$t{sql} .= $t{id1};
$t{DO} = $$pref{dbh}->do($t{sql});
print "$t{sql},OK=>$t{DO}\n";
}
# 关闭数据库
$$pref{dbh}->disconnect;
-------------------------------------
运行结果
UPDATE enq1 set seriesid = "1==1==1==1" where id = 1967,OK=>1
UPDATE enq1 set seriesid = "1==1" where id = 2216,OK=>1
UPDATE enq1 set seriesid = "1==1==1==1==1==1==1==1==1==1" where id = 2301,OK=>1
UPDATE enq1 set seriesid = "1==1" where id = 2382,OK=>1
UPDATE enq1 set seriesid = "1==1" where id = 2420,OK=>1
UPDATE enq1 set seriesid = "1==1==1==1==1==1" where id = 2436,OK=>1
UPDATE enq1 set seriesid = "1==1==1==1==1==1==1==1==1" where id = 2483,OK=>1
]]>
检查main_type1的series的程序(check_series.pl)
<![CDATA[
use strict;
use DBI;
my(%t,$n,@fld,$pref,@rec);
print "This is check_series.pl.\n";
# 连接数据库
$$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;
}
# 取出main_type1的数据
$t{sth} = $$pref{dbh}->prepare("SELECT id,series FROM main_type1");
$t{sth}->execute;
while ( @rec = $t{sth}->fetchrow_array ) {
@{ $t{seriess} } = split(/=/,$rec[1]);
if ( $t{seriess}[0] ne 'XXXSERIES' ) {
push(@{ $t{ids} },$rec[0]);
push(@{ $t{series_old} },$rec[1]);
}
}
$t{sth}->finish;
# 更新series
for $n (0 .. $#{ $t{ids} } ) {
$t{id1} = $t{ids}[$n];
$t{series1} = $t{series_old}[$n];
$t{series1} = 'XXXSERIES=' . $t{series1};
$t{sql} = 'UPDATE main_type1 set series = "';
$t{sql} .= $t{series1} . '" where id = ';
$t{sql} .= $t{id1};
$t{DO} = $$pref{dbh}->do($t{sql});
print "$t{sql},OK=>$t{DO}\n";
}
# 关闭数据库
$$pref{dbh}->disconnect;
-------------------------------------
运行结果
UPDATE main_type1 set series = "XXXSERIES=1065344" where id = 1743,OK=>1
UPDATE main_type1 set series = "XXXSERIES=3016" where id = 1750,OK=>1
UPDATE main_type1 set series = "XXXSERIES=20062" where id = 1769,OK=>1
UPDATE main_type1 set series = "XXXSERIES=15170" where id = 1808,OK=>1
UPDATE main_type1 set series = "XXXSERIES=109853/4" where id = 1820,OK=>1
UPDATE main_type1 set series = "XXXSERIES=5400505" where id = 1821,OK=>1
UPDATE main_type1 set series = "XXXSERIES=5600500" where id = 1822,OK=>1
UPDATE main_type1 set series = "XXXSERIES=277836" where id = 1831,OK=>1
UPDATE main_type1 set series = "XXXSERIES=2535" where id = 1839,OK=>1
UPDATE main_type1 set series = "XXXSERIES=558/559" where id = 1844,OK=>1
UPDATE main_type1 set series = "XXXSERIES=2403" where id = 1845,OK=>1
UPDATE main_type1 set series = "XXXSERIES=970654" where id = 1847,OK=>1
UPDATE main_type1 set series = "XXXSERIES=9CP004H1A" where id = 1849,OK=>1
UPDATE main_type1 set series = "XXXSERIES=039" where id = 1851,OK=>1
]]>
<![CDATA[
mysql> update main_type1 set series = 'XXXSERIES=9809,SPEC:EA3H37ES6J' where id
= 1730;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from main_type1 where id = 1730\G
*************************** 1. row ***************************
id: 1730
name: MET 42SC
series: XXXSERIES=9809,SPEC:EA3H37ES6J
gname_id: 150
maker_id: 93
GR: NULL
DWG: XXXDWG=N-36-846-1011-1 FOR AKASAKA 7UEC37LA
memo:
1 row in set (0.00 sec)
]]>
返回