MySQL操作程序三十九(Order summary网页程序3,整理3)
返回
作业内容
- enq2的迄今内容整理
全部已付钱的status设定置换程序
- order1的迄今内容整理
全部已付钱的status设定置换程序
- order1画面同时多个OURREF的post输入
- enq2的status变更操作
enq2删除时,status恢复为零
- enq2,time,支払時期
输入"2011-00-00"时,未收货的置status为0
输入其他"支払時期"时,未收货的置status为1
输入其他"支払時期"时,已收货的置status为3
- enq2,post,入荷確認
输入'NO'时,时间是"2011-00-00"的置status为0
输入'NO'时,其他时间置status为1
输入'NO'以外时,已付钱的置status为3
输入'NO'以外时,未付钱的置status为2
- ******************************************************************
- enq2的time的default改为"2011-00-00"
有关改写时间停止,mscenq1_start.pl,mscquo2.pl
==>enq的时间写入取消(已有缺省值)
- enq2增加收货状态确认按钮,mscqtn_input.htm,mscqtn_input.pl
- 开始画面不显示'NO',mscenq1_start.pl
- order1的status变更操作,mscorder1.htm
输入客户ORDER号码时,置status为1
输入OURREF号码时,置status为2
输入"入金時期"时,已发货的置status为5
输入"入金時期"时,未发货的置status为3
输入"納入先/時期"时,未收货款的置status为4
输入"納入先/時期"时,已收货款的置status为5
- enq2增加项目时,插入0=>意义不大,已自动赋零
mscquo2.pl,複数仕入先
- order1增加项目时,插入0,mscenq1_start.pl=>意义不大,已自动赋零
- order1增加status, INT, 0=>没有ORDER; 1=>ORDER开始,2=>和其他ORDER合并,3=>已收到货款,4=>已发货,4=>全部完成
- enq2增加status, INT, 0=>没有ORDER; 1=>ORDER开始,2=>已付货款,3=>已收到货物
<![CDATA[
mysql> ALTER TABLE enq2 CHANGE time time DATE DEFAULT '2011-00-00';
Query OK, 13929 rows affected (2.63 sec)
Records: 13929 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE enq2 ADD post CHAR(100) NOT NULL DEFAULT 'NO';
Query OK, 13929 rows affected (2.08 sec)
Records: 13929 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 | | NULL | |
| 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 | |
+------------+-----------+------+-----+---------+----------------+
23 rows in 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 | 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 | |
| paytime | date | NO | | 0000-00-00 | |
| status | int(11) | NO | | 0 | |
+-----------------+-----------+------+-----+------------+----------------+
24 rows in set (0.05 sec)
mysql> ALTER TABLE order1 CHANGE post post CHAR(100) NOT NULL DEFAULT 'NO';
Query OK, 12380 rows affected (0.75 sec)
Records: 12380 Duplicates: 0 Warnings: 0
mysql> UPDATE order1 SET post = 'NO' where post IS NULL;
Query OK, 8 rows affected (0.16 sec)
Rows matched: 8 Changed: 8 Warnings: 0
mysql> select post from order1 where id = 12373;
+------+
| post |
+------+
| NULL |
+------+
1 row in set (0.06 sec)
mysql> ALTER TABLE order1 CHANGE post post CHAR(100) NOT NULL DEFAULT 'NO';
ERROR 1265 (01000): Data truncated for column 'post' at row 12373
mysql> ALTER TABLE order1 CHANGE post post TEXT NOT NULL DEFAULT 'NO';
ERROR 1101 (42000): BLOB/TEXT column 'post' can't have a default value
mysql> select id post from order1 where post is NULL;
+-------+
| post |
+-------+
| 12373 |
| 12374 |
| 12375 |
| 12376 |
| 12377 |
| 12378 |
| 12379 |
| 12380 |
+-------+
8 rows in set (0.03 sec)
# statusを更新
# 输入是否是OURREF的确认
# 形成HASH
my %member;
my $sth = $self->dbh->prepare("SELECT ourref FROM enq1");
$sth->execute ();
while (my ($val) = $sth->fetchrow_array())
{
$member{$val} = 1;
}
# 然后使用
$t{valid} = exists ($member{$t{content}});
if ( $t{valid} == 1 ) {
$t{sql} = 'UPDATE order1 set status = 2 where id = ';
$t{sql} .= $t{order1_id};
$t{DO} = $self->dbh->do($t{sql});
} else {
$t{sql} = 'UPDATE order1 set status = 1 where id = ';
$t{sql} .= $t{order1_id};
$t{DO} = $self->dbh->do($t{sql});
}
} elsif ( $t{item} eq 'paytime' ) { # status
$t{post} = $self->dbh->selectrow_array("SELECT post FROM order1 WHERE id = $t{order1_id} and post is NOT NULL");
if ( $t{post} ne 'NO' && $t{content} ne '2010-00-00' ) {
$t{sql} = 'UPDATE order1 set status = 5 where id = ';
$t{sql} .= $t{order1_id};
$t{DO} = $self->dbh->do($t{sql});
} elsif ( $t{content} eq '2010-00-00' ) {
$t{sql} = 'UPDATE order1 set status = 1 where id = ';
$t{sql} .= $t{order1_id};
$t{DO} = $self->dbh->do($t{sql});
} else {
$t{sql} = 'UPDATE order1 set status = 3 where id = ';
$t{sql} .= $t{order1_id};
$t{DO} = $self->dbh->do($t{sql});
}
} elsif ( $t{item} eq 'post' ) { # status
$t{paytime} = $self->dbh->selectrow_array("SELECT paytime FROM order1 WHERE id = $t{order1_id}");
if ( $t{paytime} eq '2010-00-00' ) {
$t{sql} = 'UPDATE order1 set status = 4 where id = ';
$t{sql} .= $t{order1_id};
$t{DO} = $self->dbh->do($t{sql});
} elsif ( $t{content} eq 'NO' ) {
$t{sql} = 'UPDATE order1 set status = 1 where id = ';
$t{sql} .= $t{order1_id};
$t{DO} = $self->dbh->do($t{sql});
} else {
$t{sql} = 'UPDATE order1 set status = 5 where id = ';
$t{sql} .= $t{order1_id};
$t{DO} = $self->dbh->do($t{sql});
}
}
******************************************************************************
mysql> show columns from enq2;
+------------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| time | date | YES | | NULL | |
| 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 | |
+------------+---------+------+-----+---------+----------------+
21 rows in set (0.24 sec)
mysql> ALTER TABLE enq2 ADD status INT NOT NULL DEFAULT '0';
Query OK, 13921 rows affected (0.88 sec)
Records: 13921 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 | | NULL | |
| 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 | |
+------------+---------+------+-----+---------+----------------+
22 rows in set (0.01 sec)
]]>
<![CDATA[
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 | text | YES | | NULL | |
| paytime | date | NO | | 0000-00-00 | |
+-----------------+---------+------+-----+------------+----------------+
23 rows in set (0.84 sec)
mysql> ALTER TABLE order1 ADD status INT NOT NULL DEFAULT '0';
Query OK, 12374 rows affected (2.00 sec)
Records: 12374 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 | 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 | text | YES | | NULL | |
| paytime | date | NO | | 0000-00-00 | |
| status | int(11) | NO | | 0 | |
+-----------------+---------+------+-----+------------+----------------+
24 rows in set (0.14 sec)
]]>
返回