MySQL操作程序二十(mscorder1.pl,mscorder1.htm)
返回
增加消费税设定
- order1.htm增加显示
- 参照QTN,quo2.htm,Packing, 做无/有的选择。order1增加tax项目
- order1.pl增加选择功能
- 可设定具体的消费税数量
- write_excel.pl增加输出功能
- 确认统计画面mscsummary02.htm/mscsummary02.pl
- order1.htm画面的消费税应对整个费用(包括运费等)乘消费税率!
<![CDATA[
mysql> ALTER TABLE order1 MODIFY post TEXT;
Query OK, 27508 rows affected (2.52 sec)
Records: 27508 Duplicates: 0 Warnings: 0
mysql> show columns from order1;
+-----------------+---------+------+-----+------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+---------+------+-----+------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| time | date | YES | | NULL | |
| orderno | text | YES | | NULL | |
| ORIGINid | int(11) | YES | | NULL | |
| PRICEid | int(11) | YES | | NULL | |
| PAYMENTid | int(11) | YES | | NULL | |
| DELIVERY | text | YES | | NULL | |
| memo | text | YES | | NULL | |
| consignee | int(11) | YES | | NULL | |
| changeprice | text | YES | | NULL | |
| discount | text | YES | | NULL | |
| total | text | YES | | NULL | |
| LANGUAGEid | int(11) | YES | | NULL | |
| packing_charge | text | YES | | NULL | |
| freight_charges | text | YES | | NULL | |
| delivery_charge | text | YES | | NULL | |
| delivery_place | text | YES | | NULL | |
| disc | text | YES | | NULL | |
| shipping | text | YES | | NULL | |
| packing | text | YES | | NULL | |
| packing_info | text | YES | | NULL | |
| post | text | YES | | NULL | |
| posttime | date | NO | | 2030-01-01 | |
| paytime | date | NO | | 2030-01-01 | |
| status | int(11) | NO | | 0 | |
| arrivaltime | date | NO | | 2030-01-01 | |
| arrivalmemo | text | YES | | NULL | |
| size | text | YES | | NULL | |
| weight | text | YES | | NULL | |
| paymemo | text | YES | | NULL | |
| invoicetime | date | NO | | 2030-01-01 | |
| tax_ok | int(11) | YES | | 1 | |
+-----------------+---------+------+-----+------------+----------------+
32 rows in set (0.00 sec)
----------------------------------------------------------------
追加其他费用
$t{total1} =($t{total}+$t{packing_charge}+$t{delivery_charge}+$t{freight_charges})*(1+$t{addtax1}/100);
注意:消费税是JPY的total,下面的Discount和个别修正没有关系!
tax_ok.sql
--------------------------------------------------------------
DROP TABLE IF EXISTS tax_ok;
CREATE TABLE tax_ok
(
id INT AUTO_INCREMENT,
tax1 varchar(20),
PRIMARY KEY (id)
);
INSERT INTO tax_ok (tax1) VALUES("0");
INSERT INTO tax_ok (tax1) VALUES("5");
INSERT INTO tax_ok (tax1) VALUES("8");
INSERT INTO tax_ok (tax1) VALUES("10");
c:\database\sql>mysql cookbook < tax_ok.sql -u cbuser -p
Enter password: ******
c:\database\sql>mysql -u cbuser -p
Enter password: ******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.0.45-community-nt MySQL Community Edition (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> use cookbook
Database changed
mysql> select * from tax_ok;
+----+------+
| id | tax1 |
+----+------+
| 1 | 0 |
| 2 | 5 |
| 3 | 8 |
| 4 | 10 |
+----+------+
4 rows in set (0.01 sec)
mysql> ALTER TABLE order1 DROP tax_ok;
Query OK, 23278 rows affected (2.13 sec)
Records: 23278 Duplicates: 0 Warnings: 0
mysql> show columns from order1;
+-----------------+-----------+------+-----+------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+-----------+------+-----+------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| time | date | YES | | NULL | |
| orderno | text | YES | | NULL | |
| ORIGINid | int(11) | YES | | NULL | |
| PRICEid | int(11) | YES | | NULL | |
| PAYMENTid | int(11) | YES | | NULL | |
| DELIVERY | text | YES | | NULL | |
| memo | text | YES | | NULL | |
| consignee | int(11) | YES | | NULL | |
| changeprice | text | YES | | NULL | |
| discount | text | YES | | NULL | |
| total | text | YES | | NULL | |
| LANGUAGEid | int(11) | YES | | NULL | |
| packing_charge | text | YES | | NULL | |
| freight_charges | text | YES | | NULL | |
| delivery_charge | text | YES | | NULL | |
| delivery_place | text | YES | | NULL | |
| disc | text | YES | | NULL | |
| shipping | text | YES | | NULL | |
| packing | text | YES | | NULL | |
| packing_info | text | YES | | NULL | |
| post | char(100) | NO | | NO | |
| posttime | date | NO | | 2030-01-01 | |
| paytime | date | NO | | 2030-01-01 | |
| status | int(11) | NO | | 0 | |
| arrivaltime | date | NO | | 2030-01-01 | |
| arrivalmemo | text | YES | | NULL | |
| size | text | YES | | NULL | |
| weight | text | YES | | NULL | |
| paymemo | text | YES | | NULL | |
| invoicetime | date | NO | | 2030-01-01 | |
+-----------------+-----------+------+-----+------------+----------------+
31 rows in set (0.02 sec)
mysql> ALTER TABLE order1 ADD tax_ok INT DEFAULT 1;
Query OK, 23278 rows affected (1.50 sec)
Records: 23278 Duplicates: 0 Warnings: 0
mysql> show columns from order1;
+-----------------+-----------+------+-----+------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+-----------+------+-----+------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| time | date | YES | | NULL | |
| orderno | text | YES | | NULL | |
| ORIGINid | int(11) | YES | | NULL | |
| PRICEid | int(11) | YES | | NULL | |
| PAYMENTid | int(11) | YES | | NULL | |
| DELIVERY | text | YES | | NULL | |
| memo | text | YES | | NULL | |
| consignee | int(11) | YES | | NULL | |
| changeprice | text | YES | | NULL | |
| discount | text | YES | | NULL | |
| total | text | YES | | NULL | |
| LANGUAGEid | int(11) | YES | | NULL | |
| packing_charge | text | YES | | NULL | |
| freight_charges | text | YES | | NULL | |
| delivery_charge | text | YES | | NULL | |
| delivery_place | text | YES | | NULL | |
| disc | text | YES | | NULL | |
| shipping | text | YES | | NULL | |
| packing | text | YES | | NULL | |
| packing_info | text | YES | | NULL | |
| post | char(100) | NO | | NO | |
| posttime | date | NO | | 2030-01-01 | |
| paytime | date | NO | | 2030-01-01 | |
| status | int(11) | NO | | 0 | |
| arrivaltime | date | NO | | 2030-01-01 | |
| arrivalmemo | text | YES | | NULL | |
| size | text | YES | | NULL | |
| weight | text | YES | | NULL | |
| paymemo | text | YES | | NULL | |
| invoicetime | date | NO | | 2030-01-01 | |
| tax_ok | int(11) | YES | | 1 | |
+-----------------+-----------+------+-----+------------+----------------+
32 rows in set (0.00 sec)
order1.htm
--------------------------------------------------------------
<select name="TAX1">
<TMPL_LOOP NAME="TAX_LOOP">
<TMPL_VAR NAME="line1">
</TMPL_LOOP>
</select>
<form action="" method="post">
<input type="submit" value="消費税">
<input type="hidden" name="pat" value="add_tax">
<input type="hidden" name="table" value="order1">
<input type="hidden" name="item" value="tax_ok">
<input type="hidden" name="id" value=<TMPL_VAR NAME="order1_id">>
<input type="hidden" name="total" value="<TMPL_VAR NAME="total">">
<input type="hidden" name="rm" value="modeorder1"></td>
</form>
order1.pl
--------------------------------------------------------------
$t{TAX1} = $t{e}[31];
# TAXOK输出
$t{sth} = $self->dbh->prepare("select id, tax1 from tax_ok");
$t{sth}->execute;
while (@rec = $t{sth}->fetchrow_array) {
$row_ref = (); # 这个初始化非常重要!
if ( $rec[0] == $t{TAX1} ) {
$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(@tax_loop, $row_ref);
}
$t{sth}->finish;
$t{template}->param(TAX_LOOP => \@tax_loop);
} elsif ( $t{item} eq 'add_tax' ) { # Add消費税
$t{total} = $t{q}->param("total");
$t{TAX1} = $t{q}->param("TAX1");
$t{addtax1} = $self->dbh->selectrow_array("SELECT tax1 FROM tax_ok WHERE id = $t{TAX1}");
$t{packing_charge} = $self->dbh->selectrow_array("SELECT packing_charge FROM order1 WHERE id = $t{order1_id}");
$t{delivery_charge} = $self->dbh->selectrow_array("SELECT delivery_charge FROM order1 WHERE id = $t{order1_id}");
$t{freight_charges} = $self->dbh->selectrow_array("SELECT freight_charges FROM order1 WHERE id = $t{order1_id}");
$t{total1} = $t{total}*(1+$t{addtax1}/100);
$t{moneyid} = $self->dbh->selectrow_array("SELECT money FROM quo2 WHERE id = $t{order1_id}");
if ( $t{moneyid} == 1 ) {
$t{total1} = int($t{total1}+0.5);
} else {
$t{total1} = int(($t{total1}*100+0.5))/100;
$t{total1} = sprintf("%.2f",$t{total1});
}
$t{sql} = 'UPDATE order1 set total = "';
$t{sql} .= $t{total1} . '" where id = ';
$t{sql} .= $t{order1_id};
$t{DO} = $self->dbh->do($t{sql});
$t{sql} = 'UPDATE order1 set tax_ok = "';
$t{sql} .= $t{TAX1} . '" where id = ';
$t{sql} .= $t{order1_id};
$t{DO} = $self->dbh->do($t{sql});
]]>
<![CDATA[
合并"梱包サイズ和梱包重量"
size
weight
==> size
mscorder1.htm取消如下段落
<form action="" method="post">
<input type="submit" value="梱包重量">
<input type=text size=50 name="content" value="<TMPL_VAR NAME="weight">">
<input type="hidden" name="pat" value="r_header">
<input type="hidden" name="table" value="order1">
<input type="hidden" name="item" value="weight">
<input type="hidden" name="id" value=<TMPL_VAR NAME="order1_id">>
<input type="hidden" name="rm" value="modeorder1">
</form>
mysql> show columns from order1;
+-----------------+-----------+------+-----+------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+-----------+------+-----+------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| time | date | YES | | NULL | |
| orderno | text | YES | | NULL | |
| ORIGINid | int(11) | YES | | NULL | |
| PRICEid | int(11) | YES | | NULL | |
| PAYMENTid | int(11) | YES | | NULL | |
| DELIVERY | text | YES | | NULL | |
| memo | text | YES | | NULL | |
| consignee | int(11) | YES | | NULL | |
| changeprice | text | YES | | NULL | |
| discount | text | YES | | NULL | |
| total | text | YES | | NULL | |
| LANGUAGEid | int(11) | YES | | NULL | |
| packing_charge | text | YES | | NULL | |
| freight_charges | text | YES | | NULL | |
| delivery_charge | text | YES | | NULL | |
| delivery_place | text | YES | | NULL | |
| disc | text | YES | | NULL | |
| shipping | text | YES | | NULL | |
| packing | text | YES | | NULL | |
| packing_info | text | YES | | NULL | |
| post | char(100) | NO | | NO | |
| posttime | date | NO | | 2030-01-01 | |
| paytime | date | NO | | 2030-01-01 | |
| status | int(11) | NO | | 0 | |
| arrivaltime | date | NO | | 2030-01-01 | |
| arrivalmemo | text | YES | | NULL | |
| size | text | YES | | NULL | |
| weight | text | YES | | NULL | |
| paymemo | text | YES | | NULL | |
| invoicetime | date | NO | | 2030-01-01 | |
+-----------------+-----------+------+-----+------------+----------------+
31 rows in set (0.19 sec)
]]>
<![CDATA[
[Thu Jan 03 20:34:58 2013] [error] [client 127.0.0.1] Use of uninitialized value in pattern match (m//) at ./pro/mscorder1.pl line 747, <CONFIG> line 11., referer: http://localhost/cgi-bin/msc_start.cgi
$$pref{total} = $$pref{total} + $t{price2};
747: if ( $t{price1} =~ /\./ ) {
$$pref{total} = sprintf("%.2f",$$pref{total});
}
$t{price1}是空值!
-----------------------------------------------------------
总价的USD的小数点没有
$t{price2} = $t{prices}[$t{NO}-1]*$t{Q1};
==>乘法操作后,再进行一次小数点位数指定操作!
$t{price2} = $t{price1}*$t{Q1};
if ( $t{price1} =~ /\./ ) {
$t{price2} = sprintf("%.2f",$t{price2});
}
总价显示也修改
$$pref{total} = $$pref{total} + $t{price2};
if ( $t{price1} =~ /\./ ) {
$$pref{total} = sprintf("%.2f",$$pref{total});
}
]]>
- 追加packing时的处理
- 追加shipping时的处理
- ************************************************************
- 修改order1的orderno项目,NO/CANCEL/客户order编号功能=>修改现有数据的程序(没有数字的赋予NO)
- order1的orderno的DEFAULT是NO
- order1.htm画面同时多个OURREF的post输入,自动写入复数个OURREF=>就写一个?
- order1のweight,自动写入复数个OURREF=>就写一个?
- order1のsize,自动写入复数个OURREF=>就写一个?
- order1.htm画面增加,把"入荷時期"と"入荷メモ",arrivaltime/arrivalmemo
- order1.htm画面修改,把"納入先/時期"改为"納入時期,納入先"
- 梱包情報=>packing_info
- order1增加arrivaltime/arrivalmemo
- order1增加重量项目(weight)
- order1增加尺寸项目(size)
- 增加order1的posttime项目(出荷の日付)(放在post的后面)
- ==============没有ORDER;status=0;time=order1该列的创建时间;paytime:2010-00-00;orderno:空白;post=NO
- 未收到货款,未发货的ORDER;status=1;time=orderno输入时的时间;paytime:2010-00-00;orderno:客户order编号;post=NO
- 未收到货款,已发货的ORDER;status=2;time=orderno输入时的时间;paytime:2010-00-00;orderno:客户order编号;post=发货地点
- 已收到货款,未发货的ORDER;status=3;time=orderno输入时的时间;paytime:收款时间;orderno:客户order编号;post=NO
- 已收到货款,已发货的ORDER;status=4;time=orderno输入时的时间;paytime:收款时间;orderno:客户order编号;post=发货地点
<![CDATA[
追加packing时,同时在738/524的packing栏追加M670,因为738/524一齐packing。
mysql> select id, packing from order1 where length(packing) > 5;
+-------+-------------------------+
| id | packing |
+-------+-------------------------+
| 670 | 738=524 |
| 4815 | 6036=6891 |
| 6203 | 6505=6712=6714 |
| 6421 | 6665=6430=6732 |
| 6970 | 7659=7657 |
| 7028 | 10012=9968 |
| 7111 | 7112=7115 |
| 7283 | 7342=7344 |
| 7522 | 7526=7527=7528=7529 |
| 8185 | 5617=5618 |
| 8487 | 8499=8543 |
| 8630 | 8634=8643 |
| 11069 | 10959=10821=10769=11038 |
+-------+-------------------------+
13 rows in set (0.01 sec)
追加shipping时,同时在11330/11331/11332的shipping栏追加M12110,因为11330/11331/11332和12110一齐shipping。
mysql> select id, shipping,length(shipping) from order1 where id = 12110;
+-------+-------------------+------------------+
| id | shipping | length(shipping) |
+-------+-------------------+------------------+
| 12110 | 11330=11331=11332 | 17 |
+-------+-------------------+------------------+
1 row in set (0.08 sec)
mysql> update order1 set orderno = 'NO' where length(orderno) < 2;
Query OK, 9828 rows affected (0.44 sec)
Rows matched: 9828 Changed: 9828 Warnings: 0
mysql> select id,orderno from order1 where length(orderno) < 2;
Empty set (0.02 sec)
mysql> show columns from order1;
+-----------------+-----------+------+-----+------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+-----------+------+-----+------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| time | date | YES | | NULL | |
| orderno | char(100) | NO | | NO | |
| ORIGINid | int(11) | YES | | NULL | |
| PRICEid | int(11) | YES | | NULL | |
| PAYMENTid | int(11) | YES | | NULL | |
| DELIVERY | text | YES | | NULL | |
| memo | text | YES | | NULL | |
| consignee | text | YES | | NULL | |
| changeprice | text | YES | | NULL | |
| discount | text | YES | | NULL | |
| total | text | YES | | NULL | |
| LANGUAGEid | int(11) | YES | | NULL | |
| packing_charge | text | YES | | NULL | |
| freight_charges | text | YES | | NULL | |
| delivery_charge | text | YES | | NULL | |
| delivery_place | text | YES | | NULL | |
| disc | text | YES | | NULL | |
| shipping | text | YES | | NULL | |
| packing | text | YES | | NULL | |
| packing_info | text | YES | | NULL | |
| post | char(100) | NO | | NO | |
| posttime | date | NO | | 2011-00-00 | |
| paytime | date | NO | | 0000-00-00 | |
| status | int(11) | NO | | 0 | |
| arrivaltime | date | NO | | 2011-00-00 | |
| arrivalmemo | text | YES | | NULL | |
| size | text | YES | | NULL | |
| weight | text | YES | | NULL | |
+-----------------+-----------+------+-----+------------+----------------+
29 rows in set (1.16 sec)
mysql> ALTER TABLE order1 CHANGE orderno orderno char(100) NOT NULL DEFAULT 'NO'
;
Query OK, 12383 rows affected (1.13 sec)
Records: 12383 Duplicates: 0 Warnings: 0
注意:要先把NULL的orderno都写入NO!
mysql> UPDATE order1 SET orderno = 'NO' WHERE orderno IS NULL;
Query OK, 10 rows affected (0.16 sec)
Rows matched: 10 Changed: 10 Warnings: 0
mysql> select id, orderno from order1 where length(orderno) > 20;
+------+-----------------------+
| id | orderno |
+------+-----------------------+
| 2049 | 330-08,UIR28557-12-08 |
| 7036 | FQN-SP-002.005.006-10 |
+------+-----------------------+
2 rows in set (0.01 sec)
mysql> select id, orderno from order1 where length(orderno) > 30;
Empty set (0.03 sec)
mysql> ALTER TABLE order1 CHANGE orderno orderno char(100) NOT NULL DEFAULT 'NO'
;
ERROR 1265 (01000): Data truncated for column 'orderno' at row 12373
mysql> ALTER TABLE order1 MODIFY orderno TEXT NOT NULL DEFAULT 'NO';
ERROR 1101 (42000): BLOB/TEXT column 'orderno' can't have a default value
mysql> show columns from order1;
+-----------------+-----------+------+-----+------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+-----------+------+-----+------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| time | date | YES | | NULL | |
| orderno | text | YES | | NULL | |
| ORIGINid | int(11) | YES | | NULL | |
| PRICEid | int(11) | YES | | NULL | |
| PAYMENTid | int(11) | YES | | NULL | |
| DELIVERY | text | YES | | NULL | |
| memo | text | YES | | NULL | |
| consignee | text | YES | | NULL | |
| changeprice | text | YES | | NULL | |
| discount | text | YES | | NULL | |
| total | text | YES | | NULL | |
| LANGUAGEid | int(11) | YES | | NULL | |
| packing_charge | text | YES | | NULL | |
| freight_charges | text | YES | | NULL | |
| delivery_charge | text | YES | | NULL | |
| delivery_place | text | YES | | NULL | |
| disc | text | YES | | NULL | |
| shipping | text | YES | | NULL | |
| packing | text | YES | | NULL | |
| packing_info | text | YES | | NULL | |
| post | char(100) | NO | | NO | |
| posttime | date | NO | | 2011-00-00 | |
| paytime | date | NO | | 0000-00-00 | |
| status | int(11) | NO | | 0 | |
| arrivaltime | date | NO | | 2011-00-00 | |
| arrivalmemo | text | YES | | NULL | |
| size | text | YES | | NULL | |
| weight | text | YES | | NULL | |
+-----------------+-----------+------+-----+------------+----------------+
29 rows in set (0.02 sec)
]]>
<![CDATA[
[Sun Sep 12 12:42:52 2010] [error] [client 127.0.0.1] Use of uninitialized value in array dereference at ./pro/mscorder1.pl line 366, <CONFIG> line 11.,
# 输入价格表格
$$pref{id} = $t{order1_id};
($pref,$self) = show_parts3($pref,$self);
366: @loop1 = @{ $$pref{list} };
修改如下(该修改失败!):
if (@{ $$pref{list} })
@loop1 = @{ $$pref{list} };
} else {
@loop1 = ();
}
增加如下(该修改成功!):
sub show_parts3 {
my ($pref,$self) = @_;
my(%t,$n,$n1,%seen,$item);
@{ $$pref{list} } = ();
[Sun Sep 12 12:53:39 2010] [error] [client 127.0.0.1] Argument "P2" isn't numeric in multiplication (*) at ./pro/mscorder1.pl line 652, <CONFIG> line 11., referer: http://localhost/cgi-bin/msc_start.cgi
[Sun Sep 12 12:53:39 2010] [error] [client 127.0.0.1] Use of uninitialized value in multiplication (*) at ./pro/mscorder1.pl line 652, <CONFIG> line 11., referer: http://localhost/cgi-bin/msc_start.cgi
652: $t{price2} = $t{prices}[$t{NO}-1]*$t{Q1};
修改如下:
if ( $t{price1} && $t{price1} ne 'P2') {
$t{price2} = $t{prices}[$t{NO}-1]*$t{Q1};
} else {
$t{price2} = 0;
}
----------------------------------------------------------------
$t{content} = $t{q}->param("content");
$t{content} =~ s/\s+$//; # 把后面的空格去掉
]]>
返回