读零件数据处理程序
返回
<![CDATA[
# $t{sql} .= $t{u1} . '","1","0=100=1=0000-00-00=0000000=1=1","0=115=1=0000-00-00=1")'; ##BUG!!
$t{sql} .= $t{u1} . '","1","0=100=1=0000-00-00=1","0=115=1=0000-00-00=1=1")';
mysql> update a000270 set price1 = '0=100=1=0000-00-00=1' where id = 140;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update a000270 set price1 = '0=100=1=0000-00-00=1=1' where id = 139;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1 Changed: 1 Warnings: 0
]]>
<![CDATA[
# 输入零件程序(mscenq1.pl中)
# 待完善的项目
# 如何输入GROUP名(和零件一起)?
# 显示输入数据中的重复code
# 显示与DB中已有数据的重复code
#---------输入parts
} elsif ( $t{pat} eq 'parts' ) {
$t{NE1} = $t{q}->param("NE1");
$t{main_type1id} = $t{q}->param("main_type1id");
$t{name1} = $t{q}->param("name1");
$t{partsname} = $t{q}->param("partsname");
$t{partscode} = $t{q}->param("partscode");
$t{partsqty} = $t{q}->param("partsqty");
$t{DWG0} = $t{q}->param("DWG0");
$t{DWG0_id} = $t{q}->param("DWG0_id");
# 读人机界面的数据
@{ $t{names} } = split(/\r\n/,$t{partsname});
@{ $t{codes} } = split(/\r\n/,$t{partscode});
@{ $t{qtys} } = split(/\r\n/,$t{partsqty});
$t{length1} = $#{ $t{names} };
# units的存档
@{ $t{units} } = ();
for $n ( 0 .. $t{length1} ) {
$t{id} = $n + 1;
$t{unit1} = 'unit1_' . $t{id};
$t{unit1} = $t{q}->param("$t{unit1}");
push(@{ $t{units} },$t{unit1});
}
# enq1的输入数据进行配对(和DB同步时会打乱顺序)
my @b = ();
for $n ( 0 .. $t{length1} ) {
$t{n1} = $t{names}[$n];
$t{c1} = $t{codes}[$n];
$t{u1} = $t{units}[$n];
$t{c1} = $t{c1} . '===' . $t{DWG0_id};
$t{enq1_names}{$t{c1}} = $t{n1};
$t{enq1_units}{$t{c1}} = $t{u1};
push @b, $t{c1};
}
# 零件表的名称
$t{ptable} = sprintf("%06d",$t{main_type1id});
$t{ptable} = 'a' . $t{ptable};
# 先判断是否是empty table.
$t{count1} = $self->dbh->selectrow_array("SELECT count(*) FROM $t{ptable}");
# 取出DB的Parts的codes
%count = %count2 = ();
@union = @isect = @diff = ();
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 { # 空表格的情况
@union = @diff = @b;
$t{idmax} = 0;
for $n ( 0 .. $#b ) {
$t{idmax}++;
$t{enq1_ids}{$b[$n]} = $t{idmax};
}
}
# 把新增加的零件插入DB中
if ( $#diff >= 0 ) {
for $n ( 0 .. $#diff ) {
$t{c1} = $diff[$n];
$t{n1} = $t{enq1_names}{$t{c1}};
$t{u1} = $t{enq1_units}{$t{c1}};
($t{c1},$t{ctmp}) = split(/===/,$t{c1}); # 这个操作把code和DWG分开
$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=1","0=100=1=0000-00-00=1=1")';
$t{DO} = $self->dbh->do("$t{sql}");
}
}
# 把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});
]]>
返回