MySQL操作程序三,enq1的修改(把enq2的ID输入到enq1中,ENQ画面复数供应商显示),total追加
返回
<![CDATA[
# update_enq2_total.pl
# 离线程序计算并写入enq2的total项目
use strict;
use warnings;
use DBI;
my(%t,$n,$n1,$n2,@fld,$pref,@rec);
# 连接数据库
$$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;
}
# 取出有ORDERNO的enq1的id
@{ $t{enq1ids} } = ();
$t{sth} = $$pref{dbh}->prepare("SELECT id FROM order1 where orderno != 'NULL' AND orderno != 'NO'");
$t{sth}->execute;
while ( @rec = $t{sth}->fetchrow_array ) {
push(@{ $t{enq1ids} },$rec[0]);
}
$t{sth}->finish;
#print "$#{ $t{enq1ids} },@{ $t{enq1ids} }\n";
# 取出上述enq1对应的enq2的id
for $n ( 0 .. $#{ $t{enq1ids} } ) {
$t{id1} = $t{enq1ids}[$n];
$t{enq2} = $$pref{dbh}->selectrow_array("SELECT enq2s from enq1 where id = $t{id1}");
@{ $t{enq2s} } = split(/=/,$t{enq2});
for $n1 ( 0 .. $#{ $t{enq2s} } ) { # 不同的购入厂家分别处理
$t{enq21} = $t{enq2s}[$n1];
($t{QTY},$t{price},$t{discount},$t{discount0},$t{disc},$t{charges}) = $$pref{dbh}->selectrow_array("SELECT QTY,price,discount,discount0,disc,charges from enq2 where id = $t{enq21}");
@{ $t{QTYs2} } = split(/==/,$t{QTY});
@{ $t{QTYs} } = ();
for $n2 ( 0 .. $#{ $t{QTYs2} } ) {
$t{QTYs21} = $t{QTYs2}[$n2];
@{ $t{QTYs1} } = split(/=/,$t{QTYs21});
push(@{ $t{QTYs} },@{ $t{QTYs1} });
}
@{ $t{prices} } = split(/=/,$t{price});
if ( $t{discount0} != 100 ) {
@{ $t{discounts} } = split(/=/,$t{discount});
}
$t{total1} = 0;
for $n2 ( 0 .. $#{ $t{QTYs} } ) {
$t{QTY1} = $t{QTYs}[$n2];
$t{price1} = $t{prices}[$n2];
$t{price1} =~ s/\,//g;
$t{price1} =~ s/\s//g;
if ( $t{discount0} != 100 ) {
$t{discount1} = $t{discounts}[$n2];
$t{total1} = $t{total1} + $t{price1}*$t{QTY1}*$t{discount1}/100;
} else {
$t{total1} = $t{total1} + $t{price1}*$t{QTY1};
}
}
if ($t{disc}) {
$t{total1} = $t{total1} + $t{disc};
}
if ($t{charges}) {
$t{total1} = $t{total1} + $t{charges};
}
$t{sql} = 'UPDATE enq2 set total = "';
$t{sql} .= $t{total1} . '" where id = ';
$t{sql} .= $t{enq21};
$t{DO} = $$pref{dbh}->do($t{sql});
print "enq1 id=$t{id1},enq2 id=$t{enq21},OK=>$t{DO}\n";
}
# if ( $t{id1} == 29 ) {
# exit;
# }
}
# 关闭数据库
$$pref{dbh}->disconnect;
__END__;
离线写入enq2的total的程序
---------------------------------------------
1.选择所有有orderno的id
2.抽出有关的enq2
3.按照mscqtn_input.pl同样的处理计算并写入enq2的total
mscqtn_input.pl
-------------------------------------------------------
$t{total} = $$pref{total}+$t{charges}+$t{disc};
# 写入total
$t{sql} = 'UPDATE enq2 set total = "';
$t{sql} .= $t{total} . '" where id = ';
$t{sql} .= $t{enq2_id};
$t{DO} = $self->dbh->do($t{sql});
# 3位插入逗号子程序
$t{total} = commify($t{total});
mysql> show columns from enq2;
+-------------+-----------+------+-----+------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-----------+------+-----+------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| time | date | YES | | 2011-00-00 | |
| enq1id | int(11) | YES | | NULL | |
| LANGUAGEid | int(11) | YES | | NULL | |
| ORIGINid | int(11) | YES | | NULL | |
| PRICEid | int(11) | YES | | NULL | |
| PAYMENTid | int(11) | YES | | NULL | |
| makerid | int(11) | YES | | NULL | |
| DELIVERY | text | YES | | NULL | |
| type1id | text | YES | | NULL | |
| partsid | text | YES | | NULL | |
| QTY | text | YES | | NULL | |
| memo | text | YES | | NULL | |
| price | text | YES | | NULL | |
| discount | text | YES | | NULL | |
| discount0 | int(11) | YES | | NULL | |
| money | int(11) | YES | | NULL | |
| price1 | text | YES | | NULL | |
| makerref | text | YES | | NULL | |
| disc | text | YES | | NULL | |
| charges | text | YES | | NULL | |
| status | int(11) | NO | | 0 | |
| post | char(100) | NO | | NO | |
| arrivaltime | date | NO | | 2030-01-01 | |
| arrivalmemo | text | YES | | NULL | |
+-------------+-----------+------+-----+------------+----------------+
25 rows in set (0.11 sec)
mysql> ALTER TABLE enq2 ADD total text;
Query OK, 25553 rows affected (1.59 sec)
Records: 25553 Duplicates: 0 Warnings: 0
mysql> show columns from enq2;
+-------------+-----------+------+-----+------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-----------+------+-----+------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| time | date | YES | | 2011-00-00 | |
| enq1id | int(11) | YES | | NULL | |
| LANGUAGEid | int(11) | YES | | NULL | |
| ORIGINid | int(11) | YES | | NULL | |
| PRICEid | int(11) | YES | | NULL | |
| PAYMENTid | int(11) | YES | | NULL | |
| makerid | int(11) | YES | | NULL | |
| DELIVERY | text | YES | | NULL | |
| type1id | text | YES | | NULL | |
| partsid | text | YES | | NULL | |
| QTY | text | YES | | NULL | |
| memo | text | YES | | NULL | |
| price | text | YES | | NULL | |
| discount | text | YES | | NULL | |
| discount0 | int(11) | YES | | NULL | |
| money | int(11) | YES | | NULL | |
| price1 | text | YES | | NULL | |
| makerref | text | YES | | NULL | |
| disc | text | YES | | NULL | |
| charges | text | YES | | NULL | |
| status | int(11) | NO | | 0 | |
| post | char(100) | NO | | NO | |
| arrivaltime | date | NO | | 2030-01-01 | |
| arrivalmemo | text | YES | | NULL | |
| total | text | YES | | NULL | |
+-------------+-----------+------+-----+------------+----------------+
26 rows in set (0.02 sec)
]]>
<![CDATA[
增加复数enq2的显示
$t{siyire2} = $self->dbh->selectrow_array("SELECT company FROM makers WHERE id = $t{makerid}"); # EXCEL文件名用
$t{enq2s} = $t{ts}[12];
@{ $t{enq2_ids} } = split(/=/,$t{enq2s});
$t{siyire1} = '';
for $n ( 0 .. $#{ $t{enq2_ids} } ) {
$t{enq2_id} = $t{enq2_ids}[$n];
$t{makerid} = $self->dbh->selectrow_array("SELECT makerid FROM enq2 WHERE id = $t{enq2_id}"); # メーカー番号
$t{siyire11} = $self->dbh->selectrow_array("SELECT company FROM makers WHERE id = $t{makerid}"); #
$t{siyire1} = $t{siyire1} . $t{siyire11} . ';';
}
---------------------------------------------------------
# 把enq2的ID输入到enq1中
use strict;
use DBI;
my(%t,$n,@fld,@rec);
# 连接数据库
$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;
}
# 取得enq2和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};
}
# 把数值代入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;
]]>
列出enq1 ID供选择(该部分已不用,保存下来做参考)
<![CDATA[
# 列出enq1 ID供选择
$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 = (); # 这个初始化非常重要!
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">
<!-- 挑选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选择"><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>
<!-- 挑选enq1 -->
<form action="" method="post">
<input type=text name=word1 value="">
<input type="submit" value="OURREF検索"><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>
]]>
返回