MySQL操作程序十七(mscquo2.pl,mscquo2.htm)
返回
<![CDATA[
# update_quo2_total.pl
# 离线程序计算并写入quo2的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{QTY} = $$pref{dbh}->selectrow_array("SELECT QTY from enq1 where id = $t{id1}");
$t{price} = $$pref{dbh}->selectrow_array("SELECT price from quo2 where id = $t{id1}");
next if $t{price} eq 'P2';
@{ $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});
$t{total1} = 0;
for $n2 ( 0 .. $#{ $t{QTYs} } ) {
$t{QTY1} = $t{QTYs}[$n2];
$t{price1} = $t{prices}[$n2];
$t{total1} = $t{total1} + $t{QTY1}*$t{price1};
}
$t{sql} = 'UPDATE quo2 set total = "';
$t{sql} .= $t{total1} . '" where id = ';
$t{sql} .= $t{id1};
$t{DO} = $$pref{dbh}->do($t{sql});
print "enq1 id=$t{id1},OK=>$t{DO}\n";
}
# 关闭数据库
$$pref{dbh}->disconnect;
__END__;
离线写入quo2的total的程序
---------------------------------------------
1.选择所有有orderno的id
2.按照mscquo2.pl同样的处理计算并写入quo2的total
mscquo2.pl
---------------------------------------------
# 写入total
$t{sql} = 'UPDATE quo2 set total ="';
$t{sql} .= $$pref{total} . '" where id = ';
$t{sql} .= $t{quo2_id};
$t{DO} = $self->dbh->do($t{sql});
]]>
<![CDATA[
增加SGD
money.sql
DROP TABLE IF EXISTS money;
CREATE TABLE money
(
id INT AUTO_INCREMENT,
English char(30),
Chinese char(30),
PRIMARY KEY (id)
);
INSERT INTO money (English,Chinese) VALUES("JPY","日元");
INSERT INTO money (English,Chinese) VALUES("USD","美元");
INSERT INTO money (English,Chinese) VALUES("EUR","欧元");
INSERT INTO money (English,Chinese) VALUES("GBP","英镑");
INSERT INTO money (English,Chinese) VALUES("RMB","人民币");
INSERT INTO money (English,Chinese) VALUES("SGD","新加坡元");
c:\database\sql>mysql -u cbuser -p cookbook < money.sql
Enter password: ******
]]>
<![CDATA[
台塑单子增加输入"已提供業界最低價,無法再降."
fpmc_ok.sql
----------------------------
DROP TABLE IF EXISTS fpmc_ok;
CREATE TABLE fpmc_ok
(
id INT AUTO_INCREMENT,
fpmc1 varchar(20),
PRIMARY KEY (id)
);
c:\database\sql>mysql cookbook < fpmc_ok.sql -u cbuser -p
Enter password: ******
INSERT INTO fpmc_ok (fpmc1) VALUES("OK");
INSERT INTO fpmc_ok (fpmc1) VALUES("NG");
mysql> select * from fpmc_ok;
+----+-------+
| id | fpmc1 |
+----+-------+
| 1 | OK |
| 2 | NG |
+----+-------+
2 rows in set (0.00 sec)
mysql> ALTER TABLE quo2 DROP ti_ok;
mysql> ALTER TABLE quo2 ADD ti_ok INT DEFAULT 2;
mysql> show columns from quo2;
+-----------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| time | date | YES | | NULL | |
| ORIGINid | int(11) | YES | | NULL | |
| PRICEid | int(11) | YES | | NULL | |
| PAYMENTid | int(11) | YES | | NULL | |
| DELIVERY | text | YES | | NULL | |
| money | int(11) | YES | | NULL | |
| percent0 | int(11) | YES | | NULL | |
| discount0 | int(11) | YES | | NULL | |
| percent | text | YES | | NULL | |
| discount | text | YES | | NULL | |
| price | text | YES | | NULL | |
| D_FEE | text | YES | | NULL | |
| total | text | YES | | NULL | |
| memo | text | YES | | NULL | |
| disc0 | int(11) | YES | | NULL | |
| disc | text | YES | | NULL | |
| price0 | text | YES | | NULL | |
| pi_ok | int(11) | YES | | 1 | |
| ti_ok | int(11) | YES | | 2 | |
+-----------+---------+------+-----+---------+----------------+
20 rows in set (0.09 sec)
mscquo2.htm
--------------------------------------------------------------
<select name="TOK1">
<TMPL_LOOP NAME="TOK_LOOP">
<TMPL_VAR NAME="line1">
</TMPL_LOOP>
</select>
mscquo2.pl
--------------------------------------------------------------
# TOK输出
$t{sth} = $self->dbh->prepare("select id, fpmc1 from fpmc_ok");
$t{sth}->execute;
while (@rec = $t{sth}->fetchrow_array) {
$row_ref = (); # 这个初始化非常重要!
if ( $rec[0] == $t{TOKid} ) {
$t{line1} = '<option value="' . $rec[0] . '" selected="selected">' . $rec[1] . '</option>';
} else {
$t{line1} = '<option value="' . $rec[0] . '">' . $rec[1] . '</option>';
}
$$row_ref{line1} = $t{line1};
push(@tok_loop, $row_ref);
}
$t{sth}->finish;
$t{template}->param(POK_LOOP => \@pok_loop);
==>
$t{template}->param(TOK_LOOP => \@tok_loop);
$t{POKid} = $t{e}[18];
==>
$t{TOKid} = $t{e}[19];
# POKid
$t{POK1} = $t{q}->param("POK1");
$t{sql} = 'UPDATE quo2 set pi_ok = "';
$t{sql} .= $t{POK1} . '" where id = ';
$t{sql} .= $t{quo2_id};
$t{DO} = $self->dbh->do($t{sql});
==>
# TOKid
$t{TOK1} = $t{q}->param("TOK1");
$t{sql} = 'UPDATE quo2 set ti_ok = "';
$t{sql} .= $t{TOK1} . '" where id = ';
$t{sql} .= $t{quo2_id};
$t{DO} = $self->dbh->do($t{sql});
]]>
<![CDATA[
THE PRICE IS NOT INCLUDING PACKING & HANDING CHARGE.
改为
THE PRICE IS NOT INCLUDING PACKING & BANK CHARGE.
-------------------------------------------------------------
$$pref{total} = $$pref{total} + $t{price2};
==>增加
if ( $t{price2} =~ /\./ ) {
$$pref{total} = sprintf("%.2f",$$pref{total});
}
]]>
<![CDATA[
# $t{line1} .= '"0>' . '</td><td>';
修改如下==>
$t{line1} .= '0">' . '</td><td>';
修改以后,自动插入0
--------------------------------------------------------
mysql> select * from quo2 where id = 12398\G
*************************** 1. row ***************************
id: 12398
time: 2012-02-13
ORIGINid: 1
PRICEid: 1
PAYMENTid: 5
DELIVERY: NULL
money: 1
percent0: 115
discount0: 100
percent: 115=115=115=115
discount: D
price: 25=0=11=23
D_FEE: NULL
total: NULL
memo: NULL
disc0: 100
disc: D0
price0: 22=0=10=20
pi_ok: 1
1 row in set (0.00 sec)
push(@{ $t{prices} },$t{list}{$t{tts1}}{$t{pps1s1}});
修改如下==>
if ($t{list}{$t{tts1}}{$t{pps1s1}}) {
push(@{ $t{prices} },$t{list}{$t{tts1}}{$t{pps1s1}});
} else {
push(@{ $t{prices} },'0');
}
比如说,enq1有4个零件,但是enq2只有3个零件可以报出来,下面的price0处该追加零。
--------------------------------------------------------
mysql> select * from quo2 where id = 12398\G
*************************** 1. row ***************************
id: 12398
time: 2012-01-16
ORIGINid: 1
PRICEid: 1
PAYMENTid: 5
DELIVERY: NULL
money: 1
percent0: 115
discount0: 100
percent: P1
discount: D
price: P2
D_FEE: NULL
total: NULL
memo: NULL
disc0: 100
disc: D0
price0: 22=33=44=
pi_ok: 1
1 row in set (0.00 sec)
]]>
<![CDATA[
[error] [client 127.0.0.1] Use of uninitialized value in concatenation (.) or string at ./pro/mscquo2.pl line 725,
725: $t{line1} .= $t{pe}[$t{NO}-1] . '">' . '</td></tr>';
跟quo2 table的percent项目有关(注意其赋值情况)
]]>
<![CDATA[
[error] [client 127.0.0.1] Argument "" isn't numeric in numeric ne (!=) at ./pro/mscquo2.pl line 557
[error] [client 127.0.0.1] Argument "" isn't numeric in numeric ne (!=) at ./pro/mscquo2.pl line 591
[error] [client 127.0.0.1] Argument "" isn't numeric in numeric ne (!=) at ./pro/mscquo2.pl line 773
557:if ( $t{A1} != 0 ) {
591:if ( $t{pl1}[$n] != 0 ) {
773:if ( $t{pl1}[$n] != 0 ) {
分析:
mysql> select partsid from enq1 where id = 12399;
+----------+
| partsid |
+----------+
| 179==3=4 |
+----------+
修改如下:
557:if ( $t{A1} ) {
591:if ( $t{pl1}[$n] ) {
773:if ( $t{pl1}[$n] ) {
[error] [client 127.0.0.1] Argument "P0" isn't numeric in multiplication (*) at ./pro/mscquo2.pl line 108, <CONFIG> line 11.
[error] [client 127.0.0.1] Use of uninitialized value in multiplication (*) at ./pro/mscquo2.pl line 869, <CONFIG> line 11.,
[error] [client 127.0.0.1] Use of uninitialized value in concatenation (.) or string at ./pro/mscquo2.pl line 882,
108: $t{ppp1} = int($t{prices}[$n]*$t{pe}[$n]/100);
869: $t{price2} = $t{pri}[$t{NO}-1]*$t{Q1};
882: $t{line1} .= $t{pri}[$t{NO}-1] . '">' . '</td><td>';
==>修改如下
if ( $t{price0} ne 'P0' ) { # 追加
if ( $t{pri}[$t{NO}-1] ) {
$t{price2} = $t{pri}[$t{NO}-1]*$t{Q1};
} else {
$t{price2} = 0;
}
if ($t{pri}[$t{NO}-1]) {
$t{line1} .= $t{pri}[$t{NO}-1] . '">' . '</td><td>';
} else {
$t{line1} .= '"0>' . '</td><td>';
}
]]>
- 增加"THE PRICE IS NOT INCLUDING PACKING & HANDING CHARGE."按钮选择(EXCEL自动写入)
quo2.htm,default,选择功能。
mscquo2.pl/write_excel.pl
quo2表格增加项目:pi_ok=1,写入,default是1;pi_ok=0,不写
增加表格packing_ok,1:OK;2:NG
- 最后的零件的价格是0的话,数据库的长度出错。不能再现。似乎已解决?
<![CDATA[
# ORIGINid
$t{ORIGIN1} = $t{q}->param("ORIGIN1");
$t{sql} = 'UPDATE quo2 set ORIGINid = "';
$t{sql} .= $t{ORIGIN1} . '" where id = ';
$t{sql} .= $t{quo2_id};
$t{DO} = $self->dbh->do($t{sql});
==>
# POKid
$t{POK1} = $t{q}->param("POK1");
$t{sql} = 'UPDATE quo2 set pi_ok = "';
$t{sql} .= $t{POK1} . '" where id = ';
$t{sql} .= $t{quo2_id};
$t{DO} = $self->dbh->do($t{sql});
==>
mysql> update quo2 set pi_ok = 2 where id = 12395;
Query OK, 1 row affected (0.08 sec)
Rows matched: 1 Changed: 1 Warnings: 0
# ORIGIN输出
$t{sth} = $self->dbh->prepare("select id, ORIGIN from enq2_origin");
$t{sth}->execute;
while (@rec = $t{sth}->fetchrow_array) {
$row_ref = (); # 这个初始化非常重要!
if ( $rec[0] == $t{ORIGINid} ) {
$t{line1} = '<option value="' . $rec[0] . '" selected="selected">' . $rec[1] . '</option>';
} else {
$t{line1} = '<option value="' . $rec[0] . '">' . $rec[1] . '</option>';
}
$$row_ref{line1} = $t{line1};
push(@origin_loop, $row_ref);
}
$t{sth}->finish;
==>
# POK输出
$t{sth} = $self->dbh->prepare("select id, PACKING1 from packing_ok");
$t{sth}->execute;
while (@rec = $t{sth}->fetchrow_array) {
$row_ref = (); # 这个初始化非常重要!
if ( $rec[0] == $t{POKid} ) {
$t{line1} = '<option value="' . $rec[0] . '" selected="selected">' . $rec[1] . '</option>';
} else {
$t{line1} = '<option value="' . $rec[0] . '">' . $rec[1] . '</option>';
}
$$row_ref{line1} = $t{line1};
push(@pok_loop, $row_ref);
}
$t{sth}->finish;
$t{template}->param(ORIGIN_LOOP => \@origin_loop);
==>
$t{template}->param(POK_LOOP => \@pok_loop);
$t{ORIGINid} = $t{e}[2];
==>
$t{POKid} = $t{e}[18];
<select name="ORIGIN1">
<TMPL_LOOP NAME="ORIGIN_LOOP">
<TMPL_VAR NAME="line1">
</TMPL_LOOP>
</select>
<select name="POK1">
<TMPL_LOOP NAME="POK_LOOP">
<TMPL_VAR NAME="line1">
</TMPL_LOOP>
</select>
mysql> select * from packing_ok;
+----+----------+
| id | PACKING1 |
+----+----------+
| 1 | OK |
| 2 | NG |
+----+----------+
2 rows in set (0.00 sec)
c:\database\sql>mysql cookbook < packing_ok.sql -u cbuser -p
Enter password: ******
packing_ok.sql
DROP TABLE IF EXISTS packing_ok;
CREATE TABLE packing_ok
(
id INT AUTO_INCREMENT,
PACKING1 varchar(20),
PRIMARY KEY (id)
);
INSERT INTO packing_ok (PACKING1) VALUES("OK");
INSERT INTO packing_ok (PACKING1) VALUES("NG");
mysql> ALTER TABLE quo2 ADD pi_ok INT DEFAULT 1;
Query OK, 12395 rows affected (1.38 sec)
Records: 12395 Duplicates: 0 Warnings: 0
mysql> show columns from quo2;
+-----------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| time | date | YES | | NULL | |
| ORIGINid | int(11) | YES | | NULL | |
| PRICEid | int(11) | YES | | NULL | |
| PAYMENTid | int(11) | YES | | NULL | |
| DELIVERY | text | YES | | NULL | |
| money | int(11) | YES | | NULL | |
| percent0 | int(11) | YES | | NULL | |
| discount0 | int(11) | YES | | NULL | |
| percent | text | YES | | NULL | |
| discount | text | YES | | NULL | |
| price | text | YES | | NULL | |
| D_FEE | text | YES | | NULL | |
| total | text | YES | | NULL | |
| memo | text | YES | | NULL | |
| disc0 | int(11) | YES | | NULL | |
| disc | text | YES | | NULL | |
| price0 | text | YES | | NULL | |
| pi_ok | int(11) | YES | | 1 | |
+-----------+---------+------+-----+---------+----------------+
19 rows in set (0.05 sec)
mysql> show columns from quo2;
+-----------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| time | date | YES | | NULL | |
| ORIGINid | int(11) | YES | | NULL | |
| PRICEid | int(11) | YES | | NULL | |
| PAYMENTid | int(11) | YES | | NULL | |
| DELIVERY | text | YES | | NULL | |
| money | int(11) | YES | | NULL | |
| percent0 | int(11) | YES | | NULL | |
| discount0 | int(11) | YES | | NULL | |
| percent | text | YES | | NULL | |
| discount | text | YES | | NULL | |
| price | text | YES | | NULL | |
| D_FEE | text | YES | | NULL | |
| total | text | YES | | NULL | |
| memo | text | YES | | NULL | |
| disc0 | int(11) | YES | | NULL | |
| disc | text | YES | | NULL | |
| price0 | text | YES | | NULL | |
+-----------+---------+------+-----+---------+----------------+
18 rows in set (0.14 sec)
]]>
<![CDATA[
台塑输入时,自动选择FCA==>已取消
mysql> select * from enq2_price;
+----+------------------+
| id | PRICE |
+----+------------------+
| 1 | EXW KOBE (OSAKA) |
| 2 | EXW JAPAN |
| 3 | FOB KOBE (OSAKA) |
| 4 | FCA |
| 5 | CFR KAOHSIUNG |
| 6 | EXW CHINA |
| 7 | EXW EUROPE |
| 8 | EXW KOREA |
+----+------------------+
8 rows in set (0.00 sec)
]]>
<![CDATA[
[Sun Sep 12 12:12:46 2010] [error] [client 127.0.0.1] Argument "C" isn't numeric in numeric ne (!=) at ./pro/mscquo2.pl line 559, <CONFIG> line 11., referer: http://localhost/cgi-bin/msc_start.cgi
[Sun Sep 12 12:12:46 2010] [error] [client 127.0.0.1] Argument "C" isn't numeric in numeric ne (!=) at ./pro/mscquo2.pl line 739, <CONFIG> line 11.,
558:for $n ( 0 .. $#{ $t{pl1} } ) {
559: if ( $t{pl1}[$n] != 0 ) {
$t{pl2}++;
}
}
738:for $n ( 0 .. $#{ $t{pl1} } ) {
739: if ( $t{pl1}[$n] != 0 ) {
$t{pl2}++;
}
}
修改如下:
$t{pl2} = 0;
if ( $t{partsid} ne 'C' ) {
@{ $t{pl1} } = split(/=/,$t{partsid});
for $n ( 0 .. $#{ $t{pl1} } ) {
if ( $t{pl1}[$n] != 0 ) {
$t{pl2}++;
}
}
}
]]>
- 取消pat=select1,全部合并到pat=r_quo2(从r_header复制过来)
- 新:pat=select1,2,3,4,5分别对应ORIGIN,PRICE,PAYMENT,DELIVERY TIME,memo
- 旧:pat=select1,选择一个项目并更新
- QTN画面,输入时确定按钮只要按最后一个(前面几个自动一起执行)
<![CDATA[
mscquo2.htm
新
<select name="ORIGIN1">
<TMPL_LOOP NAME="ORIGIN_LOOP">
<TMPL_VAR NAME="line1">
</TMPL_LOOP>
</select>
旧
<input type="submit" value="选择"><p>
<input type="hidden" name="id" value="<TMPL_VAR NAME="quo2_id">">
<input type="hidden" name="item" value="ORIGINid">
<input type="hidden" name="select" value="ORIGIN1">
<input type="hidden" name="pat" value="select1">
<input type="hidden" name="rm" value="modequo2">
</form>
mscquo2.pl
增加
#---------读几个项目并更新
} elsif ( $t{pat} eq 'r_quo2' ) {
# ORIGINid
$t{ORIGIN1} = $t{q}->param("ORIGIN1"); #直接读即可!!!
$t{sql} = 'UPDATE quo2 set ORIGINid = "';
$t{sql} .= $t{ORIGIN1} . '" where id = ';
$t{sql} .= $t{quo2_id};
$t{DO} = $self->dbh->do($t{sql});
# PRICEid
$t{PRICE1} = $t{q}->param("PRICE1");
$t{sql} = 'UPDATE quo2 set PRICEid = "';
$t{sql} .= $t{PRICE1} . '" where id = ';
$t{sql} .= $t{quo2_id};
$t{DO} = $self->dbh->do($t{sql});
# PAYMENTid
$t{PAYMENT1} = $t{q}->param("PAYMENT1");
$t{sql} = 'UPDATE quo2 set PAYMENTid = "';
$t{sql} .= $t{PAYMENT1} . '" where id = ';
$t{sql} .= $t{quo2_id};
$t{DO} = $self->dbh->do($t{sql});
# DELIVERY
$t{content1} = $t{q}->param("content1");
$t{sql} = 'UPDATE quo2 set DELIVERY = "';
$t{sql} .= $t{content1} . '" where id = ';
$t{sql} .= $t{quo2_id};
$t{DO} = $self->dbh->do($t{sql});
# memo
$t{content2} = $t{q}->param("content2");
$t{sql} = 'UPDATE quo2 set memo = "';
$t{sql} .= $t{content2} . '" where id = ';
$t{sql} .= $t{quo2_id};
$t{DO} = $self->dbh->do($t{sql});
删除
#---------选择一个项目并更新
} elsif ( $t{pat} eq 'select1' ) {
$t{select} = $t{q}->param("select");
$t{ORIGIN1} = $t{q}->param($t{select});
$t{item} = $t{q}->param("item");
$t{sql} = 'UPDATE quo2 set ' . $t{item} . ' = "';
$t{sql} .= $t{ORIGIN1} . '" where id = ' . $t{quo2_id};
$t{DO} = $self->dbh->do($t{sql});
#---------读一个项目并更新
} elsif ( $t{pat} eq 'r_header' ) {
$t{table} = $t{q}->param("table");
$t{item} = $t{q}->param("item");
$t{content} = $t{q}->param("content");
$t{sql} = 'UPDATE ' . $t{table} . ' set ' . $t{item} . ' = "';
$t{sql} .= $t{content} . '" where id = ';
$t{sql} .= $t{quo2_id};
$t{DO} = $self->dbh->do($t{sql});]]>
<![CDATA[
Argument "" isn't numeric in numeric ne (!=) at ./pro/mscquo2.pl line 505
# 取得enq1零件的长度
$$pref{A} = $t{enq1s}[7];
($pref) = get_length($pref);
sub get_length {
my ($pref) = @_;
my (%t,$n);
@{ $t{As} } = split(/=/,$$pref{A});
$$pref{leng} = 0;
if ( $t{As}[0] eq 'C' ) {
return ($pref);
}
for $n ( 0 .. $#{ $t{As} } ) {
$t{A1} = $t{As}[$n];
if ( $t{A1} != 0 ) {
$$pref{leng}++;
}
}
return ($pref);
}
]]>
<![CDATA[
show_parts2:图号显示,注意图号集合从零开始(mscenq1.pl的show_parts也一样)
$t{DWG1} = $t{DWGs}[$t{dwg1}-1];
if ($t{DWG1} eq 'XXXDWG') {
$t{DWG1} = 'NO';
}
]]>
返回