--------------------------------------------------------------------------------
# Enter the ID of enq2 into enq1
use strict;
use DBI;
my(%t,$n,@fld,@rec);
# Connect to the database
$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;
}
# Obtain the correspondence between enq2 and enq1
$t{sth} = $t{dbh}->prepare ("SELECT id,enq1id FROM enq2");
$t{sth}->execute;
while ( @rec = $t{sth}->fetchrow_array )
{
$t{enq1}{$rec[0]} = $rec[1];
}
$t{sth}->finish;
for $n (keys %{ $t{enq1} } ) {
push(@{ $t{enq2}{$t{enq1}{$n}} },$n);
}
for $n ( keys %{ $t{enq2} } ) {
@{ $t{tmp} } = sort @{ $t{enq2}{$n} };
$t{enq2list} = join("=",@{ $t{tmp} });
$t{list}{$n} = $t{enq2list};
}
# Substitute the value into enq1
for $n ( keys %{ $t{list} } ) {
$t{value} = $t{list}{$n};
$t{sql} = 'UPDATE enq1 SET enq2s = "';
$t{sql} .= $t{value} . '" WHERE id = "' . $n . '";';
print "$t{sql}\n";
$t{dbh}->do($t{sql});
}
$t{dbh}->disconnect;
List enq1 ID for selection (this part is no longer used, save it for reference)
# List enq1 ID for selection
$t{sth} = $self->dbh->prepare("select id, ourref from enq1 ORDER BY id DESC");
$t{sth}->execute;
while (@rec = $t{sth}->fetchrow_array) {
$row_ref = (); # This initialization is very important!
if ( $rec[0] == $t{enq1_id} ) {
$t{line1} = '<OPTION VALUE="' . $rec[0] . '" selected="selected">';
$t{line1} .= $rec[0] . '==>' . $rec[1] . '</OPTION>';
} else {
$t{line1} = '<OPTION VALUE="' . $rec[0] . '">';
$t{line1} .= $rec[0] . '==>' . $rec[1] . '</OPTION>';
}
$$row_ref{line1} = $t{line1};
push(@loop, $row_ref);
}
$t{sth}->finish;
$t{template}->param(LOOP => \@loop);
<tr bgcolor="lightcyan" align="center"><td>OURREF</td><td>
<TMPL_VAR NAME="enq1_id">==><TMPL_VAR NAME="ourref1">
<!-- Select enq1(OURREF) -->
<form action="" method="post">
<SELECT NAME="enq1_id">
<TMPL_LOOP NAME="LOOP">
<TMPL_VAR NAME="line1">
</TMPL_LOOP>
</SELECT>
<input type="submit" value="OURREF selection"><p>
<input type="hidden" name="id" value="<TMPL_VAR NAME="quo2_id">">
<input type="hidden" name="pat" value="select_enq1">
<input type="hidden" name="rm" value="modequo2">
</form>
<!-- Select enq1 -->
<form action="" method="post">
<input type=text name=word1 value="">
<input type="submit" value="OURREF Scroll"><p>
<input type="hidden" name="table" value="enq1">
<input type="hidden" name="table0" value="quo2">
<input type="hidden" name="item" value="enq1id">
<input type="hidden" name="id" value="<TMPL_VAR NAME="quo2_id">">
<input type="hidden" name="rm" value="modes_header">
</form>
</td></tr>
--------------------------------------------------------------------------------
return
MySQL Operating Program Four
return
--------------------------------------------------------------------------------
It is best to clear the unwanted programs immediately!
$t{price1s}[2] is zero, the program cannot be read
# price1 processing
sub get_price1 {
my($pref,$self) = @_;
my(%t,$n);
@{ $t{prices} } = split(/==/,$$pref{price10});
for $n ( 0 .. $#{ $t{prices} } ) {
$t{prices1} = $t{prices}[$n];
@{ $t{price1s} } = split(/=/,$t{prices1});
@{ $t{price1} } = @{ $t{price1s} }[0..1];
$t{money1} = $self->dbh->selectrow_array("SELECT English FROM money WHERE id = $t{price1s}[2]");
push(@{ $t{price1} },$t{money1});
push(@{ $t{price1} },$t{price1s}[3]);
$t{maker1} = $self->dbh->selectrow_array("SELECT company FROM makers WHERE id = $t{price1s}[4]");
push(@{ $t{price1} },$t{maker1});
$t{price11} = join('/',@{ $t{price1} });
$$pref{price1} .= '<OPTION VALUE="">' . $t{price11} . '</OPTION>';
}
return($pref,$self);
}
# price2 processing
sub get_price2 {
my($pref,$self) = @_;
my(%t,$n);
@{ $t{prices} } = split(/==/,$$pref{price20});
for $n ( 0 .. $#{ $t{prices} } ) {
$t{prices2} = $t{prices}[$n];
@{ $t{price2s} } = split(/=/,$t{prices2});
@{ $t{price2} } = @{ $t{price2s} }[0..1];
$t{money1} = $self->dbh->selectrow_array("SELECT English FROM money WHERE id = $t{price2s}[2]");
push(@{ $t{price2} },$t{money1});
push(@{ $t{price2} },$t{price2s}[3]);
$t{maker1} = $self->dbh->selectrow_array("SELECT company FROM makers WHERE id = $t{price2s}[4]");
push(@{ $t{price2} },$t{maker1});
$t{price21} = join('/',@{ $t{price2} });
$$pref{price2} .= '<OPTION VALUE="">' . $t{price21} . '</OPTION>';
}
return($pref,$self);
}
Putting Commas in Numbers
$a = 10000000.33;
print "a=$a\n";
$a = commify($a);
print "a=$a\n";
sub commify {
my $text = reverse $_[0];
$text =~ s/(\d\d\d)(?=\d)(?!\d*\.)/$1,/g;
return scalar reverse $text;
}
a=10000000.33
a=10,000,000.33
Determine whether it is a positive integer
@{ $t{list} } = qw/3.3 -3 2 55.2/;
for $n ( 0 .. $#{ $t{list} } ) {
$val = $t{list}[$n];
$valid = is_positive_integer($val);
if ( $valid ) {
print "$val is valid\n";
} else {
print "$val is not valid\n";
}
}
sub is_positive_integer {
my $s = shift;
return ( $s =~ /^\+?\d+$/ && $s > 0 );
}
3.3 is not valid
-3 is not valid
2 is valid
55.2 is not valid
Some old programs
if ( $t{discount} ne 'D' ) {
@{ $t{dd} } = split(/=/,$t{discount});
} else {
for $n ( 1 .. $t{pl2} ) {
push(@{ $t{dd} },100);
}
}
<th>
<form action="" method="post">
<input type="submit" value="Disc2"><br>
<input type=text size=3 name="discount0" value="<TMPL_VAR NAME="discount0">">
<input type="hidden" name="id" value=<TMPL_VAR NAME="quo2_id">>
<input type="hidden" name="pat" value="discount0">
<input type="hidden" name="rm" value="modequo2">
</form>
</th>
#-----------Enter all the same discount
} elsif ( $t{pat} eq 'discount0' ) {
$t{discount0} = $t{q}->param("discount0");
# Get the quantity of parts
$t{partsid} = $self->dbh->selectrow_array("SELECT partsid FROM enq1 WHERE id = $t{quo2_id}");
$t{pl2} = 0;
@{ $t{pl1} } = split(/=/,$t{partsid});
for $n ( 0 .. $#{ $t{pl1} } ) {
if ( $t{pl1}[$n] != 0 ) {
$t{pl2}++;
push(@{ $t{dd} },$t{discount0});
}
}
# Update discount0 and discount of quo2
$t{discount} = join('=',@{ $t{dd} });
$t{sql} = 'UPDATE quo2 set discount0 = "';
$t{sql} .= $t{discount0} . '" where id = ';
$t{sql} .= $t{quo2_id};
$t{DO} = $self->dbh->do($t{sql});
$t{sql} = 'UPDATE quo2 set discount = "';
$t{sql} .= $t{discount} . '" where id = ';
$t{sql} .= $t{quo2_id};
$t{DO} = $self->dbh->do($t{sql});
# Big hair
@{ $t{ppp} } = ();
$t{price0} = $self->dbh->selectrow_array("SELECT price0 FROM quo2 WHERE id = $t{quo2_id}");
$t{percent} = $self->dbh->selectrow_array("SELECT percent FROM quo2 WHERE id = $t{quo2_id}");
@{ $t{prices} } = split(/=/,$t{price0});
@{ $t{pe} } = split(/=/,$t{percent});
for $n ( 0 .. $#{ $t{prices} } ) {
$t{ppp1} = int($t{prices}[$n]*$t{dd}[$n]*$t{pe}[$n]/10000);
push(@{ $t{ppp} },$t{ppp1});
}
$t{price} = join('=',@{ $t{ppp} });
$t{sql} = 'UPDATE quo2 set price = "';
$t{sql} .= $t{price} . '" where id = ';
$t{sql} .= $t{quo2_id};
$t{DO} = $self->dbh->do($t{sql});
--------------------------------------------------------------------------------
if ( $t{disc} ne 'D0' ) {
@{ $t{ddd0} } = split(/=/,$t{disc});
} else {
for $n ( 1 .. $t{pl2} ) {
push(@{ $t{ddd0} },100);
}
}
#-----------Enter all the same disc0
} elsif ( $t{pat} eq 'disc0' ) {
$t{disc0} = $t{q}->param("disc0");
# Get the quantity of parts
$t{partsid} = $self->dbh->selectrow_array("SELECT partsid FROM enq1 WHERE id = $t{quo2_id}");
$t{pl2} = 0;
@{ $t{pl1} } = split(/=/,$t{partsid});
for $n ( 0 .. $#{ $t{pl1} } ) {
if ( $t{pl1}[$n] != 0 ) {
$t{pl2}++;
push(@{ $t{d0} },$t{disc0});
}
}
# Update disc0 and disc of quo2
$t{disc} = join('=',@{ $t{d0} });
$t{sql} = 'UPDATE quo2 set disc0 = "';
$t{sql} .= $t{disc0} . '" where id = ';
$t{sql} .= $t{quo2_id};
$t{DO} = $self->dbh->do($t{sql});
$t{sql} = 'UPDATE quo2 set disc = "';
$t{sql} .= $t{disc} . '" where id = ';
$t{sql} .= $t{quo2_id};
$t{DO} = $self->dbh->do($t{sql});
# Big hair
@{ $t{ppp} } = ();
$t{price0} = $self->dbh->selectrow_array("SELECT price0 FROM quo2 WHERE id = $t{quo2_id}");
$t{percent} = $self->dbh->selectrow_array("SELECT percent FROM quo2 WHERE id = $t{quo2_id}");
$t{discount} = $self->dbh->selectrow_array("SELECT discount FROM quo2 WHERE id = $t{quo2_id}");
@{ $t{prices} } = split(/=/,$t{price0});
@{ $t{pe} } = split(/=/,$t{percent});
@{ $t{dd} } = split(/=/,$t{discount});
for $n ( 0 .. $#{ $t{prices} } ) {
$t{ppp1} = int($t{prices}[$n]*$t{dd}[$n]*$t{pe}[$n]*$t{d0}[$n]/1000000);
push(@{ $t{ppp} },$t{ppp1});
}
$t{price} = join('=',@{ $t{ppp} });
$t{sql} = 'UPDATE quo2 set price = "';
$t{sql} .= $t{price} . '" where id = ';
$t{sql} .= $t{quo2_id};
$t{DO} = $self->dbh->do($t{sql});
<form action="" method="post">
<input type="submit" value="Disc1"><br>
<input type=text size=3 name="disc0" value="<TMPL_VAR NAME="disc0">">
<input type="hidden" name="id" value=<TMPL_VAR NAME="quo2_id">>
<input type="hidden" name="pat" value="disc0">
<input type="hidden" name="rm" value="modequo2">
</form>
--------------------------------------------------------------------------------
return
MySQL Operating Program V
return
--------------------------------------------------------------------------------
Specify data writing to enq1 (insert_series2.pl)
use strict;
use DBI;
my(%t,$n,$n1,@fld,@rec,$pref);
# Connect to the database
$$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{sth} = $$pref{dbh}->prepare("SELECT id,type1id FROM enq1");
$t{sth}->execute;
while ( @rec = $t{sth}->fetchrow_array ) {
if ( $rec[1] ne 'B' ) {
@{ $t{type1ids} } = split(/==/,$rec[1]);
@{ $t{sess} } = ();
for $n ( 0 .. $#{ $t{type1ids} } ) {
push(@{ $t{sess} },1);
}
$t{sess1} = join('=',@{ $t{sess} });
$t{sql} = 'UPDATE enq1 SET seriesid = "';
$t{sql} .= $t{sess1} . '" WHERE id = "' . $rec[0] . '"';
$t{DO} = $$pref{dbh}->do($t{sql});
print "$rec[0],$rec[1],$t{sess1},DO=$t{DO}\n";
}
}
$t{sth}->finish;
# Close the database
$$pref{dbh}->disconnect;
Specify the data to be written to main_type1(insert_series.pl)
use strict;
use DBI;
my(%t,$n,$n1,@fld,@rec,$pref);
# Connect to the database
$$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;
}
# Get the length of main_type1
$t{length1} = $$pref{dbh}->selectrow_array("SELECT COUNT(*) FROM main_type1");
for $n ( 1 .. $t{length1} ) {
$t{series} = $$pref{dbh}->selectrow_array("SELECT series FROM main_type1 WHERE id = $n and series is NOT NULL");
if ( $t{series} ) {
$t{series} = 'XXXSERIES=' . $t{series};
# print "$n==>$t{series}\n";
} else {
$t{series} = 'XXXSERIES';
}
$t{sql} = 'UPDATE main_type1 SET series = "';
$t{sql} .= $t{series} . '" WHERE id = "' . $n . '"';
$t{DO} = $$pref{dbh}->do($t{sql});
if ( $t{DO} == 0 ) {
print "$n==>$t{DO}\n";
print "sql==>$t{sql}\n";
exit;
}
}
# Close the database
$$pref{dbh}->disconnect;
Specify the data to be written to the database (insert_tables.pl)
use strict;
use DBI;
my(%t,$n,$n1,@fld,@rec,$pref);
# Connect to the database
$$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;
}
# Specify the database name
print "Please input database table name=";
chop($t{table1}=<STDIN>);
# Clear the contents of the specified database table
$t{delete_table} = 'DELETE FROM ' . $t{table1};
$$pref{dbh}->do($t{delete_table});
# Read all data in the specified table
$t{inputf} = 'kobe_' . $t{table1} . '.txt';
open(IN,"../txt/$t{inputf}") or die "Can't open the file $t{inputf}\n";
$t{NO} = -1;
while(<IN>){
if ( $. == 2 ) {
chop;
@fld = split(/===/,$_);
@{ $t{columns_list} } = @fld[1..$#fld];
} elsif ( $. > 2 ) {
chop;
@fld = split(/===/,$_);
$t{NO}++;
for $n ( 1 .. $#fld ) {
if ( $fld[$n] ) {
$t{data_list}[$t{NO}][$n-1] = '"' . $fld[$n] . '"';
} else {
$t{data_list}[$t{NO}][$n-1] = 'NULL';
}
}
# The purpose of this operation is to ensure that two arrays are as long as
$t{start} = $#{ $t{data_list}[$t{NO}] };
$t{end} = $#{ $t{columns_list} };
if ($t{end} > $t{start}) {
$t{start} = $t{start} + 1;
for $n ( $t{start} .. $t{end} ) {
$t{data_list}[$t{NO}][$n] = 'NULL';
}
}
}
}
close(IN);
print "data_list=@{ $t{data_list}[0] }\n";
print "data_list=@{ $t{data_list}[1] }\n";
print "$#{ $t{columns_list} }\n";
print "$#{ $t{data_list}[0] }\n";
#exit;
# Insert data
$t{leng1} = $#{ $t{columns_list} };
$t{leng2} = $#{ $t{columns_list} } - 1;
for $n ( 0 .. $#{ $t{data_list} } ) {
$t{sql} = 'INSERT INTO ' . $t{table1} . ' (';
for $n1 ( 0 .. $t{leng2} ) {
$t{sql} .= $t{columns_list}[$n1] . ',';
}
$t{sql} .= $t{columns_list}[$t{leng1}] . ')';
$t{sql} .= ' VALUES(';
for $n1 ( 0 .. $t{leng2} ) {
$t{data1} = $t{data_list}[$n][$n1];
$t{sql} .= $t{data1} . ',';
}
$t{sql} .= $t{data_list}[$n][$t{leng1}] . ')';
$$pref{dbh}->do($t{sql});
# print $t{sql},"\n";
# exit;
}
# Close the database
$$pref{dbh}->disconnect;
Extract the id() of main_type1 that meets the criteria
use strict;
use DBI;
my(%t,$n,@fld,$aref);
# Connect to the database
$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{word1} = '17A';
$t{type1_leng} = $t{dbh}->selectrow_array("SELECT count(*) FROM main_type1");
for $n ( 1 .. $t{type1_leng} ) {
$t{ptable1} = sprintf("%06d",$n);
$t{ptable1} = 'a' . $t{ptable1};
$t{count1} = $t{dbh}->selectrow_array("SELECT count(*) FROM $t{ptable1} WHERE code LIKE \'\%$t{word1}\%\'");
print "$n===>$t{count1}\n";
}
$t{dbh}->disconnect;
Copy tables with SHOW CREATE TABLE
$t{table1} = 'enq1';
$t{table2} = $t{table1} . '_tmp';
$t{sth} = $$pref{dbh}->prepare("SHOW CREATE TABLE $t{table1}");
$t{sth}->execute;
while ( @rec = $t{sth}->fetchrow_array ) {
$t{create_table} = $rec[1];
print $t{create_table},"\n";
$t{create_table} =~ s/$t{table1}/$t{table2}/;
print $t{create_table},"\n";
}
$t{sth}->finish;
$$pref{dbh}->do($t{create_table});
Execution results
CREATE TABLE `enq1` (
`id` int(11) NOT NULL auto_increment,
`time` date default NULL,
`ourref` int(11) default NULL,
`owner` int(11) default NULL,
`ownerno` varchar(100) default NULL,
`hullnoid` int(11) default NULL,
`type1id` text,
`partsid` text,
`QTY` text,
`memo` text,
`enq2s` text,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8
CREATE TABLE `enq1_tmp` (
`id` int(11) NOT NULL auto_increment,
`time` date default NULL,
`ourref` int(11) default NULL,
`owner` int(11) default NULL,
`ownerno` varchar(100) default NULL,
`hullnoid` int(11) default NULL,
`type1id` text,
`partsid` text,
`QTY` text,
`memo` text,
`enq2s` text,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8
Take out the specified database data and write to the intermediate file (obtain_tables.pl)
use strict;
use DBI;
my(%t,$n,@fld,@rec,$pref);
# Connect to the database
$$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;
}
# Specify the database name
print "Please input database table name=";
chop($t{table1}=<STDIN>);
$t{outputf} = $t{table1} . '.txt';
# Remove COLUMNS
$t{sth} = $$pref{dbh}->prepare("SHOW COLUMNS FROM $t{table1}");
$t{sth}->execute;
$t{column_list} = '';
while ( @rec = $t{sth}->fetchrow_array ) {
push(@{ $t{columns_list} },$rec[0]);
}
$t{sth}->finish;
# Take out all data and write to intermediate files
open(OUT,">../txt/$t{outputf}");
print OUT "filename=$t{outputf}\n";
$t{line1} = join('===',@{ $t{columns_list} });
print OUT $t{table1};
print OUT '===';
print OUT $t{line1};
print OUT "\n";
$t{sth} = $$pref{dbh}->prepare("SELECT * FROM $t{table1}");
$t{sth}->execute;
while ( @rec = $t{sth}->fetchrow_array ) {
$t{line1} = join('===',@rec);
print OUT $t{table1};
print OUT '===';
print OUT $t{line1};
print OUT "\n";
}
$t{sth}->finish;
close(OUT);
# Close the database
$$pref{dbh}->disconnect;
print "The output file is ../txt/$t{outputf}\n";
Generate a table (make_table1.pl)
use strict;
use DBI;
my(%t,$n,@fld,@rec);
# File table name
$t{table1} = 'enq1list';
# Connect to the database
$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;
}
# Delete a part table
$t{sql} = 'DROP TABLE IF EXISTS ' . $t{table1} . ';';
$t{dbh}->do($t{sql});
# Create a Parts Table
$t{sql} = 'CREATE TABLE ' . $t{table1};
$t{sql} .= ' (';
$t{sql} .= 'id INT AUTO_INCREMENT,';
$t{sql} .= 'enq1s1 TEXT,';
$t{sql} .= 'enq1s2 TEXT,';
$t{sql} .= 'enq1s3 TEXT,';
$t{sql} .= 'PRIMARY KEY (id));';
$t{dbh}->do($t{sql});
$t{dbh}->disconnect;
__END__;
perl search test program
The result is correct
---------------------------------------------------------------------------
$t{orig1} = '17==28';
$t{word1} = '28';
@{ $t{name1s} } = split(/==/,$t{orig1});
$t{SEARCH_OK} = 0;
for $n ( 0 .. $#{ $t{name1s} } ) {
if ( $t{name1s}[$n] == $t{word1} ) {
$t{SEARCH_OK} = 1;
}
}
print "SEARCH_OK=$t{SEARCH_OK}\n";
SEARCH_OK=1
---------------------------------------------------------------------------
There is a wrong result
---------------------------------------------------------------------------
$t{orig1} = '17==28';
$t{word1} = '7';
@{ $t{name1s} } = split(/==/,$t{orig1});
$t{name1} = join(' ',@{ $t{name1s} });
if ( $t{name1} =~ /$t{word1}/ ) {
print "word1=$t{word1}\n";
print "name1=$t{name1}\n";
}
word1=7
name1=17 28
---------------------------------------------------------------------------
Statement that reads all IDs of a table
$aref = $t{dbh}->selectcol_arrayref("SELECT id FROM enq1");
print "enq1list=@$aref\n";
enq1list=1 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 27 28 29
--------------------------------------------------------------------------------
return
MySQL Operating Program Six
return
--------------------------------------------------------------------------------
Update price1 and price2 of all part tables (update_ptables.pl)
use strict;
use DBI;
my(%t,$n,@fld,@rec,$pref);
# Connect to the database
$$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;
}
# Take out the main_type1 number and generate the part table name at the same time
@{ $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;
# Insert price1 and price2
for $n ( 0 .. $#{ $t{ptables} } ) {
$$pref{ptable1} = $t{ptables}[$n];
($pref) = input_ptable1($pref);
}
# Close the database
$$pref{dbh}->disconnect;
print "Finished.\n";
sub input_ptable1 {
my($pref) = @_;
my(%t,$n);
# price1 assignment
$t{sql} = 'update ' . $$pref{ptable1};
$t{sql} .= ' set price1 = ';
$t{sql} .= '"0=100=1=0000-00-00=1"';
$$pref{dbh}->do($t{sql});
# price2 assignment
$t{sql} = 'update ' . $$pref{ptable1};
$t{sql} .= ' set price2 = ';
$t{sql} .= '"0=100=1=0000-00-00=1=1"';
$$pref{dbh}->do($t{sql});
return($pref);
}
__END__;
Update price1 and price2 of a part table (update_ptable1.pl)
use strict;
use DBI;
my(%t,$n,@fld,@rec);
# Enter the host serial number to form the part table name
print "Please input parts table name()=";
chop($t{input}=<STDIN>);
$t{inputf} = sprintf("%06d",$t{input});
$t{table1} = 'a' . $t{inputf};
# Connect to the database
$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;
}
# price1 assignment
$t{sql} = 'update ' . $t{table1};
$t{sql} .= ' set price1 = ';
$t{sql} .= '"0=100=1=0000-00-00=1"';
$t{dbh}->do($t{sql});
# price2 assignment
$t{sql} = 'update ' . $t{table1};
$t{sql} .= ' set price2 = ';
$t{sql} .= '"0=100=1=0000-00-00=1=1"';
$t{dbh}->do($t{sql});
$t{dbh}->disconnect;
Take out a part table data and write it to the intermediate file (obtain_ptable1.pl)
## Pay attention to the contents of price1 and price2
use strict;
use DBI;
my(%t,$n,@fld,@rec,$pref);
# Connect to the database
$$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;
}
# Enter a file name
print "Please input number=";
chop($t{number1}=<STDIN>);
$t{number1} = sprintf("%06d",$t{number1});
$t{ptable1} = 'a' . $t{number1};
@{ $t{ptables} } = ($t{ptable1});
$$pref{ptable1} = $t{ptable1};
($pref) = read_ptable($pref);
$t{outputf} = $t{ptable1} . '.txt';
# Close the database
$$pref{dbh}->disconnect;
# Write to intermediate files (../txt/)
open(OUT,">../txt/$t{outputf}");
print OUT 'filename=',"\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);
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$//; # Change the line symbol (if any)
$t[code] = $$pref[code]{$$pref{ptable1}}[$n];
# $t[code] =~ s/\x0D$//; # Change the line symbol (if any)
$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);
# Read the parts table
$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);
}
Take out a column of data table and write it to the intermediate file (obtain_table1.pl)
use strict;
use DBI;
my(%t,$n,@fld,@rec,$pref);
# Connect to the database
$$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;
}
# Specify the database name
print "Please input database table name=";
chop($t{table1}=<STDIN>);
print "Please input number=";
chop($t{number1}=<STDIN>);
$t{outputf} = $t{table1} . '_' . $t{number1} . '.txt';
# Remove COLUMNS
$t{sth} = $$pref{dbh}->prepare("SHOW COLUMNS FROM $t{table1}");
$t{sth}->execute;
$t{column_list} = '';
while ( @rec = $t{sth}->fetchrow_array ) {
push(@{ $t{columns_list} },$rec[0]);
}
$t{sth}->finish;
# Take out all data and write to intermediate files
open(OUT,">../txt/$t{outputf}");
print OUT "filename=$t{outputf}\n";
$t{line1} = join('===',@{ $t{columns_list} });
print OUT $t{table1};
print OUT '===';
print OUT $t{line1};
print OUT "\n";
$t{sth} = $$pref{dbh}->prepare("SELECT * FROM $t{table1}");
$t{sth}->execute;
while ( @rec = $t{sth}->fetchrow_array ) {
if ( $rec[0] == $t{number1} ) {
$t{line1} = join('===',@rec);
print OUT $t{table1};
print OUT '===';
print OUT $t{line1};
print OUT "\n";
}
}
$t{sth}->finish;
close(OUT);
# Close the database
$$pref{dbh}->disconnect;
Generate some database part tables (make_lost_ptables.pl)
use strict;
use DBI;
my(%t,$n,@fld,$pref,@rec);
# Connect to the database
$$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;
}
# Get out the data
open(IN,"../txt/check_ptables.txt") or die "Can't open the file check_ptables.txt.\n";
$t{NO} = 0;
while(<IN>){
if (/^PTABLE/) {
chop;
@fld = split(/===>/);
if ( $fld[1] == 0 ) {
$t{NO}++;
push(@{ $t{ptables} },$fld[2]);
}
}
}
close(IN);
print "NO=$t{NO},$#{ $t{ptables} }\n";
print "ptables=@{ $t{ptables} }\n";
# Generate part table
for $n ( 0 .. $#{ $t{ptables} } ) {
$$pref{ptable1} = $t{ptables}[$n];
($pref) = ptable1($pref);
}
# Close the database
$$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__;
Check Ptables(check_ptables.pl)
use strict;
use DBI;
my(%t,$n,@fld,@rec,$pref);
# Connect to the database
$$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;
}
# Remove the maximum number of ids of main_type1
$t{main_type1id_max} = $$pref{dbh}->selectrow_array("SELECT max(id) FROM main_type1");
# Object file (../txt/)
open(IN,"../txt/");
while(<IN>){
if (/^PT/){
@fld = split(/===/);
$t{plist}{$fld[1]} = $fld[1];
}
}
close(IN);
@{ $t{ptables} } = sort keys %{ $t{plist} };
$t{ptable_list} = join(' ',@{ $t{ptables} });
# Close the database
$$pref{dbh}->disconnect;
open(OUT,">../txt/check_ptables.txt");
for $n ( 1 .. $t{main_type1id_max} ) {
$t{ptable1} = sprintf("%06d",$n);
$t{ptable1} = 'a' . $t{ptable1};
if ( $t{ptable_list} =~ /$t{ptable1}/) {
print OUT "PTABLE===>1===>$t{ptable1}\n";
} else {
print OUT "PTABLE===>0===>$t{ptable1}\n";
}
}
Check TYPE(check_types.pl)
use strict;
use DBI;
my(%t,$n,@fld,$pref,@rec);
# Connect to the database
$$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;
}
# Get out the main_type1 data
$t{sth} = $$pref{dbh}->prepare("SELECT id,name FROM main_type1");
$t{sth}->execute;
while ( @rec = $t{sth}->fetchrow_array ) {
push(@{ $t{id_list} },$rec[0]);
push(@{ $t{name_list} },$rec[1]);
}
$t{sth}->finish;
# Close the database
$$pref{dbh}->disconnect;
open(OUT,">../txt/check_types.txt");
for $n ( 0 .. $#{ $t{id_list} } ) {
$t{id1} = $t{id_list}[$n];
$t{name1} = $t{name_list}[$n];
if ( $t{names}{$t{name1}} ) {
printf OUT ("%04d==>1==>%04d==>%s\n",$t{id1},$t{names}{$t{name1}},$t{name1});
} else {
printf OUT ("%04d==>0==>0000==>%s\n",$t{id1},$t{name1});
}
$t{NO} = $n + 1;
$t{names}{$t{name1}} = $t{NO};
}
close(OUT);
Previous page1234Next pageRead the full text