--------------------------------------------------------------------------------
返回
MySQL操作程序七
返回
--------------------------------------------------------------------------------
檢查enq1和enq2的關(guān)系(check_enq1_enq2.pl)
use strict;
use DBI;
my(%t,$n,@fld,@rec,$pref);
# 連接數(shù)據(jù)庫
$$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;
}
# 取出所有enq1的enq2s
$t{sth} = $$pref{dbh}->prepare("SELECT id,enq2s FROM enq1");
$t{sth}->execute;
while ( @rec = $t{sth}->fetchrow_array ) {
@{ $t{enq2} } = split(/=/,$rec[1]);
for $n ( 0 .. $#{ $t{enq2} } ) {
if ( $t{list}{$t{enq2}[$n]} ) {
print "NO,enq1=$rec[0],enq2=$t{enq2}[$n]\n";
} else {
$t{list}{$t{enq2}[$n]} = $rec[0];
}
}
}
$t{sth}->finish;
# 檢查取出的enq2的enq1id
for $n ( sort {$a<=>$b} keys %{ $t{list} } ) {
$t{enq1} = $$pref{dbh}->selectrow_array("SELECT enq1id FROM enq2 WHERE id = $n");
if ($t{enq1} == $t{list}{$n} ) {
# print "$n==>$t{list}{$n}=>$t{enq1},OK!\n";
} else {
print "$n==>$t{list}{$n}=>$t{enq1},NOT OK!\n";
}
}
# 關(guān)閉數(shù)據(jù)庫
$$pref{dbh}->disconnect;

--------------------------------------------------------------------------------
返回
讀零件數(shù)據(jù)處理程序
返回
--------------------------------------------------------------------------------
# 輸入零件程序(mscenq1.pl中)
# 待完善的項目
# 如何輸入GROUP名(和零件一起)?
# 顯示輸入數(shù)據(jù)中的重復(fù)code
# 顯示與DB中已有數(shù)據(jù)的重復(fù)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");
# 讀人機界面的數(shù)據(jù)
@{ $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的輸入數(shù)據(jù)進行配對(和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;
# 同步作業(yè)
@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]}; # 如果重復(fù)的話就放棄
$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等輸入到對應(yīng)的位置上(注意多主機的處理)
# 從零件表中抽出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圖紙?zhí)栆惨恢?
# 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時的順序,必須恢復(fù)原來的順序!
@{ $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} });
# 取出現(xiàn)有的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});

--------------------------------------------------------------------------------
返回
修改部分設(shè)定參數(shù)程序
返回
--------------------------------------------------------------------------------
# 復(fù)制軟件時,修改部分設(shè)定參數(shù)的程序
use strict;
use File::Copy;
my($aref);
# 處理mscenq2.pl
$$aref{inputfile} = 'mscenq2.pl';
($aref) = change_words($aref);
# 處理mscquo2.pl
$$aref{inputfile} = 'mscquo2.pl';
($aref) = change_words($aref);
# 處理order1.pl
$$aref{inputfile} = 'mscorder1.pl';
($aref) = change_words($aref);
# 處理order2.pl
$$aref{inputfile} = 'mscorder2.pl';
($aref) = change_words($aref);
# 處理packing.pl
$$aref{inputfile} = 'mscpacking.pl';
($aref) = change_words($aref);
# 處理inv1.pl
$$aref{inputfile} = 'mscinv1.pl';
($aref) = change_words($aref);
# 處理inv2.pl
$$aref{inputfile} = 'mscinv2.pl';
($aref) = change_words($aref);
sub change_words {
my($aref) = @_;
my(%t);
print "inputfile==>$$aref{inputfile}\n";
$t{oldfile} = $$aref{inputfile} . '.tmp.pl';
copy("./pro/$$aref{inputfile}","./pro/$t{oldfile}") or die "Copy failed:$!";
open(IN,"./pro/$t{oldfile}") or die "Can't open the file $t{oldfile}.\n";
open(OUT,">./pro/$$aref{inputfile}");
while(<IN>){
if ( $_ =~ /Open\(\"C/ ) {
$_ =~ s/Open\(\"C/Open\(\"E/;
print $_;
print OUT $_;
} elsif ( $_ =~ /SaveAs\(\"C/ ) {
$_ =~ s/SaveAs\(\"C/SaveAs\(\"E/;
print $_;
print OUT $_;
} else {
print OUT $_;
}
}
close(IN);
close(OUT);
return($aref);
}
# 處理msc.pm
copy("msc.pm","msc1.pm") or die "Copy failed:$!";
open(IN,"msc1.pm") or die "Can't open the file msc1.pm.\n";
open(OUT,">msc.pm");
while(<IN>){
if ( $_ =~ /localhost/ ) {
$_ =~ s/localhost/SERVER\.msc\.local/;
$_ =~ s/cookbook/msc/;
$_ =~ s/cbuser/cb2user/;
$_ =~ s/cbpass/cb2pass/;
print OUT $_;
} else {
print OUT $_;
}
}
close(IN);
close(OUT);

--------------------------------------------------------------------------------
返回
操作數(shù)據(jù)庫一個零件表的程序
返回
--------------------------------------------------------------------------------
make_ptable1.pl
use strict;
use DBI;
my(%t,$n,@fld,@rec);
# 輸入主機序號,形成零件表名
print "Please input parts table name(Enginee.NO)=";
chop($t{input}=<STDIN>);
$t{inputf} = sprintf("%06d",$t{input});
$t{table1} = 'a' . $t{inputf};
# 連接數(shù)據(jù)庫
$t{dsn} = "DBI:mysql:host=localhost;database=cookbook";
$t{dbh} = DBI->connect($t{dsn}, "cbuser", "cbpass") or die "Cannot connect to server\n";
$t{dbh}->do("SET NAMES utf8");
if(!$t{dbh}){
print "SQL read ERROR!\n";
exit;
}
# 刪除一個零件表
$t{sql} = 'DROP TABLE IF EXISTS ' . $t{table1} . ';';
$t{dbh}->do($t{sql});
# 創(chuàng)建一個零件表
$t{sql} = 'CREATE TABLE ' . $t{table1};
$t{sql} .= ' (';
$t{sql} .= 'id INT AUTO_INCREMENT,';
$t{sql} .= 'name TEXT,';
$t{sql} .= 'code TEXT,';
$t{sql} .= 'dwg_id INT,';
$t{sql} .= 'Nuid INT,';
$t{sql} .= 'weight INT,';
$t{sql} .= 'price1 TEXT,';
$t{sql} .= 'price2 TEXT,';
$t{sql} .= 'memo TEXT,';
$t{sql} .= 'PRIMARY KEY (id));';
$t{dbh}->do($t{sql});
$t{sth} = $t{dbh}->prepare ("SHOW columns FROM $t{table1}");
$t{sth}->execute;
while ( @rec = $t{sth}->fetchrow_array )
{
print "@rec\n";
}
$t{sth}->finish;
# 輸入enq1序號
print "Please input ID of enq1=";
chop($t{enqid}=<STDIN>);
$t{sql} = 'UPDATE enq1 SET partsid = "C" WHERE id = "';
$t{sql} .= $t{enqid} . '"';
$t{dbh}->do($t{sql});
$t{sql} = 'UPDATE enq1 SET QTY = "C" WHERE id = "';
$t{sql} .= $t{enqid} . '"';
$t{dbh}->do($t{sql});
$t{dbh}->disconnect;
__END__;

--------------------------------------------------------------------------------
返回
操作數(shù)據(jù)庫零件表的四個程序
返回
--------------------------------------------------------------------------------
修改所有零件表的部分數(shù)據(jù)(change_ptables.pl)
use strict;
use DBI;
my(%t,$n,@fld,$pref,@rec);
# 連接數(shù)據(jù)庫
$$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{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};
push(@{ $t{ptables} },$t{ptable1});
}
$t{sth}->finish;
# 修改數(shù)據(jù)
for $n ( 0 .. $#{ $t{ptables} } ) {
$t{ptable1} = $t{ptables}[$n];
$t{sql} = 'UPDATE ' . $t{ptable1};
$t{sql} .= ' SET price1 = "NULL"';
print "sql=$t{sql}\n";
$$pref{dbh}->do($t{sql});
$t{sql} = 'UPDATE ' . $t{ptable1};
$t{sql} .= ' SET price2 = "NULL"';
print "sql=$t{sql}\n";
$$pref{dbh}->do($t{sql});
}
# 關(guān)閉數(shù)據(jù)庫
$$pref{dbh}->disconnect;
取出已有的數(shù)據(jù)庫零件表數(shù)據(jù)并寫入中間文件(obtain_ptables.pl)
use strict;
use DBI;
my(%t,$n,@fld,@rec,$pref);
# 連接數(shù)據(jù)庫
$$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;
# 取出所有現(xiàn)有零件表的數(shù)據(jù)
for $n ( 0 .. $#{ $t{ptables} } ) {
$$pref{ptable1} = $t{ptables}[$n];
($pref) = read_ptable($pref);
}
# 關(guān)閉數(shù)據(jù)庫
$$pref{dbh}->disconnect;
# 寫入中間文件(../txt/ptables.txt)
open(OUT,">../txt/ptables.txt");
print OUT 'filename=ptables.txt',"\n";
print OUT 'C===file===id===name===code===dwg_id===Nuid===weight===price1===price2===memo',"\n";
for $n ( 0 .. $#{ $t{ptables} } ) {
$$pref{ptable1} = $t{ptables}[$n];
($pref) = write_ptable($pref);
}
close(OUT);
print "Finished.\n";
sub write_ptable {
my($pref) = @_;
my (%t,$n);

for $n ( 0 .. $#{ $$pref{id}{$$pref{ptable1}} } ) {
$t{id} = $$pref{id}{$$pref{ptable1}}[$n];
$t{name} = $$pref{name}{$$pref{ptable1}}[$n];
# $t{name} =~ s/\x0D\x0A//g;
# $t{name} =~ s/\x0D$//; # 改行符號去掉(如果有的話)
$t[code] = $$pref[code]{$$pref{ptable1}}[$n];
# $t[code] =~ s/\x0D$//; # 改行符號去掉(如果有的話)
$t{dwg_id} = $$pref{dwg_id}{$$pref{ptable1}}[$n];
$t{Nuid} = $$pref{Nuid}{$$pref{ptable1}}[$n];
print OUT 'PT===',$$pref{ptable1};
print OUT '===',$t{id};
print OUT '===',$t{name};
print OUT '===',$t[code];
print OUT '===',$t{dwg_id};
print OUT '===',$t{Nuid};
print OUT "\n";
}
return($pref);
}
sub read_ptable {
my($pref) = @_;
my (%t,@rec);
# 讀零件表
$t{sth} = $$pref{dbh}->prepare("SELECT id,name,code,dwg_id,Nuid FROM $$pref{ptable1}");
$t{sth}->execute;
while ( @rec = $t{sth}->fetchrow_array ) {
push(@{ $$pref{id}{$$pref{ptable1}} },$rec[0]);
push(@{ $$pref{name}{$$pref{ptable1}} },$rec[1]);
push(@{ $$pref[code]{$$pref{ptable1}} },$rec[2]);
push(@{ $$pref{dwg_id}{$$pref{ptable1}} },$rec[3]);
push(@{ $$pref{Nuid}{$$pref{ptable1}} },$rec[4]);
}
$t{sth}->finish;
return($pref);
}
__END__;
# 這個操作把不含Nuid的零件表刪除(作業(yè)中程序,保存下來)
$t{sth} = $$pref{dbh}->prepare("SHOW COLUMNS FROM $$pref{ptable1}");
$t{sth}->execute;
$t{column_list} = '';
while ( @rec = $t{sth}->fetchrow_array ) {
$t{column_list} .= ' ' . $rec[0];
}
$t{sth}->finish;
if ( $t{column_list} !~ /Nuid/ ) {
$t{sql} = 'DROP TABLE IF EXISTS ' . $$pref{ptable1} . ';';
$$pref{dbh}->do($t{sql});
}
生成數(shù)據(jù)庫零件表(make_ptables.pl)
use strict;
use DBI;
my(%t,$n,@fld,$pref,@rec);
# 連接數(shù)據(jù)庫
$$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{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};
push(@{ $t{ptables} },$t{ptable1});
}
$t{sth}->finish;
# 生成零件表
for $n ( 0 .. $#{ $t{ptables} } ) {
$$pref{ptable1} = $t{ptables}[$n];
($pref) = ptable1($pref);
}
# 關(guān)閉數(shù)據(jù)庫
$$pref{dbh}->disconnect;
print "Finished.\n";
sub ptable1 {
my($pref) = @_;
my(%t);

$t{sql} = 'DROP TABLE IF EXISTS ' . $$pref{ptable1} . ';';
$$pref{dbh}->do($t{sql});
$t{sql} = 'CREATE TABLE ' . $$pref{ptable1};
$t{sql} .= ' (';
$t{sql} .= 'id INT AUTO_INCREMENT,';
$t{sql} .= 'name TEXT,';
$t{sql} .= 'code TEXT,';
$t{sql} .= 'dwg_id INT,';
$t{sql} .= 'Nuid INT,';
$t{sql} .= 'weight INT,';
$t{sql} .= 'price1 TEXT,';
$t{sql} .= 'price2 TEXT,';
$t{sql} .= 'memo TEXT,';
$t{sql} .= 'PRIMARY KEY (id));';
$$pref{dbh}->do($t{sql});

return($pref);
}
__END__;
零件表插入已有的數(shù)據(jù)(input_ptables.pl)
use strict;
use DBI;
my(%t,$n,@fld,$pref,@rec);
print "This is input_ptables.pl.\n";
# 連接數(shù)據(jù)庫
$$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{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};
push(@{ $t{ptables} },$t{ptable1});
}
$t{sth}->finish;
# 從../txt/ptables.txt讀取原有零件表數(shù)據(jù)
open(IN,"../txt/ptables.txt") or die "Can't open the file ptables.txt\n";
while(<IN>){
if (/^PT/){
chop;
@fld = split(/===/);
push(@{ $$pref{id}{$fld[1]} },$fld[2]);
push(@{ $$pref{name}{$fld[1]} },$fld[3]);
push(@{ $$pref[code]{$fld[1]} },$fld[4]);
push(@{ $$pref{dwg_id}{$fld[1]} },$fld[5]);
push(@{ $$pref{Nuid}{$fld[1]} },$fld[6]);
}
}
close(IN);
# 插入數(shù)據(jù)
for $n ( 0 .. $#{ $t{ptables} } ) {
$$pref{ptable1} = $t{ptables}[$n];
($pref) = input_ptable1($pref);
}
# 關(guān)閉數(shù)據(jù)庫
$$pref{dbh}->disconnect;
print "Finished.\n";
sub input_ptable1 {
my($pref) = @_;
my(%t,$n);

if ( $$pref{id}{$$pref{ptable1}}[0] == 0 ) {
return($pref);
}
for $n ( 0 .. $#{ $$pref{id}{$$pref{ptable1}} } ) {
$t{id} = $n + 1;
$t{name} = $$pref{name}{$$pref{ptable1}}[$n];
$t[code] = $$pref[code]{$$pref{ptable1}}[$n];
$t{dwg_id} = $$pref{dwg_id}{$$pref{ptable1}}[$n];
$t{Nuid} = $$pref{Nuid}{$$pref{ptable1}}[$n];
$t{sql} = 'INSERT INTO ' . $$pref{ptable1};
$t{sql} .= ' (name,code,dwg_id,Nuid,weight,price1,price2) ';
# if ( $t{dwg_id} == 0 ) {
# $t{dwg_id} = 1;
# }
# if ( $t{Nuid} == 0 ) {
# $t{Nuid} = 1;
# }
$t{sql} .= 'VALUES("';
$t{sql} .= $t{name} . '","';
$t{sql} .= $t[code] . '","';
$t{sql} .= $t{dwg_id} . '","';
$t{sql} .= $t{Nuid} . '",1,"0=100=1=0000-00-00=1","0=100=1=0000-00-00=1=1");';
$$pref{dbh}->do($t{sql});
}

return($pref);
}
__END__;
零件表的columns的變動
mysql> show columns from a000001;
+----------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | text | YES | | NULL | |
| code | text | YES | | NULL | |
| dwg_id | int(11) | YES | | NULL | |
| Nuid | int(11) | YES | | NULL | |
| weight | int(11) | YES | | NULL | |
| price1 | int(11) | YES | | NULL | |
| time1 | date | YES | | NULL | |
| money1 | int(11) | YES | | NULL | |
| makerid | int(11) | YES | | NULL | |
| price2 | text | YES | | NULL | |
| time2 | text | YES | | NULL | |
| money2 | text | YES | | NULL | |
| makerid2 | text | YES | | NULL | |
| memo | text | YES | | NULL | |
+----------+---------+------+-----+---------+----------------+
15 rows in set (0.28 sec)
mysql> show columns from a000001;
+--------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | text | YES | | NULL | |
| code | text | YES | | NULL | |
| dwg_id | int(11) | YES | | NULL | |
| Nuid | int(11) | YES | | NULL | |
| weight | int(11) | YES | | NULL | |
| price1 | text | YES | | NULL | |
| price2 | text | YES | | NULL | |
| memo | text | YES | | NULL | |
+--------+---------+------+-----+---------+----------------+
9 rows in set (0.03 sec)
*************************** 10. row ***************************
id: 10
name: p1name
code: p1code
dwg_id: 1
Nuid: 1
weight: 1
price1: 0=100=1=0000-00-00=1
price2: 0=100=1=0000-00-00=1=1
memo: NULL
price1的定義:
0==>價格
100==>Discount
1==>貨幣單位
0000-00-00=>日期
1==>商社
price2的定義:
0==>價格
100==>Discount
1==>貨幣單位
0000-00-00=>日期
1==>船東
1==>對應(yīng)的商社價格(從后面數(shù))

--------------------------------------------------------------------------------
返回
Perl的散列(hash)
返回
--------------------------------------------------------------------------------
修改零件的數(shù)量
use strict;
my(%t,$n,$n1,$n2);
$t{type1id} = '245==332';
$t{partsid} = '3=2=4==2=8';
$t{QTY} = '30=20=40==20=80';
$t{type1id2} = '245==332';
$t{partsid2} = '4==2=8';
$t{QTY2} = '40==20=80';
@{ $t{QTYnew} } = qw/300 200 400 200 800/;
@{ $t{ttt1} } = split(/==/,$t{type1id});
@{ $t{ppp1} } = split(/==/,$t{partsid});

$t{NO}=0;
@{ $t{qqq3} } = ();
for $n ( 0 .. $#{ $t{ttt1} } ) {
$t{ttt2} = $t{ttt1}[$n];
$t{ppp2} = $t{ppp1}[$n];
@{ $t{ppp3} } = split(/=/,$t{ppp2});
@{ $t{qqq1} } = ();
for $n1 ( 0 .. $#{ $t{ppp3} } ) {
$t{NO}++;
$t{ppp4} = $t{ppp3}[$n1];
$t{tp_qty}{$t{ttt2}}{$t{ppp4}} = $t{QTYnew}[$t{NO}-1];
push(@{ $t{qqq1} },$t{QTYnew}[$t{NO}-1]);
}
$t{qqq2} = join('=',@{ $t{qqq1} });
push(@{ $t{qqq3} },$t{qqq2});
}
$t{qqq4} = join('==',@{ $t{qqq3} });
print "enq1 result:\n";
print "old==>$t{QTY}\n";
print "new==>$t{qqq4}\n\n";
@{ $t{ttt1} } = split(/==/,$t{type1id2});
@{ $t{ppp1} } = split(/==/,$t{partsid2});

$t{NO}=0;
@{ $t{qqq3} } = ();
for $n ( 0 .. $#{ $t{ttt1} } ) {
$t{ttt2} = $t{ttt1}[$n];
$t{ppp2} = $t{ppp1}[$n];
@{ $t{ppp3} } = split(/=/,$t{ppp2});
@{ $t{qqq1} } = ();
for $n1 ( 0 .. $#{ $t{ppp3} } ) {
$t{NO}++;
$t{ppp4} = $t{ppp3}[$n1];
push(@{ $t{qqq1} },$t{tp_qty}{$t{ttt2}}{$t{ppp4}});
}
$t{qqq2} = join('=',@{ $t{qqq1} });
push(@{ $t{qqq3} },$t{qqq2});
}
$t{qqq4} = join('==',@{ $t{qqq3} });
print "enq2 result:\n";
print "old==>$t{QTY2}\n";
print "new==>$t{qqq4}\n\n";
enq1 result:
old==>30=20=40==20=80
new==>300=200=400==200=800
enq2 result:
old==>40==20=80
new==>400==200=800
# 必須置零,因為下一臺主機的DWG極有可能同名!
@{ $t{plist}{id}{$t{dwg1}} } = ();
@{ $t{plist}{name}{$t{dwg1}} } = ();
@{ $t{plist}[code]{$t{dwg1}} } = ();
@{ $t{plist}{QTY}{$t{dwg1}} } = ();
@{ $t{plist}{Nuid}{$t{dwg1}} } = ();
把復(fù)數(shù)的enq2價格歸并到一個enq1
$t{enq2s} = $self->dbh->selectrow_array("SELECT enq2s FROM enq1 WHERE id = $t{quo2_id}");
@{ $t{enq2_ids} } = split(/=/,$t{enq2s});
for $n ( 0 .. $#{ $t{enq2_ids} } ) {
$t{enq2_id} = $t{enq2_ids}[$n];
($t{type1id},$t{partsid},$t{price}) = $self->dbh->selectrow_array("SELECT type1id,partsid,price FROM enq2 WHERE id = $t{enq2_id}");
@{ $t{tts} } = split(/==/,$t{type1id});
@{ $t{pps} } = split(/==/,$t{partsid});
@{ $t{pps2} } = split(/=/,$t{price});
$t{NO} = 0;
for $n1 ( 0 .. $#{ $t{tts} } ) {
$t{tts1} = $t{tts}[$n1];
$t{pps1} = $t{pps}[$n1];
@{ $t{pps1s} } = split(/=/,$t{pps1});
for $n2 ( 0 .. $#{ $t{pps1s} } ) {
$t{NO}++;
$t{pps1s1} = $t{pps1s}[$n2];
$t{list}{$t{tts1}}{$t{pps1s1}} = $t{pps2}[$t{NO}-1];
}
}
}
# enq1
@{ $t{prices} } = ();
($t{type1id},$t{partsid}) = $self->dbh->selectrow_array("SELECT type1id,partsid FROM enq1 WHERE id = $t{quo2_id}");
@{ $t{tts} } = split(/==/,$t{type1id});
@{ $t{pps} } = split(/==/,$t{partsid});
for $n1 ( 0 .. $#{ $t{tts} } ) {
$t{tts1} = $t{tts}[$n1];
$t{pps1} = $t{pps}[$n1];
@{ $t{pps1s} } = split(/=/,$t{pps1});
for $n2 ( 0 .. $#{ $t{pps1s} } ) {
$t{pps1s1} = $t{pps1s}[$n2];
push(@{ $t{prices} },$t{list}{$t{tts1}}{$t{pps1s1}});
}
}
$t{price0} = join("=",@{ $t{prices} });
#$t{price0} = $t{list}{"154"}{"2"};
$t{sql} = 'UPDATE quo2 set price0 = "';
$t{sql} .= $t{price0} . '" where id = ';
$t{sql} .= $t{quo2_id};
$t{DO} = $self->dbh->do($t{sql});
通過enq2->quo1找出原價
$t{sth} = $self->dbh->prepare("select id, enq1id from enq2");
$t{sth}->execute;
while (@rec = $t{sth}->fetchrow_array) {
if ( $rec[1] == $$pref{id} ) {
$t{NO} = 0;
$t{enq2_id} = $rec[0];
# 取出價格
$t{pri} = $self->dbh->selectrow_array("SELECT price FROM quo1 WHERE id = $t{enq2_id}");
@{ $t{pris} } = split(/=/,$t{pri});
($t{tt1},$t{pp1}) = $self->dbh->selectrow_array("SELECT type1id,partsid FROM enq2 WHERE id = $t{enq2_id}");
@{ $t{tt2} } = split(/==/,$t{tt1});
@{ $t{pp2} } = split(/==/,$t{pp1});
for $n ( 0 .. $#{ $t{tt2} } ) {
$t{tt3} = $t{tt2}[$n];
$t{pp3} = $t{pp2}[$n];
@{ $t{pp4} } = split(/=/,$t{pp3});
for $n1 (0 .. $#{ $t{pp4} } ) {
$t{NO}++;
$t{list}{$t{tt3}}{$t{pp4}[$n1]} = $t{pris}[$t{NO}-1];
}
}
}
}
$t{sth}->finish;
＞＝誤寫成＝＝的BUG(弄了一天才發(fā)現(xiàn))
foreach $e (@a,@b) { $count{$e}++ };
#@union = sort {$a<=>$b} keys %count;
@union = sort keys %count;
foreach $e ( keys %count ) {
# if ($count{$e} == 2 ) { # 正好兩個的情況(不對),這個==不對
if ($count{$e} >= 2 ) { # 應(yīng)該是>=
$count2{$e}++;
}
}
for $n ( 0 .. $#b ) {
next if $count2{$b[$n]};
$t{idmax}++;
push @diff, $b[$n];
$t{enq1_ids}{$b[$n]} = $t{idmax};
}
刪除重復(fù)的項目并排序
use strict;
my(%t,$n,@fld);
# 讀取main_maker1_order2.txt文件
open(IN,"main_maker1_order2.txt") or die "Can't open the file main_maker1_order2.txt.\n";
while(<IN>){
next if $. == 1;
chop;
@fld = split(/==>/);
$t{list}{$fld[1]}++;
}
close(IN);
# 讀取makers_tmp2.txt文件
open(IN,"makers_tmp2.txt") or die "Can't open the file makers_tmp2.txt.\n";
while(<IN>){
chop;
$t{list}{$_}++;
}
close(IN);
# 排序操作
@{ $t{orders} } = sort keys %{ $t{list} };
open(OUT,">makers.txt");
print OUT 'Filename=makers.txt',"\n";
$t{NO} = 0;
for $n ( 0 .. $#{ $t{orders} } ) {
$t{NO}++;
$t{N1} = sprintf("%05d",$t{NO});
$t{line} = $t{N1} . '==>' . $t{orders}[$n];
print OUT $t{line},"\n";
}
close(OUT);
__END__
###################################################################
# 把所有的小寫字母改成大寫字母并排序
open(IN,"makers_tmp.txt") or die "Can't open the file makers_tmp.txt.\n";
while(<IN>){
chop;
$t{line} = uc($_);
$t{list}{$t{line}}++; # 刪除相同的項目
}
close(IN);
# 排序操作
@{ $t{orders} } = sort keys %{ $t{list} };
open(OUT,">makers_tmp2.txt");
for $n ( 0 .. $#{ $t{orders} } ) {
print OUT $t{orders}[$n],"\n";
}
close(OUT);
###################################################################
數(shù)據(jù)庫操作的一個程序,不用了。留作存檔
# 從enq1取出主機編號(type1id),零件號碼(partsid),數(shù)量(QTY)
($t{type1id},$t{partsid},$t{QTY}) = $self->dbh->selectrow_array("SELECT type1id,partsid,QTY FROM enq1 WHERE id = $t{enq1_id}");
@loop1 = ();
$t{NO} = 0;
@{ $t{type1id_list} } = split(/==/,$t{type1id});
@{ $t{partsid_list} } = split(/==/,$t{partsid});
@{ $t{QTY_list} } = split(/==/,$t{QTY});
# Table的一行是一個項目
for $n ( 0 .. $#{ $t{type1id_list} } ) {
$t{type1id1} = $t{type1id_list}[$n];
$t{partsid1} = $t{partsid_list}[$n];
$t{QTY1} = $t{QTY_list}[$n];
# 從main_type1中取出主機名和DWG圖號
($t{id1},$t{type1},$t{DWG}) = $self->dbh->selectrow_array("select id, name,DWG from main_type1 where id = $t{type1id1}");
# 從零件名表中取出零件編號和圖紙?zhí)?
@{ $t{pid_list} } = split(/=/,$t{partsid1});
@{ $t{Q_list} } = split(/=/,$t{QTY1});
@{ $t{DWGs} } = split(/=/,$t{DWG});
# 生成零件表名,根據(jù)enq1的零件編號從數(shù)據(jù)庫取出零件信息和所屬圖紙?zhí)?
$t{ptable} = sprintf("%06d",$t{type1id1});
$t{ptable} = 'a' . $t{ptable};
@{ $t{dwgs1} } = ();
for $n1 ( 0 .. $#{ $t{pid_list} } ) {
$t{pid1} = $t{pid_list}[$n1];
$t{Q1} = $t{Q_list}[$n1];
@{ $t{p1} } = $self->dbh->selectrow_array("select * from $t{ptable} where id = $t{pid1}");
$t{dwg1} = $t{p1}[4];
push(@{ $t{plist}{id}{$t{dwg1}} },$t{p1}[0]);
push(@{ $t{plist}{name}{$t{dwg1}} },$t{p1}[1]);
push(@{ $t{plist}[code]{$t{dwg1}} },$t{p1}[2]);
push(@{ $t{dwgs1} },$t{dwg1});
}
# 合并重復(fù)的圖紙?zhí)?=>這個操作充分利用了Perl散列的特性
%seen = ();
@{ $t{dwgs2} } = ();
foreach $item (@{ $t{dwgs1} }) {
unless ( $seen{$item} ) {
$seen{$item} = 1;
push(@{ $t{dwgs2} },$item);
}
}
# 第一層:主機名
# 第二層:圖紙?zhí)?XXXDWG設(shè)定為不知道圖紙?zhí)?
# 第三層:零件名
# 把數(shù)據(jù)放入HTML的TABLE的TR
for $n1 ( 0 .. $#{ $t{dwgs2} } ) {
$t{dwg1} = $t{dwgs2}[$n1];
$t{DWG1} = $t{DWGs}[$t{dwg1}-1];
# 取出圖紙?zhí)?
$t{line1} = '<tr bgcolor="#FFF000" align="center"><td colspan=7>';
$t{line1} .= $t{id1} . '==>' . $t{DWG1};
$t{line1} .= '</td></tr>';
my %row = (
line1 => $t{line1}
);
push(@loop1, \%row);
# 處理零件
for $n2 ( 0 .. $#{ $t{plist}{id}{$t{dwg1}} } ) {
$t{NO}++; # enq1的所有Parts的編號
$t{pid1} = $t{plist}{id}{$t{dwg1}}[$n2];
$t{name1} = $t{plist}{name}{$t{dwg1}}[$n2];
$t{code1} = $t{plist}[code]{$t{dwg1}}[$n2];
$t{line1} = '<tr bgcolor="#F0FFF0" align="center"><td>';
$t{line1} .= $t{NO} . '</td><td>';
$t{line1} .= $t{name1} . '</td><td>';
$t{line1} .= $t{code1} . '</td><td>';
$t{line1} .= $t{code1} . '</td><td>';
$t{line1} .= $t{code1} . '</td><td>';
$t{line1} .= $t{code1} . '</td><td>';
$t{line1} .= $t{code1};
$t{line1} .= '</td></tr>';
my %row = (
line1 => $t{line1}
);
push(@loop1, \%row);
}

# 必須置零,因為下一臺主機的DWG極有可能同名!
$t{plist}{id}{$t{dwg1}} = ();
$t{plist}{name}{$t{dwg1}} = ();
$t{plist}[code]{$t{dwg1}} = ();
}
}

最新評論